Issue bug in local database

[Linulex]

situation:

plesk 17.0.17
centos 6.latest (everything up to date)
mysql updated to 5.6 with instructions from https://support.plesk.com/hc/en-us/articles/213403429-How-to-upgrade-MySQL-5-5-to-5-6-

replication:
Tools & Settings
Database Servers
click localhost
click OK button

error message:

Internal error: DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'admin_password' cannot be null, query was: replace DatabaseServers set `id`='1', `host`='localhost', `port`='3306', `type`='mysql', `admin_login`='admin', `admin_password`=NULL, `last_error`='no_error', `server_version`='5.6.35'
Message DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'admin_password' cannot be null, query was: replace DatabaseServers set `id`='1', `host`='localhost', `port`='3306', `type`='mysql', `admin_login`='admin', `admin_password`=NULL, `last_error`='no_error', `server_version`='5.6.35'
File Mysql.php
Line 53
Type Plesk\Exception\Database
Go To Previous Page

Search for related Knowledge Base articles

regards
Jan

 

[IgorG]

Try to disable stict mode for MySQL. In /etc/my.cnf find line like

sql_mode=STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_DATE

and remove STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_DATE

after that restar MySQL service.

 

[Linulex]

and remove STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_DATE

this isn't in my.cnf if it is of by default, then it was never on.

regards
Jan

 

[Linulex]

to be complete, our full my.cnf

# The following options will be passed to all MySQL clients
[client]
port        = 3306
socket        = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
#innodb_force_recovery = 4

local-infile=1
query_cache_type=1
query_cache_size=64M
query_cache_limit=32M


port        = 3306
datadir=/var/lib/mysql
socket        = /var/lib/mysql/mysql.sock
skip-external-locking

tmp_table_size = 128M
max_heap_table_size=128M


join_buffer_size=4M
sort_buffer_size=4M
read_buffer_size=4M

open_files_limit=65000

low_priority_updates=1
concurrent_insert=2

myisam-recover-options = BACKUP,FORCE

key_buffer_size=256M
max_allowed_packet=64M
#5.5
#table_cache=32000
#5.6+
table_open_cache=32000
table_definition_cache=32000


myisam_sort_buffer_size=64M
thread_cache_size=50
max_connections=512
interactive_timeout=300
wait_timeout=300
max_connect_errors=9000000
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency=16

slow_query_log
slow_query_log_file=/var/log/mysql-slow.log
long_query_time=5

#log-bin
server-id    = 1

innodb_file_per_table=1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
#innodb_mirrored_log_groups=1
innodb_log_files_in_group=2
innodb_log_file_size=256M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=4G
#innodb_additional_mem_pool_size=20M
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_lock_wait_timeout=50
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

[mysql.server]
user=mysql
basedir=/var/lib


[safe_mysqld]
log=/var/log/mysqld.log
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# Point the following paths to different dedicated disks
tmpdir=/dev/shm/   
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#set-variable    = bdb_cache_size=4M
#set-variable    = bdb_max_lock=10000


[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=2M
write_buffer=2M

[mysqlhotcopy]
interactive-timeout

 

[IgorG]

What is output of command:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "SELECT @@sql_mode"

?

 

[Linulex]

> mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "SELECT @@sql_mode"
Warning: Using a password on the command line interface can be insecure.
@@sql_mode
NO_ENGINE_SUBSTITUTION

the default

https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set).