crash with select after adding autoinc pkey

[Stewart Smith]

create temporary table t1 (v varchar(32) not null);
insert into t1 values ('def'),('abc'),('hij'),('3r4f');
select * from t1;
# Fast alter, no copy performed
alter table t1 change v v2 varchar(32);
select * from t1;
# Fast alter, no copy performed
alter table t1 change v2 v varchar(64);
select * from t1;
update t1 set v = 'lmn' where v = 'hij';
select * from t1;
# Regular alter table
alter table t1 add i int auto_increment not null primary key first;
select * from t1;
update t1 set i=5 where i=3;
select * from t1;
alter table t1 change i i bigint;
select * from t1;
alter table t1 add unique key (i, v);
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
drop table t1;

hits assert on the selecT:

update t1 set i=5 where i=3;
select * from t1;

[Joe Daly (skinny.moey)]

assertion happens here

drizzled: sql_base.cc:1754: Table* Session::open_table(TableList*, bool*, uint32_t): Assertion `!table->auto_increment_field_not_null' failed.

[Jay Pipes (jaypipes)]

Hi Joe!

Thanks for the report. What platform is this on? 32 or 64 bit? Linux?

Also, is it *only* on temporary tables?

Thanks!

Jay

[Joe Daly (skinny.moey)]

This is only on temporary tables, changing the testcase to use a regular table t1 works as expected.

My testing was on openSuse 11.1 64 bit

jdaly@rx7:~> uname -a
Linux rx7 2.6.27.7-9-default #1 SMP 2008-12-04 18:10:04 +0100 x86_64 x86_64 x86_64 GNU/Linux

[Jay Pipes (jaypipes)]

Bipeng Meng noticed a similar problem:

https://lists.launchpad.net/drizzle-discuss/msg04593.html

Can you check to see if you can verify what Bipeng noticed -- that the bug only occurs if and only if the --tmpdir option is supplied when starting drizzled (like it is when starting drizzled via the dtr script).

Thanks much, Joe!

Jay

[Jay Pipes (jaypipes)]

On trunk r1097, 64-bit Linux, I cannot reproduce the bug from Bipeng's email:

drizzle> use test
Database changed
drizzle> create TEMPORARY table t1(id int primary key auto_increment);
Query OK, 0 rows affected (0.01 sec)

drizzle> insert into t1 values (null);
Query OK, 1 row affected (0 sec)

drizzle> insert into t1 values (null);
Query OK, 1 row affected (0.01 sec)

drizzle> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0 sec)

drizzle> alter table t1 auto_increment = 50;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

drizzle> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TEMPORARY TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0 sec)

drizzle> insert into t1 values (null);
Query OK, 1 row affected (0 sec)

drizzle> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 50 |
+----+
3 rows in set (0 sec)

However, I *can* reproduce Joe's bug on r1097 64-bit Linux, so it seems that Bipeng and Joe's behaviour indicate different bugs (possibly related, though).

[Jay Pipes (jaypipes)]

Setting to critical as this is a crashing bug.

[Jay Pipes (jaypipes)]

I just noticed that Innodb was used as the temporary table engine automatically above...going to investigate why this is the case.

[Jay Pipes (jaypipes)]

Verified Bipeng's problem does not occur on 64-bit Linux on trunk r1097:

drizzle> use test
Database changed
drizzle> create TEMPORARY table t1(id int primary key auto_increment) engine=memory;
Query OK, 0 rows affected (0 sec)

drizzle> insert into t1 values (null);
Query OK, 1 row affected (0 sec)

drizzle> insert into t1 values (null);
Query OK, 1 row affected (0 sec)

drizzle> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0 sec)

drizzle> alter table t1 auto_increment = 50;
Query OK, 2 rows affected (0 sec)
Records: 2 Duplicates: 0 Warnings: 0

drizzle> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TEMPORARY TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY AUTO_INCREMENT=50 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0 sec)

drizzle> insert into t1 values (null);
Query OK, 1 row affected (0 sec)

drizzle> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 50 |
+----+
3 rows in set (0 sec)

[Joe Daly (skinny.moey)]

I tried this (bug 385808) on 6.0 mysql branch the same assertion fails.

[Jay Pipes (jaypipes)]

The comment above the auto_increment_field_not_null member in drizzled/table.h is interesting:

  /*
    To indicate that a non-null value of the auto_increment field
    was provided by the user or retrieved from the current record.
    Used only in the MODE_NO_AUTO_VALUE_ON_ZERO mode.
  */
  bool auto_increment_field_not_null;

If the above comment is correct, we need to figure out what the behaviour for Drizzle should be (since we do not have a switchable sql_mode like MySQL 5+ does). If it is determined that the behvaiour of the MODE_NO_AUTO_VALUE_ON_ZERO mode is what Drizzle should have, then we may have a use for this member variable. If not, then it can and should be safely removed from the code base.

I am extremely interested as to why on earth the table's auto_increment_field_not_null value is not being reset to false properly after the final row is updated...I will look into this.

[Stewart Smith (stewart)]

we have NO_AUTO_VALUE_ON_ZERO.... or at least we should do :)

inserting 0 gets you a 0, not an auto-inc value.

that is, unless my memory is faulty or the branch was never merged..

[Stewart Smith (stewart)]

tested on latest tree, still hits assert:
drizzled: sql_base.cc:1520: Table* Session::open_table(TableList*, bool*, uint32_t): Assertion `!table->auto_increment_field_not_null' failed.

