| Bug #13707 | Server crash with INSERT DELAYED on MyISAM table | ||
|---|---|---|---|
| Submitted: | 3 Oct 2005 11:06 | Modified: | 20 Nov 2005 3:34 |
| Reporter: | Paul Palaszewski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.16-BK, 5.0.13 | OS: | Linux (Linux, Windows XP SP2) |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
[3 Oct 2005 11:06]
Paul Palaszewski
[3 Oct 2005 11:22]
Valeriy Kravchuk
Do you have any data in your table before upgrade from 4.1.14?
On fresh 5.0.13 installation everything works OK, just as you described:
mysql> CREATE TABLE `MG_ORTT` (
-> `RateDate` DATETIME NOT NULL,
-> `endDate` DATE NOT NULL DEFAULT '0000-00-00',
-> `Currency` VARCHAR(3),
-> `Rate` DECIMAL(19, 6) NOT NULL,
-> `DataSource` CHAR(1) NOT NULL,
-> `UserSign` SMALLINT(5) NOT NULL,
-> PRIMARY KEY (`Currency`,`RateDate`)) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT DELAYED INTO `MG_ORTT`
-> (`RateDate`,`Rate`,`DataSource`,`UserSign`,`Currency`,`endDate`)
-> VALUES ('2005-01-01',0.58683,'I',1,'USD','9999-12-31');
Query OK, 1 row affected (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)
While we are analyzing the reason for a bug (I suspect, it is a DECIMAL behaviour changes in 5.0.13 vs. 4.1.14), if you want to save data from 4.1.14 database, just perform dump and restore them in the fresh 5.0.13 installation.
[3 Oct 2005 11:37]
Paul Palaszewski
Yes ... I just could reproduce it with a different data set. I had to downgrade to 4.1.14 again and reloaded some data and - to see if I can answer your response - then upgraded again to 5.0.13. With that dump the server crashed again.
-- MySQL dump 10.10
--
-- Host: localhost Database: bdm_sbo
-- ------------------------------------------------------
-- Server version 5.0.13-rc-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `mg_ortt`
--
DROP TABLE IF EXISTS `mg_ortt`;
CREATE TABLE `mg_ortt` (
`RateDate` datetime NOT NULL default '0000-00-00 00:00:00',
`endDate` date NOT NULL default '0000-00-00',
`Currency` varchar(3) NOT NULL default '',
`Rate` decimal(19,6) NOT NULL default '0.000000',
`DataSource` char(1) NOT NULL default '',
`UserSign` smallint(5) NOT NULL default '0',
PRIMARY KEY (`RateDate`,`Currency`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `mg_ortt`
--
/*!40000 ALTER TABLE `mg_ortt` DISABLE KEYS */;
LOCK TABLES `mg_ortt` WRITE;
INSERT INTO `mg_ortt` VALUES ('2004-07-31 00:00:00','2004-11-30','USD','0.737250','I',1),('2005-09-22 00:00:00','9999-12-31','USD','0.819130','I',7),('2005-09-21 00:00:00','9999-12-31','GBP','1.484490','I',7);
UNLOCK TABLES;
/*!40000 ALTER TABLE `mg_ortt` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
[3 Oct 2005 13:23]
Valeriy Kravchuk
Sorry, but my workaround was to install fresh 5.0.13 and load that dump into it. Isn't it work? I take a part of your dump, paste it and just executed in my 5.0.15-rc-nt without any problems:
mysql> DROP TABLE IF EXISTS `mg_ortt`;
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE `mg_ortt` (
-> `RateDate` datetime NOT NULL default '0000-00-00 00:00:00',
-> `endDate` date NOT NULL default '0000-00-00',
-> `Currency` varchar(3) NOT NULL default '',
-> `Rate` decimal(19,6) NOT NULL default '0.000000',
-> `DataSource` char(1) NOT NULL default '',
-> `UserSign` smallint(5) NOT NULL default '0',
-> PRIMARY KEY (`RateDate`,`Currency`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> --
mysql> -- Dumping data for table `mg_ortt`
mysql> --
mysql>
mysql> /*!40000 ALTER TABLE `mg_ortt` DISABLE KEYS */;
Query OK, 0 rows affected (0.01 sec)
mysql> LOCK TABLES `mg_ortt` WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `mg_ortt` VALUES ('2004-07-31
'> 00:00:00','2004-11-30','USD','0.737250','I',1),('2005-09-22
'> 00:00:00','9999-12-31','USD','0.819130','I',7),('2005-09-21
'> 00:00:00','9999-12-31','GBP','1.484490','I',7);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT DELAYED INTO `MG_ORTT`
-> (`RateDate`,`Rate`,`DataSource`,`UserSign`,`Currency`,`endDate`)
-> VALUES ('2005-01-01',0.58683,'I',1,'USD','9999-12-31');
Query OK, 1 row affected (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)
Is it work for you?
[4 Oct 2005 8:29]
Paul Palaszewski
As I already stated in the last comment. You have to create the table with 4.1.14 to encounter the issue ... and it's not an option for me and probably also not for many other customers to dump and reload every table just because the server could crash - if 5.x is that incompatible to 4.1, we will stick to 4.1
[15 Oct 2005 12:30]
Valeriy Kravchuk
I was able to repeat the behaviout you described in the following way on XP:
C:\Documents and Settings\openxs>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> CREATE TABLE `mg_ortt` (
-> `RateDate` datetime NOT NULL default '0000-00-00 00:00:00',
-> `endDate` date NOT NULL default '0000-00-00',
-> `Currency` varchar(3) NOT NULL default '',
-> `Rate` decimal(19,6) NOT NULL default '0.000000',
-> `DataSource` char(1) NOT NULL default '',
-> `UserSign` smallint(5) NOT NULL default '0',
-> PRIMARY KEY (`RateDate`,`Currency`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)
mysql> show variables like 'data%';
+---------------+-----------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------+
| datadir | C:\Program Files\MySQL\MySQL Server 4.1\Data\ |
+---------------+-----------------------------------------------+
1 row in set (0.02 sec)
mysql> INSERT INTO `mg_ortt` VALUES ('2004-07-31
'> 00:00:00','2004-11-30','USD','0.737250','I',1),('2005-09-22
'> 00:00:00','9999-12-31','USD','0.819130','I',7),('2005-09-21
'> 00:00:00','9999-12-31','GBP','1.484490','I',7);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> exit
Bye
Then, I stopped the service, deinstall 4.1.14 and install 5.0.13-rc into the same directory. Note, that I've got an error message upon installation (installer was not able to save security changes (new root password etc.), but I just continued):
C:\Documents and Settings\openxs>mysql --character-sets-dir="c:\Program Files\MySQL\MySQL Server 4.1\share\charsets" -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.13-rc-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> INSERT DELAYED INTO `MG_ORTT`
-> (`RateDate`,`Rate`,`DataSource`,`UserSign`,`Currency`,`endDate`)
-> VALUES ('2005-01-01',0.58683,'I',1,'USD','9999-12-31');
ERROR 2013 (HY000): Lost connection to MySQL server during query
So, it is a crash, and there is nothing special in the error log. That is true.
But (!), neither me, nor you (I suppose) performed the important step in the upgrade procedure described in http://dev.mysql.com/doc/refman/5.0/en/upgrading-grant-tables.html. I mean, running mysql_fix_privilege_tables script.
So, please, try to run it and test your INSERT after that. Inform about the results.
[17 Oct 2005 8:46]
Paul Palaszewski
Actually I made an even more aggressive installation: With 4.0/4.1 it worked fine, that we just replaced the bin + share directory in the MySQL folder. It was used to do automatic updates on customer sites where it's an effort to dump the database and reload it (an effort, that nobody wants to pay for). So yes, the result of our installation procedure and yours are the same. It would also be ok, if the server would at least tell us with a clear error message, that the database storage format changed and that it can not process the table anymore. You know, it was always a nice feature of MySQL, that you could simply copy the datafiles from one database to another and you did not have to perform any complicated backup/restore process. So I don't really agree, that it's an Installation-error, but you could say so. It would be clumsy for anybody who used to copy database files to have to check for the database version now, and we would prefer to have a server, that can also work on 4.x tables, but ... I know we can't always get what we want.
[17 Oct 2005 10:15]
Valeriy Kravchuk
Let me check on Linux and newer 5.0.x...
[18 Oct 2005 10:18]
Valeriy Kravchuk
Finally I was able to verify this bug report on Linux (Fedora Core 1). I had installed latest 4.1.16-BK (ChangeSet@1.2442.10.7, 2005-10-17 09:51:16+02:00, jonas@perch.ndb.mysql.com), then created the table and poulated it with data, just as described:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> desc mg_ortt;
ERROR 1146 (42S02): Table 'test.mg_ortt' doesn't exist
mysql> CREATE TABLE `mg_ortt` (
-> `RateDate` datetime NOT NULL default '0000-00-00 00:00:00',
-> `endDate` date NOT NULL default '0000-00-00',
-> `Currency` varchar(3) NOT NULL default '',
-> `Rate` decimal(19,6) NOT NULL default '0.000000',
-> `DataSource` char(1) NOT NULL default '',
-> `UserSign` smallint(5) NOT NULL default '0',
-> PRIMARY KEY (`RateDate`,`Currency`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0,17 sec)
mysql> show variables like 'data%';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| datadir | /home/openxs/dbs/4.1/var/ |
+---------------+---------------------------+
1 row in set (0,02 sec)
mysql> INSERT INTO `mg_ortt` VALUES
-> ,('2004-07-31 00:00:00','2004-11-30','USD','0.737250','I',1),
-> ('2005-09-22 00:00:00','9999-12-31','USD','0.819130','I',7),
-> ('2005-09-21 00:00:00','9999-12-31','GBP','1.484490','I',7);
Query OK, 3 rows affected (0,11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> exit
Bye
[openxs@Fedora 4.1]$ bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/4.1/var/Fedora.pid
051018 13:03:51 mysqld ended
Then I had installed latest 5.0.16-BK (ChangeSet@1.1616.2300.7, 2005-10-17 09:51:16+02:00, jonas@perch.ndb.mysql.com), and started the server with a datadir from 4.1.16-BK:
[openxs@Fedora 5.0]$ bin/mysqld_safe --datadir=/home/openxs/dbs/4.1/var &
[2] 2503
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/4.1/var
[openxs@Fedora 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 to server version: 5.0.16
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> desc mg_ortt;
+------------+---------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------------------+-------+
| RateDate | datetime | NO | PRI | 0000-00-00 00:00:00 | |
| endDate | date | NO | | 0000-00-00 | |
| Currency | varchar(3) | NO | PRI | | |
| Rate | decimal(19,6) | NO | | 0.000000 | |
| DataSource | char(1) | NO | | | |
| UserSign | smallint(5) | NO | | 0 | |
+------------+---------------+------+-----+---------------------+-------+
6 rows in set (0,03 sec)
mysql> INSERT DELAYED INTO `MG_ORTT`
-> (`RateDate`,`Rate`,`DataSource`,`UserSign`,`Currency`,`endDate`)
-> VALUES ('2005-01-01',0.58683,'I',1,'USD','9999-12-31');
ERROR 1146 (42S02): Table 'test.MG_ORTT' doesn't exist
mysql> INSERT DELAYED INTO mg_ortt (`RateDate`,`Rate`,`DataSource`,`UserSign`,
`Currency`,`endDate`) VALUES ('2005-01-01',0.58683,'I',1,'USD','9999-12-31');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
051018 13:07:49 mysqld restarted
Nothing special in the error log... So, this crash is a bug, still present in current -BK sources. (Also on Windows platfrom as noteed by Miguel before).
[25 Oct 2005 13:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/31452
[2 Nov 2005 10:40]
Valeriy Kravchuk
Bug #14467 was marked as a duplicate of this one.
[2 Nov 2005 19:22]
Ingo Strüwing
Test part 1 to be run on 4.1
Attachment: bug13707-1.sh (application/x-sh, text), 3.25 KiB.
[2 Nov 2005 19:23]
Ingo Strüwing
Test part 2 to be run on 5.0
Attachment: bug13707-2.sh (application/x-sh, text), 3.42 KiB.
[4 Nov 2005 9:09]
Valeriy Kravchuk
Bug report http://bugs.mysql.com/bug.php?id=14627 was marked as a duplicate of this one.
[7 Nov 2005 17:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32052
[9 Nov 2005 8:06]
Ingo Strüwing
Pushed to 5.0.16.
[14 Nov 2005 21:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32251
[16 Nov 2005 20:19]
Cassidy Larson
Patch supplied fixed the same problem I was seeing. Running on FreeBSD 6.0-RELEASE.
[20 Nov 2005 3:34]
Paul DuBois
Noted in 5.0.16 changelog.
