0

What does it mean when (according to mySQL Workbench) 2 options have option_id = 0?

Just noticed this for unrelated plugin and theme holding very different options.

Update Just noticed over 200 options had id 0 (select * from wordpress.wp_options where option_id = 0) on one test system. What causes this, and could this be a reason transients seem to be deleted early?

  • What's the table structure of wp_options ? Try DESCRIBE wordpress.wp_options The option_id should be a primary key, so I'm surprised that it's not unique. – birgire Dec 17 '15 at 23:03
  • I see option_name has key=UNI, nothing else in the key column. – NoBugs Dec 18 '15 at 1:31
  • On another WP setup I see PRI key, auto_increment. Why do some installs not have that set? – NoBugs Dec 18 '15 at 2:05
  • @Saikat That may have been it. Were you also using Windows 8 and Xampp? – NoBugs Dec 19 '15 at 16:37
  • I have moved the comment into answer. I was using Windows7 and Xampp. @NoBugs – Saikat Dec 19 '15 at 16:38
2

If we check how the wp_options table is created (in 4.4) from the schema.php file, we will find the following:

CREATE TABLE $wpdb->options (
  option_id bigint(20) unsigned NOT NULL auto_increment,
  option_name varchar(191) NOT NULL default '',
  option_value longtext NOT NULL,
  autoload varchar(20) NOT NULL default 'yes',
  PRIMARY KEY  (option_id),
  UNIQUE KEY option_name (option_name)
) $charset_collate;

Here's how this structure looks like for the wp_options table:

> DESCRIBE wp_options;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(191)        | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   |     | yes     |                |
+--------------+---------------------+------+-----+---------+----------------+

If this happened just recently you should check your error logs for PHP, MySQL, NginX/Apache.

Not sure what your MySQL version is, but the recommended version is 5.6+ but should work on 5.0+.

Make sure the table structure of the other core tables is intact as well.

Remember to backup your database before altering the table structure.

1
+50

I faced same type of issue when I imported my database via phpmyadmin. I think, if you have a database backup, then you can try another re-import operation vaia phpmyadmin. That worked for me, but not sure if that works for you.

1

If you import the database on your phpmyadmin. It has an option while importing sql files. Don't use AUTO_INCREMENT for null tables or zero rows. This option just above the go button. Read this checkbox and mark it based on your choice. Than import your database. It won't be the problem. The option_ID is an primary key and it's AUTO_INCREMENT.

If you are using phpmyadmin. Go-to the options table and it's structure view. Than make it primary key and edit the field and check the AUTO_INCREMENT in it. Than your prblem will be solved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.