Connecting libreoffice to MySQL/MariaDB via JDBC

classic Classic list List threaded Threaded
8 messages Options
odfman odfman
Reply | Threaded
Open this post in threaded view
|

Connecting libreoffice to MySQL/MariaDB via JDBC

I am trying to connect Libreoffice to MariaDB databases.

I am using the following configuration
- Linux Mint 19.2,
- MariaDB - Server version: 10.4.6-MariaDB and have connected successfully
via the mysql command line, and via PHPMyAdmin to create databases, tables
and to successfully search these tables.
- LibreOffice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.10
 - Java version openjdk 11.0.7 2020-04-14
OpenJDK Runtime Environment (build 11.0.7+10-post-Ubuntu-2ubuntu218.04)
OpenJDK 64-Bit Server VM (build 11.0.7+10-post-Ubuntu-2ubuntu218.04, mixed
mode, sharing)
- the JDBC connectors from either MySQL or MariaDB.  These have included
from MySQL connector versions: 5.0.3, 5.1.36, 5.1.49 and MariaDB connector
versions: 1.5.9, 2.5.4, and 2.6.1.  When these are configured in the Java
CLASSPATH, these drivers are loaded successfully.

Issues arise when attempting to create a new database within Libreoffice.
When I test connecting to a database, I get the following error message:
SQL Status: 08S01
Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The
driver has not received any packets from the server.

I am not sure what to test/check next.  I have spent quite a while
searching for answers to this issue but have not yet turned up a solution.
Is anyone connecting to MySQL/MariaDB databases using Linux and JDBC
successfully?

cheers
Mike
email: [hidden email]

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: Connecting libreoffice to MySQL/MariaDB via JDBC

Hi Mike,
>
> Issues arise when attempting to create a new database within Libreoffice.

Do you try to create a MySQL/MariaDB database from LO Base?
You could only connect to a database with LO Base, not create a new
database. You need a MySQL or MariaDB database and could create tables
in this database.

Regards

Robert
--
Homepage: https://www.familiegrosskopf.de/robert


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: Connecting libreoffice to MySQL/MariaDB via JDBC

In reply to this post by odfman
Le 27/07/2020 à 09:06, Michael Manning a écrit :

Hi Michael,

>
> Issues arise when attempting to create a new database within Libreoffice.
> When I test connecting to a database, I get the following error message:
> SQL Status: 08S01
> Communications link failure
> The last packet sent successfully to the server was 0 milliseconds ago. The
> driver has not received any packets from the server.
>

As Robert has indicated, could you be clearer on what you mean by
"creating a new database within LibreOffice ?"

Are you:
(1) trying to create a new database schema from within a running LO
instance ? As Robert has mentioned, you can't actually do this, the DB
schema needs to be created from the msyql command line or an alternative
GUI tool (such as e.g. phpmyadmin) ;

(2) trying to connect to an existing mysql DB schema, and receiving the
error message you have indicated ?


The error message itself might be slightly misleading, as this is
usually displayed when you can not even connect to the mariadb/mysql
instance.

What does your /etc/mysql/my.cnf or /etc/my.cnf contain next to :

bind-address
and
skip-networking




Alex


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
odfman odfman
Reply | Threaded
Open this post in threaded view
|

Re: Connecting libreoffice to MySQL/MariaDB via JDBC

Thank you for your responses.
I am testing using a database with a single table created with the mysql
command line client.  This table has 3 fields, an auto-incrementing primary
key, and two fields each defined as varchar(50).  The table was defined
using the mysql command line client, and then 3 records were inserted into
the table.  I also used the mysql command line client to ensure that I
could retrieve all 3 records using the command:
select * from test1;

I do not have a /etc/my.cnf file.

The only line that is not a comment in my /etc/mysql/my.cnf file is:
!includedir /etc/mysql/conf.d/

I should also mention that I am using XAMPP for the installation of MySQL
and that this has also installed an apache web server and PHPMyAdmin which
are all working correctly.  Should this make any difference to the way that
LibreOffice connects via the JDBC connector?

PHPMyAdmin reports the server as:

   - Server: Localhost via UNIX socket
   - Server type: MariaDB
   - Server connection: SSL is not being used [image: Documentation]
   <http://localhost/phpmyadmin/doc/html/setup.html#ssl>
   - Server version: 10.4.6-MariaDB - Source distribution
   - Protocol version: 10
   - User: michael@localhost
   - Server charset: cp1252 West European (latin1)

Is this a hint that sockets are being used and not port 3306 that
LibreOffice is expecting?  If so, how should I fix this?
cheers
Mike
email: [hidden email]



On Mon, 27 Jul 2020 at 18:22, Alexander Thurgood <[hidden email]>
wrote:

> Le 27/07/2020 à 09:06, Michael Manning a écrit :
>
> Hi Michael,
>
> >
> > Issues arise when attempting to create a new database within Libreoffice.
> > When I test connecting to a database, I get the following error message:
> > SQL Status: 08S01
> > Communications link failure
> > The last packet sent successfully to the server was 0 milliseconds ago.
> The
> > driver has not received any packets from the server.
> >
>
> As Robert has indicated, could you be clearer on what you mean by
> "creating a new database within LibreOffice ?"
>
> Are you:
> (1) trying to create a new database schema from within a running LO
> instance ? As Robert has mentioned, you can't actually do this, the DB
> schema needs to be created from the msyql command line or an alternative
> GUI tool (such as e.g. phpmyadmin) ;
>
> (2) trying to connect to an existing mysql DB schema, and receiving the
> error message you have indicated ?
>
>
> The error message itself might be slightly misleading, as this is
> usually displayed when you can not even connect to the mariadb/mysql
> instance.
>
> What does your /etc/mysql/my.cnf or /etc/my.cnf contain next to :
>
> bind-address
> and
> skip-networking
>
>
>
>
> Alex
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
odfman odfman
Reply | Threaded
Open this post in threaded view
|

Re: Connecting libreoffice to MySQL/MariaDB via JDBC

The server appears to be running on port 3306 according to this command and
result:

$ mysql -u michael -p --port=3306
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 28
Server version: 10.4.6-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

cheers
Mike
email: [hidden email] | facebook.com/MikeLouise



On Mon, 27 Jul 2020 at 22:33, Michael Manning <[hidden email]>
wrote:

> Thank you for your responses.
> I am testing using a database with a single table created with the mysql
> command line client.  This table has 3 fields, an auto-incrementing primary
> key, and two fields each defined as varchar(50).  The table was defined
> using the mysql command line client, and then 3 records were inserted into
> the table.  I also used the mysql command line client to ensure that I
> could retrieve all 3 records using the command:
> select * from test1;
>
> I do not have a /etc/my.cnf file.
>
> The only line that is not a comment in my /etc/mysql/my.cnf file is:
> !includedir /etc/mysql/conf.d/
>
> I should also mention that I am using XAMPP for the installation of MySQL
> and that this has also installed an apache web server and PHPMyAdmin which
> are all working correctly.  Should this make any difference to the way that
> LibreOffice connects via the JDBC connector?
>
> PHPMyAdmin reports the server as:
>
>    - Server: Localhost via UNIX socket
>    - Server type: MariaDB
>    - Server connection: SSL is not being used [image: Documentation]
>    <http://localhost/phpmyadmin/doc/html/setup.html#ssl>
>    - Server version: 10.4.6-MariaDB - Source distribution
>    - Protocol version: 10
>    - User: michael@localhost
>    - Server charset: cp1252 West European (latin1)
>
> Is this a hint that sockets are being used and not port 3306 that
> LibreOffice is expecting?  If so, how should I fix this?
> cheers
> Mike
> email: [hidden email]
>
>
>
> On Mon, 27 Jul 2020 at 18:22, Alexander Thurgood <[hidden email]>
> wrote:
>
>> Le 27/07/2020 à 09:06, Michael Manning a écrit :
>>
>> Hi Michael,
>>
>> >
>> > Issues arise when attempting to create a new database within
>> Libreoffice.
>> > When I test connecting to a database, I get the following error message:
>> > SQL Status: 08S01
>> > Communications link failure
>> > The last packet sent successfully to the server was 0 milliseconds ago.
>> The
>> > driver has not received any packets from the server.
>> >
>>
>> As Robert has indicated, could you be clearer on what you mean by
>> "creating a new database within LibreOffice ?"
>>
>> Are you:
>> (1) trying to create a new database schema from within a running LO
>> instance ? As Robert has mentioned, you can't actually do this, the DB
>> schema needs to be created from the msyql command line or an alternative
>> GUI tool (such as e.g. phpmyadmin) ;
>>
>> (2) trying to connect to an existing mysql DB schema, and receiving the
>> error message you have indicated ?
>>
>>
>> The error message itself might be slightly misleading, as this is
>> usually displayed when you can not even connect to the mariadb/mysql
>> instance.
>>
>> What does your /etc/mysql/my.cnf or /etc/my.cnf contain next to :
>>
>> bind-address
>> and
>> skip-networking
>>
>>
>>
>>
>> Alex
>>
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
odfman odfman
Reply | Threaded
Open this post in threaded view
|

