Beware of MySQL export
When developing Joomla! extension, most of us uses PHPMyAdmin to export database for our component. This is a great tool in PHP-MySQL world, thanks to developers. But today I have found some error using PHPMyAdmin, it is not directly related to PHPMyAdmin. But it is becauase of some statement in SQL exported script. I made SQL installation file using script from PHPMyAdmin, by changing table prefix to #__ as usual. Then I made a xml manifest file and zip package. When I install mycomponent, what happen? My backend Components menu, it was destroyed by my component menu.ย My component Submenu becomes main items in Components menu.ย What is the problem.
I sat down and tried to fix for the problem. Is it a problem of XML tag in manifest file? After verification and try to correct what Iย think a cause of problem. Nothing better. Then I went down to see what wrong with it in #__components and then I found the problem!! It is becauase my component menu ID is zero. And Joomla! use parent-child to keep track of menu and submenu. If menu item 's parent is zero then that is a first level menu. Normally menu item with ID zeor doesnot exists in the table, but now my component menu does. So all of them become childs of my component menu item. Then I have to resolve the problem by manually insert my component menu item ID (some value that doesnot exists and not zero), then I change parent ID of submenu of my component to the new entered one. Ok, it come back, all menu items display correctly.
Don't be serious if you did not encounter this error before, I have found what is the cause of problem that is one exported statement in PHPMyAdmin that I didnot delete it.
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
Normally Joomla assign ID to be zero and insert into database, in normal case without this statement, it will change to some auto-increment value. That works fine, but when this statement comes into play, it causes new inserted ID to be zero and this is the cause of problem. So beware of this statement, remove it from your installation script. Unfortunately I didnot capture any image to show you. My environment is Joomla! 1.5.22, PHP 5.2.14 and MySQL 5.0.


Articles