| Bug #68385 | CREATE USER ... with IDENTIFIED BY clause crashes mysqld | ||
|---|---|---|---|
| Submitted: | 14 Feb 2013 21:09 | Modified: | 1 Apr 2013 15:58 |
| Reporter: | Roland Bouman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.6 | OS: | Linux (mysql-5.6.10-linux-glibc2.5-x86_64) |
| Assigned to: | Matthew Lord | CPU Architecture: | Any |
[14 Feb 2013 21:09]
Roland Bouman
[14 Feb 2013 21:12]
Roland Bouman
I'm getting same problem with a SET PASSWORD command. (either with or without FOR clause). I can tell it must be after finding the row in the user table, because it will give an expected error if the user doesn't yet exist.
21:08:48 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
key_buffer_size=268435456
read_buffer_size=1048576
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 573432 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x3a7dd80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f5a3f3d6e20 thread_stack 0x40000
bin/mysqld(my_print_stacktrace+0x35)[0x8f0d25]
bin/mysqld(handle_fatal_signal+0x3e8)[0x66b138]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7f5a7f793cb0]
bin/mysqld[0x67eb12]
bin/mysqld(_Z15change_passwordP3THDPKcS2_Pc+0x36a)[0x695a6a]
bin/mysqld(_ZN16set_var_password6updateEP3THD+0x1e)[0x66960e]
bin/mysqld(_Z17sql_set_variablesP3THDP4ListI12set_var_baseE+0x89)[0x669fc9]
bin/mysqld(_Z21mysql_execute_commandP3THD+0x241e)[0x6e71fe]
bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x318)[0x6eba58]
bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x827)[0x6ec377]
bin/mysqld(_Z10do_commandP3THD+0xd7)[0x6ed617]
bin/mysqld(_Z24do_handle_one_connectionP3THD+0x116)[0x6b7e06]
bin/mysqld(handle_one_connection+0x45)[0x6b7ee5]
bin/mysqld(pfs_spawn_thread+0x13b)[0xab465b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7f5a7f78be9a]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f5a7e45fcbd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f5a28004f00): set password for sakila = PASSWORD('sakila')
Connection ID (thread ID): 1
Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
[14 Feb 2013 21:42]
Matthew Lord
Hi Roland!
I'm unable to repeat this:
mysql> select version();
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.10-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.04 sec)
mysql> create user sakila@localhost;
Query OK, 0 rows affected (0.08 sec)
mysql> set password for sakila@localhost = PASSWORD('sa90k!ilSa');
Query OK, 0 rows affected (0.01 sec)
mysql> create user sakila2@localhost identified by 'sa90k!ilSa';
Query OK, 0 rows affected (0.00 sec)
Here's the config that I'm currently using:
[mysqld56]
user=mysql
server_id=5
skip-slave-start
mysqld=/usr/local/mysql56/bin/mysqld
basedir=/usr/local/mysql56
datadir=/var/lib/mysql/mysql5.6
log_error=/var/lib/mysql/mysql5.6/bootyjr.err
innodb_data_home_dir=/var/lib/innodb/5.6
innodb_log_group_home_dir=/var/lib/innodb/5.6
socket=/tmp/mysql56.sock
port=3356
lc-messages-dir = /usr/local/mysql56/share
open_files_limit = 20000
max_connections = 2500
thread_cache_size=64
plugin-load=thread_pool.so;authentication_pam.so;validate_password.so
thread_pool_size = 36
validate-password = FORCE_PLUS_PERMANENT
validate_password_dictionary_file = "/usr/share/hunspell/en_US.dic"
# 2 == STRONG
validate_password_policy = 2
query_cache_type = 0
query_cache_size = 0
# setup P_S
performance_schema
performance_schema_instrument='%=on'
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances=2
innodb_max_dirty_pages_pct=75
innodb_adaptive_max_sleep_delay=1000000
innodb_data_home_dir=/var/lib/innodb/5.6
innodb_log_group_home_dir=/var/lib/innodb/5.6
innodb_log_file_size=2000M
innodb_log_buffer_size=32M
innodb_flush_method=O_DSYNC
innodb_flush_log_at_trx_commit=2
innodb_flushing_avg_loops = 100
innodb_flush_log_at_timeout = 10
innodb_max_dirty_pages_pct_lwm = 50
innodb_purge_threads=4
innodb_purge_batch_size=300
innodb_max_purge_lag=5000000
innodb_io_capacity=400
innodb_io_capacity_max=2400
innodb_read_io_threads=12
innodb_write_io_threads=10
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_autoinc_lock_mode=2
innodb_open_files = 512
#innodb_strict_mode=1
innodb_support_xa=0
innodb_locks_unsafe_for_binlog
innodb_table_locks=0
innodb_thread_sleep_delay=0
innodb_use_native_aio=1
innodb_spin_wait_delay = 24
#innodb_analyze_is_persistent=1
innodb_stats_persistent=1
innodb_stats_on_metadata=0
innodb_stats_persistent_sample_pages=40
innodb_stats_transient_sample_pages=16
innodb_buffer_pool_dump_at_shutdown=1
innodb_change_buffer_max_size=40
innodb_checksum_algorithm=crc32
innodb_concurrency_tickets=2000
# Good for SSDs
innodb_flush_neighbors=0
innodb_page_size=4k
innodb_print_all_deadlocks=1
innodb_lru_scan_depth=512
innodb_change_buffering = ALL
innodb_sync_array_size = 1024
innodb_undo_logs = 4
binlog_format = ROW
binlog-checksum = CRC32
# binlog_direct_non_transactional_updates = ON
binlog_row_image = minimal
binlog_rows_query_log_events = 1
gtid-mode = ON
enforce-gtid-consistency=true
log-bin
log-slave-updates
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave-parallel-workers = 5
master-verify-checksum = 1
slave-sql-verify-checksum = 1
table_open_cache = 8000
table_open_cache_instances = 16
# END CONFIG
Can you show me the config file that you're using?
Have you yet generated a core file that shows where the crash occurred?
[14 Feb 2013 21:43]
Roland Bouman
my.cnf at Matts request
Attachment: my.cnf (application/octet-stream, text), 4.75 KiB.
[14 Feb 2013 21:51]
Roland Bouman
Hi Matt, btw here's my version (already in the report but good to confirm) mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.10 | +-----------+ I enabled core dumps by adding a core-file line to my.cnf. I now have a 1G core file. What do you want me to do with it?
[14 Feb 2013 22:01]
Matthew Lord
Hi Roland, OK, you have a pretty minimal config. I don't see any settings clearly related. I downloaded 5.6.10 community and started it with a fresh datadir and no defaults: 1) ./mysql-5.6.10-linux-glibc2.5-x86_64/scripts/mysql_install_db --no-defaults --datadir=./mysql-5.6.10-linux-glibc2.5-x86_64/data --basedir=./mysql-5.6.10-linux-glibc2.5-x86_64 2) chown -R mysql:mysql ./mysql-5.6.10-linux-glibc2.5-x86_64/data/ 3) ./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld --no-defaults --basedir=./mysql-5.6.10-linux-glibc2.5-x86_64 --datadir=./mysql-5.6.10-linux-glibc2.5-x86_64/data --user=mysql & 4) ./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysql --socket=/tmp/mysql.sock mysql> show global variables like "%version%"; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 1.2.10 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.10 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+------------------------------+ 7 rows in set (0.00 sec) mysql> create user sakila@localhost identified by 'sakila'; Query OK, 0 rows affected (0.00 sec) mysql> status; -------------- ./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysql Ver 14.14 Distrib 5.6.10, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 1 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.10 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 2 min 23 sec Threads: 1 Questions: 7 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.048 -------------- mysql> show grants for sakila@localhost; +---------------------------------------------------------------------------------------------------------------+ | Grants for sakila@localhost | +---------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'sakila'@'localhost' IDENTIFIED BY PASSWORD '*FDAF706717E70DB8DDAD0C5214B13770E1A80B0E' | +---------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[14 Feb 2013 22:04]
Matthew Lord
This is becoming more of a support request than a bug report. You can examine the core file to get some basics this way: gdb <path to mysqld> <path to core file> gdb) set print pretty on gdb) set print object on gdb) set pagination off gdb) bt full gdb) thread apply all bt Those are just the most basic commands to start with. Please let me know if you feel that I'm missing anything in my attempts to repeat the bug. I am marking this as can't repeat for now, because I cannot repeat it.
[14 Feb 2013 22:06]
Matthew Lord
Hi Roland, Are you able to repeat the crash using a freshly created datadir? I suspect that perhaps we have some old and un-upgraded system tables in use? That would be my first guess. Best Regards, Matt
[14 Feb 2013 22:06]
Roland Bouman
Ok fair enough. I'll try and see what I can find with gdb.
[14 Feb 2013 22:13]
Matthew Lord
Hi Roland, OK, please let me know what you find. Once we can at least get a good stack trace, then we can go from there. Best Regards, Matt
[14 Feb 2013 22:43]
Roland Bouman
Investigated a bit more. It seems I can reproduce it using this path: #1 start with empty data dir. #2 run the mysql_install_db script (verified it completes normally) at this point, everything works fine and the statement doesn't crash #3 reload a dump of the mysql database. (attached) at this point, the crashing starts after issuing statements mentioned above.
[14 Feb 2013 22:45]
Roland Bouman
Dump of the mysql database. Load this after fresh install to reproduce the crash.
Attachment: mysql.dump.sql.zip (application/zip, text), 209.28 KiB.
[14 Feb 2013 23:05]
Matthew Lord
Hi Roland,
Thank you for the bug report! I was able to verify it this way:
1) ./mysql-5.6.10-linux-glibc2.5-x86_64/scripts/mysql_install_db --no-defaults --datadir=./mysql-5.6.10-linux-glibc2.5-x86_64/data --basedir=./mysql-5.6.10-linux-glibc2.5-x86_64
2) ./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld --no-defaults --basedir=./mysql-5.6.10-linux-glibc2.5-x86_64 --datadir=./mysql-5.6.10-linux-glibc2.5-x86_64/data --user=root &
3) ./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysql --socket=/tmp/mysql.sock
4) USE `mysql`;
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin DEFAULT '',
`authentication_string` text COLLATE utf8_bin,
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
create user sakila@localhost identified by 'sakila';
I then get the crash:
mysql> create user sakila@localhost identified by 'sakila';
23:00:59 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68216 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x2775600
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f4d09135e20 thread_stack 0x40000
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(my_print_stacktrace+0x35)[0x8f0d25]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(handle_fatal_signal+0x3e8)[0x66b138]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7f4d20064cb0]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld[0x68fbdf]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(_Z17mysql_create_userP3THDR4ListI11st_lex_userE+0x2f7)[0x693267]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(_Z21mysql_execute_commandP3THD+0x3c05)[0x6e89e5]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x318)[0x6eba58]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x827)[0x6ec377]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(_Z10do_commandP3THD+0xd7)[0x6ed617]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x116)[0x6b7e06]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(handle_one_connection+0x45)[0x6b7ee5]
./mysql-5.6.10-linux-glibc2.5-x86_64/bin/mysqld(pfs_spawn_thread+0x13b)[0xab465b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7f4d2005ce9a]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f4d1ed2ccbd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f4cfc004ec0): create user sakila@localhost identified by 'sakila'
Connection ID (thread ID): 1
Status: NOT_KILLED
I will now copy this over to the development team so that they can begin working on the fix. The issue seems to be that we're not properly handling a mysql.user table taken from 5.5 or earlier.
Thanks again!
Matt
[14 Feb 2013 23:09]
Roland Bouman
Matt, no problem, glad to.
I can also report that my current workaround for the password is to simply do:
UPDATE mysql.user
SET password = PASSWORD('sakila')
WHERE user = 'sakila';
..and so on.
This seems to restore ability to login as respective user, and also gives access to their databases. But a SHOW GRANTS statement will still fail to print out actual privileges.
Should I find more detailed info on that issue, I will file a separate request.
Thanks again for your effort - I really appreciate how fast you moved in to pick this up.
Cheers, Roland.
[14 Feb 2013 23:14]
Matthew Lord
OK, I've now copied it over. The suggested fix is: We should check the existing structure of the mysql.user table and produce an error when it has not yet been upgraded for 5.6 use.
[15 Feb 2013 0:04]
Roland Bouman
Hi Matt, Thanks! FWIW, I have some doubt regarding the fix. I mean, AFAIK the initial run of mysql_install_db should have created the table. Since the dump uses CREATE TABLE IF NOT EXISTS statements, it seems that cannot be responsible for changing the structure of any table at all - only for loading data into them. Anyway - not my job to dictate the direction of the solution but it seems to me it's the data, not the structure that was culprit.
[15 Feb 2013 6:01]
MySQL Verification Team
if you reload the mysql database after running mysql_install_db, you need to run mysql_upgrade....
[15 Feb 2013 6:04]
MySQL Verification Team
you are lacking the password_expired column in the user table!
[21 Feb 2013 13:32]
MySQL Verification Team
bug #68409 is a duplicate of this.
[5 Mar 2013 17:34]
MySQL Verification Team
bug #68576 is a duplicate
[6 Mar 2013 8:35]
Venu Anuganti
You should def fix this bug, check the patch in Bug #68576; when I tested in debugger it worked fine for 5.5 & 5.6
[1 Apr 2013 15:58]
Paul DuBois
Noted in 5.6.12, 5.7.2 changelogs. In a MySQL server newer than MySQL 5.5 using a nonupgraded mysql.proc table (for which mysql_upgrade had not been run), statements to set passwords caused a server exit due to a faulty check for the password_expired column.
[26 Apr 2013 17:40]
Paul DuBois
Corrected changelog entry: In a MySQL server newer than MySQL 5.5 using a nonupgraded mysql.user table (for which mysql_upgrade had not been run), statements to set passwords caused a server exit due to a faulty check for the password_expired column.
[11 Nov 2013 19:20]
Ben Ritchie
Hey guys i used mysql_upgrade and it fixes my crashing issue - thanks for sharing the workaround
