High-level transaction isolation architecture of InnoDB

[Yang Zhang]

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

[Yang Zhang]

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

[Yang Zhang]

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

[Yang Zhang]

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

Duplicate entries despite group by

[Yang Zhang]

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

Dumping table contents to stdout in tab-separated value format

[Yang Zhang]

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

auto_increment weirdness

[Yang Zhang]

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

Complex SQL optimization vs. general-purpose language

[Yang Zhang]

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|  | 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
  

Subquery scoping

[Yang Zhang]

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

Re: auto_increment without primary key in innodb?

[Yang Zhang]

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  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" 
> 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

auto_increment without primary key in innodb?

[Yang Zhang]

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

MySQL InnoDB memory performance tuning

[Yang Zhang]

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

[Yang Zhang]

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