Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
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
38 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
38 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
73 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
55 views

VARCHAR vs TEXT performance when data fits on row

mysql> desc temp1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | value | ...
1
vote
0answers
52 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
107 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
99 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
29 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
78 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
23 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 ...
1
vote
1answer
97 views

Cannot remove orphaned InnoDB Temp Table - missing .frm file

For the second time, I have encountered an issue, where our MySQL server (MariaDB v10.1) suddenly threw an error - Could not remove temporary table: './database_name/#sql-4593_791', error: 120. ...
1
vote
0answers
38 views

Unable to recover MySQL 5.6 (InnoDB) database

I've tried multiple different suggestions from other questions on here, with no luck... This is a MySQL database running version 5.6.41, using the InnoDB engine, and the instance is running out of a ...
1
vote
1answer
37 views

MySQL 5.7 Can slave be ahead of master during replication

From my understanding, once commit() is received from client, transactions are written to binlog first before any commit is done to InnoDB. In this case, is it possible for slaves to be ahead of ...
0
votes
2answers
326 views

MySQL DBs taking up way too much disk space

I'm on Ubuntu Server 18.04 running MySQL server 5.7.27 with InnoDB. I confirmed that InnoDB file-per-table is On. I have a mysqldump containing all of my DBs that takes up about 1.3 GB disk space. ...
0
votes
2answers
13 views

Mysql - tablename.ibd file is huge in size

I'm using MySQL 5.6 and innodb file per table has been enabled. I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB. I have done a huge ...
0
votes
1answer
35 views

Size of record pointer in InnoDB?

I am trying to put on a scale redundant attributes and performance gain. Although storing redundant information causes many anomalies, I am solely focusing on the size of a redundant attribute vs. ...
0
votes
0answers
190 views

Got an error reading communication packets

I have a server application that's using MySQL INNODB databases to store its data. I know that this application has worked as is in the past, but I don't know anything about the database server ...
0
votes
0answers
63 views

index corrupt error while trying to index a large table

I am trying to index a large table, approximately 12 million rows and 33GB in size. There is currently no indexing on the table. When I am trying to index one of the columns in a table, I get the ...
0
votes
1answer
32 views

Why do some write sometimes go faster than expected for MEDIUMTEXT

We are running a MySQL Innodb database, our server has 4gb memory. We are trying to improve the speed at which a 800KB XML File is stored in a MEDIUMTEXT column. Currently, we have this query ...
0
votes
0answers
183 views

MySQL Service won't start with error [ERROR] InnoDB: Trying to access page number 4294963199 in space 0

My MySQL installation on Ubuntu 18.04 crashed and now won't start. The error log says Trying to access page number 4294963199 in space 0, space name innodb_system, which is outside the ...
0
votes
0answers
104 views

MySQL 5.7 isn't using new innodb buffer pool settings

I have a very tiny VM which keeps running out of memory. I believe this is due to the buffer pool size. Default appears to be 128M for innodb_buffer_pool_size and innodb_buffer_pool_chunk_size. So I'...
0
votes
0answers
24 views

Optimising InnoDB configuration in MySQL80 Server

I have a WAMP production environment, currently running Apache24, MySQL57, PHP73 on two Windows Server 2016 servers - an application server and a dedicated database server - and I'm toying with the ...
0
votes
0answers
16 views

mysql db data folder restore in 5 days old backup [duplicate]

recently I had the need to install a driver on a server and because of the destination I finished uninstalling XAMPP in which I had a database that I need to recover. The fact is that after ...
0
votes
0answers
7 views

innodb corrupt database - recover events and functions

There is plenty of articles how to restore the data but can't find anything about the scheduled events and functions recovery. The db crashed due to running out of disk space (plesk). I managed to ...
1
vote
1answer
713 views

MySQL Replication stuck at 'preparing'

My MariaDB replication stuck at 'preparing'. This happend after an update and restart of the server yesterday. Since then, replication is not working. Setup: 2 MariaDB servers with master-master ...
0
votes
0answers
207 views

How do I recover an innodb database when I have ibdata1 intact and a subset of the original .ibd/.frm pairs of files?

Originaly it runned on mysql Server version: 5.6.28-0ubuntu0.15.04.1-log (Ubuntu) now it runs on Server version: 5.7.27-0ubuntu0.18.04.1-log (Ubuntu) I can get the structure by generating a .sql file ...
0
votes
1answer
32 views

Selecting consecutive rows with same values and one that differs

So there is this question that explains how to do it but what I want is to find the duration from idle to active for a given userID and status for example for Brown it should show something like this: ...
0
votes
3answers
35 views

How to maintain unique sequence across table columns?

I hava two tables registed_products and unregisted_products. Both have autoincrement primary key which can be overlapping which if 100 id exist reg table, it can exist in unreg table as well. Insert ...
0
votes
1answer
50 views

