Tag Info

Hot answers tagged

9

The reason is that that some statements, like CREATE TABLE cause an implicit commit. You can read about them in the documentation: Statements That Cause an Implicit Commit. So the original sequence of statements: START TRANSACTION SHOW TABLES LIKE customers CREATE TABLE `customers__20150119_14_08_20` LIKE `customers` INSERT INTO ...


4

Query 1, tested in SQLFiddle-1 SET @ward_id_to_check = 1 ; SELECT st.patient_id, st.bed_id AS starting_bed_id, -- the first bed a patient uses -- can be omitted st.admitted, MIN(en.discharged) AS discharged FROM ( SELECT patient_id, bed_id, admitted, discharged FROM t WHERE ...


3

It looks like the order of the statements is causing the problem. In my old post row locking within ACID transaction innodb, I named 12 statements that break a transaction intermittently. In your particular case, it was the CREATE TABLE statement. Once you ran CREATE TABLE inside a START TRANSACTION ... COMMIT/ROLLBACK block, there was no framework to ...


2

A bit late to the game...but here's a quite comprehensive post I wrote a few months back, detailing the major differences between MYISAM and InnoDB. Grab a cuppa (and maybe a biscuit), and enjoy. The major difference between MyISAM and InnoDB is in referential integrity and transactions. There are also other difference such as locking, rollbacks, and ...


2

SINGLE DATABASE DATABASE_TO_VIEW=mydb cd /var/lib/mysql/$(DATABASE_TO_VIEW) watch "ls -lt | grep ibd$ | head -20" If you want just the table names DATABASE_TO_VIEW=mydb cd /var/lib/mysql/$(DATABASE_TO_VIEW) watch "ls -lt | grep ibd$ | head -20 | awk '{print $9}' | sed 's/\.ibd//'" MULTIPLE DATABASES Create a script called /root/InnoDBChanges.sh with ...


2

What I did MariaDB [test]> select version(); +---------------------+ | version() | +---------------------+ | 10.0.15-MariaDB-log | +---------------------+ 1 row in set (0.00 sec) Are you using 5.5? CREATE TABLE terms_of_use ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, current_version varchar(20), last_updated timestamp, ...


1

Does mysqldump have an option that allows multi-valued inserts to be batched for each table? Yes. net_buffer_length - The initial size of the buffer for client/server communication.


1

With just the two foreign keys in there I would leave it as it is. If other tables have a foreign key pointing to person_image then the debate becomes a lot more nuanced and you will start considering table size and query (plan) complexity.


1

There are two ways Stop MySQL gracefully. Check the error log and make sure it was a normal shutdown. Move ib_logfile0 and ib_logfile1 out of the datadir (Save them just in case you need to roll back) Start MySQL. InnoDB will notice there are no REDO log files and will create empty ones. Write to some InnoDB table some junk records until InnoDB ...


1

Just to add my solution as I had a similar problem. TL;DR Recreate the table with the same foreign key specification but with a different name as previously held by the table . Drop the resulting table (will also drop original orphan foreign key) Recreate table with original or no foreign key Detail I ran into the nasty situation where an ALTER TABLE ...


1

Dropping multiple indexes all in one SQL command is way better. Why ? SCENARIO #1 ALTER TABLE mytable DROP INDEX ndx1; ALTER TABLE mytable DROP INDEX ndx2; ALTER TABLE mytable DROP INDEX ndx3; This is what would happen Creates an empty temp table Removes the index from the empty temp table Copies the data into the temp table Swaps temp table with ...


1

Rather than try to guess, you should download Percona Tools. You should use the tool pt-duplicate-key-checker. It will compare all the indexes for you and give your the ALTER TABLE commands to drop the ones you do not need and still maintain fully compliance with all your index search needs. Here is the code to do it: ...



Only top voted, non community-wiki answers of a minimum length are eligible