| Bug #18443 | ALTER to TEXT w/o PK deletes fail, ALTER w/ PK both update and deletes fail | ||
|---|---|---|---|
| Submitted: | 22 Mar 2006 23:06 | Modified: | 23 Mar 2006 13:02 |
| Reporter: | Jonathan Miller | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.8 | OS: | Linux (Linux 32 Bit OS) |
| Assigned to: | CPU Architecture: | Any | |
[23 Mar 2006 2:54]
Jonathan Miller
So now this is interesting.
If I update the table to have a PK so that the ALTER to text won't complain about not having a PK to replicate blobs with, then updates to the table also start to fail after the ALTER:
mysql> CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE, PRIMARY KEY(id, t)) ENGINE=NDB PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901), PARTITION p1 VALUES LESS THAN (1946), PARTITION p2 VALUES LESS THAN (1966), PARTITION p3 VALUES LESS THAN (1986), PARTITION p4 VALUES LESS THAN (2005), PARTITION p5 VALUES LESS THAN MAXVALUE);
ALTER TABLE t1 MODIFY vc TEXT;
INSERT INTO t1 VALUES(42,1,'Testing MySQL databases is a cool ',
'Must make it bug free for the customer',
654321.4321,15.21,0,1965,"1905-11-14");
INSERT INTO t1 VALUES(2,1,'Testing MySQL databases is a cool ',
'Must make it bug free for the customer',
654321.4321,15.21,0,1965,"1965-11-14");
INSERT INTO t1 VALUES(4,1,'Testing MySQL databases is a cool ',
'Must make it bug free for the customer',
654321.4321,15.21,0,1965,"1985-11-14");
INSERT INTO t1 VALUES(142,1,'Testing MySQL databases is a cool ',
'Must make it bug free for the customer',
654321.4321,15.21,0,1965,"1995-11-14");
INSERT INTO t1 VALUES(412,1,'Testing MySQL databases is a cool ',
'Must make it bug free for the customer',
654321.4321,15.21,0,1965,"2005-11-14");
INSERT INTO t1 VALUES(422,1,'Testing MySQL databases is a cool ',
'Must make it bug free for the customer',
654321.4321,15.21,0,1965,"2005-11-14");
select id,hex(b1),vc,bc,d,f,total,y,t from t1 order by id;
UPDATE t1 SET b1 = 0, t="2006-02-22" WHERE id = 412;
SELECT id,hex(b1),vc,bc,d,f,total,y,t FROM t1 WHERE id = 412;
DELETE FROM t1 WHERE id = 42;
SELECT COUNT(*) FROM t1;
INSERT INTO t1 VALUES(32,1,'Testing MySQL databases is a cool ',
'Must make it bug free for the customer',
654321.4321,15.21,0,1965,"2005-11-14");
DELETE FROM t1;
Results:
Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+-----+---------+------------------------------------+----------------------------------------+-------------+-------+-------+------+------------+
| id | hex(b1) | vc | bc | d | f | total | y | t |
+-----+---------+------------------------------------+----------------------------------------+-------------+-------+-------+------+------------+
| 2 | 1 | Testing MySQL databases is a cool | Must make it bug free for the customer | 654321.4321 | 15.21 | 0 | 1965 | 1965-11-14 |
| 4 | 1 | Testing MySQL databases is a cool | Must make it bug free for the customer | 654321.4321 | 15.21 | 0 | 1965 | 1985-11-14 |
| 42 | 1 | Testing MySQL databases is a cool | Must make it bug free for the customer | 654321.4321 | 15.21 | 0 | 1965 | 1905-11-14 |
| 142 | 1 | Testing MySQL databases is a cool | Must make it bug free for the customer | 654321.4321 | 15.21 | 0 | 1965 | 1995-11-14 |
| 412 | 1 | Testing MySQL databases is a cool | Must make it bug free for the customer | 654321.4321 | 15.21 | 0 | 1965 | 2005-11-14 |
| 422 | 1 | Testing MySQL databases is a cool | Must make it bug free for the customer | 654321.4321 | 15.21 | 0 | 1965 | 2005-11-14 |
+-----+---------+------------------------------------+----------------------------------------+-------------+-------+-------+------+------------+
6 rows in set (0.05 sec)
ERROR 1296 (HY000): Got error 311 'Unknown error code' from NDBCLUSTER
+-----+---------+------------------------------------+----------------------------------------+-------------+-------+-------+------+------------+
| id | hex(b1) | vc | bc | d | f | total | y | t |
+-----+---------+------------------------------------+----------------------------------------+-------------+-------+-------+------+------------+
| 412 | 1 | Testing MySQL databases is a cool | Must make it bug free for the customer | 654321.4321 | 15.21 | 0 | 1965 | 2005-11-14 |
+-----+---------+------------------------------------+----------------------------------------+-------------+-------+-------+------+------------+
1 row in set (0.04 sec)
ERROR 1296 (HY000): Got error 311 'Unknown error code' from NDBCLUSTER
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set (0.04 sec)
ERROR 1296 (HY000): Got error 311 'Unknown error code' from NDBCLUSTER
[23 Mar 2006 6:17]
Jonas Oreland
This is a duplicate of bug#16976 When using blobs and non native partitioning, the partition id is not set in blob operation casuing 311. TEXT is impl. as blob. Range partitioning is handled by mysqld (not ndbd)
[23 Mar 2006 12:59]
Jonas Oreland
That should be bug#16796
[23 Mar 2006 13:02]
Jonathan Miller
Duplicate of the bug Jonas pointed out. I will add a link to this report to original bug report.
Description: I created a table using a range parition with no PK. After I alter the table changing a varchar column to a text column. This produced an error 1539: | Error | 1539 | Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' | But the alter still went through, so if you do a show create table the varchar is now text. You can insert data, select data and update the data, but you can no longer delete the data from the table. When trying to delete the data you will get the following: ERROR 1296 (HY000): Got error 311 'Unknown error code' from NDBCLUSTER This makes the table unsable. mysql> CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE) ENGINE=NDB PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901), PARTITION p1 VALUES LESS THAN (1946), PARTITION p2 VALUES LESS THAN (1966), PARTITION p3 VALUES LESS THAN (1986), PARTITION p4 VALUES LESS THAN (2005), PARTITION p5 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (0.57 sec) mysql> ALTER TABLE t1 MODIFY vc TEXT; Query OK, 0 rows affected, 1 warning (2.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show warnings -> ; +-------+------+----------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------+ | Error | 1539 | Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' | +-------+------+----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table t1; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL, `b1` bit(8) DEFAULT NULL, `vc` text, `bc` char(255) DEFAULT NULL, `d` decimal(10,4) DEFAULT '0.0000', `f` float DEFAULT '0', `total` bigint(20) unsigned DEFAULT NULL, `y` year(4) DEFAULT NULL, `t` date DEFAULT NULL ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901) ENGINE = ndbcluster, PARTITION p1 VALUES LESS THAN (1946) ENGINE = ndbcluster, PARTITION p2 VALUES LESS THAN (1966) ENGINE = ndbcluster, PARTITION p3 VALUES LESS THAN (1986) ENGINE = ndbcluster, PARTITION p4 VALUES LESS THAN (2005) ENGINE = ndbcluster, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = ndbcluster) | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO t1 VALUES(422,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer', 654321.4321,15.21,0,1965,"2005-11-14"); Query OK, 1 row affected (0.00 sec) mysql> update t1 set d = 0.0 where id = 422; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from t1 where id = 422; ERROR 1296 (HY000): Got error 311 'Unknown error code' from NDBCLUSTER How to repeat: CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t DATE) ENGINE=NDB PARTITION BY RANGE (YEAR(t)) (PARTITION p0 VALUES LESS THAN (1901), PARTITION p1 VALUES LESS THAN (1946), PARTITION p2 VALUES LESS THAN (1966), PARTITION p3 VALUES LESS THAN (1986), PARTITION p4 VALUES LESS THAN (2005), PARTITION p5 VALUES LESS THAN MAXVALUE); ALTER TABLE t1 MODIFY vc TEXT; show warnings; show create table t1; INSERT INTO t1 VALUES(422,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer', 654321.4321,15.21,0,1965,"2005-11-14"); update t1 set d = 0.0 where id = 422; delete from t1 where id = 422; delete from t1;