Yes, it is correct: NO ACTION: […] InnoDB rejects the delete or update operation for the parent table. RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause. […] Apparently NO ACTION and RESTRICT are synonymous. Additionally, … Read more
It can’t work that way. Consider: program one, you open a transaction and insert into a table FOO which has an autoinc primary key (arbitrarily, we say it gets 557 for its key value). Program two starts, it opens a transaction and inserts into table FOO getting 558. Program two inserts into table BAR which … Read more
Along with the general phasing out of MyISAM, InnoDB full-text search (FTS) is finally available in MySQL 5.6.4 release. Lots of juicy details at https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html. While other engines have lots of different features, this one is InnoDB, so it’s native (which means there’s an upgrade path), and that makes it a worthwhile option.
If you use SHOW CREATE TABLE, you have to parse the engine out of the query. Selecting from the INFORMATION_SCHEMA database is poor practice, as the devs reserve the right to change its schema at any time (though it is unlikely). The correct query to use is SHOW TABLE STATUS – you can get information … Read more
try this. There are in general three ways to use mysqldump— in order to dump a set of one or more tables, shell> mysqldump [options] db_name [tbl_name …] a set of one or more complete databases shell> mysqldump [options] –databases db_name … or an entire MySQL server—as shown here: shell> mysqldump [options] –all-databases
At my job, we use UUID as PKs. What I can tell you from experience is DO NOT USE THEM as PKs (SQL Server by the way). It’s one of those things that when you have less than 1000 records it;s ok, but when you have millions, it’s the worst thing you can do. Why? … Read more
On demand, now as an answer… When using MySQL Query Browser or phpMyAdmin, it appears that a new connection is opened for each query (bugs.mysql.com/bug.php?id=8280), making it neccessary to write all the drop statements in one query, eg. SET FOREIGN_KEY_CHECKS=0; DROP TABLE my_first_table_to_drop; DROP TABLE my_second_table_to_drop; SET FOREIGN_KEY_CHECKS=1; Where the SET FOREIGN_KEY_CHECKS=1 serves as an … Read more
InnoDB and MYISAM, are storage engines for MySQL. These two differ on their locking implementation: InnoDB locks the particular row in the table, and MyISAM locks the entire MySQL table. You can specify the type by giving MYISAM OR InnoDB while creating a table in DB.
Here is a more complete answer with regard to InnoDB. It is a bit of a lengthy process, but can be worth the effort. Keep in mind that /var/lib/mysql/ibdata1 is the busiest file in the InnoDB infrastructure. It normally houses six types of information: Table Data Table Indexes MVCC (Multiversioning Concurrency Control) Data Rollback Segments … Read more
Just to put a finer point on several other answers here, since in my experience, those looking at questions like this are in the same boat we were, we’ve all heard that indexing Boolean fields is pointless, and yet… We have a table with about 4 million rows, only about 1000 or so at a … Read more