Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
0
votes
1answer
388 views

MySQL cannot write to tables, only read

Running MySQL 5.7.15 with InnoDB, any queries which modify any of the database tables (eg. insert, update, delete) run indefinitely and never complete. Queries which do not modify the data (eg. select)...
0
votes
0answers
39 views
+50

Understanding INSERT … ON DUPLICATE KEY UPDATE deadlock scenario

I'm trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency. The two tables in question: hosts: "CREATE TABLE `...
73
votes
5answers
98k views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
0
votes
1answer
20 views

mysql 5.6 flushing all dirty pages on ib_logfile rotation creating checkpoint stall

Server version: 5.6.23-72.1-log Percona I have been trying to track down why our mysql server is sharp checkpointing and it seems to correlate with rotating of the ib_logfile. I inherited this ...
0
votes
1answer
17 views

Optimization: Increase Innodb_pool_size useful?

We run an AWS RDS instance with 8vCPUs and 64GB memory. All tables are using InnoDB engine. I have kept the default setup of innodb_pool_size to be half the memory. What I see is that almost 30Gb of ...
0
votes
1answer
25 views

Mariadb is not starting

Please help me with this issue MariaDB not starting. Its a production server so not able to reinstall and clean the data at all. (This command executed by me) systemctl -l status mariadb.service ...
0
votes
1answer
14 views

Recovering deleted records from ibdata1

Is it possible to recover deleted rows from ibdata1? I have only ibdata1 file and no other files or tables. I want to recover all table structures and datas with deleted records. Is it possible ?
0
votes
0answers
16 views

MySQL: InnoDB data dictionary has unknown flags

I tried to delete and recreate a MySQL service and when I tried to start the service again I can't start it and I'm getting this error in error logs: InnoDB: Warning: table 'sampledb/table1' InnoDB: ...
3
votes
2answers
5k views

MariaDB is ignoring /etc/my.cnf

The variable I am trying to change is innodb_buffer_pool_size. As you can see below the settings is 512M. But SELECT @@innodb_buffer_pool_size; returns 134217728. When I execute: /usr/libexec/mysqld -...
0
votes
2answers
35 views

How to turn off autocommit in phpMyAdmin

I want to practice rolling back commands, savepoints and more on phpMyAdmin but I couldn't find a way to turn off autocommit in phpMyadmin. Currently I use the command SET autocommit = 0;. ...
1
vote
1answer
808 views

Can you rollback a query in state 'committing alter table to storage engine'

We've got an InnoDB table with 70 million rows, and we have been trying to run an alter table statement to modify and add a couple of columns. The query seems to have altered the table, and is now in ...
0
votes
1answer
22 views

Generated DDL doesn't match with CREATE TABLE statement

I'm using MySQL with InnoDB engine. I create a new table that contains a foreign key, eg: CREATE TABLE rooms ( id INTEGER NOT NULL AUTO_INCREMENT, my_id VARCHAR(15), house_id INTEGER, ...
0
votes
2answers
823 views

Unable to fix ROW size too large even with innodb_file_per_table

I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large" Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table` Row size ...
0
votes
1answer
928 views

Meaning of 'In current row format, BLOB prefix of 768 bytes is stored inline.'

I encounter below issue on MySQL v5.6.16: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format,...
10
votes
5answers
36k views

Can't create table, but table doesn't exist

I'm using these steps to create a table my_user, that already existed but somehow vanished from my database my_db: mysql> USE my_db; mysql> DROP TABLE my_user; mysql> ERROR 1051 (42S02): ...
0
votes
2answers
658 views

Galera / InnoDB: how can “mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Lock wait timeout exceeded” happen?

Galera 3 node cluster with MariaDB 5.5 error mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Lock wait timeout exceeded; try restarting transaction (1205) backup command mysqldump --...
0
votes
2answers
379 views

How to hot-backup mysql slave database with read operations

I have a slave database of about 120GB in size on which application is performing select statements. I also have a backup system (Acronis backup) available to me from a hosting company. Unfortunately ...
0
votes
1answer
88 views

Why are my InnoDB tables larger than calculated?

I have a DB where some of the tables are much larger than I think that they should be. For 23 of 39 tables the following query overestimates their size, as it is designed to. But for 16 tables my ...
0
votes
1answer
1k views

phpMyAdmin trigger error

The following oddly does work, but shows an error in PhpMyAdmin... (see image below) (in the definition block as seen in the image) BEGIN SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration ...
3
votes
2answers
654 views

MariaDB 10.3: InnoDB much slower than MyISAM

I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine: drop ...
0
votes
2answers
58 views

Mysql 8: count(*) on INT primary is 13500 times slower than supposed to be. any innodb dev here?

I am aware there are many questions on this topic, mysql has not improved much on these single-query performance bugs in the past years but my case is more unusual. If mysql would access the table ...
0
votes
1answer
972 views

Periodically very slow execution of simplest queries

Inspecting MySQL slow query log I found out that in certain periods of time there are a bunch of queries in log which are supposed to be very fast but were actually very slow. For example: # User@...
4
votes
1answer
486 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
2
votes
1answer
34 views

Primary Key At the End of Foreign Key index in MySQL is in order for sure?

