Hot answers tagged

8

You need to specify that you want the delete action to cascade like so: CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`) ON DELETE CASCADE If you do not specify either the ON DELETE or ON UPDATE then it defaults to RESTRICT, from the documentation: For an ON DELETE or ON UPDATE that is not specified, the default ...


8

As pointed out by Mr Brownstone (this would just be a comment but it turned out too long) the behaviour you are expecting can be achieved if you define the key as ON DELETE CASCADE. I would be very very careful with ON DELETE CASCADE, in fact I almost never use it and generally recommend against it. This counts for triggers that take action based upon rows ...


2

InnoDB will only lock the row. But it will eventually have to update all the indexes; however this is mostly deferred. Was something else going on while the INSERT was taking 36 seconds? Perhaps a SELECT ... FOR UPDATE? Or even a SELECT can block the INSERT in certain circumstances. Next time it happens, quickly do SHOW FULL PROCESSLIST to see what else ...


2

I can only speculate what the people meant. Maybe it's related to how MySQL computes index statistics depending on n. The only case that comes to my mind is whether n is higher than 127 or not. As you may know InnoDB stores a string with its length. The length is stored in so called offset fields. The offset can be either one or two bytes. In REDUNDANT ...


2

Fool that I am... the server was restarted this morning. I realized this as I was finishing the question. The buffer pool won't fill up until the database actually needs to use that much data. Indeed, if I re-run the buffer pool query I can see the "Free buffers" number slowly decrease. I could run some queries to force tables into memory, but given I know ...


2

Please note this Pictorial Representation of ibdata1 (from Vadim Tkachenko) You have many moving parts inside ibdata1 Data Dictionary Double Write Buffer Insert Buffer Rollback Segments Undo Space Almost 3 years ago, I wrote a post addressing why ibdata1 can grow too fast : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set? ...


2

Un-panic. Your queue had lots of rows inserted and quickly deleted, correct? And now it has very few rows in it? Specifically, about 17K? More importantly, there was, at one point, maybe a million rows? So the tablespace expanded to handle it, but has not shrunken. The dump dumped only the data for the current 17K rows. The reload loaded only those ...


2

Replace @datenow with CURRENT_DATE(). Add composite INDEX(R, S, C_D) to cls. Do not "restart" -- look at the log; look at SHOW PROCESSLIST; look at SHOW ENGINE InnoDB STATUS.


2

Using GB or MB wont work. However, any variable that you needs to be defined as a numeric value can use the shorthand byte suffixes K, M or G. From the docs: For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 1024^2 or 1024^3. (For example, ...


1

(For InnoDB) All the rows with the same a will be next to each other; all rows with same a and b, will be adjacent, etc. So, I guess "yes" is the answer to #1. No. An InnoDB "block" is 16KB. If the entire table fits in one block, then everything will be in that one block. Note: The PRIMARY KEY is "clustered" with the table. So talking about the ...


1

My understanding is following: Historically InnoDB didn't scale well with large buffer pool. So - with a lot or RAM - OS disk cache might impact performance a lot -> this is why O_DIRECT performed bad for those environments. MySQL 5.6 has those issues addressed and in recent versions you may set buffer pool as big as ~75% of any RAM. When most of RAM is used ...


1

I have recovered my MySQL 5.5 *.ibd and *.frm files with using MySQL Utilites and MariaDB 10. 1) Generating Create SQLs. You can get your create sql's from frm file. You must use : https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html shell> mysqlfrm --server=root:pass@localhost:3306 c:\MY\t1.frm --port=3310 Other way you may have your ...


1

What version are you running? 5.6 doc gives the details: innodb_flush_log_at_trx_commit can be set (1) on the commandline, (2) in my.cnf, or (3) via SET GLOBAL. sql_safe_updates can only be set via SET SESSION or SET GLOBAL. Note that SET GLOBAL does not impact anyone currently connected (including yourself). It will effect anyone connecting later, but ...



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