Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

0
votes
0answers
19 views

Mysql 8.0 table_rows problem

I'm doing an application and I have the following problem: I'm doing a LOAD DATA INFILE into a table from a txt file, that has 2 million rows. I want to see the table_rows of my table with: SELECT ...
0
votes
1answer
29 views

MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows: CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(100), PRIMARY KEY (id) ) ...
0
votes
1answer
32 views

Will too many tables slow Mysql down?

I'm making a database which should be able to handle over 1,000,000,000 rows. in order to optimize it's performance i've decided to split tables. so instead of having 1,000,000,000 entries in one ...
0
votes
1answer
28 views

What will be the order of statement execution in this use case?

Say I execute select * from users at t1 which gets finised at t3. At t2(in between t1 and t3) update comes for some of the records in users table say update users set address='xyz' where name = 'abc'....
0
votes
0answers
46 views

MySQL 5.1.73 Tuning for PHP5.6 Application [on hold]

I have a PHP5.6 application running Symfony 2.x, backed by MySQL 5.1.73. We are currently experiencing latency in our data tier during peak load. During normal operation we see about 2k queries per ...
1
vote
0answers
38 views

The GET_LOCK() and RELEASE_LOCK() functions are taking too long

We are trying to use the MySQL's locking functions GET_LOCK() and RELEASE_LOCK() in our Spring Java application. The code we are using is defined in a procedure for both seperately which simply invoke ...
0
votes
0answers
42 views

buffer overflow detected in mariadb 10.3.11

We are getting Assertion failure every time memory usage of MySQL spikes up to the overall system's memory. Example: InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.3....
2
votes
1answer
76 views

Mysql/innodb deadlock on simple delete query

Mysql/innodb 8.0.16, read committed transactions, several statements in transaction raise deadlock on deletion of non intercepting rows. Trying to understand what is happening: -----------------------...
0
votes
1answer
17 views

How to change all columns from one type to another, changing all FK columns as well?

In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint. For a few of those, it's an imminent problem, since this optimistic optimization clashes with the ...
0
votes
1answer
17 views

Make Xtrabackup ignore myisam tables & backup only innoDB tables?

I'm using mysql 5.7 (or rather percona 5.7) Is it possible to backup only the innodb tables (easily, not to specify each table one by one because I have a lot of tables) with xtrabackup? and not take ...
0
votes
2answers
37 views

Query not returning results if selecting by indexed columns (Mysql 5.6)

We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column ...
3
votes
2answers
84 views

Gap Locking in Read Committed isolation level in Mysql

MYSQL VERSION : 5.7.X STORAGE ENGINE : Innodb I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where ...
0
votes
0answers
42 views

How to improve the speed of a large data migration between SQL Server and MySQL server?

I have an application that is connected to a SQL Server Express database. The database grew and the database is almost at 10GB which is the max limit that SQL Server Express allows for. I am trying ...
1
vote
0answers
19 views

Preserving some data on rollback in InnoDB?

I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table. Now, ...
0
votes
1answer
29 views

Wrong InnoDB table status (Size/Rows) after updating from MySQL 5.7 to 8

I've upgraded from Mysql 5.7 to 8 on Ubuntu 18.04 Made it by guide, very accurate, on a new empty server, checked all error/warning logs and fixed it. Everything works just fine with it. I have 2 ...
0
votes
0answers
43 views

INSERT INTO SELECT for a large table

I have a large table (200 million rows, 40GB). I tried to create a new table out of it by INSERT INTO SELECT. The process took 5 hours and consumed over 60GB of RAM. The size of the ibd file of the ...
0
votes
2answers
36 views

What is going on with my user and max date query?

OK query below. Hitting about a half million rows and I am trying to grab the last time a user (uid) touched a page on our system denoted by LogDate. I have used this query quite a few times but ...
1
vote
1answer
36 views

Optimize MySQL InnoDB query for SUM and Order By

I have an MySQL InnoDB table with 1.3M rows and 670MB size: +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------...
1
vote
3answers
66 views

Mysql innodb table dead lock while batch update in multiple thread

I've two tables of innoDB type and I'm updating one table using batch statement from 10 thread (one batch statement per thread) but some time I'm getting dead lock. java.sql.BatchUpdateException: ...
4
votes
1answer
74 views

Is there a way to improve performance of this simple SELECT query?

I'm still relatively new to MySQL so please forgive any ignorance on my part. I've tried looking through other posts on here and StackExchange, but haven't had much luck in improving the performance ...
0
votes
0answers
29 views

Counter Field that Increments on Update?

I need a "counter" field that gets the latest value of a counter and then increments the global counter value so that the next update on a reocrd gets the new latest value. For example, lets say this ...
0
votes
0answers
30 views

MySQL InnoDB: how to interpret this InnoDB monitor output of Next-Key locks?

Next-Key Locks from MySQL documentation A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. InnoDB performs row-level ...
0
votes
1answer
23 views

Table metadata lock MariaDB

I'm having serious problems in a DB, where a "Waiting for table level lock" box is loitering and locks the whole system: Based on some research, when the problem occurred to get the command "SELECT * ...
3
votes
1answer
119 views

