Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
1
vote
2answers
1k views

MySQL: Merge Same Database on Several Location into Single Location

I have a registration form, which is exactly the same for several locations. Each of them has a REG column, which is unique for each inserted row. Basically, each data input has a different REG number ...
0
votes
1answer
367 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 ...
1
vote
2answers
225 views

MYSQL Update data based on top 10 records of each user

I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
3
votes
2answers
274 views

Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Is there any different how mysql treats UNIQUE (column1, column2), INDEX (column1) and UNIQUE INDEX (column1, column2) and UNIQUE (column1, column2) In the latter, I think mysql will ...
0
votes
1answer
52 views

MariaDB innodb bufferpool load taking long time

When I am restarting my MariaDB database engine it takes a long time to show 'innodb bufferpool load completed' depending on data volume. Is there a way to make it faster? +----------------------------...
0
votes
0answers
21 views

MySQL Dead Lock on Delete Issue

I have recently updated my production code with the new query that is supposed to clean up orphaned records. It runs within the transaction as the last step. And, as a result, I am seeing occasional ...
0
votes
2answers
530 views

Multiple Schemas for increasing MySQL (5.7) Performance

Lets start with my question first: Does having multiple schemas (1 main and 1 daily - having 5-6 tables with same structure as main, but only working for the day and will be synced back with the main ...
1
vote
0answers
13 views

ALTER innoDB table from row to page compression

My tables have been created with InnoDB row compression (ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Not I am changing them to page compression. According to the official documentation of MariaDB, enabling ...
0
votes
1answer
33 views

Mysqld draining memory, using way more then assigned and not giving any back

This is a production server. Whenever mysqld is restarted the memory consumption slowly builds up to about 75 to 90% of my total memory (while only 8 out of 32GB is allocated to mysqld). Sometimes ...
0
votes
2answers
191 views

MySQL 5.6.26 high concurrency dead lock

Table structure: CREATE TABLE `extend_0` ( `id` bigint(20) NOT NULL, `code` varchar(30) NOT NULL, `data_key` varchar(50) NOT NULL, `data_value` varchar(200) NOT NULL, `create_time` timestamp NOT ...
3
votes
1answer
140 views

Table was deleted during ALTER, how to recover?

I tried to ALTER a table to add a FOREIGN KEY as: ALTER TABLE artists ADD FOREIGN KEY(country_id) REFERENCES countries(country_id) ON DELETE CASCADE; and I received an error of ERROR 2013 (HY000): ...
0
votes
2answers
101 views

Mysql locking - How to acquire a lock in the DB for each userId

Hi I have a J2EE Server(clustered) that executes requests from multiple users. I would like that each request of a given user will be handled like so: { acquireLockForUser(userId); // i.e. insert ...
0
votes
0answers
11 views

InnoDB Format Row from DYNAMIC to COMPRESSED increase table size (and never decrease back)

I recently decided to make some experiment with compressed table format. Most of the time when I convert a table from DYNAMIC to COMPRESSED I get a disk space saving from 50% to 80%. However yesterday ...
1
vote
1answer
859 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.
0
votes
1answer
206 views

Cannot rejoin or add instance to MySQL InnoDB cluster

I have a MySQL InnoDB cluster running where I had added three nodes, one is having the Primary role and the others are Secondary. Today morning, the two secondary nodes were removed from the cluster ...
3
votes
1answer
9k views

Very poor insert performance MySQL / Percona Server

I'm running a Percona-Server instance off an Ubuntu server install. I'm using an application that needs to access this database, and its having VERY poor performance. Once the database has been ...
0
votes
2answers
629 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 ...
-1
votes
0answers
16 views

Why my rds instance has so high write iops?

the sum count of the tps is about 500 (Com_insert: 30, Com_delete: 3~4, Com_update: 450~500) while the write iops is 8k+ and I found that the Create_tmp_disk_table is nearly always 0 ...
0
votes
1answer
398 views

Cannot allocate memory for the buffer pool when using remote access MySQL

I tried to separate the webserver and mysql server. MySQL server = 16GB RAM and the config of Innodb_buffer_pool_size = 10GB. And my Web server = 4GB RAM My website connect to my database server ...
0
votes
1answer
93 views

Simple Query intermittently returning 0 results

I'm having a strange problem and need help troubleshooting. if I run this query on my gallery table: SELECT * FROM `gallery` WHERE awarded = 1 ORDER BY awarded_date DESC LIMIT 29 OFFSET ...
0
votes
1answer
133 views

Backing up a single node Percona XtraDB Cluster

I have a single node percona XtraDB Cluster. Version: Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.5, Revision 759, wsrep_25.5.r4061 All tables in the DB are using InnoDB ...
0
votes
2answers
258 views

Is a update-only-once-row table worth sharding?

I have a big MySQL 5.6 Inno DB table that contains user's request. A table row updates only once, shortly-after the initial insert! (The initial insert is to get the unique auto-incrementing ...
0
votes
0answers
11 views

How to resolve the corrupted innodb table

I had a corrupted InnoDB MySql database after the customer downloading the wamp server on the computer again for wrong! so I worked on a lot and I got back most of the data, but one of the tables not ...
0
votes
0answers
38 views

Why initializing variable with var = (SELECT .. FROM t) puts an S lock on selected rows, but SELECT .. INTO do not put locks?

How to repeat: -- Create table CREATE TABLE test_lock_s ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , user INT UNSIGNED DEFAULT NULL , PRIMARY KEY (id) ); -- Fill it with some data INSERT INTO ...
0
votes
1answer
897 views

InnoDB ibdata1 corrupted, innodb_force_recover not working

I think I have checked every post in the internet. Thing is my MySQL (MariaDB) won't come up and after much testing I have seen it is because of the InnoDB files. I have tried with different ...
0
votes
2answers
92 views

Time taken to drop/add index is lesser than adding a column. Why is that?

I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though. I'm performing an alter by default it does ONLINE alter. When I add column it takes at least 50 mins for 50G table in our ...
0
votes
0answers
24 views

MySQL database is corrupted each time the server is restarted

It has happened to me several times that there has been a power failure of the MySQL server. I know that the tables can be corrupted, but in my case it never happened that the server restarted without ...
0
votes
1answer
2k views

Innodb: When bulk inserting or updating - how to disable transactions?

When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using ...
1
vote
0answers
31 views

Got fatal error 1236 from master when reading data from binary log

I am trying to do a MySQL failover, in some situations I endup with broken replication. Any idea what could be the cause of it ? Background Needed to perform a host updates on master host. Hence ...
4
votes
2answers
24k views

The best MySQL Settings for 32GB RAM on a Dedicated Server

I am worrying about my memory usage as it is reaching more than 90% if I will not restart php-fpm. I have the following configuration on my MySQL: [client] port = 3306 socket = /...
1
vote
0answers
46 views

MariaDB service fails to start with InnoDB Space id error

I've been running MariaDB on a Raspberry Pi 4 (Raspbian 10 / buster) as the database server for my Webtrees and Owncloud sites. Recently, I noticed that the server wasn't looking right, and after a ...
0
votes
1answer
2k 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 ...
1
vote
2answers
319 views

What is the most convenient indexing strategy for two foreign key constraints?

Which of the two following options is more convenient for indexing the relevant foreign keys? Option A: Create Table table3( t3_id int not null auto_increment, t1_id int not null, t2_id int ...
1
vote
1answer
335 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
0answers
23 views

How can I tune my MySQL server to bulk import XML file (42 GB)?

I have a 40GB xml file which needs to be loaded into the db. Currently we are not sure about PK/FK relationships or schema details. So I am just loading them up without any indexes or keys. Every time ...
1
vote
0answers
864 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
236 views

SHOW INNODB STATUS averages calculated

I am reading to all tutorials/books that when running SHOW ENGINE INNODB STATUS the Per second averages calculated from the last X sec, the X should be 20 to 30 seconds. I have never manage to exceed ...
0
votes
0answers
10 views

MySQL 5.7 32-bit table is full on unix

As per the subject, I'm receiving an error on an InnoDB table: "table is full" when it reached 4GB the filesystem is far from being full so the only thing that I'm thinking right now is that ...
0
votes
0answers
9 views

Truncating partition locking whole table in mysql

Novice here. I have a huge data that is generated every day and are stored in day wise partition. However, I have a cron that automatically deletes the old day (30 days ago). The query for deletion is ...
0
votes
1answer
1k views

MariaDB crashed: Unknown/unsupported storage engine: InnoDB

I've a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I've done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables: 2020-10-17 0:51:18 ...
0
votes
1answer
15 views

Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance

In the official documentation of Boolean Full-Text Searches: Boolean full-text searches have these characteristics: They do not automatically sort rows in order of decreasing relevance. However, ...
0
votes
1answer
101 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
34 views

Can large data deletion affect Innodb buffer pool performance? [duplicate]

At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of ...
0
votes
1answer
92 views

generate unique primary key for all clients from the local database and NOT the server

I'm using MySQL Innodb database to work as a distributed database among many places and also each place will work offline but at the end all the data will be centralized in one server, I want to ...
1
vote
1answer
186 views

Small MySQL table swap/replace taking incredibly long time

We have an import job that commits data to a staging table, and then swaps the production/staging tables using the below SQL: RENAME TABLE user_accounts TO user_accounts_temp, user_accounts_staging ...
1
vote
1answer
3k views

Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

I know it is often asking question. But I can't find answer for my scenario with specific condition. We use MySQL 5.5 We have always(I am sure) innodb_file_per_table = ON option in our mysql ...
3
votes
2answers
5k views

Ample disk space with error: “1034 Incorrect key file for table 'table_name'; try to repair it”

I'm attempting to alter a 1.6 GB InnoDB table with the following query: ALTER TABLE `table_name` ADD `field_id` int(11) unsigned DEFAULT NULL The query fails with the following error: 1034 ...
0
votes
1answer
109 views

How to set mariadb read_write for some specific tables while starting as read_only=1

I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But ...
0
votes
2answers
2k views

MySQL crashes and currently the error.log file is huge

I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs ...
0
votes
0answers
94 views

How to restore a MySQL InnoDB with ONLY .ibd files?

I had a Bitnami Joomla! stack running on a computer that suddenly kicked the bucket. I had neglected to make a back-up before this happened so I do not have any dumps or backed up files, but was able ...

1
2 3 4 5
42