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
