crash with select after adding autoinc pkey

assertion happens here

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

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

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

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

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).

Setting to critical as this is a crashing bug.

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

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)

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

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.

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..

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

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.

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

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.