Your message dated Mon, 19 Mar 2012 19:53:04 +0000
with message-id <[email protected]>
and subject line closing
has caused the Debian Bug report #277138,
regarding mysql-server: InnoDB query caching returning incorrect values?
to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact [email protected]
immediately.)
--
277138: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=277138
Debian Bug Tracking System
Contact [email protected] with problems
--- Begin Message ---
Package: mysql-server
Version: 4.0.21-7
Severity: important
With InnoDB (AUTOCOMMIT turned off), I'm seeing weird results from SQL
queries across different database threads. What happens is:
1) 2 threads connect to the DB
2) thread #1 does a SELECT on some tables, and prints the results
3) thread #2 does an INSERT onto the tables, and calls COMMIT
4) both threads do a similar SELECT on the tables; thread #1 gets
the correct result (the values inserted into the db in step 3), and
thread #2 gets incorrect results (no values).
The following schema is used:
DROP TABLE IF EXISTS domains;
CREATE TABLE domains (
domain_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
owner INTEGER UNSIGNED NOT NULL,
enabled ENUM('Y','N') NOT NULL DEFAULT 'Y',
PRIMARY KEY (domain_id),
UNIQUE KEY name (name)
) TYPE=InnoDB;
DROP TABLE IF EXISTS domain_access;
CREATE TABLE domain_access (
user_id INTEGER UNSIGNED NOT NULL,
domain_id INTEGER UNSIGNED NOT NULL,
section
ENUM('www','subwww','dns','shell','ftp','mail','db','subdb','net') NOT NULL,
access ENUM('add','view','edit') NOT NULL DEFAULT 'add'
) TYPE=InnoDB;
The ruby script below is used to test the database:
#!/usr/bin/ruby -w
require 'dbi'
DATABASE = 'testing'
USER = 'root'
PASSWORD = ''
DOMAIN = 'test.com'
module DBI
class DatabaseHandle
def insert_id
self.func(:insert_id)
end
end
end
def create_conn
dbh = DBI.connect('dbi:Mysql:' + DATABASE, USER, PASSWORD)
dbh['AutoCommit'] = false
dbh
end
def add_with_template(dbh, domain, template, id)
dbh.do('INSERT INTO domains (name, owner) VALUES (?, ?)', domain, id)
domain_id = dbh.insert_id
dbh.do('INSERT INTO `domain_access` VALUES (?, ?, \'subwww\',
\'edit\')', id, domain_id)
dbh.commit
domain_id
end
def list(dbh, id)
dbh.select_all('SELECT * FROM domains AS dom LEFT JOIN domain_access ON
dom.domain_id=domain_access.domain_id WHERE user_id=? ', id) { |r| }
end
def info(dbh, domain_id, user_id)
dbh.select_all('SELECT * FROM domain_access LEFT JOIN domains ON
domains.domain_id=domain_access.domain_id WHERE domain_access.domain_id=? AND
domain_access.user_id=?', domain_id, user_id) { |r|
return true
}
return 'Unable to find domain!'
end
system("mysql -u #{USER} #{DATABASE} < schema.sql")
dbh = create_conn
dbh2 = create_conn
id = '1'
# Cache values in second database thread
list(dbh2, id)
# Insert/change values in first database thread
child_id = add_with_template(dbh, DOMAIN, 'basic', id)
# Check values in each thread
p info(dbh, child_id, id)
p info(dbh2, child_id, id)
p info(dbh2, child_id, id)
# Create a new connection; it pulls the correct results
dbh3 = create_conn
p info(dbh3, child_id, id)
This script requires schema.sql to exist in the cwd. When this
script is run, the results I see are:
true
"Unable to find domain!"
"Unable to find domain!"
true
Note that if you enable AUTOCOMMIT, or change the table types to MyISAM,
the script succeeds:
true
true
true
true
-- System Information:
Debian Release: 3.1
APT prefers unstable
APT policy: (500, 'unstable'), (500, 'testing'), (1, 'experimental')
Architecture: i386 (i686)
Kernel: Linux 2.6.8-1-k7
Locale: LANG=en_US, LC_CTYPE=en_US
Versions of packages mysql-server depends on:
ii adduser 3.59 Add and remove users and groups
ii debconf 1.4.39 Debian configuration management sy
ii libc6 2.3.2.ds1-18 GNU C Library: Shared libraries an
ii libdbi-perl 1.45-1 The Perl5 Database Interface by Ti
ii libmysqlclient12 4.0.21-7 mysql database client library
ii libssl0.9.7 0.9.7d-5 SSL shared libraries
ii libstdc++5 1:3.3.5-1 The GNU Standard C++ Library v3
ii libwrap0 7.6.dbs-6 Wietse Venema's TCP wrappers libra
ii mailx 1:8.1.2-0.20040524cvs-1 A simple mail user agent
ii mysql-client 4.0.21-7 mysql database client binaries
ii passwd 1:4.0.3-30.2 Change and administer password and
ii perl 5.8.4-2.3 Larry Wall's Practical Extraction
ii psmisc 21.5-1 Utilities that use the proc filesy
ii zlib1g 1:1.2.2-1 compression library - runtime
-- debconf information:
* mysql-server/start_on_boot: true
mysql-server/postrm_remove_databases: false
* mysql-server/mysql_install_db_notes:
mysql-server/nis_warning:
mysql-server/mysql_update_hints1:
* mysql-server/postrm_remove_database: false
--- End Message ---
--- Begin Message ---
Upstream has:
"[20 Oct 2004 15:28] Matthew Lord
I changed the synopsis as this has turned into a feature request to
change the default
isolation level."
Also it is marked not a bug upstream. I don't see this as adding much
value and I'm closing.
--- End Message ---
