Bug #26080 Memory Storage engine not working properly
Submitted: 5 Feb 2007 12:34 Modified: 15 Mar 2007 13:23
Reporter: Popescu Cosmin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S2 (Serious)
Version:MySQL Server: 5.1.14-beta-community-nt-l OS:Windows (Windows XP Home Edition, Linux)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: alter, Memory, table

[5 Feb 2007 12:34] Popescu Cosmin 
Description:
I have a memory table with the following structure: 

+------------------+---------------+------+-----+---------------------+----------------+
| Field            | Type          | Null | Key | Default             | Extra          |
+------------------+---------------+------+-----+---------------------+----------------+
| id_fisa_cont     | bigint(20)    | NO   | PRI | NULL                | auto_increment |
| data             | datetime      | YES  | MUL | 0000-00-00 00:00:00 |                |
| numar            | varchar(100)  | YES  |     |                     |                |
| suma             | double(19,7)  | YES  |     | 0.0000000           |                |
| din_care         | double(19,7)  | YES  |     | 0.0000000           |                |
| suma_achitata    | double(19,7)  | YES  |     | 0.0000000           |                |
| termen           | datetime      | YES  |     | 0000-00-00 00:00:00 |                |
| data_inchidere   | datetime      | YES  |     | 0000-00-00 00:00:00 |                |
| zile             | mediumint(5)  | YES  |     | 0                   |                |
| id_client        | bigint(20)    | YES  | MUL | -1                  |                |
| reserved         | varchar(100)  | YES  | MUL |                     |                |
| ts               | timestamp     | NO   |     | CURRENT_TIMESTAMP   |                |
| id_factura       | bigint(20)    | YES  |     | -1                  |                |
| id_factura_plata | bigint(20)    | YES  |     | -1                  |                |
| banca            | varchar(3)    | YES  |     |                     |                |
| curs             | float(9,3)    | YES  |     | 0.000               |                |
| fise_casare      | varchar(5000) | YES  |     |                     |                |
+------------------+---------------+------+-----+---------------------+----------------+

I inserted a row in it, I altered the table (the NUMAR column) and all the data was messed up. 

