High-level transaction isolation architecture of InnoDB
I've noticed that Innodb seems to exhibit true serializability for the serializable transaction isolation level. Does this mean it implements predicate locking? Also out of curiosity, is it possible to set a snapshot isolation transaction isolation level (is Innodb implemented using MVCC)? Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Incorrect key file for table '...'; try to repair it on Innodb table
I'm getting Incorrect key file for table 'stock'; try to repair it for alter table stock add constraint pk_stock primary key (s_w_id, s_i_id);. I can't do repair table on this since it's an innodb table. Googling doesn't turn up any clear answers. Any way to recover from this, or is mysqldump + load data the way to go? -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
EXPLAIN says DEPENDENT SUBQUERY despite no free variables
I have the following query. Note that the nested query has no dependencies on the outer one, yet mysql reports it as dependent. Furthermore, it says the join type is an ALL (nested loop join, the slowest possible one, in which each row of the outer table results in a complete inner table scan), whereas I know that the subquery yields only 50 tuples, so a const join would've made more sense. Any ideas on how to optimize this by convincing mysql to see the independence use a const join? (This is in mysql 5.4.3 beta.) Thanks in advance. mysql explain select thread_id, argument, event_time from general_log where command_type in (Query, Execute) and thread_id in ( select distinct thread_id from general_log where ( (command_type = Init DB and argument like tpcc50) or (command_type = Connect and argument like %tpcc50) ) and thread_id 0 ) order by thread_id, event_time desc; +++-+--+---+--+-+--+---+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +++-+--+---+--+-+--+---+--+--+ | 1 | PRIMARY| general_log | ALL | NULL | NULL | NULL| NULL | 335790898 | 100.00 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | general_log | ALL | NULL | NULL | NULL| NULL | 335790898 | 100.00 | Using where; Using temporary | +++-+--+---+--+-+--+---+--+--+ 2 rows in set, 1 warning (0.04 sec) -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Garbage collecting/trimming ibdata1
I recently tried to run INSERT INTO general_log SELECT * FROM mysql.general_log; but that failed a few hours in because I ran out of disk space. 'SELECT COUNT(*) FROM general_log' returns 0, yet ibdata1 is still 49GB (started at 3GB before the INSERT; the source mysql.general_log, a CSV table, was initially 43GB). I tried TRUNCATE then DROP on general_log, then restarted mysqld, to no avail. From Googling, the only thing that appears remotely relevant to garbage collection is OPTIMIZE TABLE, but I'm not sure how to apply it in this case (now that the table has been dropped). How do I reclaim my disk space? Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Dumping table contents to stdout in tab-separated value format
Hi, I'm interested in piping out the contents of a mysql table to stdout in tab-separated value format, but: - using 'select * into outfile' can't write to stdout. - mysqldump --fields-... requires --tab, which requires an output file path. - I also tried mkfifo /tmp/pipe and select * into outfile '/tmp/pipe', but mysql complains about the file existing already. Is there any other quick way to do this without having to write a client app? Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Duplicate entries despite group by
I have the following table:
CREATE TABLE `graph` (
`tableid1` varchar(20) NOT NULL,
`tupleid1` int(11) NOT NULL,
`tableid2` varchar(20) NOT NULL,
`tupleid2` int(11) NOT NULL,
`node1` int(11) NOT NULL,
`node2` int(11) NOT NULL,
`weight` int(10) NOT NULL,
PRIMARY KEY (`tableid1`,`tupleid1`,`tableid2`,`tupleid2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
and I'm running this query (note the 'group by'):
insert into graph (node1, node2, tableid1, tupleid1, tableid2,
tupleid2, weight)
select 0, 0, a.tableid, a.tupleid, b.tableid, b.tupleid, count(*)
from transactionlog a, transactionlog b
where a.transactionid = b.transactionid
and (a.tableid, a.tupleid) {''} (b.tableid, b.tupleid)
group by a.tableid, a.tupleid, b.tableid, b.tupleid
However, after running for a few hours, the query fails with the
following error:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Duplicate entry 'new_order-248642-order_line-13126643' for key
'group_key'
How is this possible? There were no concurrently running queries
inserting into 'graph'. I'm using mysql-5.4.3; is this a beta
bug/anyone else happen to know something about this? Thanks in
advance.
--
Yang Zhang
http://www.mit.edu/~y_z/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
auto_increment weirdness
Hi, for some reason, I have an auto_increment field that's magically bumped up to the next biggest power of 2 after a big INSERT...SELECT that inserts a bunch of tuples (into an empty table). Is this expected behavior? I couldn't find any mention of this from the docs (using the MySQL 5.4.3 beta). Small reproducible test case: First, generate some data: from bash, run seq 3 /tmp/seq Next, run this in mysql: create table x (a int auto_increment primary key, b int); create table y (b int); load data infile '/tmp/seq' into table y; insert into x (b) select b from y; show create table x; This will show auto_increment = 32768 instead of 3. Is this a bug introduced in the beta? Has it been fixed in newer releases? Couldn't find a mention in the bug database. Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Subquery scoping
I have the following query: select concat(weight, ' ', ids, '\n') from ( select tableid, tupleid, group_concat(id separator ' ') as ids, ( select count(*) from ( select transactionid from transactionlog where (tableid, tupleid, querytype) = (t.tableid, t.tupleid, 'update') group by transactionid having count(*) 0 ) v ) weight from transactionlog t group by tableid, tupleid having weight 0 and count(*) 1 ) u; However, mysql complains about the reference to t from the innermost query: ERROR 1054 (42S22): Unknown column 't.tableid' in 'where clause' Why is this an error? Is this a bug? The MySQL docs on scoping rules don't say anything about this. I was able to suppress the error with this hack rewrite: select concat(weight, ' ', ids, '\n') from ( select tableid, tupleid, group_concat(id separator ' ') as ids, ( select count(distinct transactionid) from transactionlog where transactionid in ( select transactionid from transactionlog where (tableid, tupleid, querytype) = (t.tableid, t.tupleid, 'update') group by transactionid having count(*) 0 ) ) weight from transactionlog t group by tableid, tupleid having weight 0 and count(*) 1 ) u; I'm not sure if this creates an additional unnecessary join, though -- trying to make sense of the output of EXPLAIN has been a separate exercise in frustration all to itself (even with mk-visual-explain). Thanks in advance for any answers. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Complex SQL optimization vs. general-purpose language
Any SQL rewriting gurus know how I might be able to optimize this
query? The schema:
mysql show columns from transactionlog;
+---+---+--+-+-++
| Field | Type | Null |
Key | Default | Extra |
+---+---+--+-+-++
| id| int(11) | NO |
PRI | NULL| auto_increment |
| transactionid | varchar(10) | NO |
MUL | NULL||
| queryid | tinyint(4)| NO |
| NULL||
| tableid | varchar(30) | NO |
MUL | NULL||
| tupleid | int(11) | NO |
| NULL||
| querytype | enum('select','insert','delete','update') | NO |
| NULL||
| schemaname| varchar(20) | YES |
| NULL||
| partition | tinyint(3) unsigned | YES |
| NULL||
+---+---+--+-+-++
8 rows in set (0.04 sec)
The query:
select concat(weight, ' ', ids, '\n')
from (
select
tableid,
tupleid,
group_concat(id separator ' ') as ids,
(
select count(distinct transactionid)
from transactionlog
where transactionid in (
select transactionid
from transactionlog
where (tableid, tupleid, querytype) =
(t.tableid, t.tupleid, 'update')
group by transactionid
having count(*) 0
)
) weight
from transactionlog t
group by tableid, tupleid
having weight 0 and count(*) 1
) u;
This is the output of EXPLAIN and mk-visual-explain:
++++---+---+---+-+---+--+--
+
| id | select_type| table | type | possible_keys |
key | key_len | ref | rows | Extra
|
++++---+---+---+-+---+--+--+
| 1 | PRIMARY| derived2 | ALL | NULL |
NULL | NULL| NULL | 13 |
|
| 2 | DERIVED| t | ALL | NULL |
NULL | NULL| NULL | 68 | Using filesort
|
| 3 | DEPENDENT SUBQUERY | transactionlog | index | NULL |
transactionid | 12 | NULL | 68 | Using where; Using index
|
| 4 | DEPENDENT SUBQUERY | transactionlog | ref | tableid |
tableid | 36 | func,func |2 | Using where; Using
temporary; Using filesort |
++++---+---+---+-+---+--+--+
Table scan
rows 13
+- DERIVED
table derived(t,transactionlog,temporary(transactionlog))
+- DEPENDENT SUBQUERY
+- DEPENDENT SUBQUERY
| +- Filesort
| | +- TEMPORARY
| | table temporary(transactionlog)
| | +- Filter with WHERE
| |+- Bookmark lookup
| | +- Table
| | | table transactionlog
| | | possible_keys tableid
| | +- Index lookup
| | keytransactionlog-tableid
| | possible_keys tableid
| | key_len36
| | reffunc,func
| | rows 2
| +- Filter with WHERE
| +- Index scan
|keytransactionlog-transactionid
|key_len12
|rows 68
+- Filesort
+- Table scan
rows 68
+- Table
table t
That is a lot of work. I can write the equivalent logic in Python
while making a single pass:
results = query(
select tableid, tupleid, transactionid, id, querytype
from transactionlog_2warehouse
)
_tab, _tup = None
ids = []
weight = 0
saw_upd = False
for tab, tup, txn, id, qt in results:
if (_tab, _tup) != (tab, tup):
if len(ids) 1 and weight 0:
print weight, ids
weight = 0
ids = []
_txn = None
if _txn != txn:
saw_upd = False
if qt == 'update' and not saw_upd:
weight += 1
saw_upd = True
ids += [id]
Is it possible to achieve the Python single-pass performance using
pure SQL? Thanks in advance!
--
Yang Zhang
http://www.mit.edu/~y_z
auto_increment without primary key in innodb?
In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL InnoDB memory performance tuning
Hi, I have a fairly small (data dir is 1.2GB) InnoDB database managed by MySQL 5.4.3-beta on an 8-core x86_64 Linux box with 16GB RAM. I'd like to use as much of the memory as possible, but despite specifying (e.g.) --innodb-buffer-pool-size=30, mysql only ever takes up 374M of resident memory (though the virtual memory totals about 3.5GB). Is there another setting that I should consider tweaking which will actually fully utilize the allotted resources? A (very) rough comparison: the hsqldb main-memory Java RDBMS can run jTPCC an order of magnitude faster than mysql. I know hsqldb lacks durability, but only a WAL would be needed, which (with group commits) is characterized by largely sequential writes. Ideally, I can bring mysql to this point -- the database is stored in memory, with only a WAL producing sequential writes to disk, as opposed to incurring random seeks due to buffer page flushes. OTOH, it may be possible that the DB is already entirely in memory, and the performance difference is due entirely to mysql runtime overheads. Either way, explanations/hints would be much appreciated. Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Can't initialize mysqld data dir
I'm trying to get started with running mysqld but I can't set up the data directory. Any hints? Thanks in advance. Details follow: $ uname -a Linux hammer.csail.mit.edu 2.6.27.35-170.2.94.fc10.x86_64 #1 SMP Thu Oct 1 14:41:38 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux $ mysql/bin/mysqld --version mysql/bin/mysqld Ver 5.4.3-beta for unknown-linux-gnu on x86_64 (MySQL Community Server (GPL)) $ cat my.cnf [mysqld] port=3307 datadir=./data-mysql basedir=mysql $ mkdir data-mysql $ mysql/scripts/mysql_install_db --basedir=mysql --datadir=./data-mysql Installing MySQL system tables... 091213 13:45:13 [ERROR] Can't find messagefile '/local-home/yang/packdb/mysql/share/mysql/share/english/errmsg.sys' 091213 13:45:13 [ERROR] Aborting 091213 13:45:13 [Note] Installation of system tables failed! Examine the logs in ./data-mysql/ for more information. You can try to start the mysqld daemon with: shell mysql//bin/mysqld --skip-grant and use the command line tool mysql//bin/mysql to connect to the mysql database and look at the grant tables: shell mysql//bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data-mysql/ that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com/. Please consult the MySQL manual section 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source are the MySQL email archives available at http://lists.mysql.com/. Please check all of the above before mailing us! And remember, if you do mail us, you MUST use the mysql//scripts/mysqlbug script! $ mysql//bin/mysqld --defaults-file=my.cnf --skip-grant 091213 14:04:18 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins. 091213 14:04:18 InnoDB: highest supported file format is Barracuda. 091213 14:04:19 InnoDB Plugin 1.0.4 started; log sequence number 44254 091213 14:04:19 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them 091213 14:04:19 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 091213 14:04:19 [Note] mysql//bin/mysqld: ready for connections. Version: '5.4.3-beta' socket: '/tmp/mysql.sock' port: 3307 MySQL Community Server (GPL) $ mysql//bin/mysql -u root mysql -e 'show tables;' -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
