Bug 88796 - Enhancement - Mysql native connector extension - add automatic reconnect to server capability in connector code after idle timeout
Summary: Enhancement - Mysql native connector extension - add automatic reconnect to s...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Extensions (show other bugs)
Version:
(earliest affected)
4.3.6.1 rc
Hardware: Other All
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: difficultyBeginner, easyHack, skillCpp, skillSql
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2015-01-25 23:15 UTC by hc.stoellinger
Modified: 2018-10-10 21:49 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description hc.stoellinger 2015-01-25 23:15:58 UTC
Problem description: 
When using a MySQL database on a server with a short "inactivity timeout"-value (in my present case this is 1 minute), the server goes away if the LO-Base client is idle for longer than this value. 
Now, I have experienced THREE situations in this case:
(1) Using a JDBC-connection - when the server goes away after an idle time of more than one minute, a message box is displayed. Pressing "ok" will reconnect to the server if the LO-Base-connection specifies "autoReconnect=true". Since this situation may occur rather often in the course of normal work, it would make sense to reconnect automatically without the necessity of intervention by the enduser, since this is obviously the intent of the enduser.
(2) When using a command-line MySQL client (mysql command under Linux Mint 17.1 in my case) the situation described as desirable in (1) DOES in fact occur automatically! 
(3) Using the native MySQL-connector, LO-Base displays a message "Server has gone away" (and goes away "for good", meaning that LO has to be restarted. This makes the native connector unusable. In my view the native connector would be the option of choice in my view. 

Current behavior: see Problem Description above


Expected behavior: see Problem Description above
Comment 1 Alex Thurgood 2015-01-27 16:03:50 UTC
@Heinrich : I don't know the internals of this, but I suspect that the auto-reconnect would be dependent on the connection driver code. JDBC already has this possibility, and allows the user to add that parameter to the connection URL, even if it does mean that you get a message. However, I don't know whether the C++ connector code even has this option buried somewhere within it and just no UI, or whether it would require having to add in a wad of code to manage it all.
Comment 2 Alex Thurgood 2015-01-27 16:12:16 UTC
Seems a valid enhancement request to me, but I have no idea whether it is feasible, so setting to new and we'll see what db devs think of it.
Comment 3 hc.stoellinger 2015-01-27 19:04:31 UTC
Hello Alex,
You are right, the "autoReconnect"-feature depends on the type of connection. I use the JDBC-version and it works NEARLY perfectly (see my enhancement request). As you say, the native C++-connector might not have the necessary API available. This would be a pity, since - after all - it performs better and isn't there also a tendency in LO to want to become independent of Java? Again, the Linux command-line client works o.k., just the way it should work. I don't know its internals, but - isn't it maybe reasonable to assume it might be written in c++ or at least c? Regards from snowed-in Salzburg...
Comment 4 Julien Nabet 2015-01-27 20:31:32 UTC
What's the precise message for JDBC?

For the rest, I prefer waiting by the global use of MariaDB before trying to investigate.
Comment 5 Lionel Elie Mamane 2015-01-27 22:30:08 UTC
For the native connector:

STEP 1
======

In file 
mysqlc/source/mysqlc_connection.cxx
around line 175 in OConnection::construct
add
connProps["MYSQL_OPT_RECONNECT"] = sql::ConnectPropertyVal(static_cast< my_bool >(true));


This should work... If not, http://stackoverflow.com/questions/4879467/how-to-set-autoreconnect-option-with-mysql-connector-c says to add something like this to the connection code after whatever C++ code calls mysql_init, but before whatever C++ code calls mysql_connect:

my_bool myTrue = true;
con->setClientOption("MYSQL_OPT_RECONNECT", &myTrue);

see also http://dev.mysql.com/doc/refman/5.5/en/mysql-options.html for the option definition.


STEP 2
======

At the end of OConnection::construct, there is

    std::auto_ptr<sql::Statement> stmt(m_settings.cppConnection->createStatement());
    stmt->executeUpdate("SET session sql_mode='ANSI_QUOTES'");
    stmt->executeUpdate("SET NAMES utf8");


So that these commands are automatically reexecuted at reconnection time, you need to change this to something like (with the other connProps settings before the actual connect):

connProps["MYSQL_INIT_COMMAND"] = sql::ConnectPropertyVal(std::string("SET session sql_mode='ANSI_QUOTES'; SET NAMES utf8;"));


STEP 3
======

(That's probably a bit more than really meant by this EasyHack, but IMHO is what is necessary to make this reconnection business really useful.)

The user needs to be able to add other commands to MYSQL_INIT_COMMAND. Add it to the Edit / Database / Properties screen, and then concatenate the value of what the user has set with the above:

Change
    OUString aUser, aPass, sUnixSocket, sNamedPipe;
to
    OUString aUser, aPass, sUnixSocket, sNamedPipe, sInitCmd;

In the
    for (;pIter != pEnd;++pIter) {
add a case (Replace "InitCommand" by whatever you called the option)
    } else if ( pIter->Name.equalsAscii("InitCommand")) {
            OSL_VERIFY( pIter->Value >>= sInitCmd );

and change what you created in STEP 2 to something like:

const std::string default_init_str("SET session sql_mode='ANSI_QUOTES'; SET NAMES utf8; ");
std::string init_str(default_init_str + OUStringToOString(sInitCmd, m_settings.encoding).getStr());
Comment 6 Lionel Elie Mamane 2015-01-27 22:37:30 UTC
To add the setting to the database properties screen, see the hints and instructions at bug 43369
Comment 7 hc.stoellinger 2015-01-28 08:59:32 UTC
Communications link failure
The message received under LO-Base when the server "goes away" is:
The last package successfully received from the server was nnnnn milliseconds ago. The last package sent successfully to the server was 0 milliseconds ago. 
ok more (choices to respond with...)
Comment 8 hc.stoellinger 2015-01-30 13:44:55 UTC
Hello, pardon my naive question! But - is anybody working on adding the code describe in the comments? I myself have never compiled the connector... Thanks in advance
Comment 9 Alex Thurgood 2015-01-30 14:18:21 UTC
(In reply to hc.stoellinger from comment #8)
> Hello, pardon my naive question! But - is anybody working on adding the code
> describe in the comments? I myself have never compiled the connector...
> Thanks in advance

Heinrich, my understanding is these are hints and code pointers provided by Lionel to any wannabe LO developer who might like to take this on, but no one is working on it actively until they decide to give it a go. That is why Lionel has indicated that this is an EasyHack for a beginner LO coder that nonetheless requires CPP coding skills and knowledge of SQL programming.


Alex
Comment 10 hc.stoellinger 2015-01-30 15:18:39 UTC
Alex,
Thanks for the comment! I used to code in c++ and have worked with SQL (DB2 for that matter) since its "creation" decades ago. However, for the last years I have been just an "ordinary" user with - unfortunately - no time at all to dirty my fingers...
Comment 11 hc.stoellinger 2015-02-03 14:30:25 UTC
Hello, Things are working again. All I did was to add ?zeroDateTimeBehavior=convertToNull to the connection string. I DO have date fields within the definition of table "inventar". Regards H. Stoellinger
Comment 12 Lionel Elie Mamane 2015-02-03 15:30:16 UTC
(In reply to hc.stoellinger from comment #11)
> Hello, Things are working again. All I did was to add
> ?zeroDateTimeBehavior=convertToNull to the connection string. I DO have date
> fields within the definition of table "inventar". Regards H. Stoellinger

You are probably connecting through JDBC and not through the native connector?
Comment 13 Robinson Tryon (qubit) 2015-12-14 04:52:12 UTC Comment hidden (obsolete)
Comment 14 hc.stoellinger 2016-02-11 14:47:05 UTC
Hello, 
Sorry wor not answering earlier! Yes, I am using JDBC to connect to my MySQL-database. May I add that in THAT case there is also something that should be looked at: Even though one can specify "autoReconnect=true" in the connection string, it is still necessary to answer the resulting message box with "ok" when the server times out. When using the command-line client (mysql) for the connection (NOT through LO!), that client reconnects automatically when loosing the connection.
Regards 
H. Stoellinger
Comment 15 tim 2016-02-15 20:45:17 UTC
I too have this problem using the Native MySQL Connector.  I get 'MySQL Server has gone away' quite regularly and although I have tried to tweak timeouts on the MySQL side I've never got rid of the problem entirely.

Am I right in assuming that to add parameters to the connection string using JDBC one simply adds them to the database name in the connection properties dialogue, so that if my database is called DB it becomes something like

DB?autoreconnect=true&tinyInt1isBit=false

and so on?


I'm reluctant to use JDBC because it is quite a bit slower on my system, and has strange issues where sometimes it returns no data for the first few attempts at opening a form to show data (doesn't much matter which one of many), and then all of a sudden it works. I can't reproduce this reliably enough to report it properly.  It also took me ages to find the tinyInt1isBit tweak, and I was on the verge of reporting the tinybit(1) problem when I found Alex & Lionel's very useful report on the topic.
Comment 16 Robinson Tryon (qubit) 2016-02-18 14:51:47 UTC Comment hidden (obsolete)
Comment 17 Tamas Bunth 2018-10-10 21:49:47 UTC
(In reply to Lionel Elie Mamane from comment #5)
> For the native connector:
> 
> STEP 1
> ======
> 
> In file 
> mysqlc/source/mysqlc_connection.cxx
> around line 175 in OConnection::construct
> add
> connProps["MYSQL_OPT_RECONNECT"] = sql::ConnectPropertyVal(static_cast<
> my_bool >(true));

The mysqlc library moved to connectivity/source/drivers/mysqlc/

You can find mysqlc_connection.cxx there.