#2 0x00007fade5920f09 in *__GI___assert_fail (
    assertion=0x84ac30 "!table->auto_increment_field_not_null",
    file=0x84a669 "sql_base.cc", line=1520,
    function=0x84b2e0 "Table* Session::open_table(TableList*, bool*, uint32_t)") at assert.c:78
#3 0x000000000053ff2e in Session::open_table (this=0x24976e0,
    table_list=0x2921508, refresh=0x7faddf41638f, flags=0) at sql_base.cc:1520
#4 0x00000000005401fb in Session::open_tables_from_list (this=0x24976e0,
    start=0x7faddf4163b0, counter=0x7faddf4163c8, flags=0) at sql_base.cc:2301
#5 0x000000000051531c in Session::open_and_lock_tables (this=0x24976e0,
    tables=0x2921508) at session.cc:2028
#6 0x000000000055b721 in execute_sqlcom_select (session=0x24976e0,
    all_tables=0x2921508) at sql_parse.cc:1373
#7 0x000000000040eaf1 in drizzled::command::Select::execute (this=0x2492130)
    at command/select.cc:29
#8 0x000000000055ea31 in mysql_execute_command (session=0x24976e0)
    at sql_parse.cc:1330
#9 0x000000000055ec34 in mysql_parse (session=0x24976e0,
    inBuf=0x2921390 "select * from t1", length=16,
    found_semicolon=0x7faddf416ff8) at sql_parse.cc:1641

[Brian Aker (brianaker)]

lt-drizzled: drizzled/sql_base.cc:1421: drizzled::Table* drizzled::Session::openTable(drizzled::TableList*, bool*, uint32_t): Assertion `!table->auto_increment_field_not_null' failed.

[Brian Aker (brianaker)]

Basically this is no longer used. We do set it from time to time in the code, but we never make use of the value.

The kicker of this though is that it shows that we don't have a perfect reset of a table object once we reopen (which is not too surprising).

My thoughts? I believe my tree that has the fix in it for a bit of TableShare will fix this. So I am going to push that, then enable the test case I put in for this, test the fix, and then delete auto_increment_field_not_null;

  -Brian

[Brian Aker (brianaker)]

This is the same as bug http://lists.mysql.com/commits/53147 for MySQL. Their solution does not solve the problem if an update one the record is done.