MySQL V5.0 has been out for a while; it addresses most of the major objections people have had about “MySQL is not a ‘real’ database”. With support for XA transactions, triggers, stored procedures and support for views, i think MySQL has the feature set that will enable people to migrate to MySQL.

The only thing that’s still missing is resolution of the InnoDB storage engine problem, and some additional work to get on-line transactional backups if InnoDB goes away. I previously wrote a post about these issues; i’ll dredge it up and re-post it. Innobase is a problem because it’s (currently 12/28/2005) the only transactional storage engine, and since Oracle bought the company that owns InnoDB (Innobase Oy), it’s going to be a licensing challenge until the dust settles. InnoDB is the only choice for applications that require transactions.

As far as the choice of what database to select as a migration candidate, my first …

Depending on your education or where you live AJAX can mean one of at least four things, if you studied classics you will known Ajax from Homer's Iliad, if your in to Football then you might know Ajax of Amsterdam and if you live in the UK and are over 30 you will know Ajax toilet cleaner. For me fitting in all three categories it summons up visions of a football playing Greek hell bent on cleaning the toilets after the match.

Anyway, I recently started a new job as a web/DB developer, the first few weeks have been heavy on the database side, working with some large data sets trying to load and then extract information for a bulk email system (boo hiss you all say). Anyway, I've now been asked to investigate AJAX, which as you may know stands for Asynchronous JavaScript and XML. AJAX isn't a language but a web development technique for building sites which don't need constant refreshing of the sites pages, instead small sections of the …

I just wanted to show case some of the new features in MDB2. I especially wanted to demo some PHP5 specific features. The following example just creates a table with an autoincrementing primary key column 'id', inserts some values, fetches the last inserted id and finally, after selecting those values and outputs them using an iterator, drops the table again ..

I quickly hacked up this example after releasing MDB2 2.0.0RC2 and a number of driver updates. It should work fine with mysql and pgsql. It will not work with sqlite, because automagically determining the types from a result set is not supported by the sqlite API. I have not tried another backend.

<?php

/**************************************/
/* a nice PHP5 only show case of MDB2 */
/**************************************/

require 'MDB2.php';

$dsn = array(
    'phptype'  => 'mysql',
    'username' => 'root',
    'password' => '',
    'hostspec' => …

I just wanted to show case some of the new features in MDB2. I especially wanted to demo some PHP5 specific features. The following example just creates a table with an autoincrementing primary key column 'id', inserts some values, fetches the last inserted id and finally, after selecting those values and outputs them using an iterator, drops the table again ..

I quickly hacked up this example after releasing MDB2 2.0.0RC2 and a number of driver updates. It should work fine with mysql and pgsql. It will not work with sqlite, because automagically determining the types from a result set is not supported by the sqlite API. I have not tried another backend.

<?php

/**************************************/
/* a nice PHP5 only show case of MDB2 */
/**************************************/

require 'MDB2.php';

$dsn = array(
    'phptype'  => 'mysql',
    'username' => 'root',
    'password' => '',
    'hostspec' => …

This is a probably bug in MySQL! It took a little while to catch and it was pretty annoying. I moved over the 'data' directory from another machine and one of the databases had mixed MyISAM/InnoDB tables. The InnoDB tables did not have separate data files, hence I ended up with only .frm files. Since it was development stuff I didn't worry too much about it. My applications broke saying they couldn't read from the table. SHOW TABLES would show that table but all other operations failed. CREATE TABLE said that table already existed and DROP TABLE would say the table doesn't exist! Unless I had write access to my data directory, I'd be in a really painful situation!

Possible fixes:
Better error message
Allow DROP TABLE for zombie tables
Maybe a new CHECK DATABASE operation which checks all tables for such consistency issues.

I took the chance today to order some books from Amazon today to add to the library. Of course I’m still reading 2 current books Spring in Action and the MySQL Certification Study Guide in order to site the second MySQL Professional Certification Exam.

As with most things, you start off looking or reading on the web for something and you end up completely somewhere else. In this case, it was looking at Linux Software Labs (Australia) at the price of their Linux Distribution CD’s, which lead me to the book Beyond Java listed on their site. …

Is it possible to break the referential integrity when a foreign key constraint is set on an InnoDB table? The answer is an (unfortunate?) yes.

A little example demonstrates this:

mysql> CREATE TABLE t1 (
    ->   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ->   ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (id) VALUES
    ->   (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (
    ->   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   id_t1 INT NOT NULL,
    ->   FOREIGN KEY (id_t1) REFERENCES t1(id))
    ->   ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 (id_t1) VALUES
    ->   (3), (2), (2), (3), (1);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0 …

A friend of mine was getting the following error and called me up. After telling him how to fix it, I decided to post it here too. Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) The solution was simple, just start MySQL :)

--
Did you know you can help educate the world through your bookmarks? Join my alpha project featuring social media tools (OpenEncyclopaedia.com) and share your bookmarks while educating the world.

The virtual doors have opened for registration for the 2006 MySQL Users Conference. This annual event is an unmatched opportunity for database developers, DBAs, users, and vendors to gather together and share the latest information on MySQL and open source technology. The theme for the 2006 conference is Discover. Connect. Succeed. Scale Your Business with MySQL. The conference brings over 1,000 MySQL and open source enthusiasts to Santa Clara, California during April 24-27.

As I just told, my new job requires me to do a lot of work with PostgreSQL, which is relatively new to me, since I have done almost all of my database work with MySQL so far.

I also told that this will give me a wider perspective on RDBMS in general and I would like to use this opportunity to discuss about how features are implemented in MySQL and how in PostgreSQL.

Don't worry, this will not end up in a MySQL vs. PostgreSQL fight. As I know MySQL much better than PostgreSQL, I do understand that this position doesn't allow me to make representative judgements on which database system is better (most people would naturally prefer the one they know better). I also believe that both of them are great RDBMS and none of them deserves to be involved in a "A rulez, B suckz" struggle (I love MySQL and I try to fall in love with …