| Bug #14000 | FORMAT(ColumnName, 2) breaks connection if column value is NULL | ||
|---|---|---|---|
| Submitted: | 13 Oct 2005 9:48 | Modified: | 18 Oct 2005 14:11 |
| Reporter: | Tim Hancock | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.13rc | OS: | Linux (Redhat Linux AS 3 EM64T) |
| Assigned to: | CPU Architecture: | Any | |
[13 Oct 2005 9:48]
Tim Hancock
[13 Oct 2005 10:09]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of "SHOW CREATE TABLE EU_Issues" command. How many rows are there in this table. Do you have anything notable in the error log?
[13 Oct 2005 10:16]
Tim Hancock
CREATE TABLE `EU_Issues` (
`offering_circular_id` bigint(20) NOT NULL auto_increment,
`file_available` tinyint(1) NOT NULL default '0',
`ISMA` tinyint(1) NOT NULL default '0',
`ISMARef` varchar(100) default NULL,
`ISMARefAlias` varchar(8) default NULL,
`AdditionalInfo` mediumtext,
`CIRC_ISIN_KEY` varchar(25) NOT NULL default '',
`ISIN_Alias` varchar(25) default NULL,
`Parent_ISIN` varchar(15) default NULL,
`Parent_ISIN_Issue_Date` datetime default NULL,
`Temp_ISIN` varchar(12) default NULL,
`PAGES` int(11) default NULL,
`STATUS` int(11) default NULL,
`CURRENCY` int(11) default NULL,
`COUPON_TYPE` varchar(50) default NULL,
`COUPON_VALUE` decimal(6,5) default NULL,
`COUPON_FRN` tinyint(1) unsigned NOT NULL default '0',
`PRINCIPAL_AMOUNT` decimal(20,2) default NULL,
`US_AMOUNT` decimal(20,2) default NULL,
`ISSUE_DATE` datetime default NULL,
`MATURITY_DATE` datetime default NULL,
`PERPETUAL` enum('Y','N','') default NULL,
`CALL_OPTION` enum('Y','N','') default NULL,
`PUT_OPTION` enum('Y','N','') default NULL,
`NEGATIVE_PLEDGE` enum('Y','N','') default NULL,
`CROSS_DEFAULT` enum('Y','N','') default NULL,
`TAX_GROSS_UP` enum('Y','N','') default NULL,
`IS_144A` enum('Y','N','') default NULL,
`REG_S` enum('Y','N','') default NULL,
`PRINTER_NUMBER` varchar(50) default NULL,
`LegallyBindingLanguage` smallint(6) default NULL,
`charField` varchar(50) default NULL,
`RATING_MOODY` int(11) default NULL,
`RATING_FITCH` int(11) default NULL,
`RATING_SP` int(11) default NULL,
`LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`UpdatedBy` varchar(50) default NULL,
`StoreInDatabase` smallint(6) default NULL,
`CreationDate` datetime default NULL,
`ISINProvidedBy` smallint(6) default NULL,
`ParentISINConfirmed` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`offering_circular_id`),
UNIQUE KEY `CIRC_ISIN_KEY` (`CIRC_ISIN_KEY`),
KEY `STATUS` (`STATUS`),
KEY `CURRENCY` (`CURRENCY`),
KEY `StoreInDatabase` (`StoreInDatabase`),
KEY `ISINProvidedBy` (`ISINProvidedBy`),
KEY `IDX_Oc_Isin` (`offering_circular_id`,`CIRC_ISIN_KEY`),
KEY `Index_Oc_Isin_Alias` (`offering_circular_id`,`CIRC_ISIN_KEY`,`ISIN_Alias`),
KEY `Idx_StoreInDB_OC` (`StoreInDatabase`,`offering_circular_id`),
KEY `Idx_Alias_OC` (`ISIN_Alias`,`offering_circular_id`),
KEY `Idx_Parent_OC_Isin_Alias` (`Parent_ISIN`,`offering_circular_id`,`CIRC_ISIN_KEY`,`ISIN_Alias`),
KEY `Idx_StoreInDB_Isin` (`StoreInDatabase`,`CIRC_ISIN_KEY`),
KEY `Idx_Isin_StoreInDB` (`CIRC_ISIN_KEY`,`StoreInDatabase`),
KEY `Idx_Isin_OC` (`CIRC_ISIN_KEY`,`offering_circular_id`),
KEY `Idx_Temp_Isin` (`Temp_ISIN`),
KEY `Idx_OC_CreationDate` (`offering_circular_id`,`CreationDate`),
KEY `Idx_Isin_OC_Parent` (`CIRC_ISIN_KEY`,`offering_circular_id`,`Parent_ISIN`),
KEY `Idx_OC_Isin_IssueDate` (`offering_circular_id`,`CIRC_ISIN_KEY`,`ISSUE_DATE`),
KEY `Idx_StoreinDB_OC__Isin_IssDate` (`StoreInDatabase`,`offering_circular_id`,`CIRC_ISIN_KEY`,`ISSUE_DATE`),
KEY `Idx_Multiple` (`offering_circular_id`,`CIRC_ISIN_KEY`,`STATUS`,`CURRENCY`,`COUPON_VALUE`,`PRINCIPAL_AMOUNT`,`ISSUE_DATE`,`MATURITY_DATE`),
CONSTRAINT `FK_EU_Issues_4` FOREIGN KEY (`CURRENCY`) REFERENCES `EU_Currency` (`ID`),
CONSTRAINT `FK_EU_Issues_5` FOREIGN KEY (`StoreInDatabase`) REFERENCES `EU_Databases` (`ID`),
CONSTRAINT `FK_EU_Issues_6` FOREIGN KEY (`ISINProvidedBy`) REFERENCES `EU_ISIN_Provider` (`ID`),
CONSTRAINT `FK_EU_Issues_7` FOREIGN KEY (`STATUS`) REFERENCES `EU_Status` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 868352 kB; (`CURRENCY`) REFER `DBIssues/EU_Curr'
There are about 320,000 rows in this table. Many have NULL values in the PRINCIPAL_AMOUNT field.
The error log reports this:
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=52428800
read_buffer_size=52424704
max_used_connections=3
max_connections=200
threads_connected=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 20530398 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Number of processes running now: 0
051013 11:04:30 mysqld restarted
051013 11:04:30 [Warning] Changed limits: max_open_files: 65535 max_connections: 200 table_cache: 32662
051013 11:04:31 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
051013 11:04:31 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 8 3126570897.
InnoDB: Doing recovery: scanned up to log sequence number 8 3126570897
InnoDB: Last MySQL binlog file position 0 42811, file name /mysql/data/binarylog.000069
051013 11:04:31 InnoDB: Started; log sequence number 8 3126570897
051013 11:04:31 [Note] Recovering after a crash using /mysql/data/binarylog
051013 11:04:31 [Note] Starting crash recovery...
051013 11:04:31 [Note] Crash recovery finished.
051013 11:04:31 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.13-rc-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3308 MySQL Community Edition - Standard (GPL)
[13 Oct 2005 10:50]
Valeriy Kravchuk
OK, you've got a crash. Please, describe your hardware (how much RAM do you really have etc.) Have you seen that up to 20G may be needed for MySQL in your configuration? Please, post the my.cnf content and be ready to post the definitions of the other tables involved (by foreign keys). Are you able to repeat the crash using this only statement: SELECT FORMAT(PRINCIPAL_AMOUNT, 2) AS PRINCIPAL_AMOUNT FROM EU_Issues WHERE CIRC_ISIN_KEY = 'XS0229511901000'
[13 Oct 2005 11:14]
Tim Hancock
The server is a Dell 2850 Dual 3.2Ghz Intel EM64T with 8GB RAM. The realistic number of concurrent connections expected is about 50 during periods of heavy use so the 20GB memory requirement is probably not going to be reached. The SELECT statement you show does crash the server. Substituting the CIRC_ISIN_KEY value for another row that has a PRINCIPAL_AMOUNT that is null also crashes it. Out my.cnf config is: [mysqld] port=3308 datadir=/mysql/data max_connections=200 default_table_type=innodb key_buffer_size=50M sort_buffer_size=50M table_cache=2M read_buffer_size=50M innodb_buffer_pool_size=1G query_cache_size=1G server-id=1 log-bin=/mysql/data/binarylog log-slow-queries=/mysql/data/mysql_slow_query.log long_query_time=10 max_allowed_packet=64M max-binlog-size=30M
[14 Oct 2005 15:36]
Valeriy Kravchuk
I was able to repeat the problem you described on 5.0.13-rc-nt on Windows, using the following simple actions:
mysql> CREATE TABLE `EU_Issues` (
-> `offering_circular_id` bigint(20) NOT NULL auto_increment,
-> `file_available` tinyint(1) NOT NULL default '0',
-> `ISMA` tinyint(1) NOT NULL default '0',
-> `ISMARef` varchar(100) default NULL,
-> `ISMARefAlias` varchar(8) default NULL,
-> `AdditionalInfo` mediumtext,
-> `CIRC_ISIN_KEY` varchar(25) NOT NULL default '',
-> `ISIN_Alias` varchar(25) default NULL,
-> `Parent_ISIN` varchar(15) default NULL,
-> `Parent_ISIN_Issue_Date` datetime default NULL,
-> `Temp_ISIN` varchar(12) default NULL,
-> `PAGES` int(11) default NULL,
-> `STATUS` int(11) default NULL,
-> `CURRENCY` int(11) default NULL,
-> `COUPON_TYPE` varchar(50) default NULL,
-> `COUPON_VALUE` decimal(6,5) default NULL,
-> `COUPON_FRN` tinyint(1) unsigned NOT NULL default '0',
-> `PRINCIPAL_AMOUNT` decimal(20,2) default NULL,
-> `US_AMOUNT` decimal(20,2) default NULL,
-> `ISSUE_DATE` datetime default NULL,
-> `MATURITY_DATE` datetime default NULL,
-> `PERPETUAL` enum('Y','N','') default NULL,
-> `CALL_OPTION` enum('Y','N','') default NULL,
-> `PUT_OPTION` enum('Y','N','') default NULL,
-> `NEGATIVE_PLEDGE` enum('Y','N','') default NULL,
-> `CROSS_DEFAULT` enum('Y','N','') default NULL,
-> `TAX_GROSS_UP` enum('Y','N','') default NULL,
-> `IS_144A` enum('Y','N','') default NULL,
-> `REG_S` enum('Y','N','') default NULL,
-> `PRINTER_NUMBER` varchar(50) default NULL,
-> `LegallyBindingLanguage` smallint(6) default NULL,
-> `charField` varchar(50) default NULL,
-> `RATING_MOODY` int(11) default NULL,
-> `RATING_FITCH` int(11) default NULL,
-> `RATING_SP` int(11) default NULL,
-> `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update
-> CURRENT_TIMESTAMP,
-> `UpdatedBy` varchar(50) default NULL,
-> `StoreInDatabase` smallint(6) default NULL,
-> `CreationDate` datetime default NULL,
-> `ISINProvidedBy` smallint(6) default NULL,
-> `ParentISINConfirmed` tinyint(1) NOT NULL default '0',
-> PRIMARY KEY (`offering_circular_id`),
-> UNIQUE KEY `CIRC_ISIN_KEY` (`CIRC_ISIN_KEY`),
-> KEY `STATUS` (`STATUS`),
-> KEY `CURRENCY` (`CURRENCY`),
-> KEY `StoreInDatabase` (`StoreInDatabase`),
-> KEY `ISINProvidedBy` (`ISINProvidedBy`),
-> KEY `IDX_Oc_Isin` (`offering_circular_id`,`CIRC_ISIN_KEY`),
-> KEY `Index_Oc_Isin_Alias`
-> (`offering_circular_id`,`CIRC_ISIN_KEY`,`ISIN_Alias`),
-> KEY `Idx_StoreInDB_OC` (`StoreInDatabase`,`offering_circular_id`),
-> KEY `Idx_Alias_OC` (`ISIN_Alias`,`offering_circular_id`),
-> KEY `Idx_Parent_OC_Isin_Alias`
-> (`Parent_ISIN`,`offering_circular_id`,`CIRC_ISIN_KEY`,`ISIN_Alias`),
-> KEY `Idx_StoreInDB_Isin` (`StoreInDatabase`,`CIRC_ISIN_KEY`),
-> KEY `Idx_Isin_StoreInDB` (`CIRC_ISIN_KEY`,`StoreInDatabase`),
-> KEY `Idx_Isin_OC` (`CIRC_ISIN_KEY`,`offering_circular_id`),
-> KEY `Idx_Temp_Isin` (`Temp_ISIN`),
-> KEY `Idx_OC_CreationDate` (`offering_circular_id`,`CreationDate`),
-> KEY `Idx_Isin_OC_Parent`
-> (`CIRC_ISIN_KEY`,`offering_circular_id`,`Parent_ISIN`),
-> KEY `Idx_OC_Isin_IssueDate`
-> (`offering_circular_id`,`CIRC_ISIN_KEY`,`ISSUE_DATE`),
-> KEY `Idx_StoreinDB_OC__Isin_IssDate`
-> (`StoreInDatabase`,`offering_circular_id`,`CIRC_ISIN_KEY`,`ISSUE_DATE`),
-> KEY `Idx_Multiple`
-> (`offering_circular_id`,`CIRC_ISIN_KEY`,`STATUS`,`CURRENCY`,`COUPON_VALUE
`,`PRINCIPAL_AMOUNT`,`ISSUE_DATE`,`MATURITY_DATE`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.16 sec)
mysql> SELECT
-> FORMAT(PRINCIPAL_AMOUNT, 2) AS PRINCIPAL_AMOUNT
-> FROM EU_Issues
-> WHERE CIRC_ISIN_KEY = 'XS0229511901000';
Empty set (0.05 sec)
mysql> insert into EU_Issues(circ_isin_key) values('XS0229511901000');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT
-> FORMAT(PRINCIPAL_AMOUNT, 2) AS PRINCIPAL_AMOUNT
-> FROM EU_Issues
-> WHERE CIRC_ISIN_KEY = 'XS0229511901000';
ERROR 2013 (HY000): Lost connection to MySQL server during query
My table is the same as yours, but without foreign keys.
On the other side, I was not able to repeat it the same way on Linux and 5.0.15-BK (current sources) on MyISAM table of the same structure:
mysql> insert into EU_Issues(circ_isin_key) values('XS0229511901000');
Query OK, 1 row affected (0,01 sec)
mysql> insert into EU_Issues(circ_isin_key, principal_amount) values('XS0229511901001', 123);
Query OK, 1 row affected (0,01 sec)
mysql> SELECT FORMAT(PRINCIPAL_AMOUNT, 2) AS PRINCIPAL_AMOUNT FROM EU_Issues
-> WHERE CIRC_ISIN_KEY = 'XS0229511901000';
+------------------+
| PRINCIPAL_AMOUNT |
+------------------+
| NULL |
+------------------+
1 row in set (0,02 sec)
mysql> SELECT FORMAT(PRINCIPAL_AMOUNT, 2) AS PRINCIPAL_AMOUNT FROM EU_Issues WHERE CIRC_ISIN_KEY = 'XS0229511901001';
+------------------+
| PRINCIPAL_AMOUNT |
+------------------+
| 123.00 |
+------------------+
1 row in set (0,00 sec)
So, while I am still testing and working on a simpler test case, can you, please, try my test case (change table name!) on your platform too and inform about the results.
[18 Oct 2005 13:53]
Tim Hancock
Hi, your test was a little complex, so I simplified it and got the same results. CREATE TABLE `TimTest` ( `ID` int(11) NOT NULL auto_increment, `PRINCIPAL_AMOUNT` decimal(20,2) default NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO TimTest (PRINCIPAL_AMOUNT) VALUES (NULL),(1),(2),(NULL) SELECT FORMAT(PRINCIPAL_AMOUNT, 2) FROM TimTest This crashed the database with the following error report: 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=52428800 read_buffer_size=52424704 max_used_connections=2 max_connections=200 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 205303 98 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Number of processes running now: 0 051018 14:49:35 mysqld restarted 051018 14:49:35 [Warning] Changed limits: max_open_files: 65535 max_connections : 200 table_cache: 32662 051018 14:49:35 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051018 14:49:35 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 8 3343489166. InnoDB: Doing recovery: scanned up to log sequence number 8 3343489702 051018 14:49:35 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 287, file name /mysql/data/binarylog.0 00077 051018 14:49:36 InnoDB: Started; log sequence number 8 3343489702 051018 14:49:36 [Note] Recovering after a crash using /mysql/data/binarylog 051018 14:49:36 [Note] Starting crash recovery... 051018 14:49:36 [Note] Crash recovery finished. 051018 14:49:36 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.13-rc-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 33 08 MySQL Community Edition - Standard (GPL)
[18 Oct 2005 14:11]
Valeriy Kravchuk
Now I've got it! It's a perfect duplicate of http://bugs.mysql.com/bug.php?id=13361! That bug is fixed in 5.0.14-rc. Just wait for the release.
