sheepy.org - sheepy goings on...

Can anyone explain this behaviour ?

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.30-2-log | 
+--------------+
1 row in set (0.00 sec)

mysql> create database foo;
Query OK, 1 row affected (0.01 sec)
mysql> use foo;
Database changed
mysql> CREATE TABLE test ( 
   id bigint(20) NOT NULL AUTO_INCREMENT,
   stuff varchar(10) DEFAULT NULL,
   PRIMARY KEY (id)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW CREATE TABLE test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stuff` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test (id, stuff) VALUES (-1,'Hello!');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM test;
+----+--------+
| id | stuff  |
+----+--------+
| -1 | Hello! | 
+----+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO test (id, stuff) VALUES (0,'Hello!');
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

mysql> SHOW CREATE TABLE test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stuff` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

We have found a workaround (sort of):

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE test (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   stuff varchar(10) DEFAULT NULL,
   PRIMARY KEY (`id`) 
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO test (id, stuff) VALUES (-1,'Hello!');
Query OK, 1 row affected (0.03 sec)

mysql> SHOW CREATE TABLE test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stuff` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test (id, stuff) VALUES (0,'Hello!');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test;
+----+--------+
| id | stuff  |
+----+--------+
| -1 | Hello! | 
|  1 | Hello! | 
+----+--------+
2 rows in set (0.00 sec)

For those that missed it, the difference is the backticks in the CREATE TABLE line.
Also I've yet to test with other versions.

Update

Looks like this is a bug that was fixed in 5-1-31

http://bugs.mysql.com/bug.php?id=41841
http://bugs.mysql.com/bug.php?id=36411

Some people are just too transparent :)

05:23 -|- MyName [n=MyName@********************] has joined #mySql
05:23 <MyName> if i have a table of 3 columns, and 3 rows, and i do a cartesian
product of the table with itself, do i get 6 columns and 9 rows?
05:24 <MyName> if the table originally have column names (A B C), do i get ( A
B C A B C) as the column names after the cartesian product?
05:24 <MyName> then all the entries are just each entry of the original tables
with each of the entreis again?
05:25 <MyName> or do i eliminate any duplicate columns?

... after no response for some time

06:21 <MyName> hello?
06:22 <pc_> hello
06:22 <Gm4n> hello!
06:27 <MyName> ok
06:27 <MyName> if i have two tables , one is movies(title, studio), and the other is stars(name, title)
06:28 <MyName> i want to find all stars who played in ALL movies by studio "ABC" for example
06:28 <MyName> not all stars who played in movies by studio ABC, but all stars who played in ALL movies by ABC
06:28 <pc_> MyName, when is it due?
06:28 <MyName> tomorrow
06:28 <pc_> lol
06:28 <Gm4n> haha


Another one I've just added to my feedlist,

http://blog.kovyrin.net/

some interesting stuff, especially on the MySQL front.

He is also the author of the MySQL Master-Master Replication Manager
which can be found here:

http://code.google.com/p/mysql-master-master/

definitely something I'll be looking at.

Here is a quick guide to getting MySQL installed on Debian Etch without pulling
in anything from testing or unstable.

Now there are a number of different ways you could do this but I wanted a debian
package (as I have banned installing anything from source on new servers at work).

After some google action I found that Norbert Tretkowski had submitted packages to experimental
However after checking these out they wanted to pull things in from testing and/or unstable and I didn't want
that (especially considering it wanted to upgrade libc6)

That said it turned out that this package would build fine on Etch by doing the following:

Add the following to /etc/apt/sources.list:

deb http://ftp.uk.debian.org/debian/ experimental main contrib non-free
deb-src http://ftp.uk.debian.org/debian/ experimental main contrib non-free


Then create /etc/apt/preferences with the following in:

Package: *
Pin: release o=Debian,a=stable
Pin-Priority: 900
Package: *
Pin: release o=Debian
Pin-Priority: -1

which basically says "only install stuff from stable".

Next we get the dev tools and the build dependencies:

aptitude install dpkg-dev devscripts
apt-get build-dep mysql-server-5.1
apt-get source mysql-server-5.1


Then build the .debs:

cd mysql-dfsg-5.1-5.1.24rc
debuild


Once that has finished get the run time deps:

aptitude install libdbd-mysql-perl libdbi-perl


And finally install the .debs we created earlier:

cd ..
dpkg -i *.deb


...and there you go, MySQL 5.1 on Debian Etch :)

Database monitoring is a must if unless you like living on the edge.

Here are some tools to help monitor MySQL so you don't get any nasty surprises:

The MySQL Graph Collection for cacti

Cacti is a great tool for monitoring, giving you pretty
graphs allowing you to easily spot sudden and long term trends.

It takes some setting up but well worth it.

I do not currently know how much extra load this will put on a database but I don't expect
it to be much. Expect and updated post once I've done some testing.

mytop

mytop is a console based monitoring tool by Jeremy Zawodny
who helps look after Yahoo's MySQL databases.

It is basically a clone of top for MySQL and is useful for seeing, in real time, what is
happening on you MySQL server.

innotop

innotop is much like mytop with but specifically for InnoDB tablespace. It can show loads of things
such as
transactions,
deadlocks and
statistics

MySQL Report

This tool does nothing other than take the output of SHOW STATUS and puts it into a more readable form.
The main thing is not necessarly the tool itself but the documentation
that goes through a sample report and explains it line by line.

This tool is extreamely use for for quickly diagnosing problems related to high load on a database.

Other Notes

You really should monitor the size of your InnoDB table space and also individual table sizes.
Its often the case that tables which grow rapidly contain redundant or old data eating up space
and reducing the performance of the server.

I have yet to find a nice script that will do this for you, especially when you have 100s or 1000s of tables,
although it shouldn't be that tricky to hack one together.