Skip to content

Primary key in Mysql doubled

Got a nice problem: a table, which has the following primary key: id, id.

You can use or dump this table without problems ... but you cannot reinsert the dump into the same (4.1) or a newer (5.0) mysql version.

This is the table dump, created with "SHOW CREATE TABLE tablename":

----- cut -----
CREATE TABLE tablename (
id int(11) NOT NULL auto_increment,
title_en text,
...
PRIMARY KEY (id,id)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
----- cut -----

Now i try to reinsert this table into another database:

----- cut -----
mysql> CREATE TABLE tablename (
-> id int(11) NOT NULL auto_increment,
-> title_en text,
...
-> PRIMARY KEY (id,id)
-> ) TYPE=MyISAM DEFAULT CHARSET=latin1;
ERROR 1060 (42S21): Duplicate column name 'id'
----- cut -----

What the ...


Should i repeat myself?

  • Twitter
  • Bookmark Primary key in Mysql doubled at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Primary key in Mysql doubled
  • Bloglines Primary key in Mysql doubled
  • Technorati Primary key in Mysql doubled
  • Fark this: Primary key in Mysql doubled
  • Bookmark Primary key in Mysql doubled at YahooMyWeb
  • Bookmark Primary key in Mysql doubled at Furl.net
  • Bookmark Primary key in Mysql doubled at reddit.com
  • Bookmark Primary key in Mysql doubled at blinklist.com
  • Bookmark Primary key in Mysql doubled at Spurl.net
  • Bookmark Primary key in Mysql doubled at Simpy.com
  • Bookmark Primary key in Mysql doubled at blogmarks
  • Bookmark Primary key in Mysql doubled with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Pitfalls of missing transactions when handling multiple inserts in Mysql

Imagine, you have a table and a colum is defined NOT NULL. You (and i) expect, that NULL arguments will not go into the table but instead get rejected.


----- cut -----

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.41-log Source distribution

mysql> CREATE TABLE notnulltest (id INT, test TEXT NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO notnulltest (id, test) VALUES (1, NULL);
ERROR 1048 (23000): Column 'test' cannot be null
mysql> INSERT INTO notnulltest (id, test) VALUES (1, NULL), (2, NULL);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2

mysql> SELECT id, test FROM notnulltest;
+------+------+
| id | test |
+------+------+
| 1 | |
| 2 | |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT id, test FROM notnulltest WHERE test IS NULL;
Empty set (0.00 sec)
----- cut -----


So whats going on here?


Since Mysql had no transactions (in the past), it tries to avoid any error in multi-column inserts. Without transactions, the insert cannot be rolled back, so an error would leave an unknown number of inserted rows around and the application had to deal with the problem. For Mysql it seems a far better solution to modify your data so it fit's the requirements.


Worth mentioning, this is the default behaviour in a recent (5.0.41) mysql version, installed from source, with no config modifications.

  • Twitter
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Pitfalls of missing transactions when handling multiple inserts in Mysql
  • Bloglines Pitfalls of missing transactions when handling multiple inserts in Mysql
  • Technorati Pitfalls of missing transactions when handling multiple inserts in Mysql
  • Fark this: Pitfalls of missing transactions when handling multiple inserts in Mysql
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at YahooMyWeb
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at Furl.net
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at reddit.com
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at blinklist.com
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at Spurl.net
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at Simpy.com
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql at blogmarks
  • Bookmark Pitfalls of missing transactions when handling multiple inserts in Mysql with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

This years april fool

Too pity, this news is only an april fool:


Postgres Storage Engine for MySQL, Stranger then Fiction


Imagine, which problems could be solved in an instance ...

  • Twitter
  • Bookmark This years april fool at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg This years april fool
  • Bloglines This years april fool
  • Technorati This years april fool
  • Fark this: This years april fool
  • Bookmark This years april fool at YahooMyWeb
  • Bookmark This years april fool at Furl.net
  • Bookmark This years april fool at reddit.com
  • Bookmark This years april fool at blinklist.com
  • Bookmark This years april fool at Spurl.net
  • Bookmark This years april fool at Simpy.com
  • Bookmark This years april fool at blogmarks
  • Bookmark This years april fool with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

NULL handling in Mysql

Here is an interesting article about the NULL (mis)handling in Mysql:


http://www.databasejournal.com/features/mysql/article.php/3519116

  • Twitter
  • Bookmark NULL handling in Mysql at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg NULL handling in Mysql
  • Bloglines NULL handling in Mysql
  • Technorati NULL handling in Mysql
  • Fark this: NULL handling in Mysql
  • Bookmark NULL handling in Mysql at YahooMyWeb
  • Bookmark NULL handling in Mysql at Furl.net
  • Bookmark NULL handling in Mysql at reddit.com
  • Bookmark NULL handling in Mysql at blinklist.com
  • Bookmark NULL handling in Mysql at Spurl.net
  • Bookmark NULL handling in Mysql at Simpy.com
  • Bookmark NULL handling in Mysql at blogmarks
  • Bookmark NULL handling in Mysql with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

f* Mysql

Today I woke up because the Nagios used by our isp sent me a sms: something does not work well on one of the association servers. According to the sms, the webserver was not responding. Ok, the webserver is never the cause of the problem, something else is going wrong.

 

Logged in over ssh, at least this works well. ps told me that the nightly database backup is hanging around, a script which fills some tables is also waiting. Mysql shell does not really work. Disc space? Ok, enough free space.

 

Shutting down webserver and database, killed all scripts hanging around and restarted the db. After a while, I got an error mail:

 

 

xxxxxxx.xxxxxx_gaestebuch
warning  : Table is marked as crashed and last repair failed
warning  : Size of indexfile is: 281307136      Should be: 267612160
error    : Found 5174283 keys of 5174282
error    : Corrupt

 

Nice ...

Ok, Let's repair the beast. myisamchk seems to work his way through the table, until I got this message:

 

myisamchk: warning: 301 records have been removed

 

So this wannabe database just deleted 301 datasets for whatever reason. Nice to see how data integrity is not handled at all.

 

I'm happy at all that the new platform is coming around, betatest just started yesterday. It's time to kick Mysql in the ass and use a real database.

  • Twitter
  • Bookmark f* Mysql at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg f* Mysql
  • Bloglines f* Mysql
  • Technorati f* Mysql
  • Fark this: f* Mysql
  • Bookmark f* Mysql at YahooMyWeb
  • Bookmark f* Mysql at Furl.net
  • Bookmark f* Mysql at reddit.com
  • Bookmark f* Mysql at blinklist.com
  • Bookmark f* Mysql at Spurl.net
  • Bookmark f* Mysql at Simpy.com
  • Bookmark f* Mysql at blogmarks
  • Bookmark f* Mysql with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Mysql Limitations

You will find Casey Allen Shobe's site with limitations of mysql very interesting:


http://casey.shobe.info/mysql_limitations.html


Oh, of course only, if you are not a mysql nerd ;-)

  • Twitter
  • Bookmark Mysql Limitations at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Mysql Limitations
  • Bloglines Mysql Limitations
  • Technorati Mysql Limitations
  • Fark this: Mysql Limitations
  • Bookmark Mysql Limitations at YahooMyWeb
  • Bookmark Mysql Limitations at Furl.net
  • Bookmark Mysql Limitations at reddit.com
  • Bookmark Mysql Limitations at blinklist.com
  • Bookmark Mysql Limitations at Spurl.net
  • Bookmark Mysql Limitations at Simpy.com
  • Bookmark Mysql Limitations at blogmarks
  • Bookmark Mysql Limitations with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca