In the past, I had been able to connect Base to my local mysql databases using the integrated SQL connector in Base, and it was easy with the integrated SQL connector. Then later it became impossible. As it was not critical for my work, I just thought it would come back… but I think I have not been able to get this connection working for a couple of years now. I do have some bases for which I need a SQL version to use them online. The workaround I have is to use a standard LO Base document and transfer data from Base to SQL using an intermediate calc file connected to LO Base database. I tried the different kind of connections, including JDB connector (the class is working ok) and direct connection, but the connection is systematically refused. Whatever I do, I get the error «Could not connect to localhost:3306 : (Connection refused). Direct SQL connection returns this message : Code d’erreur: 2003 Can't connect to MySQL server on 'localhost' (111) For the mysql installation, I am using xampp – which is launched at start-up. I can connect normally to localhost from my internet browser, as well as from the command line (mysql). My system uses MariaDB version 10.1.37, on Ubuntu 18.04. Any indication on why it does not work for me would be appreciated.
(In reply to Silvain Dupertuis from comment #0) > Whatever I do, I get the error «Could not connect to localhost:3306 : > (Connection refused). This looks like your system refuses the connection. Firewall? Is the user you are using in the *.odb-file, allowed to connect on localhost?
The 111 error code is indicative of a configuration /access problem with your mysql server, and not LO. Instead of "localhost", try using "127.0.0.1" as the IP address to which to connect. One thing that is not entirely clear to me is whether you are trying to access a remote mysql server with the wrong address, as you mention both XAMPP and MariaDB in following sentences. If you are trying to access an XAMPP instance from your Ubuntu machine then you need to configure my.cnf on the XAMPP machine to allow general access from any IP address (or limited to your LAN pool of IP addresses). Doesn't actually seem like a LibreOffice problem to me.
Thanks for answering! I hope you can help me find out why I can't connect. Here are the details on my situation: I am trying to access the local mysql instance on my local computer, not on a LAN nor a remote server on the internet. Using 127.0.0.1 in Base returns the same error (111) as using localhost. Using mysql socket (/opt/lampp/var/mysql/mysql.sock) instead of server/port returns the same error (111) I do not understand what to configure in my.cfg file, as I can access my mysql server - using my browser (using url localhost or 127.0.0.1) - using mysql from the command line I mentionned MariaDB because I am usig a XAMPP server, and it uses mariadb On the command line, I just call it "mysql" phpmyadmin indicates the following: - Server : Localhost via UNIX socket - Type of server : MariaDB - Version of server : 10.1.37-MariaDB - Utilisateur : root@localhost Also, I do not understad why it stopped working at a certain point As far as I remember, I have been using xampp for a number of years. As for the my.cfg file, the [mysqld] part indicates : # The MySQL server [mysqld] user = mysql port=3306 socket = /opt/lampp/var/mysql/mysql.sock skip-external-locking
There you have your answer : - Server : Localhost via UNIX socket socket = /opt/lampp/var/mysql/mysql.sock Your server is only providing the Unix socket connection. The fact that you can access it via your web browser using 127.0.0.1 or localhost is down to the way your phpmyadmin instance is configured, which redirects the call to the Unix socket. The msyql connection setup wizard used to allow you to specify the path to the Unix socket (this is not the same as the URL using an IP address), but I believe that this is now hardcoded to some usual default of a normally installed Linux mysql/MariaDB server, i.e. /var/local/tmp/ or some such directory, so: - either you create a symbolic link between your actual mysql Unix socket and the place where LO expects it to be (with appropriate privileges), or you reconfigure your server to create a Unix socket in the place where LO expects to find it. - or your reconfigure your mariadb server to use TCP/IP (see the bind-address entry) instead of acessing the Unix socket - this is generally done by editing the my/cnf file, but beware of the safety considerations in doing so (especially if you have access from outside your network into your LAN.
(In reply to Alex Thurgood from comment #4) > There you have your answer : > > - Server : Localhost via UNIX socket > > socket = /opt/lampp/var/mysql/mysql.sock > > Your server is only providing the Unix socket connection. > > The fact that you can access it via your web browser using 127.0.0.1 or > localhost is down to the way your phpmyadmin instance is configured, which > redirects the call to the Unix socket. > > > The msyql connection setup wizard used to allow you to specify the path to > the Unix socket (this is not the same as the URL using an IP address), but I > believe that this is now hardcoded to some usual default of a normally > installed Linux mysql/MariaDB server, i.e. /var/local/tmp/ or some such > directory, so: > > - either you create a symbolic link between your actual mysql Unix socket > and the place where LO expects it to be (with appropriate privileges), or > you reconfigure your server to create a Unix socket in the place where LO > expects to find it. > > - or your reconfigure your mariadb server to use TCP/IP (see the > bind-address entry) instead of acessing the Unix socket - this is generally > done by editing the my/cnf file, but beware of the safety considerations in > doing so (especially if you have access from outside your network into your > LAN. You'll also need to restart your mariadb server once you've made the change to the my.cnf file.
@Sylvain - have you solved your problem ?
I have the same problem of Silvain. You says to do a symlink from the lampp socket (in /opt) to "the place where LO expects to find it". But can you kindly tell me where is this place?
I have tried different combinations with no success so far, then I did not have the time to do more tests, and no time to give more details just now. On another computer, I installed both xampp and mysql. Both work independanty, LO Base can connect to mysql, but I did not manage so far to to connect Base to the xampp databases. I tried using symlinks to use the same databases for both, but it did not work. If I find my way, I will post a comment.
(In reply to Silvain Dupertuis from comment #8) > I have tried different combinations with no success so far, then I did not > have the time to do more tests, and no time to give more details just now. Try it with JDBC, not with the native connector. Works better. You could create views for example ...
*** Bug 136290 has been marked as a duplicate of this bug. ***
but install jdbc is very complicated. You know if there is a guide to install it?
To see where should point symlink of mysql soceket, I'm trying to install mysql server on KDE Neon, but both in a fresh installation in Virtualbox and on my main PC I have a problem of held packages and conflit of dependecies. Why developers have complicated so much our lives? (maybe the covid, I guess... I am grateful for their work, any way)
Not sure whether this is going to be of any use, BUT, it might be that you can no longer specify a link to a Unix socket... Reading this : https://docs.libreoffice.org/connectivity/html/mysqlc__connection_8cxx_source.html the code at lines 76-86 76 // use TCP as connection 77 mysql_protocol_type protocol = MYSQL_PROTOCOL_TCP; 78 mysql_options(&m_mysql, MYSQL_OPT_PROTOCOL, &protocol); 79 OString charset_name{ "utf8mb4" }; 80 mysql_options(&m_mysql, MYSQL_SET_CHARSET_NAME, charset_name.getStr()); 81 82 sal_Int32 nIndex; 83 OUString token; 84 OUString aHostName("localhost"); 85 sal_Int32 nPort = 3306; 86 OUString aDbName; would seem to indicate that one can only use a TCP connection (and therefore not a pure Unix socket) with the native driver code to connect to a running mysql instance. I seem to recall that this was not the case in the past and one could previously specify a Unix socket path in the UI wizard configuration of the native connector driver extension. It would appear that that possibility has been removed, probably during UI rework of the connection wizard (guessing here). If this is indeed the case, then the only solution for people like Silvain and Duns, etc, would be to edit their mysql configuration file and change the connection type to explicitly allow TCP connections. See my comment 4 here: "- or your reconfigure your mariadb server to use TCP/IP (see the bind-address entry) instead of acessing the Unix socket - this is generally done by editing the my/cnf file, but beware of the safety considerations in doing so (especially if you have access from outside your network into your LAN."
Also note however, that the code in lines 124-174 of mysql_connection.cxx seems to imply that a Unix sokcet, or even a named pipe, can be used as a connection paramater: // get user and password for mysql connection 125 const PropertyValue* pIter = info.getConstArray(); 126 const PropertyValue* pEnd = pIter + info.getLength(); 127 OUString aUser, aPass, sUnixSocket, sNamedPipe; 128 bool unixSocketPassed = false; 129 bool namedPipePassed = false; 130 131 m_settings.connectionURL = url; 132 for (; pIter != pEnd; ++pIter) 133 { 134 if (pIter->Name == "user") 135 { 136 OSL_VERIFY(pIter->Value >>= aUser); 137 } 138 else if (pIter->Name == "password") 139 { 140 OSL_VERIFY(pIter->Value >>= aPass); 141 } 142 else if (pIter->Name == "LocalSocket") 143 { 144 OSL_VERIFY(pIter->Value >>= sUnixSocket); 145 unixSocketPassed = !sUnixSocket.isEmpty(); 146 } 147 else if (pIter->Name == "NamedPipe") 148 { 149 OSL_VERIFY(pIter->Value >>= sNamedPipe); 150 namedPipePassed = !sNamedPipe.isEmpty(); 151 } 152 else if (pIter->Name == "PublicConnectionURL") 153 { 154 OSL_VERIFY(pIter->Value >>= m_settings.connectionURL); 155 } 156 else if (pIter->Name == "NewURL") 157 { // legacy name for "PublicConnectionURL" 158 OSL_VERIFY(pIter->Value >>= m_settings.connectionURL); 159 } 160 } 161 162 OString host_str = OUStringToOString(aHostName, m_settings.encoding); 163 OString user_str = OUStringToOString(aUser, m_settings.encoding); 164 OString pass_str = OUStringToOString(aPass, m_settings.encoding); 165 OString schema_str = OUStringToOString(aDbName, m_settings.encoding); 166 OString socket_str; 167 if (unixSocketPassed) 168 { 169 socket_str = OUStringToOString(sUnixSocket, m_settings.encoding); 170 } 171 else if (namedPipePassed) 172 { 173 socket_str = OUStringToOString(sNamedPipe, m_settings.encoding); 174 } As I'm not familiar with which part takes priority or why, I can't help out here, this would require someone more knowledgeable about the code in the connector. @Lionel : any insights you might be able to offer ?
Thank you, @Alex: «allow TCP connections». But how should I do? Searching with Google so far I didn't find a clear and complete answer...
To sum up I think that fixing this issue should be made by LibreOffice developers: either go back to the previous relase of mysql-connector or (better) developing a new release *working*. A not working app is useless, isn't it? Ask users to waste their time in useless attempts is not the best. If the solution requires time, we will wait. But, please, do something to fix this problem. Thanks!
(In reply to Duns from comment #16) > To sum up I think that fixing this issue should be made by LibreOffice > developers: either go back to the previous relase of mysql-connector or > (better) developing a new release *working*. A not working app is useless, > isn't it? You are assuming that the issue is a LibreOffice one, and not an issue caused by packaging and/or library version decisions made by Ubuntu... Having just gone through the native connector setup dialog again on macOS this morning and yesterday on Linux Ubuntu 20.04, when you set up the database, you get to indicate the path of the of the socket. If the entry you type here isn't registered for some reason, that would be a bug in that dialog, or the parsing of information by LO which handled by that dialog. The alternative suggestion to use TCP connectivity instead of Unix socket, relies on you knowing how to activate TCP connections in your mysql server instance, by modifying the relevant configuration file. That information can be found all over the internet, and has absolutely nothing to do with LibreOffice. By default these days, current versions of mysql server provided on Linux platforms do not allow TCP access via Class C IP addresses. Again, this is nothing to do with LibreOffice, but decisions made by the packager and provider of the mysql server. Telling the LibreOffice project that they have to fix a bug which is as yet undetermined to be within the LO project's remit, is a bit like saying "I can't fly to the moon, it's your fault, fix it for me." For all we know, this might indeed be a LO problem, then again, it might not. I have yet to see any information from any of the posters complaining about the problem, that they have attempted to at least try and solve it by themselves, and report back here what exactly they have done. For starters, one might try installing standard Linux distribution provided mysql server packages rather than some weird separate bundle that someone thought might conveniently go in /opt ? Where did you get that XAMPP bundle from ? How is the server configured in that bundle ? How can you get it to accept TCP connections over IP ? All of these questions are the responsibility of the person who chooses to install that particular bundle, and have zero/zilch/nada to do with LO. Once you know the answer to these questions, how the XAMPP bundle is supposed to function, the attempts you have tried to resolve the problem, and still come up with a failure of LO to connect, then we can start trying to see where there might be an issue with LO.
I see. Thank you. I just add that I have beforehand reported this bug on Ubuntu bugs (https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1892635). I will wait and do some other attempt, but, as I said, so far I was not able to install mysql server from ubuntu packages (held packages issue). I will ask XAMPP developers if they can help.
I. tested in Linux Mint (fresh installation yesterday in virtualbox). 1) installed mysql-server but a deal of problems because of password. Disintalled! 2) Installed lampp: I get another kind of error in LibreBase when I try a direct connection: Error code: 1698 Access denied for user 'root'@'localhost' II. reported the problem in apachefriends forum: https://community.apachefriends.org/f/viewtopic.php?f=17&t=79847&e=0
(In reply to Duns from comment #19) > > Error code: 1698 > Access denied for user 'root'@'localhost' This is depending on your installation. I don't think you really want to connect to the MySQL database as root of a Linux system. You will create a user with a password, who should connect to the database. And you have to allow this user a connection from localhost, something like SET PASSWORD FOR testuser@localhost=PASSWORD('.....');
But here the point is not security: the point is that in Linux Mint as well Libre-mysql-connector doesn't work, even user root (I'm not sure if "mysql root" is the same as "system user root") is recognized by mysql server and by phpmyadmin, f.e. Any way, I tried with other name: same failure.
(In reply to Duns from comment #21) > But here the point is not security: the point is that in Linux Mint as well > Libre-mysql-connector doesn't work, even user root (I'm not sure if "mysql > root" is the same as "system user root") is recognized by mysql server and > by phpmyadmin, f.e. And with phpmyadmin you could see in "users" the user root (root is the same as root of the system!) is connected to localhost?
I think so: Server: Localhost via UNIX socket Server type: MariaDB Server connection: SSL is not being used Documentation Server version: 10.4.14-MariaDB - Source distribution Protocol version: 10 User: root@localhost Server charset: cp1252 West European (latin1)
(In reply to Robert Großkopf from comment #22) > And with phpmyadmin you could see in "users" the user root (root is the same > as root of the system!) is connected to localhost? @Robert, forget it, Linux Mint uses the DEB packages provided by Ubuntu, and these are currently configured by default to only allow mysql/mariadb root account access from a process that is already running as root using a unix socket authentication plugin. This is a security "feature" of the DEB packages provided by those distros. https://stackoverflow.com/questions/30815971/mariadb-installed-without-password-prompt/34674254#34674254 I am closing this report as NOTOURBUG. Both Sylvain and Duns need to learn to configure their mariadb/mysql server with the relevant authentication plugin for each user and assign them a corresponding password, and then try and connect from LO with a supported password authentication mechanism before filing a report here. This bugzilla isn't the place for general exchanges on configuring mysql/mariab, and only muddles any real issue that might be present.
FWIW, testing with : Version: 7.0.1.2 Build ID: 88a63c56098013eb4038e11ebe7c8c0daab09aa8 CPU threads: 1; OS: Linux 4.15; UI render: default; VCL: gtk3 Locale: fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR Calc: threaded accessing a mariadb server 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 installed on DISTRIB_ID=LinuxMint DISTRIB_RELEASE=19 DISTRIB_CODENAME=tara DISTRIB_DESCRIPTION="Linux Mint 19 Tara" works just fine.
(In reply to Alex Thurgood from comment #25) > FWIW, testing with : > > Version: 7.0.1.2 > Build ID: 88a63c56098013eb4038e11ebe7c8c0daab09aa8 > CPU threads: 1; OS: Linux 4.15; UI render: default; VCL: gtk3 > Locale: fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR > Calc: threaded > > accessing a mariadb server > 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 > > installed on > DISTRIB_ID=LinuxMint > DISTRIB_RELEASE=19 > DISTRIB_CODENAME=tara > DISTRIB_DESCRIPTION="Linux Mint 19 Tara" > > works just fine. FWIW, using the native mysql connector.
Today I managed to fix this problem: commenting the line skip-networking in my.conf (using lampp, therefore in /opt/lampp/etc). LO Version: 6.4.6.2 Build ID: 1:6.4.6-0ubuntu0.20.04.1 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: kf5; Locale: it-IT (en_US.UTF-8); UI-Language: en-US Calc: threaded
Created attachment 170575 [details] Fix MySQL UNIX socket connector This patch fixes the problem for me. With this applied, LibreOffice Base can connect securely - without a need for a password - to MySQL using /var/.../mysql.sock UNIX socket, instead of using (insecure) TCP on localhost or going through the complicated JDBC case. This patch is thus compatible with MySQL's "skip-networking". I haven't tested the MYSQL_PROTOCOL_PIPE case.
Guess I need to reopen this bug to propagate the fix upstream.
(In reply to Krzysztof Hałasa from comment #28) > Created attachment 170575 [details] > Fix MySQL UNIX socket connector > > This patch fixes the problem for me. With this applied, LibreOffice Base can > connect securely - without a need for a password - to MySQL using > /var/.../mysql.sock UNIX socket, instead of using (insecure) TCP on > localhost or going through the complicated JDBC case. This patch is thus > compatible with MySQL's "skip-networking". > > I haven't tested the MYSQL_PROTOCOL_PIPE case. I can't tell if it's ok or not but since you already analyzed the code and thought about a fix, would you be interested in contributing to LO by submitting the patch? (see https://wiki.documentfoundation.org/Development/GetInvolved)
Hello Julien, (In reply to Julien Nabet from comment #30) > I can't tell if it's ok or not but since you already analyzed the code and > thought about a fix, would you be interested in contributing to LO by > submitting the patch? (see > https://wiki.documentfoundation.org/Development/GetInvolved) Well, the above seems very, very, very complicated and, most certainly, very time-consuming. A lot of effort for such a trivial patch. Isn't there a simpler way? I don't even know if the patch still applies cleanly to current code, after all this time, while such things are trivial for someone working with right git trees etc. Merry Christmas, and a better New Year :-)
(In reply to Krzysztof Hałasa from comment #31) > Hello Julien, > > (In reply to Julien Nabet from comment #30) > > > I can't tell if it's ok or not but since you already analyzed the code and > > thought about a fix, would you be interested in contributing to LO by > > submitting the patch? (see > > https://wiki.documentfoundation.org/Development/GetInvolved) > > Well, the above seems very, very, very complicated and, most certainly, very > time-consuming. A lot of effort for such a trivial patch. Isn't there a > simpler way? >... Ok but since I'm not familiar to submit a patch from someone else because even if there's the command "git commit --author="xxx <xxx@email.com>" -m "patch from xxx"", I got 2 pbs: 1) what email to put 2) there's no license statement from you (see https://wiki.documentfoundation.org/Development/GetInvolved#License_statement) Anyway, not much people complain about this pb so if there's some workaround, no big deal.
(In reply to Julien Nabet from comment #32) > Ok but since I'm not familiar to submit a patch from someone else because > even if there's the command "git commit --author="xxx <xxx@email.com>" -m > "patch from xxx"", I got 2 pbs: > 1) what email to put Right, Krzysztof Hałasa <khalasa@piap.pl>, or in pure ASCII, Krzysztof Halasa. Or you can submit it under your own name, it's so trivial so there is no difference for me. > 2) there's no license statement from you (see > https://wiki.documentfoundation.org/Development/ > GetInvolved#License_statement) Sure: All of my past & future contributions to LibreOffice may be licensed under the MPLv2/LGPLv3+ dual license. Thanks for handling this patch.
Thank you Krzysztof Hałasa for your feedback! Patch submitted here: https://gerrit.libreoffice.org/c/core/+/127673
Krzysztof Hałasa committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/0143ec4d3d56eeee7edf16371c1d3c3981890b71 tdf#134982: use the right protocol for Mysql/Mariadb It will be available in 7.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Krzysztof Hałasa committed a patch related to this issue. It has been pushed to "libreoffice-7-3": https://git.libreoffice.org/core/commit/0c5e0dcd19403f967bc1ae46ebb8649b27b29b86 tdf#134982: use the right protocol for Mysql/Mariadb It will be available in 7.3.0.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.