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:
None 
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
Description:
We are having a problem where MySQL reports "Lost connection to mysql during query" in MySQL Query Browser and no results via JDBC. 

The problem only occurs when the SELECT field list contains a FORMAT(ColumnName, 2) whose column value is NULL.

How to repeat:
The SELECT statement is as follows:

SELECT
FORMAT(PRINCIPAL_AMOUNT, 2) AS PRINCIPAL_AMOUNT
FROM EU_Issues
WHERE CIRC_ISIN_KEY = 'XS0229511901000'

which returns no results. If we change the SELECT statement to:

SELECT
PRINCIPAL_AMOUNT
FROM EU_Issues
WHERE CIRC_ISIN_KEY = 'XS0229511901000'

we do get results and the field value of PRINCIPAL_AMOUNT is NULL.

However, replacing the field name with the value NULL does returns the expected NULL result. eg.

SELECT FORMAT(NULL, 2) AS PRINCIPAL_AMOUNT
[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.