I am rather confused by the hurt-mongering here. I know how to do them, see below, but no idea why? What are they for? create table orders (order_no int not null auto_increment, FK_cust_no int not null, foreign key(FK_cust_no) references customer(cust_no), primary key(order_no)) type=InnoDB; create table orders (order_no int not nul...
I am trying to find out the difficulty of implementing a queue system. I know how to implement a basic queue, so i'll explain a little about what i'm after with some background: I will be implementing a queue where messages will be placed, this will come from several users, the messages will be scheduled to be posted at user defined tim...
I've tried searching on this error and nothing I've found helps me, so I apologize in advance if this is a duplicate and I'm just too dumb to find it. I've created a model with MySQL Workbench and am now attempting to install it to a mysql server. Using File > Export > Forward Engineer SQL CREATE Script... it outputs a nice big file fo...
I've noticed that mysql (5.0.60) often freezes for up to minutes at a time under load, during which time the server is completely non-responsive. I've been able to isolate this to only happening when innotop shows the main thread state as "making checkpoint". What is the server actually doing at this point? ...
In manual it said that InnoDB has row-level locking, so why if I select some of the rows with FOR UPDATE statement it won't let me insert new row into that table? New row shouldn't be locked after all, it wasn't selected. ...
Hi all, I am developing a multi-tenant web application using LAMP. All my user session data is currently stored in mysql with table type InnoDB. Is there any way that I can use MEMORY (used to be HEAP) table type to store the current sessions and use the garbage collector function of the session handler to move sessions between the Inn...
I was just looking at the MySql database created by drupal after I installed it. All the tables are in MyISAM. With a complex software like drupal, wouldn't it make more sense to use foreign keys and hence InnoDB tables to enforce referential integrity? Without foreign keys all the constraint checking will happen at the PHP end. ...
We have MySQL 4 master masters and several read slaves of each master running MySQL. We roll with InnoDB, should we be looking towards XtraDB? We have to be able to average many comments a second which typically trigger 10 inserts and 100 reads. ...
I am developing a web app for which I plan to use InnoDB. However I read that sometimes InnoDB is not enabled by default and, one needs to change mysql config to enable it... Is that true? Since my web app will be installed by client themselves on their own web space, I need to make sure my app is as compatible as possible. If InnoDB is ...
My database experienced some corruption last week, and the technicians from the web hosting company changed all my tables to MyISAM and performed repair on those tables. However, my application requires InnoDB tables. I've tried the method mentioned in this website However, I get this message #1025 - Error on rename of './user_db/#sql...
When recovering a MyISAM table the only file that is strictly needed is the data file (tablename.MYD) - the rest of the files (the index file tablename.MYI and tablename.frm) can be recreated from the data file using REPAIR TABLE. Assume I'm using InnoDB (with the "innodb_file_per_table" setting) instead - what is the minimum set of fil...
The following is my default production MySQL configuration file (my.cnf) for a pure UTF-8 setup with InnoDB as the default storage engine. [server] bind-address=127.0.0.1 innodb_file_per_table default-character-set=utf8 default-storage-engine=innodb The setup does the following: Binds to localhost:3306 (loopback) instead of the defa...
I am redeveloping a web application and its infrastructure originally using SQL Server 2005, ASP.NET & Windows 2003 to a LAMMP (extra M for memcached of course) stack and since the schema is heavily refactored (with very good reason to do so) I must write a custom migration app. The problem is the InnoDB primary + foreign key constrain...
MySQL 4.0 doesn't have information_schema and 'show table status from db' only gives approximate row count for innodb tables. So whats the quickest way to get the count of innodb tables, of course other than count(*), which could be slower with big tables. ...
When changing the content of an InnoDB MySQL table the following files on the file system appears to be involved/changed: /path/to/mysql/data/[database]/[table].ibd (due to innodb_file_per_table) /path/to/mysql/data/data/ib_logfile0 /path/to/mysql/data/data/ib_logfile1 /path/to/mysql/data/data/ibdata1 For each of these files: When ...
When using MyISAM the configuration setting key_buffer_size defines the size of the global buffer where MySQL caches frequently used blocks of index data. What is the corresponding setting for InnoDB? ...
Below is the ammount of rows for 2 tables in my mysql DB 1 is myisam the other innodb, can someone tell me why the innodb one has this ~ in front of the number? These numbers came from phpmyadmin 10,308 MyISAM ~118,011 InnoDB ...
Hi, I need to implement something like a full text search on a couple if index's in a large products table, using innodb, MyISAM is not an option due to its lack of transactions and relationship support. I can think of a couple of ways of doing this, plugin, stored procedure, search table with keys and copied index's in MyIsam format. ...
I'm wondering what are are the pros/cons of the new PBXT Engine vs the good old one InnoDB? Where can I find a list of features and maybe some benchmarks comparing these two DB engines? ...
I have one mysql datatable of type INNODB, it contains about 2M data rows. When I deleted data rows from the table, it did not release alloted disk space. Is there any way to reclaim disk space from mySQL. I am in a bad sitution, this application is running at about 50 different locations and now problem of low disk space is appearing ...