Ubuntu 16 mysql error in myamiweb setup wizard

From JC Duncan

FYI-

A new lab got a new cryoemscope. They already have a workstation they wanted to run myamiweb before moving to HPC cluster. It was installed with Ubuntu16.04.

Besides the fact it uses php7.x (mysql_connect call has been deprecated since then), initTablesReport.php was reporting following issue

Error: CREATE TABLE IF NOT EXISTS dataStatusReport ( `DEF_id` int(11) NOT NULL auto_increment, `DEF_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP , `appion_project` int(11) NOT NULL , `processed_session` int(11) NOT NULL , `processed_run` int(11) NOT NULL , `last_exp_runtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' , `ace_run` int(11) NOT NULL , `ace2_run` int(11) NOT NULL , `ctfind_run` int(11) NOT NULL , `ace_processed_image` int(11) NOT NULL , `particle_selection` int(11) NOT NULL , `dog_picker` int(11) NOT NULL , `manual_picker` int(11) NOT NULL , `tilt_picker` int(11) NOT NULL , `template_picker` int(11) NOT NULL , `selected_particle` bigint(20) NOT NULL , `classification` int(11) NOT NULL , `classes` int(11) NOT NULL , `classified_particles` bigint(20) NOT NULL , `RCT_Models` int(11) NOT NULL , `tomogram` int(11) NOT NULL , `stack` int(11) NOT NULL , `stack_particle` bigint(20) NOT NULL , `3D_recon` int(11) NOT NULL , `recon_iteration` int(11) NOT NULL , `classified_particle` bigint(20) NOT NULL , `template` int(11) NOT NULL , `initial_model` int(11) NOT NULL , `first_exp_runtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' , PRIMARY KEY (`DEF_id`), KEY `DEF_timestamp` (`DEF_timestamp`) ) ;Invalid default value for 'last_exp_runtime'

it was fixed by adding

sql_mode = NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
in mysql.conf

It is specific to Ubuntu and I know this distribution is NOT supported. But I will not be surprised that such issue might arise in future version of Centos.

Replies (3)

RE: Ubuntu 16 mysql error in myamiweb setup wizard - Added by Patrick Goetz about 7 years ago

I will certainly try this, but can't fathom how it will do anything unless there is a bug in mysql 5.7.20 or unless you are enabling strict mode as well.

Quoting from the mysql documentation:

As of MySQL 5.7.4, NO_ZERO_IN_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_IN_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_IN_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa.

And the version of mysql in Ubuntu 16.04 is:

  ii  mysql-server              5.7.20-0ubuntu0.16.04.1   all        MySQL database server (metapackage depending on the latest version)
  ii  mysql-server-5.7          5.7.20-0ubuntu0.16.04.1   amd64      MySQL database server binaries and system database setup
  ii  mysql-server-core-5.7     5.7.20-0ubuntu0.16.04.1   amd64      MySQL database server binaries

So at the very least NO_ZERO_IN_DATE can't be right. Other problems are that there is no mysql.conf configuration file -- maybe you meant mysql.cnf?

root@kraken:/etc/mysql# ls -l
total 24
drwxr-xr-x 2 root root 4096 Oct 24 13:04 conf.d
-rw------- 1 root root  317 Oct 24 13:04 debian.cnf
-rwxr-xr-x 1 root root  120 Jul 19 14:28 debian-start
lrwxrwxrwx 1 root root   24 Aug 15 16:43 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- 1 root root  839 Jan 21  2017 my.cnf.fallback
-rw-r--r-- 1 root root  684 Nov  9 15:01 mysql.cnf
drwxr-xr-x 2 root root 4096 Nov  9 15:03 mysql.conf.d

NO_ENGINE_SUBSTITUTION is the default mode.

And again, from the documentation:

As of MySQL 5.7.4, ERROR_FOR_DIVISION_BY_ZERO is deprecated. In MySQL 5.7.4 through 5.7.7, ERROR_FOR_DIVISION_BY_ZERO does nothing when named explicitly.

That leaves us with NO_AUTO_CREATE_USER, and

It is preferable to create MySQL accounts with CREATE USER rather than GRANT. NO_AUTO_CREATE_USER is deprecated and the default SQL mode includes NO_AUTO_CREATE_USER.

RE: Ubuntu 16 mysql error in myamiweb setup wizard - Added by Patrick Goetz about 7 years ago

Beginning with version 5.7, mysql turns on STRICT mode by default, in particular STRICT_TRANS_TABLES. You can check the mode of your mysql server using

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

In particular, this makes '0000-00-00' an invalid date structure. To turn off STRICT mode, add the following line to the bottom of /etc/mysql/mysql.conf.d:

sql_mode = ''

For more details see, for example, https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime/36374690

RE: Ubuntu 16 mysql error in myamiweb setup wizard - Added by Anchi Cheng over 6 years ago

This troublesome query was modified in revision 4486b3e0 and then further debugged for Issue #5861 It should not matter what mode is used now.

(1-3/3)

Project

General

Profile

Leginon