Are innodb_large_prefix and innodb_file_format settings backwards compatible?

I got the following message when I tried to install a piece of software trough the command line. I use a mySQL db. * innodb_large_prefix = ON is required. * innodb_file_format = Barracuda is ...
0
votes
1answer
26 views

How to PARTITION a table in mysql?

I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of CREATE TABLE pages ( page_id int(11) ...
1
vote
1answer
40 views

Much slower replication on Innodb compared to myisam

Good morning. I wanted to test how InnoDB will work on my servers and for this I made a slave and connected it to my master with myisam. The problem is that when the slave works on innodb, replication ...
0
votes
0answers
22 views

Innodb errors - considering changing to aria

On my local machine, using XAMPP, my MySQL would intermittently not start. I have followed lots of solutions for this, and the innodb_force_recovery=1 usually fixes it, at least for a while. It is ...
0
votes
2answers
18 views

Considerable size difference between mysql versions

We usually employ different MySQL versions for different scenarios. And I realized that similar databases, whith the same amount of tables and data, just because of different mysql versions differs in ...
0
votes
2answers
38 views

How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

I wrote the following SQL query to group results and then sort results in each group by date: SELECT origin, provider, SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY departure_date ...
3
votes
0answers
25 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
0
votes
1answer
56 views

Subtract values in same column from 2 different dates in same Mysql table

I have a MySql table like: Item Date Likes Book 2012-05-20 402 Book 2012-05-21 432 Book 2012-05-22 450 Pen 2012-05-20 20 Pen 2012-05-...
0
votes
1answer
40 views

How to find the MySQL query that got rolled back

I want to know if a certain transaction got rolled back. If we analyze SHOW ENGINE INNODB STATUS\G; we get the list rolled back transactions with transactionid , but how to get the actual query ...
0
votes
1answer
33 views

Planning and Optimizing the table structure InnoDB MySql

I am planning to have an InnoDB MySql table with tens of millions of rows. Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or ...
0
votes
2answers
109 views

Upgrade Antelope to Barracuda on Slave

I received a Last_Errno: 1118, Last_Error: Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row ...
1
vote
0answers
29 views

Why is my InnoDB table size so much larger than the data?

I have a MySQL table (version 8.0.13). One table is unreasonably large and doesn't correspond to the current data. I rebuilt the table by running ALTER TABLE users FORCE and OPTIMIZE TABLE users, but ...
1
vote
1answer
128 views

MariaDB 10.3: InnoDB much slower than MyISAM

I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine: drop ...
0
votes
2answers
38 views

How does MySQL store data, can I detach a mysql Database?

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
0
votes
0answers
47 views

MySQL InnoDb crash when importing tablespace from remote machine

InnoDB crashes when importing ibd file from remote machine. Source version is MySQL-5.6.40, Destination version is MySQL-5.6.40. Database was backed up was backed Percona Xtrabackup with xbstream ...
0
votes
1answer
36 views

Safe online dumping of an InnoDB database in the presence of DDL changes

When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + ...
1
vote
2answers
69 views

Overly eager explicit row locking causes deadlock

I'm running into deadlock problems on MariaDB using the INNODB engine. In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those....
0
votes
0answers
35 views

tokudb, rocksdb or aria engine with huge number of tables

My question is about what engine to use with a super big quantity of tables and lots of tables with some million rows. My quantity of table is increasing and it will get to near 100 000 tables... ...
4
votes
2answers
49 views

Performance issues with groupwise MAX in JOIN clause

Problem I have some assets in my application which are updated from time to time in an asynchronous fashion. The example I'm gonna use here is Vehicles. There are two tables: Vehicles: holds ...
2
votes
1answer
112 views

SELECT execution is slow on InnoDB when compared to MyISAM

We experiencing a slow select on InnoDB comparing to MyISAM - difference for execution on MyISAM is about 2 min., on InnoDB is 2 h. The number of rows involved is 33 M. The row set in result is about ...
5
votes
4answers
463 views

Which columns should be indexed when all may be used in different search queries?

Background I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, ...
0
votes
1answer
39 views

MySQL: Change compression on an existing InnoDB table

Is there any way to turn on compression for an existing table? I don't care if there's downtime for this. The docs only mention setting compression when the table is created.
0
votes
2answers
133 views

Unable to fix ROW size too large even with innodb_file_per_table

I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large" Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table` Row size ...
0
votes
1answer
72 views

Unable to turn on compression using innodb

I am unable to create a new table using compression. I'm using the following options which from what I understand should allow me to use compression. default-storage-engine = InnoDB ...
2
votes
2answers
71 views

When does MySQL begin to start sending data to a client?

I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to ...
0
votes
0answers
72 views

Mysql 8.0:Innodb index performance loss until server is dead. Performance perfect after index is re-created?

Important Update Spent 15 hours on this problem, rebuilt and changed indexes and the table many times. Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue. The "...
1
vote
0answers
19 views

Getting DEADLOCKS frequently on Versioned TABLE

I am having trouble to understand the increase in DEADLOCKS for Update transactions on Versioned TABLES. The problem, was not present earlier and then we enabled the versioning and after a while we ...