MySQL Bugs: #25319: Total Crash,probably for datetime field

Bug #25319	Total Crash,probably for datetime field
Submitted:	28 Dec 2006 20:51	Modified:	29 Jan 2007 8:31
Reporter:	Lorenzo Viola	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.24a	OS:	Other (OpenBSD 4.0)
Assigned to:		CPU Architecture:	Any
Tags:	datetime openbsd

Description:

I've checked that this bugs didn't happen on previous version of mysql-server-5.x for openbsd, and it also it doesn't happen for the same version (5.0.24a) on a debian system.

The mysql process crashes with a signal 11, probably causes by some datetime problem.

How to repeat:

Create this table :

DROP TABLE IF EXISTS `SESSIONI`;
CREATE TABLE `SESSIONI` (
  `COD_SOC` char(3) NOT NULL default '',
  `SESSIONI_ID` varchar(30) NOT NULL default '0',
  `SESSIONI_LOGIN` varchar(30) default NULL,
  `SESSIONI_DT_LOGIN` datetime default NULL,
  `SESSIONI_DT_LOGOUT` datetime default NULL,
  `SESSIONI_OP` varchar(30) default NULL,
  `SESSIONI_LANG` varchar(10) default NULL,
  `SESSIONI_PAGE_ID` varchar(30) default NULL,
  `SESSIONI_CAT` varchar(60) default NULL,
  `SESSIONI_CODREF` varchar(30) default NULL,
  `SESSIONI_ANAG_COD` varchar(40) default NULL,
  `SESSIONI_SOCALT` varchar(5) default NULL,
  `SESSIONI_LOGINTYPE` varchar(10) default NULL,
  `SESSIONI_IP` varchar(15) default NULL,
  PRIMARY KEY  (`COD_SOC`,`SESSIONI_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  

Then try inserting some rows in this table.
The first row doesn't create any problem, any more row makes the entire mysql process to crash with a signal 11.

This happens with any operation, like a simple "SELECT".

Suggested fix:

If you put a datetime default value in the field definition, this doesn't happen.
Anyway I'm finding other bugs on this release of mysql-server, I'll post asap.

This didn't seem to happen with a single datetime field with NULL as default.

It seem to happen if you have a table with two datetime field with NULL as default , and only on a openbsd 4.0 system.

ps.
I also had a lot of trouble because of not optimized tables, as I exported the data from an old server, and totally recreated and reimported to the new server.
Wouldn't it be nice to make by default an opmitization command, placed at the end of an export file ?
I lost one hour checking my app, because the database didn't find any data even with very easy query....

Best Regards

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27, and inform about the results. I was not able to repeat the behaviour described with the latest OpenBSD I have (3.8) and 5.0.27:

$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `SESSIONI` (
    ->   `COD_SOC` char(3) NOT NULL default '',
    ->   `SESSIONI_ID` varchar(30) NOT NULL default '0',
    ->   `SESSIONI_LOGIN` varchar(30) default NULL,
    ->   `SESSIONI_DT_LOGIN` datetime default NULL,
    ->   `SESSIONI_DT_LOGOUT` datetime default NULL,
    ->   `SESSIONI_OP` varchar(30) default NULL,
    ->   `SESSIONI_LANG` varchar(10) default NULL,
    ->   `SESSIONI_PAGE_ID` varchar(30) default NULL,
    ->   `SESSIONI_CAT` varchar(60) default NULL,
    ->   `SESSIONI_CODREF` varchar(30) default NULL,
    ->   `SESSIONI_ANAG_COD` varchar(40) default NULL,
    ->   `SESSIONI_SOCALT` varchar(5) default NULL,
    ->   `SESSIONI_LOGINTYPE` varchar(10) default NULL,
    ->   `SESSIONI_IP` varchar(15) default NULL,
    ->   PRIMARY KEY  (`COD_SOC`,`SESSIONI_ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) values('abc', 'id1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) values('abc', 'id2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) values('abc', 'id3');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) values('abc', 'id4');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) values(concat('abd',rand()), 'id4');
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> show warnings\G                                                          *************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'COD_SOC' at row 1
1 row in set (0.00 sec)

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) values('abd', concat('abd',rand()));
Query OK, 1 row affected (0.00 sec)

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) values('abd', concat('abd',rand()));
Query OK, 1 row affected (0.00 sec)

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) SELECT 'abd', concat('abd',rand()) from `SESSIONI`;
Query OK, 7 rows affected (0.04 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) SELECT 'abd', concat('abd',rand()) from `SESSIONI`;
Query OK, 14 rows affected (0.00 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) SELECT 'abd', concat('abd',rand()) from `SESSIONI`;
Query OK, 28 rows affected (0.00 sec)
Records: 28  Duplicates: 0  Warnings: 0

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) SELECT 'abd', concat('abd',rand()) from `SESSIONI`;
Query OK, 56 rows affected (0.03 sec)
Records: 56  Duplicates: 0  Warnings: 0

mysql> insert into `SESSIONI` (`COD_SOC`, `SESSIONI_ID`) SELECT 'abd', concat('abd',rand()) from `SESSIONI`;
Query OK, 112 rows affected (0.01 sec)
Records: 112  Duplicates: 0  Warnings: 0

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye
$ uname -a
OpenBSD openbsd.localdomain 3.8 GENERIC#138 i386

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".