Re: Connecting libreoffice to MySQL/MariaDB via JDBC

Thank you for your assistance.  Your hints have allowed me to resolve the
problem.

XAMPP stores its my.cnf file in /opt/lampp/etc.  The my.cnf file at that
location had skip-networking uncommented.  Once this had been commented out
and the server restarted I was able define an .odb file connecting to the
test database in MariaDB.  This has now worked perfectly.

Many thanks again.

Mike
email: [hidden email]



On Mon, 27 Jul 2020 at 22:57, Michael Manning <[hidden email]>
wrote:

> The server appears to be running on port 3306 according to this command
> and result:
>
> $ mysql -u michael -p --port=3306
> Enter password:
> Welcome to the MariaDB monitor.  Commands end with ; or \g.
> Your MariaDB connection id is 28
> Server version: 10.4.6-MariaDB Source distribution
>
> Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
>
> cheers
> Mike
> email: [hidden email] | facebook.com/MikeLouise
>
>
>
> On Mon, 27 Jul 2020 at 22:33, Michael Manning <[hidden email]>
> wrote:
>
>> Thank you for your responses.
>> I am testing using a database with a single table created with the mysql
>> command line client.  This table has 3 fields, an auto-incrementing primary
>> key, and two fields each defined as varchar(50).  The table was defined
>> using the mysql command line client, and then 3 records were inserted into
>> the table.  I also used the mysql command line client to ensure that I
>> could retrieve all 3 records using the command:
>> select * from test1;
>>
>> I do not have a /etc/my.cnf file.
>>
>> The only line that is not a comment in my /etc/mysql/my.cnf file is:
>> !includedir /etc/mysql/conf.d/
>>
>> I should also mention that I am using XAMPP for the installation of MySQL
>> and that this has also installed an apache web server and PHPMyAdmin which
>> are all working correctly.  Should this make any difference to the way that
>> LibreOffice connects via the JDBC connector?
>>
>> PHPMyAdmin reports the server as:
>>
>>    - Server: Localhost via UNIX socket
>>    - Server type: MariaDB
>>    - Server connection: SSL is not being used [image: Documentation]
>>    <http://localhost/phpmyadmin/doc/html/setup.html#ssl>
>>    - Server version: 10.4.6-MariaDB - Source distribution
>>    - Protocol version: 10
>>    - User: michael@localhost
>>    - Server charset: cp1252 West European (latin1)
>>
>> Is this a hint that sockets are being used and not port 3306 that
>> LibreOffice is expecting?  If so, how should I fix this?
>> cheers
>> Mike
>> email: [hidden email]
>>
>>
>>
>> On Mon, 27 Jul 2020 at 18:22, Alexander Thurgood <[hidden email]>
>> wrote:
>>
>>> Le 27/07/2020 à 09:06, Michael Manning a écrit :
>>>
>>> Hi Michael,
>>>
>>> >
>>> > Issues arise when attempting to create a new database within
>>> Libreoffice.
>>> > When I test connecting to a database, I get the following error
>>> message:
>>> > SQL Status: 08S01
>>> > Communications link failure
>>> > The last packet sent successfully to the server was 0 milliseconds
>>> ago. The
>>> > driver has not received any packets from the server.
>>> >
>>>
>>> As Robert has indicated, could you be clearer on what you mean by
>>> "creating a new database within LibreOffice ?"
>>>
>>> Are you:
>>> (1) trying to create a new database schema from within a running LO
>>> instance ? As Robert has mentioned, you can't actually do this, the DB
>>> schema needs to be created from the msyql command line or an alternative
>>> GUI tool (such as e.g. phpmyadmin) ;
>>>
>>> (2) trying to connect to an existing mysql DB schema, and receiving the
>>> error message you have indicated ?
>>>
>>>
>>> The error message itself might be slightly misleading, as this is
>>> usually displayed when you can not even connect to the mariadb/mysql
>>> instance.
>>>
>>> What does your /etc/mysql/my.cnf or /etc/my.cnf contain next to :
>>>
>>> bind-address
>>> and
>>> skip-networking
>>>
>>>
>>>
>>>
>>> Alex
>>>
>>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: Connecting libreoffice to MySQL/MariaDB via JDBC