I am aware that any Index in MySQL Innodb contains primary key value in the end of it. If there was a table as follows. create table BaseTbl ( base_id int not null primary key auto_increment, ...
1
vote
2answers
91 views

MySQLTuner request - high CPU problems

I've been running a pretty high pop website for a few months new and as its growing we've been having some problems lately. Its a 2 core Xeon, 8GB, SSD server. We're pulling +- 150k people a day. ...
3
votes
2answers
122 views

MySQL incorrectly saying serverID non unique when adding an instance to the InnoDB cluster

I Get the following error when adding the second instance to a MYSQL Innodb Cluster ERROR: Cannot add instance 'XXX' to the cluster because it has the same server ID of a member of the cluster. ...
6
votes
1answer
985 views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
0
votes
1answer
771 views

MySQL keep crashing over and over on reading ibd files

My MySQL instance keeps crashing over and over again, I am running it with innodb_force_recovery = 3 innodb_purge_threads=0 I have tried it with force_recovery = 1 as well. It has happened before but ...
0
votes
1answer
203 views

Wrong InnoDB table status (Size/Rows) after updating from MySQL 5.7 to 8

I've upgraded from Mysql 5.7 to 8 on Ubuntu 18.04 Made it by guide, very accurate, on a new empty server, checked all error/warning logs and fixed it. Everything works just fine with it. I have 2 ...
0
votes
2answers
280 views

How does MySQL store data, can I detach a mysql Database?

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
0
votes
1answer
1k views

MySQL crashed, InnoDB fails to recover

On MySQL 5.0 on CentOS 5.11, MySQL service has crashed and fails to start. Adding innodb_force_recovery=3 in /etc/my.cnf, creates the following error, which repeats with all table names of database ...
0
votes
0answers
21 views

i cannot install mysql plugin keyring_file.dll

I been trying to install keyring_file.dll mysql 5.7.26 plugin from wamp 3.1.9 without success it keeps me telling in cmd ERROR 1123 (HY000): Can't initialize function 'keyring_file'; Plugin ...
0
votes
0answers
34 views

Attempting to Restore Database From ibd Files but Receiving Errors on Tablespace Import

Recently, a customer (who didn't take backups of their system for whatever reason) had a database disaster so their production database had all of its files removed EXCEPT its ibd files. I have ...
3
votes
2answers
2k views

Can two mysql SELECT FOR UPDATE FROM mytable WHERE .. IN (..) deadlock?

Can two or more instances of the following deadlock with one another? START TRANSACTION SELECT mycolumn FROM mytable WHERE myid IN (list-of-ids) FOR UPDATE : COMMIT Points to keep in mind: mycolumn ...
3
votes
1answer
915 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
0
votes
5answers
541 views

Slow queries on indexed columns (large datasets)

I've got some large MySQL (5.5.60) tables where queries take 60 seconds+ to complete. product_base (1.5GB, 26 million rows) CREATE TABLE `product_base` ( `EAN13` varchar(13) NOT NULL DEFAULT '', ...
4
votes
1answer
3k views

How to restore corrupted InnoDB tables?

We have a data loss: the MySQL database is dropped and we have to restore the tables from ibd files. The backup is too old so we have to undelete the deleted database files. Most of the tables are ...
5
votes
1answer
760 views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
0
votes
1answer
697 views

Innodb not started?

I installed MariaDB_Galera_server10.0 , but when i check the error log i see this: 170118 14:49:09 [Note] InnoDB: 128 rollback segment(s) are active. 170118 14:49:09 [Note] InnoDB: Waiting for purge ...
1
vote
2answers
128 views

IN clause not working on mysql 8.0.17

I was on mysql:8.0.15 and I updated to mysql:8.0.17 On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect On 8.0.17 the same query with same data return 0 ...
0
votes
1answer
5k views

Check (health) status of MySQL schema table

I have a large MySQL database which has around 1,500 tables and the file of the database is around 30GB. I would like to suggest me some ways to monitor the health of my database in overall and how i ...
0
votes
3answers
170 views

Identifying root cause of slow query outliers from pt-query-digest?

I've run pt-query-digest and noticed many outliers with very high query times. Many of these queries do not perform slowly when I run them myself, and I'm having trouble figuring out the root cause of ...
0
votes
1answer
92 views

What are the pros and cons of adding secondary columns to a table's primary key?

In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table. I have a photos table. Queries on it come in many different ...
1
vote
1answer
1k views

Randomly slow inserts with InnoDB

TL;DR : How can I improve insertion time in my table so that every inserts take under 100ms to perform? This table is used by a webservice which receives transactions one by one so I can't group ...
0
votes
1answer
22 views

Is it ok if I use only the latest binary log file when restoring MySQL db?

mysqlbinlog --verbose --stop-datetime="2019-12-28 10:00:00" bin.000009 > backup.sql mysql -u root -p < backup.sql (The stop-datetime parameter here is just for testing) I did the recovery ...
0
votes
1answer
21 views

MySQL Lock Wait Time Out Exceeded upon Delete

I have a Delete Statement which fails with Below Exception com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction It waits for 50 ...
0
votes
1answer
149 views

“Lock wait timeout exceeded; try restarting transaction” for my delete query

I'm using RDS Aurora with MySQL 5.6. I have a delete query on a table which runs once in a day and it fails with lock wait time outexception, try restarting transaction. This issue is not happening ...
2
votes
4answers
167 views

Optimizing slow query

For a few days now, i'm trying to optimize this query. But still I haven't found the right solution in how to fix this. The contents table contains about 20 million records. As the connect table ...
0
votes
1answer
946 views

Large long-running reporting queries are causing very slow new mysql connections

Usually, mysql's connecton time is in the 0.001 second range. However, when I run a large reporting query (that spans multiple tables with aggregates), I get tons of warnings saying that new mysql ...
0
votes
1answer
637 views

Transferring IBD Files from one MySQL database to another in Windows

Without going into loads of historical detail I'll just get straight to the point. I am trying to move IBD files from one Windows server to another. MySQL is corrupt on the source server but I have a ...