Mike,
thank you very much for a repeatable bug report!
This revealed an embarrasing bug in InnoDB SELECTs: if you selected with the
full value of a primary key, and the row which matched to it was
delete-marked, then InnoDB could in some cases return the NEXT row.
Since purge cleans off delete-marked records in the background, normally
after a few seconds you would again get the correct result.
I pushed the fix to the 4.0 tree, but am not sure if it makes it to 4.0.5.
The fix will also be in 3.23.54.
Thank you,
Heikki
Innobase Oy
sql query
----- Original Message -----
From: ""Mike W-M"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Wednesday, November 13, 2002 11:07 AM
Subject: Sequence of SQL actions gives wrong query result for 10 seconds!
> All,
>
> I've been having problems with a pretty simple series of SQL statements
run
> against some InnoDB tables returning the wrong results - temporarily!
> Basically, I have one "middle" table that maps a "role" to an "operation"
> (many-to-many), and I have separate role and operation tables that contain
> respective descriptions for the codes. If I remove all the rows for a
> particular "role id" from that middle table and run a SELECT looking for
> that "role" and joining to both other tables to get relevant descriptions,
I
> get erroneous rows back! (The number of rows seems to differ depending
upon
> whether my isolation-level is SERIALIZABLE or READ_COMMITTED.) These
> erroneous rows continue to be returned until about 10 seconds after I've
> committed the transaction, when I receive the correct "Empty set"
response.
> I've managed to come up with a fully repeatable test that I'll include
> below. (I'm not sufficiently familiar with MySQL that I know how to use
the
> "test" script or whatever it is, and I don't think it came with my
Windows'
> binary distribution anyhow. Hopefully I'm providing enough info to make
it
> easy anyway...)
> I'm running mysqlmax 3.23.52 on Win2k. I understand there's a .53 in
> existence but I checked the change-logs and they didn't mention anything
> about a problem like this having been fixed.
> The script follows. After that, there's the mysqladmin variables output
and
> the (not-very-pretty) results I get when running the test - though it
should
> be dead easy for anyone to replicate using the script (as long as it's not
a
> problem with my setup). BEWARE - the script drops / creates tables.
Note
> also that I'm running in ANSI compatible mode. (Oh - one last thing: I
may
> be off track, but I seem to recall that not having primary keys on the
> tables (when I was trying to produce my test-script) affected the
results.)
>
> Any info appreciated! Thanks,
> Mike.
>
> <THE SCRIPT STARTS HERE.....>
> # SET UP THE STARTING POSITION.
> drop table application_role;
> drop table application_role_operation;
> drop table application_operation;
> CREATE TABLE application_role (aro_role_id integer not null, aro_role_desc
> varchar(30) not null, last_updated_by integer null, last_updated datetime
> null, constraint aro_pk primary key (aro_role_id)) type=InnoDB;
> CREATE TABLE application_operation (aop_operation_code varchar(30) not
null,
> aop_operation_desc varchar(30) not null, last_updated_by integer null,
> last_updated datetime null, constraint aop_pk primary key
> (aop_operation_code)) type=InnoDB;
> CREATE TABLE application_role_operation (aro_role_id integer not null,
> aop_operation_code varchar(30) not null, last_updated_by integer null,
> last_updated datetime null, constraint arn_pk primary key (aro_role_id,
> aop_operation_code)) type=InnoDB;
>
>
> begin;
>
> insert into application_role(aro_role_id, aro_role_desc) values
(1000,'test
> 1');
> insert into application_role(aro_role_id, aro_role_desc) values
(1001,'test
> 2');
> insert into application_operation(aop_operation_code, aop_operation_desc)
> values
> ('OP1','Operation 1');
> insert into application_operation(aop_operation_code, aop_operation_desc)
> values
> ('OP2','Operation 2');
> insert into application_role_operation(aro_role_id, aop_operation_code)
> values
> (1000,'OP1');
> insert into application_role_operation(aro_role_id, aop_operation_code)
> values
> (1001,'OP1');
> insert into application_role_operation(aro_role_id, aop_operation_code)
> values
> (1000,'OP2');
>
> commit;
>
> # END OF SETUP. Below are the statements that cause the problem.
>
> begin;
>
> DELETE FROM application_role_operation WHERE aro_role_id=1000 AND
> aop_operation_code='OP1';
>
> DELETE FROM application_role_operation WHERE aro_role_id=1000 AND
> aop_operation_code='OP2';
>
> SELECT * FROM application_role_operation WHERE aro_role_id=1000;
>
> #This is the problem query. It still brings back a row, even though we've
> shown above that there
> #are not rows in application_role_operation that have an aro_role_id of
> 1000.
> SELECT * FROM application_role_operation a, application_role b,
> application_operation c
> WHERE a.aro_role_id = b.aro_role_id
> AND a.aop_operation_code = c.aop_operation_code
> AND a.aro_role_id = 1000
> AND b.aro_role_id = 1000;
>
>
> commit;
>
> #The problem persists past a commit, but only for about 10 seconds or
so!!!!
> begin;
> SELECT * FROM application_role_operation a, application_role b,
> application_operation c
> WHERE a.aro_role_id = b.aro_role_id
> AND a.aop_operation_code = c.aop_operation_code
> AND a.aro_role_id = 1000
> AND b.aro_role_id = 1000;
> <THE SCRIPT ENDS HERE>
>
>
> SETUP INFORMATION......
> mysqld-max-nt Ver 3.23.52-max-nt for NT on i32 (Windows 2000
Professional
> 5.00.2195 Service Pack 2 on a Compaq Evo D510SFF 1.8GHz P4, 1GB Ram.) [I
> think I've also run the non-NT version of the executable against the same
> datafiles.]
>
>
>
> OUTPUT OF mysqladmin variables:
>
+---------------------------------+-----------------------------------------
> ----
> --------------------------------------------------------------------------
--
> ----
> --------------------------------------------------------------------------
--
> +
> | Variable_name | Value
>
>
>
+---------------------------------+-----------------------------------------
> ----
> --------------------------------------------------------------------------
--
> ----
> --------------------------------------------------------------------------
--
> +
> | back_log | 50
>
>
> | basedir | c:\apps\mysqlmax\
>
>
> | bdb_cache_size | 8388600
>
>
> | bdb_log_buffer_size | 0
>
>
> | bdb_home |
>
>
> | bdb_max_lock | 10000
>
>
> | bdb_logdir |
>
>
> | bdb_shared_data | OFF
>
>
> | bdb_tmpdir |
>
>
> | bdb_version | Sleepycat Software: Berkeley DB
3.2.9a:
> (Aug
> ust 12, 2002)
>
> | binlog_cache_size | 32768
>
>
> | character_set | latin1
>
>
> | character_sets | latin1 big5 czech euc_kr gb2312 gbk
sjis
> tis
> 620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish
hebrew
> win1
> 251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257
latin5
> |
> | concurrent_insert | ON
>
>
> | connect_timeout | 5
>
>
> | datadir | c:\apps\mysqldata\
>
>
> | delay_key_write | ON
>
>
> | delayed_insert_limit | 100
>
>
> | delayed_insert_timeout | 300
>
>
> | delayed_queue_size | 1000
>
>
> | flush | OFF
>
>
> | flush_time | 1800
>
>
> | have_bdb | DISABLED
>
>
> | have_gemini | NO
>
>
> | have_innodb | YES
>
>
> | have_isam | YES
>
>
> | have_raid | NO
>
>
> | have_openssl | NO
>
>
> | init_file |
>
>
> | innodb_additional_mem_pool_size | 2097152
>
>
> | innodb_buffer_pool_size | 16777216
>
>
> | innodb_data_file_path | ibdata1
>
>
> | innodb_data_home_dir | c:\apps\mysqldata\innodata
>
>
> | innodb_file_io_threads | 4
>
>
> | innodb_force_recovery | 0
>
>
> | innodb_thread_concurrency | 8
>
>
> | innodb_flush_log_at_trx_commit | 1
>
>
> | innodb_fast_shutdown | ON
>
>
> | innodb_flush_method |
>
>
> | innodb_lock_wait_timeout | 50
>
>
> | innodb_log_arch_dir | c:\apps\mysqldata\innologs
>
>
> | innodb_log_archive | OFF
>
>
> | innodb_log_buffer_size | 8388608
>
>
> | innodb_log_file_size | 5242880
>
>
> | innodb_log_files_in_group | 3
>
>
> | innodb_log_group_home_dir | c:\apps\mysqldata\innologs
>
>
> | innodb_mirrored_log_groups | 1
>
>
> | interactive_timeout | 28800
>
>
> | join_buffer_size | 131072
>
>
> | key_buffer_size | 12288
>
>
> | language | c:\apps\mysqlmax\share\english\
>
>
> | large_files_support | ON
>
>
> | log | ON
>
>
> | log_update | OFF
>
>
> | log_bin | ON
>
>
> | log_slave_updates | OFF
>
>
> | log_long_queries | OFF
>
>
> | long_query_time | 10
>
>
> | low_priority_updates | OFF
>
>
> | lower_case_table_names | 1
>
>
> | max_allowed_packet | 1047552
>
>
> | max_binlog_cache_size | 4294967295
>
>
> | max_binlog_size | 1073741824
>
>
> | max_connections | 100
>
>
> | max_connect_errors | 10
>
>
> | max_delayed_threads | 20
>
>
> | max_heap_table_size | 16777216
>
>
> | max_join_size | 4294967295
>
>
> | max_sort_length | 1024
>
>
> | max_user_connections | 0
>
>
> | max_tmp_tables | 32
>
>
> | max_write_lock_count | 4294967295
>
>
> | myisam_max_extra_sort_file_size | 256
>
>
> | myisam_max_sort_file_size | 2047
>
>
> | myisam_recover_options | 0
>
>
> | myisam_sort_buffer_size | 8388608
>
>
> | named_pipe | OFF
>
>
> | net_buffer_length | 1024
>
>
> | net_read_timeout | 30
>
>
> | net_retry_count | 10
>
>
> | net_write_timeout | 60
>
>
> | open_files_limit | 0
>
>
> | pid_file | c:\apps\mysqldata\VICDEV.pid
>
>
> | port | 3306
>
>
> | protocol_version | 10
>
>
> | record_buffer | 131072
>
>
> | record_rnd_buffer | 131072
>
>
> | query_buffer_size | 0
>
>
> | safe_show_database | OFF
>
>
> | server_id | 1
>
>
> | slave_net_timeout | 3600
>
>
> | skip_locking | ON
>
>
> | skip_networking | OFF
>
>
> | skip_show_database | OFF
>
>
> | slow_launch_time | 2
>
>
> | socket | MySQL
>
>
> | sort_buffer | 65528
>
>
> | sql_mode | 63
>
>
> | table_cache | 4
>
>
> | table_type | MYISAM
>
>
> | thread_cache_size | 0
>
>
> | thread_stack | 65536
>
>
> | transaction_isolation | SERIALIZABLE
>
>
> | timezone | GMT Standard Time
>
>
> | tmp_table_size | 33554432
>
>
> | tmpdir | C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\
>
>
> | version | 3.23.52-max-nt-log
>
>
> | wait_timeout | 28800
>
>
>
+---------------------------------+-----------------------------------------
> ----
> --------------------------------------------------------------------------
--
> ----
> --------------------------------------------------------------------------
--
> +
>
>
>
>
> MYSQL OUTPUT OF RUN (PLUS MANUAL QUERY AFTER 10 SECONDS):
> Query OK, 0 rows affected (0.03 sec)
>
> Query OK, 0 rows affected (0.01 sec)
>
> Query OK, 0 rows affected (0.03 sec)
>
> Query OK, 0 rows affected (0.03 sec)
>
> Query OK, 0 rows affected (0.02 sec)
>
> Query OK, 0 rows affected (0.03 sec)
>
> Query OK, 0 rows affected (0.00 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)
>
> 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, 0 rows affected (0.02 sec)
>
> Query OK, 0 rows affected (0.00 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> Empty set (0.00 sec)
>
>
+-------------+--------------------+-----------------+--------------+-------
> ----
> --+---------------+-----------------+--------------+--------------------+-
--
> ----
> -------------+-----------------+--------------+
> | aro_role_id | aop_operation_code | last_updated_by | last_updated |
> aro_role_i
> d | aro_role_desc | last_updated_by | last_updated | aop_operation_code |
> aop_op
> eration_desc | last_updated_by | last_updated |
>
+-------------+--------------------+-----------------+--------------+-------
> ----
> --+---------------+-----------------+--------------+--------------------+-
--
> ----
> -------------+-----------------+--------------+
> | 1001 | OP1 | NULL | NULL |
> 100
> 0 | test 1 | NULL | NULL | OP1 |
> Operat
> ion 1 | NULL | NULL |
> | 1001 | OP1 | NULL | NULL |
> 100
> 0 | test 1 | NULL | NULL | OP2 |
> Operat
> ion 2 | NULL | NULL |
>
+-------------+--------------------+-----------------+--------------+-------
> ----
> --+---------------+-----------------+--------------+--------------------+-
--
> ----
> -------------+-----------------+--------------+
> 2 rows in set (0.00 sec)
>
> Query OK, 0 rows affected (0.01 sec)
>
> Query OK, 0 rows affected (0.00 sec)
>
>
+-------------+--------------------+-----------------+--------------+-------
> ----
> --+---------------+-----------------+--------------+--------------------+-
--
> ----
> -------------+-----------------+--------------+
> | aro_role_id | aop_operation_code | last_updated_by | last_updated |
> aro_role_i
> d | aro_role_desc | last_updated_by | last_updated | aop_operation_code |
> aop_op
> eration_desc | last_updated_by | last_updated |
>
+-------------+--------------------+-----------------+--------------+-------
> ----
> --+---------------+-----------------+--------------+--------------------+-
--
> ----
> -------------+-----------------+--------------+
> | 1001 | OP1 | NULL | NULL |
> 100
> 0 | test 1 | NULL | NULL | OP1 |
> Operat
> ion 1 | NULL | NULL |
> | 1001 | OP1 | NULL | NULL |
> 100
> 0 | test 1 | NULL | NULL | OP2 |
> Operat
> ion 2 | NULL | NULL |
>
+-------------+--------------------+-----------------+--------------+-------
> ----
> --+---------------+-----------------+--------------+--------------------+-
--
> ----
> -------------+-----------------+--------------+
> 2 rows in set (0.00 sec)
>
> mysql> # Waited 10+ seconds and reran the final query from the script...
> mysql> SELECT * FROM application_role_operation a, application_role b,
> applicati
> on_operation c
> -> WHERE a.aro_role_id = b.aro_role_id
> -> AND a.aop_operation_code = c.aop_operation_code
> -> AND a.aro_role_id = 1000
> -> AND b.aro_role_id = 1000;
> Empty set (0.00 sec)
>
> mysql> # This time the result is as expected!
>
> END OF POST.
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
