| Bug #24418 | GRANT statement causes server crash when specific table is specified | ||
|---|---|---|---|
| Submitted: | 18 Nov 2006 22:30 | Modified: | 19 Feb 2007 14:59 |
| Reporter: | Brian Moureaux | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.26 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[18 Nov 2006 22:30]
Brian Moureaux
[20 Nov 2006 15:38]
Valeriy Kravchuk
Thank you for a problem report. Please, send exact SQL statements that I should use to reproduce the crash each and every time from mysql command line client.
[20 Nov 2006 22:24]
Brian Moureaux
This is easily reproduced by issuing commands such as: GRANT SELECT ON mydatabase.mytable TO 'someuser'@'somehost'; or GRANT INSERT ON someotherdb.thetable TO 'user'@'anotherhost'; However, something like: GRANT SELECT ON someotherdb.* TO 'someuser'@'somehost'; will not crash the server because no specific table was specified. I originally encountered this problem while setting up phpmyadmin's database/tables. Importing the commands from a file led to a server crash (it used to work just fine with a 4.x server). I typed the commands manually at the prompt via the mysql console client and the server crashed again on the GRANT statements. I went further from there to see what I could rule out and I ended up finding that users and hosts were not the problem. I also found that which specific privileges were being granted didn't matter nor did the database. The only thing which made any difference was whether or not I was granting permissions on all tables of a database (using the wildcard - *) or just a specific one. Hopefully this clarifies things a bit.
[21 Nov 2006 10:27]
Valeriy Kravchuk
As you mentioned version 4.x, I have additional question. Have you upgrade to 5.0.26 from version 4.x? How you did it? Have you dumped and restored all the databases? I am not able to repeat crashes on "clean" 5.0.x: openxs@suse:~/dbs/5.0> bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.32-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show tables like 't%'; +----------------------+ | Tables_in_test (t%) | +----------------------+ | t | | t1 | | t_2 | | tb | | ti1 | | ti2 | | ti3 | | ti4 | | ti5 | | tracking_noid_myisam | +----------------------+ 10 rows in set (0.01 sec) mysql> GRANT SELECT ON test.t1 TO 'user1'@'192.168.0.46'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT INSERT ON test.t1 TO 'user1'@'suse'; Query OK, 0 rows affected (0.00 sec) hence the questions.
[21 Nov 2006 17:47]
Brian Moureaux
Yes, this was an upgrade from version 4.x which was handled by Gentoo's Portage package management system. The problems were first noticed with operations on tables in the mysql database itself but only when a specific table was specified and not when all tables were targeted. I then tested it on another database which contained a single table (this database was clean as far as 5.0.26 was concerned and was being actively used for INSERT, SELECT, etc). GRANT led to a server crash again on that database when I used it to research this bug - but only when the table was explicitly specified; it was fine with the wildcard (bills.* versus bills.bill_items).
[22 Nov 2006 8:10]
Valeriy Kravchuk
Please, try to repeat on clean installation (without tables in mysql database left from 4.x.y) of 5.0.26 (or 5.0.27), MySQL binaries (not Gentoo), and inform about the results.
[23 Nov 2006 2:35]
Brian Moureaux
Ok. I removed my install of mysql (I had downgraded for the time being) and tried messing around with getting 5.0.27 running, gave up and decided to move my old /var/lib/mysql/ out of the way, installed a fresh version of 5.0.26 (from Gentoo) and went from there restoring other databases from a dump I had on file from yesterday. The crash no longer occurs which makes me believe that 5.x doesn't seem to handle an old version of the mysql database well (privileges, etc) but that's just a logical guess. So now that takes us to the question of why does the server crash - obviously something in the code somewhere which doesn't handle this condition properly. What part of it? I don't know. Best upgrading practices here would include doing a dump on --all-databases and then importing everything again that way after the upgrade (which is best so that those binlog things catch it all too). Bottom line is that something isn't being handled correctly in the server code, but having a clean mysql database after a major upgrade is a good idea in any event.
[23 Nov 2006 16:02]
Valeriy Kravchuk
Have you (or portage system) run mysql_upgrade script, as required in the manual when you upgrade from 4.x.y? Please, check. If not, it is surely NOT a MySQL bug, as this step is clearly documented now (http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html).
[23 Nov 2006 17:46]
Brian Moureaux
Well with that aside, I felt that there might have been some changes in the columns of the mysql database tables affecting permissions. I'd have to contact Gentoo to find out anything specific to the distribution. However, whenever there is a reproducable crash in a program and there are no other hardware/power/etc issues affecting the mix (processing environment is stable), then there is a bug in said program as a crash should never happen because of reasons including a piece of software choking on data input/processing. This indicates an area of further needed development in the project or of a single module at least. Yes, I'm sure that utility does correct some, if not all, of my problems. Yes, there is a possibility that my distribution has overlooked an upgrade detail in some form or another (perhaps it was intended not to be run automatically so as to not alter the mysql database until the administrator had read documentation relating to a proper upgrade of his server which includes making a backup of his databases before the upgrade). The question here is: Should the server _crash_ when it runs into a case when it cannot properly alter its records pertaining to accounts and permissions (due to whatever reason)? I'll take the blame for a slightly botched upgrade job but the project should acknowledge a _potential_ flaw in a module somewhere. Deal?
[24 Nov 2006 8:09]
Valeriy Kravchuk
I agree with you. Crash is a bug anyway. Please, try to figure out the exact steps to repeat it and(!), please, better use MySQL binaries for that test case, not Gentoo portages, as we do not have control on what and how is done there.
[25 Nov 2006 2:23]
Brian Moureaux
I switched my clean /var/lib/mysql out with a dirty backup copy of the directory. I ran mysqld through gdb with the same options as a normal run. This is what I came up with:
(mysql):
mysql> GRANT SELECT ON bills.bill_items TO 'statux'@'localhost';
(in gdb):
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1276916832 (LWP 11099)]
0x0844ad4b in list_walk ()
(gdb) bt
#0 0x0844ad4b in list_walk ()
#1 0x0844b283 in my_hash_insert ()
#2 0x08209199 in mysql_table_grant ()
#3 0x08194b8c in mysql_execute_command ()
#4 0x08197713 in mysql_parse ()
#5 0x08198536 in dispatch_command ()
#6 0x08198eb2 in do_command ()
#7 0x08199914 in handle_one_connection ()
#8 0xb7e00512 in start_thread () from /lib/libpthread.so.0
#9 0xb7c5ffce in clone () from /lib/libc.so.6
0844acfb <list_walk>:
844acfb: 55 push %ebp
844acfc: 89 e5 mov %esp,%ebp
844acfe: 57 push %edi
844acff: 56 push %esi
844ad00: 53 push %ebx
844ad01: 83 ec 0c sub $0xc,%esp
844ad04: 8b 5d 08 mov 0x8(%ebp),%ebx
844ad07: 8b 7d 0c mov 0xc(%ebp),%edi
844ad0a: 8b 75 10 mov 0x10(%ebp),%esi
844ad0d: eb 13 jmp 844ad22 <list_walk+0x27>
844ad0f: 89 74 24 04 mov %esi,0x4(%esp)
844ad13: 8b 43 08 mov 0x8(%ebx),%eax
844ad16: 89 04 24 mov %eax,(%esp)
844ad19: ff d7 call *%edi
844ad1b: 85 c0 test %eax,%eax
844ad1d: 75 09 jne 844ad28 <list_walk+0x2d>
844ad1f: 8b 5b 04 mov 0x4(%ebx),%ebx
844ad22: 85 db test %ebx,%ebx
844ad24: 75 e9 jne 844ad0f <list_walk+0x14>
844ad26: 31 c0 xor %eax,%eax
844ad28: 83 c4 0c add $0xc,%esp
844ad2b: 5b pop %ebx
844ad2c: 5e pop %esi
844ad2d: 5f pop %edi
844ad2e: 5d pop %ebp
844ad2f: c3 ret
844ad30: 55 push %ebp
844ad31: 89 e5 mov %esp,%ebp
844ad33: 53 push %ebx
844ad34: 83 ec 24 sub $0x24,%esp
844ad37: c7 45 f4 01 00 00 00 movl $0x1,0xfffffff4(%ebp)
844ad3e: c7 45 f8 04 00 00 00 movl $0x4,0xfffffff8(%ebp)
844ad45: 8b 48 30 mov 0x30(%eax),%ecx
844ad48: 8d 45 f8 lea 0xfffffff8(%ebp),%eax
844ad4b: 8b 59 68 mov 0x68(%ecx),%ebx
844ad4e: 89 44 24 10 mov %eax,0x10(%esp)
844ad52: 8d 45 f4 lea 0xfffffff4(%ebp),%eax
844ad55: 89 44 24 0c mov %eax,0xc(%esp)
844ad59: 8b 45 08 mov 0x8(%ebp),%eax
844ad5c: 89 54 24 04 mov %edx,0x4(%esp)
844ad60: 89 44 24 08 mov %eax,0x8(%esp)
844ad64: 89 0c 24 mov %ecx,(%esp)
844ad67: ff 53 24 call *0x24(%ebx)
844ad6a: 8b 45 f4 mov 0xfffffff4(%ebp),%eax
844ad6d: 83 c4 24 add $0x24,%esp
844ad70: 5b pop %ebx
844ad71: 5d pop %ebp
844ad72: c3 ret
Provided that there is only one list_walk() function in the mix:
int list_walk(LIST *list, list_walk_action action, gptr argument)
{
int error=0;
while (list)
{
if ((error = (*action)(list->data,argument)))
return error;
list=list_rest(list);
}
return 0;
}
But after working all day at my retail job, my eyes are tired and I can't easily translate the one to the other. My apologies.
I crashed the server twice and found that it landed on the same instruction both times. I could probably donate my dirty database which is definitely known, by this point, to crash at least 5.0.26 (btw, the patchset which Gentoo uses doesn't touch anything relating to this side of the puzzle).
I apologise if I've violated any rules about not posting traces, dumps and code snippets but I feel that it may be useful to someone.
[25 Nov 2006 8:48]
Valeriy Kravchuk
Please, upload your "dirty database" (either as file to the reports, or to our FTP server, with 24418 in a file name somewhere), and inform when you'll do it.
[28 Nov 2006 4:02]
Brian Moureaux
I uploaded a tarball of my dirty /var/lib/mysql/mysql directory which, obviously, caused the crashes.
[19 Feb 2007 14:59]
Valeriy Kravchuk
I was not able to repeat the behaviour described even with your mysql database content on latest 5.0.36-BK after I did the following (as recommended in the manual, http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html): 1. Just after copying your mysql database (from 4.1.x) to 5.0.36 data directory, I ran mysql_upgrade. It informed, among other things, about mysql.uaer table that needed REPAIR. 2. I started mysql_safe with --skip-grant option. 3. I conected as root and executed REPAIR TABLE mysql.user; 4. I reset root's password to empty as described in http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html (with UPDATE + FLUSH PRIVILEGES; 5. I had created database and table in it for your GRANT. 6. I stopped server with mysqladmin and started it as usual. Then: openxs@suse:~/dbs/5.0> bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.36 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT SELECT ON bills.bill_items TO 'statux'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> select host, user, password from mysql.user; +-----------+---------+-------------------------------------------+ | host | user | password | +-----------+---------+-------------------------------------------+ | localhost | root | | | statux | root | | | localhost | | | | statux | | | | localhost | statux | 50e92799731d0587 | | localhost | webuser | *3875CB0BDA3FAF092F2DEB9908F42D3FADDD9384 | | localhost | pma | *09DEFEAFFCEEE0A8CFFFB65222A6D4A68A402BF0 | | localhost | amarok | *7EB6FE281607DDD2EE77187949ABE3E7D311BCF8 | +-----------+---------+-------------------------------------------+ 8 rows in set (0.01 sec) So, no crash in current version if you follow upgrade procedures.
[9 Nov 2007 12:36]
Kristofer Pettersson
See also Bug#16470 crash on grant if old grant tables
