Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
1,871
questions
2
votes
1answer
25 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
0answers
9 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
12 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
13 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
21 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
21 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
26 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
48 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
1answer
8 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
29 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
6 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
22 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
31 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
26 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
21 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
22 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
15 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
429 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
34 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
25 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
28 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
20 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
13 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
25 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
93 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
19 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
104 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
11 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
39 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
42 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
25 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
11 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
25 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
29 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 ...
0
votes
1answer
61 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
26 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
40 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
170 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
327 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 ...
0
votes
0answers
51 views
Mysql 5.7.25 crash on first query to corrupted innodb table
Setup:
Ubuntu 18.04.1 LTS
4.15.0-34-generic #37-Ubuntu
Docker: 18.06.1-ce build e68fc7a
mysql:5@sha256:e47e309f72c831cf880cc0e1990b9c5ac427016acdc71346a36c83806ca79bb4
mysql --version = Ver 14.14 ...
0
votes
0answers
5 views
InnoDB clustered index performance when using random values as primary key
By default, my primary keys of InnoDB storage engines are auto increment integers. In effort, to hide the number of rows in the database, application code implements some random generator for the ...
0
votes
0answers
26 views
innodb file per table
We are running with shared tablespace on MySQL database server in our environment. We are looking to enable file per table on database. We want to know
What would be ideal plan to enable file per ...
0
votes
0answers
15 views
Mysql 8.0.16 crash due to informatiin_schema.Keywords table
How to resolve issue of mysql 8.0.16 crash?mysql 8.0.16 crash many times and shown server logs due to invalid argument.and in error log query is select WORD from information_schema.KEYWORDS.
how to ...
2
votes
1answer
182 views
How to clear the InnoDB redo log before starting mysqld for crash recovery
System: Windows Server 2012 R2, MariaDB 10.2.8
We are currently trying to recover from a quasi-crash (it was a normal shutdown, but mysqld won't start up again)
and are having problems with the redo ...
0
votes
1answer
123 views
MySQL 5.6 too much write after enabling compression
Recently I have enabled table compression (i.e. ROW_FORMAT=COMPRESSED) on half of the tables in a MySQL 5.6 server. I am observing:
much higher write IOPS (~700 -> ~3000)
dangerously lower dirty ...
0
votes
0answers
29 views
Different Permissions Behavior For Information Schema on Views
I have a view which have following definition
Create view view1
as select *
from information_schema.events
where event_schema="database1"
I have granted select permissions on the view1 to ...
1
vote
2answers
76 views
MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table?
In our system, we don't use MyISAM - only InnoDB (and occasionally, MEMORY).
Is it safe to set key_buffer_size to 0? Or there are unexpected side effects? Based on the manual, it seems it's safe.
0
votes
2answers
88 views
innodb high i/o , some parameters make mysql crash
i have a windows 2012 server with 16 core cpu and 64 gig of ram and 3*146gig sas 15k that have been used as a database server and just have mysql installed as a part of xampp.
the mysql version is 5....