InnoDB Mariadb 10.0.27 high IO while idle

centos7 mariadb 10.0.27 Yesterday i did a lot of delete operations from innodb tables (table size - 100gb, deleted part - 40gb. Many indexes on table). And now i have strange situation on this ...
0
votes
0answers
31 views

Best setup variables for mysql with big large table row size?

Here are the variables for our server, and I want to know what are the best settings for mysql server with 2.5 million records transaction... MariaDB [(none)]> show variables like 'innodb%'; +-----...
0
votes
0answers
41 views

mariadb import - all rows missing except those in final INSERT of InnoDB tables

This is a very strange bug. I'm using this on a mac: mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.13 (x86_64) If I import an SQL dump of a WordPress DB (so not very complicated structure, but ...
1
vote
2answers
129 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
22 views

ibdata1 back to it's original size after deleting it

I have followed the following steps in order to save space but I am surprised after restarting MySQL ibdata1 gets back to it's original size. As you want to reclaim the space from ibdata1 you ...
1
vote
1answer
435 views

Unable to add new instance on MySQL InnoDB cluster

I'm setting up new MySQL InnoDB cluster 8.0.16 consisting of three instances: srv-mysql-01 srv-mysql-02 srv-mysql-03 I run JS-script in mysql shell for build cluster: mysqlsh --file=/tmp/...
0
votes
0answers
13 views

How Do Multiple Innodb Buffer Pool Work in Mysql 5.7

Assume that I am using the default settings in Mysql 5.7 which has 8 buffer pools and each is allocated 1GB. Then assume that there are 3 tables, each table are 2GB each. Also, assume this is a test ...
0
votes
0answers
49 views

How do I use the .ibd/.frm files to recover an InnoDB table that went missing when MySQL crashed during OPTIMIZE TABLE?

I have seen How to Recover an InnoDB table whose files were moved around but I am not sure if it's relevant, since MySQL renamed the files and there was no tablename.frm afterwards. I ran OPTIMIZE ...
0
votes
1answer
53 views

Table Partitioning and Performance

I have to migrate one large table and need to do partitioning of the same. I would like to know whether i do Partitioning first and migrate the data second or I do migrate the data first and ...
0
votes
1answer
60 views

How to find if my innodb row uses overflow/off pages?

I use INNODB engine. My row format is Dynamic. For variable-length columns like TEXT, BLOB and varchar etc. , data is stored in off page and not in the same page itself. Do I have a way to ...
0
votes
1answer
22 views

MySQL 5.6 service not going to stop

Something happennig in my environment MySQL 5.6.34 RHEL 6 22G buffer pool size When i stop MySQL it shows: service mysql stop ..................................... Until and unless I kill using ...
0
votes
1answer
28 views

Percona/XtraDB Database with many of writes to a small table uses less CPU than the same database with less writes?

I'm trying to understand why Percona and xtradb InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it. I've a InnoDB database with only one table ...
0
votes
1answer
44 views

Delete duplicate rows that have same non-unique values in MySQL InnoDB

How do I drop rows that have the same values in the column title? I do have a unique hash for all the rows, but they seem to have failed me anyway. As an added question, If my row_id is all over the ...
-1
votes
1answer
140 views

MariaDB: Duplicate entry for key on OPTIMIZE with no duplications

There is a table named contracts with following structure: MariaDB [db_name]> DESCRIBE `contracts`; +-----------------------------+--------------+------+-----+---------+----------------+ | Field ...
0
votes
0answers
11 views

mysql innodb locking

In one transaction I'm inserting a lot of rows into table T1. "After insert" trigger of table T1 inserts into table T2. Is it possible during that process select from T2 in another transaction? Or ...
0
votes
0answers
34 views

MySQL - Investigate deadlock on atomic update

On a high traffic queue managed by Redis there is the following code: update `ads_publishers` set `impressions` = `impressions` + 1 where `ad_id` = ? and `pub_id` = ? Here's is the table schema: ...
0
votes
0answers
143 views

Corrupted InnoDB still won't start even after removing corrupted tables

My MySQL server stopped working randomly and upon checking log files I found this: 190622 23:45:10 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer ...
0
votes
1answer
865 views

Mysql innodb recover from frm and ibd

There are numerous question (in this site and elsewhere on the internet) about how to recover innodb database from *.frm and *.ibd file (the point: when you have the undamaged ibdata1). Situation: I ...
8
votes
2answers
329 views

Covering index used despite missing column

I have the following query, using MariaDB 10 / InnoDB: SELECT id, sender_id, receiver_id, thread_id, date_created, content FROM user_message WHERE thread_id = 12345 AND placeholder = FALSE ORDER ...