Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
-1
votes
0answers
10 views

MySQL REDO log path permission [closed]

Im not sure if there is a duplicate question but What is the right permission for path directory for redo log on MySQL coz i got this error. ERROR 3847 (HY000): Cannot create redo log archive file ...
-3
votes
1answer
19 views

innodb crashed with error number 5 and mysql wont start [closed]

hello my database stop today and dont start again. 2019-12-10 10:08:02 26295 [Note] InnoDB: Completed initialization of buffer pool 2019-12-10 10:08:03 26295 [ERROR] InnoDB: Error in system call ...
0
votes
2answers
34 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
0answers
50 views

sysbench runs give different result for mysql database performance evaluation [closed]

i am trying to evaluate the performance of mysql database cluster , INNODB cluster (group replication) , so i use sysbench , i run multiple test using the lua scripts (read_write.lua / read_only.lua /...
0
votes
0answers
15 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
54 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
1answer
39 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
41 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
21 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
48 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
53 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
25 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
42 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
26 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
47 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
25 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
14 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
50 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
72 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
49 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
40 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
38 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
34 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
24 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
22 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
91 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 ...
1
vote
1answer
57 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
37 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
48 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
36 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
23 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
19 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
34 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 ...
0
votes
0answers
25 views

Benchmarking MariaDB/InnoDB using TATP - Writers slow down readers?

I'm benchmarking MariaDB 10.4 with TATP. The InnoDB config file has the following parameters set: [mysqld] innodb_data_home_dir=/usr/local/mysql/data innodb_undo_directory=/usr/local/mysql/data ...
0
votes
1answer
29 views

InnoDB table size increased substantially after ALTER

I'm trying to understand why the data size of my table increased so much after performing this ALTER TABLE statement on an InnoDB table: ALTER TABLE alert ADD COLUMN receiver_id INT(10) UNSIGNED ...
-1
votes
1answer
32 views

InnoDB not starting

I tried to start mysql service on xampp and is giving me the following error: Error: MySQL shutdown unexpectedly. 19:17:07 [mysql] This may be due to a blocked port, missing dependencies, ...
0
votes
1answer
32 views

InnoDB Data Dictionary Cleanup for a Non-Existent Database Named “tmp”?

I have a number of error messages like this in my error.log file referencing different files that can't be opened when I start my mariadb 10.4.8 server: 2019-09-30 17:38:19 0 [ERROR] InnoDB: Cannot ...
0
votes
1answer
49 views

MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB

I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB. I converted all row formats to dynamic first for all the tables to be on Barracuda then running alter table <...
-2
votes
1answer
54 views

VARCHAR vs TEXT performance when data fits on row

mysql> desc temp1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | value | ...
1
vote
0answers
40 views

MySQL Won't Start - InnoDB Table Corruption

I've found lots of articles and questions related to this issue, but none that seem to apply or work in my situation. MySQL will not start after I had to do a fsck repair on my server's hard drive (...
3
votes
2answers
61 views

What happens when MySQL/InnoDB buffer pool size is small and a large transaction is run causing the buffer pool overflow?

Let's say we have a transaction running 50,000 inserts which dirties approximately 600 MB of the database. If the buffer pool size is 128 MB, there will be dirty pages in the buffer pool while the ...
0
votes
1answer
17 views

MySQL datafiles on SMB3 network storage

We have MySQL-server on Ubuntu with InnoDB-datafiles on a local disk. As part of an upgrade program, the company is planning to put the datafiles on a Windows fileserver where they can be mounted with ...
2
votes
2answers
83 views

InnoDB errors and crashes with MyISAM database?

I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB ...
0
votes
1answer
22 views

show extended index equivalent for MySQL 5.7

If you look at this documentation for show index in version 8.0, it says SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] The ...
0
votes
0answers
39 views

can delete manually a .ibd file and .frm file?

I have the following trouble in a mysql server database: 190919 17:35:30 [ERROR] Cannot find or open table abril2017/mexico from the internal data dictionary of InnoDB though the .frm file for ...
0
votes
1answer
17 views

In InnoDB, how does fuzzy checkpointing's recovery consistency work?

I've looked the documentation quite thoroughly but still cannot figure out so asking this question. In InnoDB, I understand that any updates against buffer pool are tracked by redo log, which gets ...