Recent Topics
  1. b2evolution CMS Support Forums
  2. Archives
  3. Older bugs
  4. Bugs in versions 1.8.x
  5. plugin can't change primary key

plugin can't change primary key

1 Jan 09, 2007 23:34    

I tried to change a plugin's primary key to an auto-increment ID field. I made the changes to my plugin, incremented the version number, uploaded it, and clicked the 'enable' button for the plugin. It failed. The first screen I saw was this:

The plugin needs the following database changes.

The following database changes will be carried out. If you are not sure what this means, it will probably be alright.

* ALTER TABLE evo_plugin_acrolink_8_Keywords ADD COLUMN ID int(10) unsigned NOT NULL auto_increment FIRST, DROP PRIMARY KEY, ADD PRIMARY KEY (ID)
* ALTER TABLE evo_plugin_acrolink_8_Keywords DROP PRIMARY KEY

I clicked 'Install' and got this:

An unexpected error has occured!

If this error persits, please report it to the administrator.

Go back to home page
Additional information about this error:

MySQL error!

Incorrect table definition; there can be only one auto column and it must be defined as a key(Errno=1075)

Your query:

ALTER TABLE evo_plugin_acrolink_8_Keywords DROP PRIMARY KEY

Looking at the table via phpmyadmin tells me the new field (ID) is created, and that it is the primary key, but it did not remove "COLLATE latin1_general_cs" from the field where I removed that from the plugin.

Returning to the plugins tab and clicking 'enable' again said it was successful, but it still did not remove the "COLLATE latin1_general_cs" bit.

Back to top

2 Jan 14, 2007 19:19

EdB, please post the CREATE TABLE statements you've used before and after the change.
While you're at it, the MySQL server version would be helpful, too.

Back to top

3 Jan 14, 2007 22:01

Original: 

function GetDbLayout() {
	return array(
		"CREATE TABLE IF NOT EXISTS ".$this->get_sql_table('Keywords')." (
			keyword varchar(50) COLLATE latin1_general_cs default '',
			linkurl varchar(255) NOT NULL default '',
			titletag varchar(80) NOT NULL default '',
			PRIMARY KEY (keyword)
			)",
		);
	}


Replaced with: 

function GetDbLayout() {
	return array(
		"CREATE TABLE IF NOT EXISTS ".$this->get_sql_table('Keywords')." (
			ID int(10) unsigned NOT NULL auto_increment,
			keyword varchar(50) default '',
			linkurl varchar(255) NOT NULL default '',
			titletag varchar(80) NOT NULL default '',
			PRIMARY KEY (ID)
			)",
		);
	}

MySQL Version: 4.0.27. phpinfo show some other stuff about mysql but I don't know what else might be valuable so I'll leave it at that.

I got the "collate latin1_general_cs thing from searching the web a bit for a way to make a field not be case insensitive, and it worked for me so I was happy. A bit worried that maybe it won't work everywhere b2evo works, and that became obvious shortly after releasing the plugin. Adding an ID and making it be the primary key (then putting in an 'edit' link next to each keyword) became the solution, but I got the error you see in this thread when I changed the version and tried to enable the plugin again. The solution to this problem was the thing that both you and Yabba showed me about using PluginVersionChanged with an if(version_compare) thing and a temporary table to hold the contents before dropping the old table and replacing it with the new layout.

Back to top

5 Jan 15, 2007 13:13

Cool. When I did that collate thing I had a nagging doubt in the back of my mind because I hadn't seen it in b2evo, but for some reason I was convinced the best way to edit a keyword was to type it in again. In fact that's a horrible way to edit something. Clicking the 'edit' link is much easier for the user, so that's where I'm at now with that plugin.

Hey good news on the improvement. I'm looking forward to 1.9.2 because my 1.8.6 installation is starting to feel old ... ;)

Back to top


Form is loading...