Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MariaDB defaults cause `2013, 'Lost connection to MySQL server during query'` #401

Closed
nickw444 opened this issue Jun 9, 2016 · 7 comments
Closed
Milestone

Comments

@nickw444
Copy link
Contributor

@nickw444 nickw444 commented Jun 9, 2016

Whilst not directly an issue with this library, it might be worth noting that the default that is chosen for SQLALCHEMY_POOL_RECYCLE when using MariaDB as a drop in replacement for MySQL causes a hard to debug connection error.

Comparing the defaults between MYSQL and MariaDB we find:

MySQL 5.7.12:

mysql> SHOW GLOBAL VARIABLES LIKE "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB 10.1.14-MariaDB-1~xenial

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 600   |
+---------------+-------+
1 row in set (0.00 sec)

root@c554e4589da8:/# cat /etc/mysql/my.cnf | grep timeout
connect_timeout     = 5
wait_timeout        = 600

Flask-SQLAlchemy defaults to using a 2 hour recycle time (7200 seconds), which obviously is too long for MariaDB's default 600 second timeout, however is a reasonable value for MySQL, which defaults to 28800 seconds.

Personally, I think it might be worth noting this in the configuration keys page of the documentation for the SQLALCHEMY_POOL_RECYCLE key, since that is the first place users are generally directed when searching the issue.

@davidism
Copy link
Member

@davidism davidism commented Jun 9, 2016

Is there a way to detect Maria vs MySQL during apply_driver_hacks?

@davidism
Copy link
Member

@davidism davidism commented Jun 9, 2016

I'm fine with just documenting it too.

@nickw444
Copy link
Contributor Author

@nickw444 nickw444 commented Jun 9, 2016

Unless the user provides a mariadb specific database URI that we re-write to MySQL, I can't think of a way. Plus this also takes away from mariadb being a drop in replacement. I suppose we could run the select query above to calculate the timeout on initialisation?

But I think that's putting too much responsibility into this library. It really should come down to user configuration.

@immunda
Copy link
Member

@immunda immunda commented Aug 12, 2016

Whilst a valid suggestion @nickw444, I think you're right that it's better to avoid confusing the issue with a mariadb URI.

It seems that short of setting the default timeout to 600 seconds (which would actually solve some issues we get with users having timeout issues due to DB providers settings shorter timeouts), documenting this behaviour does seem the way to go.

Would you be up for submitting a PR @nickw444 ?

@nickw444
Copy link
Contributor Author

@nickw444 nickw444 commented Aug 13, 2016

PR Created. Cheers

andrew-gardener added a commit to ubc/compair that referenced this issue Nov 2, 2016
2013, "Lost connection to MySQL server during query" occurs due to connections timing out on mariadb end. See pallets/flask-sqlalchemy#401 for more background.

Solution is to add SQLALCHEMY_POOL_RECYCLE with value less than mariadb's default timeout (600 seconds)
andrew-gardener added a commit to ubc/compair that referenced this issue Nov 2, 2016
2013, "Lost connection to MySQL server during query" occurs due to connections timing out on mariadb end. See pallets/flask-sqlalchemy#401 for more background.

Solution is to add SQLALCHEMY_POOL_RECYCLE with value less than mariadb's default timeout (600 seconds)
@nickw444
Copy link
Contributor Author

@nickw444 nickw444 commented Dec 21, 2016

Closing as I just realised this PR had been merged.

@nickw444 nickw444 closed this Dec 21, 2016
@davidism
Copy link
Member

@davidism davidism commented Jan 15, 2017

I just installed MariaDB on Arch Linux, and the default is 28800, the same as MySQL. Lending further difficulty to detecting this, since it's down to the distro, not the database, what the defaults are. Might be better to mention defaults in general rather than distinguishing between MySQL and MariaDB.

@davidism davidism modified the milestones: v2.2, v3.0 Jan 15, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.