Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
1,920
questions
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
16 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
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: ...
0
votes
0answers
36 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 `...
0
votes
1answer
24 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
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;.
...
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 ...
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
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
33 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
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
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
36 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
65 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
121 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
57 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
60 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
78 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
54 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
147 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
51 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
63 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
73 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
25 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
150 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
79 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
29 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
38 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
46 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 ...
