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