How to repeat:
mysql>  CREATE TABLE `fisa_cont` (
    `id_fisa_cont` bigint(20) NOT NULL AUTO_INCREMENT,
    `data` datetime DEFAULT '0000-00-00 00:00:00',
    `numar` varchar(100) DEFAULT '',
    `suma` double(19,7) DEFAULT '0.0000000',
    `din_care` double(19,7) DEFAULT '0.0000000',
    `suma_achitata` double(19,7) DEFAULT '0.0000000',
    `termen` datetime DEFAULT '0000-00-00 00:00:00',
    `data_inchidere` datetime DEFAULT '0000-00-00 00:00:00',
    `zile` mediumint(5) DEFAULT '0',
    `id_client` bigint(20) DEFAULT '-1',
    `reserved` varchar(100) DEFAULT '',
    `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `id_factura` bigint(20) DEFAULT '-1',
    `id_factura_plata` bigint(20) DEFAULT '-1',
    `banca` varchar(3) DEFAULT '',
    `curs` float(9,3) DEFAULT '0.000',
    `fise_casare` varchar(5000) DEFAULT '',
    PRIMARY KEY (`id_fisa_cont`),
    UNIQUE KEY `id_fisa_cont` (`id_fisa_cont`),
    KEY `data` (`data`),
    KEY `id_client` (`id_client`),
    KEY `reserved` (`reserved`)
  ) ENGINE=MEMORY DEFAULT CHARSET=latin1;

mysql> insert into fisa_cont(data, numar, suma, din_care, suma_achitata, termen, data_inchidere, zile, id_client, reserved, id_factura, id_factura_plata, banca, curs, fise_casare) values(now(), '99123499', 1000, 1000, 0, now(), now(), 0, -1, '00003root#192.168.0.3', -1, -1, 'BNC', 2.5, '');
Query OK, 1 row affected (0.00 sec) 
mysql> select * from fisa_cont;
+--------------+---------------------+----------+--------------+--------------+---------------+---------------------+---------------------+------+-----------+-----------------------+---------------------+------------+------------------+-------+-------+-------------+
| id_fisa_cont | data                | numar    | suma         | din_care     | suma_achitata | termen              | data_inchidere      | zile | id_client | reserved              | ts                  | id_factura | id_factura_plata | banca | curs  | fise_casare |
+--------------+---------------------+----------+--------------+--------------+---------------+---------------------+---------------------+------+-----------+-----------------------+---------------------+------------+------------------+-------+-------+-------------+
|            1 | 2007-02-05 14:28:52 | 99123499 | 1000.0000000 | 1000.0000000 |     0.0000000 | 2007-02-05 14:28:52 | 2007-02-05 14:28:52 |    0 |        -1 | 00003root#192.168.0.3 | 2007-02-05 14:28:52 |         -1 |               -1 | BNC   | 2.500 |             |
+--------------+---------------------+----------+--------------+--------------+---------------+---------------------+---------------------+------+-----------+-----------------------+---------------------+------------+------------------+-------+-------+-------------+
mysql> select * from fisa_cont where reserved = '00003root#192.168.0.3';
+--------------+---------------------+----------+--------------+--------------+---------------+---------------------+---------------------+------+-----------+-----------------------+---------------------+------------+------------------+-------+-------+-------------+
| id_fisa_cont | data                | numar    | suma         | din_care     | suma_achitata | termen              | data_inchidere      | zile | id_client | reserved              | ts                  | id_factura | id_factura_plata | banca | curs  | fise_casare |
+--------------+---------------------+----------+--------------+--------------+---------------+---------------------+---------------------+------+-----------+-----------------------+---------------------+------------+------------------+-------+-------+-------------+
|            1 | 2007-02-05 14:28:52 | 99123499 | 1000.0000000 | 1000.0000000 |     0.0000000 | 2007-02-05 14:28:52 | 2007-02-05 14:28:52 |    0 |        -1 | 00003root#192.168.0.3 | 2007-02-05 14:28:52 |         -1 |               -1 | BNC   | 2.500 |             |
+--------------+---------------------+----------+--------------+--------------+---------------+---------------------+---------------------+------+-----------+-----------------------+---------------------+------------+------------------+-------+-------+-------------+
mysql> alter table fisa_cont modify column numar varchar(150) default '';
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0
(Notice the resul - 0 rows affected, although, there is one row in the table)
mysql> select suma from fisa_cont;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| suma                                                                                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 127947429882512240000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.0000000 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[5 Feb 2007 13:19] Sveta Smirnova 
Thank you for the report.

Verified as described using last development sources.
[5 Feb 2007 13:20] Sveta Smirnova 
test case

Attachment: bug26080.test (application/octet-stream, text), 1.58 KiB.
[1 Mar 2007 11:18] Ingo Strüwing 
The patch I approve is in http://lists.mysql.com/commits/20750
[1 Mar 2007 11:19] Ingo Strüwing 
Switched to "in review" for Ramil, btw.
[14 Mar 2007 8:54] Sergey Vojtovich 
Fixed in 5.1.17.
[15 Mar 2007 8:55] Sergey Vojtovich 
ChangeSet@1.2439, 2007-02-28 14:27:19+04:00, svoj@mysql.com +3 -0
  BUG#26080 - Memory Storage engine not working properly
  
  Extending varchar column length with ALTER TABLE may result in unusable
  memory table.
  
  The problem is that we use fast ALTER TABLE in this case, which is not
  supported by now.
  
  This is fixed by refusing fast ALTER TABLE when extending varchar column.
  In other words force copy of a table during ALTER TABLE.
  
  Affects MEMORY tables in 5.1 only.
[15 Mar 2007 13:23] Paul DuBois 
Noted in 5.1.17 changelog.