A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
0
votes
1answer
11 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
11 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
12 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
12 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: ...
0
votes
0answers
16 views

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 `...
0
votes
1answer
23 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
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
31 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;. ...
0
votes
2answers
57 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 ...
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, ...
0
votes
0answers
19 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
32 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 ...
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
20 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
0answers
16 views

Why does START TRANSACTION WITH CONSISTENT SNAPSHOT not work with SERIALIZABLE isolation level?

The MySQL doc for START TRANSACTION says: The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. ...
0
votes
0answers
24 views

InnoDB Cluster Error

Is anyone encountered this error "Unable to join the group: peers not configured" in creating Innodb cluster? or I need to configure group replication first? I follow the documentation step by step ...
0
votes
2answers
41 views

How to speed up full text query on a table with 4 million rows? (MariaDB)

I have an InnoDB full text table that serves the ajax-powered searchbox at the top of my website. I generate it with a daily script that pulls data from a dozen entity tables on the site, and ...
0
votes
1answer
35 views

How to change default engine in WAMP to InnoDB

Can someone tell me how to change my default storage engine to InnoDB using wamp? Currently it is MyISAM.
2
votes
0answers
64 views

MySQL performance degradation with heavy inserts

I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
3
votes
2answers
119 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. ...
1
vote
2answers
54 views

What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log?

I'm puzzled by the innodb_max_dirty_pages_pct parameter, and its relation to the redo log. The definition itself is simple: InnoDB tries to flush data from the buffer pool so that the percentage ...
1
vote
0answers
40 views

Stop MySql InnoDB truncate on reboot

How can I stop a truncate of a MySql innodb? When I restart the database it tries to continue the truncating which leads to the problem of the log group capacity exceeding. To increase this limit it ...
1
vote
1answer
56 views

Is there a maximum IOPS for MySQL asynchronous replication on the slave server?

I have been struggling with this issue for a while now. I have a simple Master-Slave setup with MySQL 8.0.15 installed on both systems. In day-to-day operations the slave keeps up the Master with no ...
1
vote
3answers
58 views

Constant deadlocks on a high load table

I have a table files with about 73m rows, which totals to about 45GB. The rows being inserted in a day are about 100k. But the total rows which we run IODKU can reach tens of millions a day(since ...
0
votes
0answers
29 views

Where is the rest of the MySQL “HANDLER” documentation?

I've been a long time user of Handlersocket for MySQL / Percona, but other than MariaDB keeping it going, it's been dead for a long time now. The final nail in the coffin seemed to be MySQL choosing ...
1
vote
1answer
76 views

Different default innodb_buffer_pool_size on Windows with MySQL 8.0.18

I have installed MySQL 8.0.18 Community with the same configuration (mysql-installer-community-8.0.18.0.msi installer, custom install with server + workbench, standalone server, legacy authentication, ...
0
votes
1answer
28 views

Migrate legacy application to new db/schema

I apologize if I made any mistake/missing info, fist post here! I am in the process of rewriting a website from scratch to a newer tech stack with a completely redesigned architecture. The website in ...
-1
votes
1answer
53 views

What are the right ways to analyse and tune up innodb_thread_concurrency in mysql 5.7?

I'm in a plan to increase thread concurrency in my production MySQL server 5.7 but not sure, before what are the params/configurations that need to be measured and configured to increase the thread ...
0
votes
1answer
28 views

Minimizing and Handling Deadlocks Strategies

I want to discuss deadlock minimizing strategies. From isolation level to retries to different insert strategies. Lets say we have table A: CREATE TABLE `A` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
0
votes
0answers
21 views

Long Running Transactions with Active Status

I could see long running transactions in my Innodb Status, I'm using RDS Aurora Mysql 5.6 Version. ---TRANSACTION 977075315, ACTIVE 70113 sec 2 lock struct(s), heap size 376, 0 row lock(s), undo log ...
-1
votes
2answers
53 views

How do I select specific columns in a sql prepared statement?

I have created a prepared statement to output a file and add a date to the filename. That part works if I select the entire database. I want to select specific columns to include in the query, not all ...
3
votes
1answer
45 views

Auto-Inc gaps - Id value explodes

We have a big issue in production environment, many tables were created with Int Auto-Inc Primary Key. Adding to the fact that our insert mechanizes includes using Insert Ignore/Insert.. On Duplicate ...
0
votes
1answer
141 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 ...
0
votes
1answer
50 views

Maintaining a production big table

I have a very large table: Table1 in a production environment which I need to alter a column in. What's the best preferred way to do so without down time? Option 1: CREATE TABLE Table1_new LIKE ...
1
vote
1answer
62 views

How to restore a database with innodb tables from files?

I am trying to restore a MariaDB database containing innodb tables from files. Blue-eyed as I am, I considered this an easy task: I just stopped the server, copied the files and restarted it as ...
1
vote
0answers
70 views

mysql - importing large Tablespace: Lost connection during query

iam trying to recover innodb table which has 1.5M rows from ibd file ( 5.5 GB ) this is the exact steps i do: getting create table query using mysqlfrm command create the table Alter Table discard ...
0
votes
1answer
28 views

why innodb generate table-lock when two sql using different index?

I know innodb-engine row lock by add lock to index item. But I don't understand the following scene. Prepare sql: DROP TABLE IF EXISTS `tbl_order`; CREATE TABLE tbl_order ( `order_id` BIGINT NOT ...
0
votes
1answer
35 views

Avoiding deadlocks when using trigger

I have a database with a table called subscribers. The engine is InnoDB. The table has approximately 100,000 rows. This table contains multiple columns: CREATE TABLE `subscribers` ( `id` int(11) ...
0
votes
1answer
25 views

Procedure to recreate table automatically to free InnoDB space

I have a few MySQL tables for a website running PHP Laravel that keeps getting bigger and bigger. For example the jobs table is currently at 18GB, and has a total of 6 rows in it. I have a log table ...
0
votes
1answer
24 views

MySQL - InnoDB - Partitioning a table with uniqueness

Following a previous post I posted: date-based-partitioning This is my solution for partitioning a table with primary key. I wanted to add partitioning by months and delete old record. CREATE TABLE ...
2
votes
2answers
93 views

After upgrade from MySQL 5.5 to 5.7 queries are more frequently encountering deadlock

Recently we migrated our production DB to Amazon RDS with version upgrade from 5.5 to 5.7 using AWS DMS service. After that, we are frequently getting deadlock issues for our insert...on duplicate key ...
2
votes
1answer
137 views

#1036 - Table 'pma__recent' is read only in phpMyAdmin / xampp / Windows

I have about 40 databases on localhost powered by xampp 5.6.15. Some of them use MyISAM and others InnoDB. The database has been corrupt and phpMyAdmin was permanently outputing error messages. I ...
0
votes
1answer
40 views

Problem with writing data to MySQL database [duplicate]

We have a problem with peak loading while writing data to database This is example of error SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction SQL: insert ...
0
votes
0answers
77 views

Which MySQL settings are the closest equivalent to RDS' “Queue depth”?

I'm trying to find out which MySQL status variables can be used to get as close as possible to the RDS value of "Queue depth". On a test system, I reckon that %pending% variables may be at least a ...
0
votes
0answers
41 views

MySQL 5.7 fine when multiple clients inserting new data, but load goes to 100% on one core when any client inserts duplicate data

I've been frustrated by a problem with MySQL going CPU bound and effectively locking up. I'm unsure whether it's a bug, quirk, or I'm hitting some resource limit. I believe I have narrowed down the ...
1
vote
1answer
24 views

LOAD DATA keeps CPU usage high after it finishes

I'm loading multiple files into the database using the LOAD DATA LOCAL INFILE command combined with Golang database/sql driver like this: mysql.RegisterLocalFile(fileName) _, err = dbConnection.Exec(...
-1
votes
1answer
25 views

MySql query suddenly became very slow after a while

I have a program that reads a set of entries from a database, performs some set of operations on these entries, and updates them in the database, the select query is pretty straight forward, It just ...
0
votes
1answer
27 views

MySQL InnoDB add new instance to existing cluster

Server version: 5.7.23-log MySQL Community Server (GPL) I'm running an InnoDB cluster and trying to add a new node, the cluster is a couple of years old and binlogs are being cleared after 2 weeks. ...
2
votes
0answers
37 views

Why InnoDB storage engine not loading csv files concurrently?

While loading different csv files to different tables of the same database using innodb storage engine(even tested on aria), csv files seems not to be loaded concurrently. Because, a single csv ...
0
votes
0answers
44 views

Recover InnoDB tables with FRM, ibdata1 and log files, but without ibd files (innodb_file_per_table = OFF)

I have spent three days investigating how to recover my database. I have the ibdata1, the FRM, and the ib_logfile0 and ib_logfile1 files (It seems I don't have the ibd files since my configuration ...