Hot answers tagged

56

After checking the logs I found the following error: [ERROR] Unknown/unsupported storage engine: InnoDB I removed these files: rm /var/lib/mysql/ib_logfile0 rm /var/lib/mysql/ib_logfile1 at /var/lib/mysql This resolved my problem after restart.


31

Ouch. InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery. Check the suggested webpage: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html. Basically, try to start the MySQL ...


14

You need a full database lock to backup a (most) database(s) consistently. The manual https://dev.mysql.com/doc/refman/5.5/en/backup-methods.html says FLUSH TABLES WITH READ LOCK is correct for ZFS snapshots specifically. Making Backups Using a File System Snapshot If you are using a Veritas file system, you can make a backup like this: ...


12

I just had to do this recently. What Amazon recommended was using the Percona Toolkit. I downloaded it and was able to run something like: ./pt-online-schema-change h=databasenameHostName,D=databasename,t=tablename --recursion-method=none --execute --user username --password password --alter "MODIFY someColumn newDataType" and it works great. It tells you ...


11

In MySQL 5.7 it is now possible to modify the innodb_buffer_pool size on the fly dynamically: 15.4.3.2 Configuring InnoDB Buffer Pool Size The new pool size must be a multiple of: innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances or will use the next highest multiple, if set to an invalid number.


9

Check your mysql error log. tail -100 /var/log/mysql/error.log If your log says (like mine did): InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 [ERROR] InnoDB: Cannot allocate memory for the buffer pool You don't have enough memory to use the default buffer size of 128M Edit the config file /etc/mysql/my....


9

I hope it will be helpful to you. try performance_schema = off in [mysqld] section of your configuration. https://mariadb.com/resources/blog/starting-mysql-on-low-memory-virtual-machines/


8

I've ripped off and adapted a conceptually simple script in Bash which I found in another Server Fault post by Tobia. It should get you about 90% of the way there. mysql_locked=/var/run/mysql_locked # flush & lock MySQL, touch mysql_locked, and wait until it is removed mysql -hhost -uuser -ppassword -NB <<-EOF & flush tables with read ...


7

I am glad you asked about InnoDB and the Query Cache. IMHO They should never come up in the same sentence or the same conversation. Please forgive the my use of them in the first sentence. All joking aside, I have addressed in the DBA StackExchange many times Jun 11, 2014 : Increased query_cache_size, Queries slowed drastically on increased traffic Sep 26,...


7

Here's what worked for me (ubuntu 19, mysql 8): sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf add in this at the bottom of the file innodb_buffer_pool_size = 8G save and exit. restart MySQL. sudo systemctl restart mysql


7

If you only use InnoDB for all tables and set innodb_flush_log_at_trx_commit to: 1 (the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk) or, 2 (the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk ...


4

DROP TABLE always needs a few global locks. I would do exactly what you described in your question: delete rows as quickly as is safe (don't upset disk IO and various caches too much, be careful about replication as well), and then drop it.


4

innodb_buffer_pool_instances: You cannot see its effect unless innodb_buffer_pool_size is few gigs, dividing the buffer pool into separate instances can improve efficiency. This is also a tuning practice for innodb_buffer_pool_instance so that each buffer pool instance is at least 1GB. eg, If innodb_buffer_pool_size = 4GB then, ...


3

According to the mysql documentation, the 'opening tables' state shouldn't be seen unless there are locks getting in the way, or when your table_open_cache is too small. Check mysql's errorlog to find out what happened, check the show engine innodb status output for innodb info.


3

I was facing this same error while using mysql:5.7 docker image. Main mistake was trying to create root user which exists by default. More information: https://github.com/docker-library/mysql/issues/129 As given in the above link, solution was to NOT set MYSQL_USER and MYSQL_PASSWORD in the environment variables while starting the docker image.


3

The following my.ini for mysql 5.6 causes the "private bytes" to go from 630MB down to 20MB and the "working set" from 450MB down to 21MB on a Windows 7 64 bit machine. The performance of a mysql server configured like this is probably going to be very bad, but technically, if you want it to occupy as little memory as possible, then this is the answer. # ...


3

Beyond the shadow of any doubt, I strongly believe it is the InnoDB Storage Engine. It is almost like a living, breathing organism. Here is a Pictorial Representation from Percona's CTO Vadim Tkachenko Please note the InnoDB Buffer Pool. If it has lots of dirty pages (changes to write back to the physical tables) and corresponding index changes (Insert ...


3

You need FLUSH TABLES WITH READ LOCK for myisam because it isn't journaling. You don't really need anything for innodb at all, IMO, because it's journaling. It'll be consistent anyway, just rolls back the journal automatically if anything is happening at the atomic instant you snapshot. If you want application level consistency, your application should use ...


3

This is my solution how to create an ZFS snapshot while keeping the lock: mysql << EOF FLUSH TABLES WITH READ LOCK; system zfs snapshot data/db@snapname UNLOCK TABLES; EOF


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


2

We want to kill transactions that are idle and are blocking the table(s) that I want to use. It's easy enough to kill the thread that has the lock, in the example from the original question the lock was on a single table where no changes had been made so it would be fast but be careful if the killed transaction had been running for hours and had loads of ...


2

In my case the problem was innodb_buffer_pool_instances. Since I was reducing innodb_buffer_pool_size, it became less than one 1GB per instance, so it ended up rounding it. When I also reduced the instances, it finally changed the pool size!


2

MySQL doesn't start because it cannot allocate 128M for the InnoDB buffer pool. You have to either set lower innodb_buffer_pool_size or limit number of Apache processes so they don't consume all memory. I had the same problem and described it in a blog


2

Your InnoDB database buffer pool is about 6,6 gigabytes. When inserting data to the database, InnoDB engine first adds the data to the buffer pool, and then writes it to the disk. So, what happened here is that when the buffer pool got full, InnoDB started to write its contents to the disk. Therefore the insert performance dropped. Now, when you stopped ...


2

The problem is /tmp/ filling up and MySQL putting some files there. One of the choices MySQL can make when dealing with a subquery: Ref: MySQL 5.6 - Subquery Optimization Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used ...


2

In general case innodb_force_recovery mode is supposed to let a user to start InnoDB and dump valuable data. A common belief InnoDB will heal tablespace after enabling innodb_force_recovery. No, it won't. (In some cases you can fix a tablespace dropping particular tables, but that's another story). If you are lucky enough and MySQL starts the next step is ...


2

This log entry is the key to the most likely issue: 141207 20:50:34 mysqld_safe Number of processes running now: 0 If there is not an extremely verbose block of log messages, including a stack trace, immediately before this line, then MySQL's not actually crashing... the kernel is killing it because another process (such as the web server) is making ...


2

The problem is, there are no such settings in my /etc/mysql/my.conf. So add the lines needed to the [mysqld] section of the config file. The default my.cnf file only contains a tiny fraction of the configuration values that are available.


2

I doubt any of the SQL engines is rolling back the updates for days. Usually it's a couple of last transactions. It can roll back a couple of days only in one case - you have like 0.5 transactions per day, and you were lucky enough to get the power outage in the exact moment the transaction was happening. Even if I'm mistaken, besides adding an UPS (the ...


2

It's a corruption. See the failed assertion - page_is_comp(next_page) == page_is_comp(page). It checks if a next page is in the same format (COMPACT or REDUNDANT) as the current one. InnoDB index can be in either format, but the mix is impossible. So, go ahead with innodb_force_recovery=1,2,3,4,5,6 (try each value until MySQL starts), dump databases and ...


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