New answers tagged

0

Fixed it. I just reinstalled MySQL and now the variable updates just like it should. Don't know why it wasn't working, but I don't really care since it works now.


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, ...


0

make backup of data for some case then go to mysql bin folder and run mysqld --tc-heuristic-recover=ROLLBACK don't forget to restart mysql after source: http://lampretard.blogspot.com/2016/01/you-have-to-start-mysqld-with-tc.html


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 ...


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 ...


0

TL;DR -- explain how db's work Long version: You can't show that removing a FK can not cause corruption, because that is not actually true -- theoretically it could. What you can show, is that it doesn't cause corruption and explain why the chance of it causing corruption is more theoretical than actual. I would start with why the chance of it actually ...


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 ...


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 ...


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 ...


0

With mysql 5.7 you can consider moving your undo logs to separate files: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_undo_logs This will also give you the option to truncate those logs when you like: https://dev.mysql.com/doc/refman/5.7/en/truncate-undo-tablespace.html In my.cnf this setting should do the trick: ...


0

If your hardware fails, you can loose all your data, so I use param = 2 without any worries. Anyway you can split you sensitive (order, virtual money,...) and regular (statistics, cart,...) data between 2 db servers and keep them safe and fast. For transactions between databases you can use http://dev.mysql.com/doc/refman/5.7/en/xa.html


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 ...


0

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances Check innodb_buffer_pool_instances with: mysql> SHOW GLOBAL VARIABLES LIKE ...


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

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? ...


0

If you're running 5.6+ you can have the server dump and preload the buffer pool by adding a couple of settings to my.cnf, rather than waiting for it to warm up after each restart. innodb_buffer_pool_dump_at_shutdown=ON innodb_buffer_pool_load_at_startup=ON https://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html


0

I had this same issue on my database. I had a table that was constantly being updated and used INSERT...ON DUPLICATE KEY. The updates would take longer and longer and the connections would build up in the queue until mysql crashed. I tested innotop, instat, reinstalled and restored the tables from a snapshot and nothing worked. Finally I tracked down ...


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 ...


0

It will do a full table scan. The columns in an index are ordered. Think about finding a person in a list of people where the list is sorted by last name, but all you know is the first name. PRIMARY KEY(b,a) would be somewhat good for WHERE b=1 AND c=2 -- it could at least narrow it down to all the rows where b=1. That is, it would do a "range scan" of ...


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 ...


0

Nothing obvious in the VARIABLES that would cause the repeated crashing. But here are some comments... Edit: parts of the following were edited to reflect 120GB of RAM, not 2.9 Uptime = 4d 04:41:57 -- so there has not been a crash in 4 days? You appear to be running entirely (or mostly) InnoDB. -- Increasing innodb_buffer_pool_size from the current 128M ...


-3

The main idea is to increase performance and place each file to it's own HDD drive. As I have to much IO related to InnoDB log files. ...


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.


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 ...


0

Since MySQL 5.6 import tablespace is possible (http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html). Since it's quite cumbersome I would only do that though if you need to 'merge' production schemas from multiple physical servers into a single dev database. Otherwise as Rick wrote replication, snapshotting or xtrabackup are all better options.


0

We had 2 problems: 1) We had one mysql query that used three joins. Turns out this function was crashing Mysql. We rewrote this query to use 4 mysql queries without joins and that solved that problem. (Bit of a hot-fix, we will probably rewrite the function so it is possible to cache it). 2) We were experiencing around 99.9 % I/O wait when we only used 10% ...


2

The file ibdata1 has "tablespace ids" in it that need to correspond to the .ibd files containing the data, etc. Since these ids are generated by the instance, when you reloaded the dump, they may be different than the ones on the production machine. Hence, the rsync could be copying wrong ids. Moral of the story: Don't mix source dumps (mysqdump, ...


3

So I'm answering this question almost 4 years late: InnoDB file formats were conceived at a time when InnoDB was independent of the MySQL Server (for example: MySQL 5.1 could run two different versions of InnoDB). The reason why you would not want to run Barracuda (in 2012) is that it could reduce flexibility in downgrading MySQL (i.e. after a failed ...



Top 50 recent answers are included