Questions tagged [innodb]

1

votes
0

answer
9

Views

Disk io in mysql clusterd index

I learned that the leaf node of a cluster index in mysql stores row data. If data is retrieved from the cluster index, does not physical disk io occur?
dkp
1

votes
1

answer
25

Views

In InnoDB,is all sql in the transaction?

I seems have a wrong idea that : update table_name set id=222 where id >333; my old opinion is above single sql without begin and commit will not start a transaction. but it seems wrong. But when i read the mysql doc,i found that seems that all sql will in a transaction.if you do not explicit use...
Mattia Dinosaur
1

votes
2

answer
54

Views

MariaDB - INNODB skipping the number sequence while creating incremental records - why?

I do not know if this is expected behavior with INNODB, but I really think it's totally weird. If I use the same SQL statement using MYISAM, the behavior occurs as expected. MYISAM CREATE TABLE main_database.numero ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) ) ENGINE = MYISAM DEFAULT CHARSET...
Magno Alberto
1

votes
2

answer
7.4k

Views

MySQL REPLACE in an auto incremented row

Let say I have a MySQL table which contains three columns: id, a and b and the column named id is an AUTO INCREMENT field. If I pass a query like the following to MySQL, it will works fine: REPLACE INTO `table` (`id`, `a`, `b`) VALUES (1, 'A', 'B') But if I skip the field id it will no longer works,...
Mahdi
1

votes
2

answer
807

Views

MySql missing from Services - could not open innodb_index_stats.ibd

I have Ampps 3.7 installed on Windows 10, and whilst Apache runs ok Mysql will no longer start up; It use to run ok but now has an innidb error. My issue is similar to the question here, except that I've not upgraded mysql, and the suggested solution is to delete and reinstate several inno_db tables...
Nick W
1

votes
1

answer
255

Views

MySQL DELAYED vs LOW_PRIORITY - what is the difference?

Is there any difference between: INSERT DELAYED INTO tableA SET val='1' and INSERT LOW_PRIORITY INTO tableA SET val='1' both are supported by the official MySQL doc and there is a dedicated section to INSERT DELAYED This section says that DELAYED is planned for removal in future releases. DELAYED i...
michnovka
1

votes
1

answer
1.6k

Views

How can I solve “[ERROR] InnoDB: Operating system error number 87 in a file operation” on mysql?

When I run : mysqld --initialize, on the DESKTOP-HU2DG1T.err file exist error like this : 2018-01-30T09:35:35.078489Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-01-30T09:35:35....
Success Man
1

votes
1

answer
117

Views

How Mysql Query Plan is generated?

Mysql before executing a query with multiple joins. It creates multiple query plan and find the lowest cost plan to execute the query(approx). My questions are How the optimiser will perform a cost-based calculation to select a plan on what basis(like number of fields in the where condition)? How My...
vinieth
1

votes
1

answer
125

Views

Locking a row in a MYSQL table during a transaction

I have a batch of queries like this: START TRANSACTION SELECT amount FROM piggybank WHERE id = 2 UPDATE piggybank SET amount = amount + 5 WHERE id = 1 COMMIT I need to make sure that no one else can read or write the rows with ID 1 and 2, until the transaction ends. (Locking the whole table would be...
Askerman
1

votes
0

answer
43

Views

MediumBLOB will not support a 4KB File - Data too long for column

So yeah, I am adding billing documents as data and as XML in a Mediumblob column to then query it and export as PDF but the error says Data too long for column 'XML' at row 1 max_allowed_packet is currently 16777216 both in my.ini and in the database. Innodb log was raised from 8M to 16 even 32 Mand...
Glas
1

votes
0

answer
139

Views

Very slow INSERT using InnoDB with unique key

I'm getting very slow inserts, about 40ms each, on small InnoDB tables, for this table: CREATE TABLE IF NOT EXISTS events ( serial INT NOT NULL, -- client side serial number time BIGINT NOT NULL, -- UNIX timestamp, client side, not server side shard...
John Nagle
1

votes
1

answer
167

Views

How to solve Deadlock in query with sub select?

When I try to make UPDATE with subselect I catch deadlocks. I caught this deadlock in my application I am using Hibernate for get data from DB and in my log, I see next: Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarti...
Volodymyr Bilovus
1

votes
1

answer
579

Views

Windows service for MySQL 5.7 suddenly won't start. Error 1067

I have had a MySQL5.7 master database on a Win7 machine that replicates to a Linux slave. It seems that after working for about a month, the Windows service for MySQL 5.7 server can no longer start. Running it from services.msc it gives Error 1067. Here is the MySQL error log: 2018-04-03T19:25:18.7...
oldgregg
1

votes
0

answer
456

Views

ERROR 1049 (42000): Unknown database 'test'

I am trying to install mySQL server on CentOS6.6 without root access. Here are my steps. Unpack mysql-8.0.11-linux-glibc2.12-i686 in a directory(EX:[Base MySQL Directory]) Initialize and generate root password: ./mysqld --initialize --user=mysql --lc_messages_dir=[Base MySQL Directory]/share --bas...
Ted
1

votes
0

answer
30

Views

Execute List Query on Table

I want to change all table type on my database but not using SUPER USER. I want to try change for all table using ALTER TABLE script. Do you know how I can execute any query on list SELECT QUERY result? This example query I create : SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE...
Wisnu
1

votes
0

answer
885

Views

JSON_EXTRACT does not exist in mysql with mysql 5.7.22

My query is SELECT JSON_EXTRACT('{'test_Id':'661_0t7sb-52','user_id':'661'}','user_id'); and I'm getting error MySQL said: Documentation 1305 - FUNCTION test.JSON_EXTRACT does not exist how to solve this?
Er Kapil Mehta
1

votes
1

answer
18

Views

Selecting multiple products from the same category?

Statement Attempt 1: It seemed to ignore everything and get products from anywhere regardless of country SELECT * FROM products WHERE `country` = 'America' AND product = 'Product 1' AND product = 'Product 2' AND completed = 'complete' Statement Attempt 2: The result of this was that is returned 0 re...
John Konen
1

votes
2

answer
31

Views

Multi employer to employee manage

Hope you are doing good. I am facing issue in multiple employer to employee. What's going on my mind: Employer table employerID, fname, lname, created_at employee table employeeID fname lname creted_at This is structure of basic tables then after i created separate table for relation for multi emplo...
Bhargav Dave
1

votes
0

answer
25

Views

How can i know through which mysql router request has been sent to InnoDB cluster in a multirouter environment

I had configured my Java application to connect through multiple routers to the InnoDB cluster so that if one of my router stops working other router can take up the routing task (I have 6 router configured ) Now Can some one help me in finding through which router request has been sent in a multir...
Uttam Dey
1

votes
1

answer
46

Views

I want to achieve row level locking using php in mysql

I am designing this page where users will see the change orders for the day. I want only one person to edit one change order at a time. While other user can still edit other change order. The code i am pasting is for final page where the user is redirected after clicking on particular change order....
addminuse
1

votes
3

answer
77

Views

Weird behavior optimizing query indices (MariaDB + InnoDB)

I'm currently trying to optimize the indices for a quite large table of a project and experiencing a very counter intuitive behavior between the explain result and the actual query runtime. The server is running MariaDB version 10.1.26-MariaDB-0+deb9u1 with the following configuration options: key_b...
Jens N
1

votes
0

answer
265

Views

MySQL high CPU usage slows down the server, how to optimize my query?

I have a problem when I'm reading data from MySQL database in every second with multiple clients MySQL CPU usage raises to 26-27% and after that my other service which measures and logs real time data into database slows down/crashes. So I run two different web applications in same server: Applicati...
Tvak
1

votes
1

answer
198

Views

MySql 5.6: Ignores innodb_page_size setting in my.ini

So I tried to increase the innodb_page_size to 32k, by setting this value in my.ini: innodb_page_size=32k I then stopped the MySql service, and restarted it. The service in Win looks like this: 'C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld' --defaults-file='C:\ProgramData\MySQL\MySQL Server 5....
Ted
1

votes
1

answer
252

Views

MySQL error 1114: The table is full

x86 MySQL 5.7.23 on Win 7 Pro x86. NTFS. Encountering error 1114 'the table is full' when the table space exceeds 4G. I've tried creating databases with both 0 and 1 for the 'file_per_table' setting. This thread How many rows can mysql store? suggests I should be good all the way up to 2T. Converti...
Tom Price
1

votes
0

answer
17

Views

MySQL InnoDB Deadlock on two instances of the same query with primary key

Two instances of the same query, executed from the same code path, each in their own transaction, are non-deterministically deadlocking on one another in my MySQL 5.7 database. The issue is only observed when application servers are under heavy load. The queries are select'ing the row to update by i...
Jason
1

votes
1

answer
413

Views

MySQL:Cannot create cluster Got Error:The server is not configured properly to be an active member of the group

I want to create a MySQL cluster (InnoDB) so I create three AWS EC2 Instances (CentOS 7). When I run var cluster = dba.createCluster('testCluster'); I got a Error below: A new InnoDB cluster will be created on instance '[email protected]:3306'. Validating...
Rukeith
1

votes
1

answer
23

Views

mysql copy file between different version of InnoDB

I got two MySql instances (A and B) running with InnoDB storage engine. I want to perform a backup from A and B. What I am trying to do is copy data file directly from A to B. Will there be any conflict on B after startup? To put it in another way: are the file format of different versions of InnoDB...
walkerlala
1

votes
1

answer
655

Views

innodb_additional_mem_pool_size in MySQL 5.7

Official MySQL documentation says: innodb_use_sys_malloc and innodb_additional_mem_pool_size were deprecated in MySQL 5.6 and removed in MySQL 5.7. Is there an alternative to this parameter in MySQL 5.7? How i can specify additional memory size for MySQL with manual mode?
spikeyspik
1

votes
0

answer
20

Views

Amazon LightSail - Param Mysql Native

I need to set up Amazon Lightsail native Mysql database parameters but I could not find any place that could make these changes: See eg.: innodb_flush_log_at_trx_commit = 1 Change to 0 I try via Console Mysql but no permission
Santos
1

votes
1

answer
18

Views

InnoDB monitor output unclear, foreign key constraint fails

I have two database tables, InnoDB, both have a column id_fornitore, there's no way to create a foreign key and i don't understand why. It's a simple foreign key, and I have succesfully created other on the same table. Here is my query: ALTER TABLE tbl_prima_nota ADD CONSTRAINT fk_id_fornitore FOREI...
uomopalese
1

votes
1

answer
36

Views

MySQL lock and statements that lock rows using a unique index

In MySQL document 14.6.1 InnoDB Locking 'Gap Locks' section, it is said Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. ... For example, if the id column has a unique index, the following statement uses only an index-record lock for the row ha...
da_miao_zi
1

votes
1

answer
174

Views

MySQL auto_increment innodb_autoinc_lock_mode = 2 but still populating in sequence sometimes

I have the MySQL my.cnf file settings of innodb_autoinc_lock_mode = 2 but looking at a table, the primary key is sometimes not following that rule. I mostly have: 710 712 714 716 718 etc... which is fine. But in some records I have: 720 722 723 724 725 726 728 730 etc... Should the increment by 2 al...
Casy Choate
1

votes
2

answer
400

Views

MySQL 100% CPU every 12-18 hours

I've inherited a Windows Server (2016) and we have a proprietary piece of software not written by me which uses a MySQL db. I am a novice w/ SQL. There is client software that will connect to the server randomly throughout the day. Anywhere between 10 and 200 different computers will log into this s...
sla_seth
1

votes
0

answer
73

Views

MySQL - lock row for reading

I am using socket.io server and multiple clients are making requests to this server. Server checks a MySQL innoDB table for data and if there is some data it updates its bit/column in table and send data to requested client. But the problem is my multiple clients are occasionally getting same data....
Irfan DANISH
1

votes
1

answer
33

Views

MariaDB: SELECT in stored procedure locks rows

I have a problem with SELECT in stored procedure. Isolation level is READ-COMMITED, engine is InnoDB so all select should be nonlocking consistent read: https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html Database is MariaDB version 10.1: MariaDB [(none)]> select @@version; +--------...
Pustovalov Dmitry
1

votes
1

answer
34

Views

Structure of auto increment index in MySQL,InnoDB

When I use auto increment index,it is more efficient. ---《High Performance MySQL》 High Performance MySQL But B+ tree's build process is flow: B+ tree build It is not full or 15/16 ,only half in the red circle,and never insert data in the future in the red circle when I use auto increment index...
chensir
1

votes
2

answer
89

Views

Do MySQL transactions for INSERT lock foreign key referenced tables?

I am trying to do a huge transaction in my Java application and doing single insert entries (volume in thousands) for user_account_entry table which has foreign key reference to user table. When the transaction is running, i am not able to update the any user entity for which belongs to the transact...
Paras Diwan
1

votes
1

answer
95

Views

MySQL: Cannot set MySQL server for InnoDB Cluster with dba.configureLocalInstance()

I am trying to setup a Production Deployment of InnoDB Cluster (https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-production-deployment.html) by following the steps outlined in the documentation. I am using MySQL 5.7.25 My MySQL server starts successfully and I run (from the MySQL Shell)...
Ignacio de Castro
1

votes
0

answer
31

Views

Different numbers of rows when importing csv

I ran into a problem that I didn't understand. I have a .csv file that is about 10 GB in size. I'm transferring it into the database. When I use only the innodb engine, 1.863.941 lines are added. But when I use the Aria engine, 1.765.972 lines are added. I can't see how many rows there are in this o...
Özgür Can Karagöz
1

votes
2

answer
49

Views

SQL Error: Row size too large for VARCHAR(20)

I'm trying to create a column named 'brand_slug' type VARCHAR(20), nullable, collation 'utf8mb4_unicode_ci' and I'm getting the error: SQL Error: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. The weird thing is that for some VARCHAR columns it allow...
Bogdan Pirvu

View additional questions