I haven't done much tweaking in the past so this might be relatively easy however I am running into issues. This is what I do: Stop MySQL Edit my.cnf (changing innodb_log_file_size) Remove ib_logfile0/1 Start MySQL Starts fine however all InnoDB tables have the .frm file is invalid error, the status shows InnoDB engine is disabled so...
Hi, I have a table with a single unique VARCHAR(512) field. I want to have another table hold a foreign key reference to this first table. Both tables use InnoDB. If I add a VARCHAR(512) key to the second table and add a foreign key constraint on it will the 512 bytes long data be held twice? If so, is there a way to hold only a refere...
I did this: ALTER TABLE `db`.`my_table` ADD CONSTRAINT FOREIGN KEY (`my_second_table_id`) REFERENCES `my_second_table` (`id`); I should have done this: ALTER TABLE `db`.`my_table` ADD CONSTRAINT FOREIGN KEY (`my_second_table_id`) REFERENCES `my_second_table` (`id`) ON DELETE SET NULL; Is it possible to ALTER this, or do I have...
http://docs.djangoproject.com/en/1.0/ref/databases/#creating-your-tables says: Another option is to use the init_command option for MySQLdb prior to creating your tables: DATABASE_OPTIONS = { "init_command": "SET storage_engine=INNODB", } This sets the default storage engine upon connecting to the database. After your tables have ...
We have a (currently InnoDB) table which contains roughly 500,000 rows. This represents a queue of tasks to run. It is stored in a MySQL database. An a continual basis, at least once per second but sometimes more frequently, we select data from it and subsequently update some rows. Once per day, we prune old rows from the table. We ...
I have a table looking like this: CREATE TABLE `item` ( `id` int(11) NOT NULL auto_increment, `title` varchar(255), `my_number` int(10) unsigned default NULL ); There are a hundreds of thousands of items, and I usually order them by 'my_number'. Will adding an index on 'my_number' increase performance on queries when I order by this ...
Is there any migration procedures or tools to migrate a Database (Liferay database to be exact) from InnoDB to PBXT? ...
Create table returns error Can't create table './Identity/Roles.frm, however it does create Roles.ibd file. CREATE TABLE IF NOT EXISTS `Roles` (`role` VARCHAR(25) NOT NULL , PRIMARY KEY (`role`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 I can create table using MyISAM but not innodb. I also tryed to copy frm+ibd files from anoth...
Hello all, While performing a conditional DELETE operation in one of my InnoDB tables, which apparently required some temporary table to be created in ibdata1, the hard disk filled up and mysql crashed. I couldn't get it to start again until I deleted the ibdata1 file (~30 GB). Now mysql starts again, but all the tables in the database...
I am developing a personal PHP/MySQL app, and I came across this particular scenario in my project: I have various comment threads. This is handled by two tables - 'Comments' and 'Threads', with each comment in 'Comments' table having a 'thread_id' attribute indicating which thread the comment belongs to. When the user deletes a comment...
Hello, I'm trying to insert about 500 million rows of garbage data into a database for testing. Right now I have a PHP script looping through a few SELECT/INSERT statements each inside a TRANSACTION -- clearly this isn't the best solution. The tables are InnoDB (row-level locking). I'm wondering if I (properly) fork the process, will ...
I'm developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly. I was originally planning on using MyISAM for the tables (for the built-in fulltext search capabilities), but the thought of the entire table being locked due to a single write operation m...
One of my clients complained that his database disappeared without any reason. I guess he must have dropped the database by mistake. But I just want to make sure that this can not happen automatically. The ibdata and iblog files are there but the directory (for e.g. company) is missing. ...
I have a big MySQL InnoDB table (about 1 milion records, increase by 300K weekly) let's say with blog posts. This table has an url field with index. By adding new records in it I'm checking for existent records with the same url. Here is how query looks like: SELECT COUNT(*) FROM `tablename` WHERE url='http://www.google.com/'; Curren...
Consider that there is a bunch of tables which link to "countries" or "currencies" tables. For making data easier to read I'd like make CHAR field with country code (eg US, GB, AU) and currency code (USD, AUD) a primary keys in each of those 2 tables and all other tables will use this CHAR as a foregin key. Database is mysql with innod...
In MySQL (particularly InnoDB) is there any way to know which tables were affected (updated / deleted rows) by CASCADE UPDATES / DELETES or regular triggers? ...
How to select the MySQL's engine name of some table. MyISAM or InnoDB. Can we do it by a simple sql query? ...
I had all my tables in myISAM but the table level locking was starting to kill me when I had long running update jobs. I converted my primary tables over to InnoDB and now many of my queries are taking over 1 minute to complete where they were nearly instantaneous on myISAM. They are usually stuck in the Sorting result step. Did I do som...
We are getting a lot of deadlocks during certain queries, and we believe it is due to the gap locking due to the fact that we are inserting lots of rows into this table. If we eliminate the autoincrementing primary key field (since we don't ever use it) ... will this get rid of the gap locking? ...
Here's some background info. I have three MySQL tables (all InnoDB). The first table is used to store images records. The second table is used to store tags (to tag the images with. OMG). The third table is used to store relationships between the images and the tags. Yippee. Here's the structure of the tables. CREATE TABLE `images` ( ...