EDIT: First check, if you did not run out of disk-space, before resolving to the configuration-related resolution. You seem to have a too low maximum size for your innodb_data_file_path in your my.cnf, In this example innodb_data_file_path = ibdata1:10M:autoextend:max:512M you cannot host more than 512MB of data in all innodb tables combined. Maybe you should switch … Read more
MySQL doesn’t reduce the size of ibdata1. Ever. Even if you use optimize table to free the space used from deleted records, it will reuse it later. An alternative is to configure the server to use innodb_file_per_table, but this will require a backup, drop database and restore. The positive side is that the .ibd file … Read more
If your cascading deletes nuke a product because it was a member of a category that was killed, then you’ve set up your foreign keys improperly. Given your example tables, you should have the following table setup: CREATE TABLE categories ( id int unsigned not null primary key, name VARCHAR(255) default null )Engine=InnoDB; CREATE TABLE … Read more
I was a bit annoyed by the lack of benchmarks for this online, so I ran a test myself. Note though that I don’t do it on a regular basic, so please check my setup and steps for any factors that could have influenced the results unintentionally, and post your concerns in comments. The setup … Read more
The main differences between InnoDB and MyISAM (“with respect to designing a table or database” you asked about) are support for “referential integrity” and “transactions”. We choose InnoDB if we need the database to enforce foreign key constraints or support transactions (i.e. changes made by two or more DML operations handled as single unit of … Read more
What gives this away is the word transaction. It is evident by the statement that the query was attempting to change at least one row in one or more InnoDB tables. Since you know the query, all the tables being accessed are candidates for being the culprit. From there, you should be able to run … Read more
Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) to retrieve all the MyISAM tables in your database. Replace value of the name_of_your_db variable with your database name. SET @DATABASE_NAME = ‘name_of_your_db’; SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` ENGINE=InnoDB;’) AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND `ENGINE` = ‘MyISAM’ … Read more
SHOW TABLE STATUS WHERE Name=”xxx” This will give you (among other things) an Engine column, which is what you want.
I found the generated set of drop statements useful, and recommend these tweaks: Limit the generated drops to your database like this: SELECT concat(‘DROP TABLE IF EXISTS `’, table_name, ‘`;’) FROM information_schema.tables WHERE table_schema=”MyDatabaseName”; Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to … Read more
That ibdata1 isn’t shrinking is a particularly annoying feature of MySQL. The ibdata1 file can’t actually be shrunk unless you delete all databases, remove the files and reload a dump. But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow … Read more