In reply to this post by odfman
Le 27/07/2020 à 14:33, Michael Manning a écrit :

>
> I do not have a /etc/my.cnf file.

Well you have a configuration file somewhere in that XAMPP stack. The
question is where ?

BTW, not quite sure why you are using XAMPP rather than just installing
the distrib-provided  versions of LAMP on the Ubuntu machine ?



>
> The only line that is not a comment in my /etc/mysql/my.cnf file is:
> !includedir /etc/mysql/conf.d/


Have you looked in  /etc/mysql/conf.d/ to see which configuration files
are in there ?


>
> I should also mention that I am using XAMPP for the installation of MySQL
> and that this has also installed an apache web server and PHPMyAdmin which
> are all working correctly.  Should this make any difference to the way that
> LibreOffice connects via the JDBC connector?
>

XAMPP will be running the mysql server instance (and the Apache server
and phpyadmin) using its own particular configuration files.



>    - Server: Localhost via UNIX socket
>    - Server type: MariaDB

> Is this a hint that sockets are being used and not port 3306 that
> LibreOffice is expecting?  If so, how should I fix this?


That message tells you that the server instance is running locally
(localhost) on a Unix socket, accessible via the default port 3306.

In order for it to be available to LO, you need to be able to point to
the socket file in the driver configuration dialog when setting up your
connection from LO. I seem to recall that we have had this exchange
already on the bug report you filed.

If you can't point LO to the Unix socket file (for whatever reason, e.g.
XAMMP is running in a jail, or you don't have access rights to the file,
or the dialog won't recognize any other path than a hardcoded default
(which is perfectly possible), then you will need to find and edit the
configuration file of the mysql server to allow general TCP/IP connections.

Usually this is done by :
- commenting out the line "skip-networking" ;
- adding the keyword bind-address=0.0.0.0 (to allow access from any
TCP/IP address, which is a potential security risk) or else specifying
your local IP LAN mask, to limit access to the instance from the local
network only.



Alex





--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: Connecting libreoffice to MySQL/MariaDB via JDBC

In reply to this post by odfman
Le 27/07/2020 à 15:21, Michael Manning a écrit :
> Thank you for your assistance.  Your hints have allowed me to resolve the
> problem.
>

Great to hear that you managed to solve the problem. Can you close the
bug report you opened as well please (if you haven't done so already) ?
Thanks !

Alex




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy