Questions tagged [mysql]
All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.
4,763
questions with no upvoted or accepted answers
13
votes
1
answer
1k
views
MySQL deadlock: upsert query acquiring gap lock twice?
I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen.
Here is the deadlock output:
------...
- 131
12
votes
1
answer
16k
views
Conceptual data model for a billing system
I am working on an existing MySQL database. My task is to create a new data model for a billing system on that existing application.
I have created a conceptual data model (click to enlarge):
##...
- 2,194
9
votes
1
answer
1k
views
Select * from statement execute very slowly, innodb io read speed is low
I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
- 91
8
votes
0
answers
14k
views
Generate UUID v4 automatically in MySQL
Is it possible to generate a UUID version 4 (completely random) in MySQL? I would like to use it as a primary key.
Use Case
Many systems will insert to the table, some may specify a UUID when ...
- 181
8
votes
1
answer
2k
views
MySQL: Is the read committed isolation level safe with mixed binary log format?
When using the MIXED format with the READ COMMITTED isolation level, am I safe?
The docs state (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed)
If you use READ ...
- 181
8
votes
1
answer
3k
views
MySQL query to create a Matrix view using pivot
I have a table with the following fields:
p1
p2
s1
s2
where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2
The number of players can vary from 3 - 10.
The table have the results between them....
- 181
8
votes
1
answer
125
views
Does MySQL/InnoDB use table caches when dealing with foreign tables?
TL;DR: Does a table referred to via schema.table get held in table cache?
I have a large number of client databases that our application connects to based on an http request. These are used for every ...
- 251
8
votes
1
answer
1k
views
RDS MYSQL instance rebooted and won't load INNODB table
So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
- 203
6
votes
0
answers
6k
views
Getting "Cannot open datafile for read-only" for an intentionally DROPped InnoDB table
It's been too long to remember how we got here accurately, but we get the following error on MySQL start (though it does still start the service with InnoDB):
[ERROR] InnoDB: Cannot open datafile for ...
6
votes
1
answer
2k
views
Unable to get rid of orphan temporary tables
After migrating from MariaDB 5.5 to 10.0, I noticed an error message mentioning a table named piwik/#sql-1526_3a.
From there I started googling and landed on the innodb troubleshooting docs
Indeed, ...
- 161
6
votes
0
answers
3k
views
Random Spikes in DB Connections - Amazon RDS
I'm running an EC2 instance (c3.large) with an RDS instance (db.t2.small). I'm frequently having issues with DB Connections spikes all of a sudden.
PROBLEM:
As you can see, I'm getting huge amounts ...
- 111
6
votes
0
answers
681
views
Create an SSL connection with ODBC driver to MySQL(5.6) using openSSL
I want to create an asymmetric SSL connection from MySQL to an ODBC driver but don't fully know how to generate the keys and certificates (I'm using openSSL as recommended here: https://dev.mysql.com/...
- 115
6
votes
2
answers
14k
views
MySQL - run SELECT statement on another server without defining the table structure
In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally.
In MS SQL Server, however, I can run any SQL statement against a ...
- 161
5
votes
0
answers
400
views
Is setting a foreign key as primary key in a One-to-One relationship a good idea?
I have a table user and a table user_statistic with a one to one (1:1) relationship.
Note that we have an identifying relationship between user and user_statistic.
Which is best practice for ...
- 167
5
votes
1
answer
2k
views
Getting duplicate record errors like "Duplicate Entry for key 'PRIMARY', error code 1062" when importing a full backup into slave
We're restoring our slave with a full backup from our master, and getting a bunch of duplicate key errors. Once the slave gets caught up, it does not throw any errors (only when it is behind the ...
- 51
5
votes
1
answer
4k
views
What to do when I get “mysqld dead but subsys locked” in my amazon ec2 micro instance?
I have an Amazon ec2 micro instance that holds a WordPress site. Since yesterday, my mysql db keep crashing saying:
[ec2-user@ip-xxx-xx-xx-xxx ~]$ sudo service mysqld status
mysqld dead but subsys ...
- 151
5
votes
1
answer
607
views
Running Triggers on Slave with Row-based Events in MySQL >=5.6
We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys
If one uses statement-based ...
- 151
5
votes
1
answer
508
views
Mysqlfailover command - No slave is listed in health status
I have successfully created replication using GTID_MODE. It works perfectly. Now I need to setup automatic failover feature in it. I have run the following command.
mysqlfailover --master=root:abc@10....
- 51
5
votes
2
answers
408
views
How to compare all rows in a table and extract data using triggers
I have two tables, one table stores the GPS data of various users and another table stores the idle time of the users based upon the GPS latitude and longitude values and timestamp difference.
Table ...
- 51
5
votes
0
answers
2k
views
Why is adding a FK constraint slow in MySQL?
I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand.
CREATE TABLE A (
`pk` INT(11) NOT NULL,
`fk` INT(11) NOT NULL,
PRIMARY KEY `pk_A` (`...
5
votes
1
answer
9k
views
How to prevent "ibdata files do not match the log sequence number"?
I am dealing with a very large set of databases that are all innodb.
I've enountered this on mysql restart too many times for my comfort:
ibdata files do not match the log sequence number
But I've ...
- 271
5
votes
0
answers
6k
views
Achieve PostgreSQL hstore functionality in MySQL
Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table.
I don't know if this feature is available out of the box in MySQL like ...
- 151
5
votes
1
answer
427
views
Store regular MySQL query results in a timeseries DB
I'm looking into installing a timeseries database like InfluxDB or Prometheus to handle data for our Grafana monitoring system.
One of the things I like to do is run a couple of MySQL queries every ...
4
votes
1
answer
151
views
MS Azure Mysql flexible server consumes all Memory
I have MS Azure MySQL Flexible server running in Azure with 4vCPU and 32GB of RAM.
It consumes all memory it gets and definitely is choking at lower configurations than 32GB, which seems to be an ...
- 41
4
votes
0
answers
79
views
MySQL - Understanding what is causing deadlocks
I've researched the topic on the official documentation and other questions, however weeks into it I'm still failing to understand what's causing deadlocks in my application. It only happens in ...
- 141
4
votes
0
answers
252
views
MySQL replication - Follower stuck and behind
I understand there are lots of similar problems and threads but non of those solution helped me. Please read through the thread I will try to give as much information as possible:
I have setup a ...
- 141
4
votes
0
answers
981
views
Database Design for tracking user flow
I want to design a user tracking system between different tables/models. I want to track the user's progress. There is a Module table which is related to sections and sections are related to sub-...
- 141
4
votes
0
answers
624
views
How to get the Slave commit timestamp using MySql replication?
I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.
By reading its ...
- 41
4
votes
0
answers
2k
views
How to put an execution time limit for MySQL Temporary table creation
We can use SET MAX_EXECUTION_TIME=10; statement to stop the execution of a SELECT statement, if it took more than 10 milliseconds to execute the query. But the same wont work for other statements like ...
- 163
4
votes
1
answer
2k
views
Cannot restore MySQL database from .frm and .ibd files
I have a bigger database with around 1000 tables. Unfortunately after a OS X update everything vanished, but I was able to recover all the file that now consists of only .frm and libd files. Following ...
- 141
4
votes
0
answers
283
views
When would you use InnoDB PAGE compression over InnoDB TABLE compression?
From what I understand, InnoDB table level compression is "transparent" and appears to perform well on the surface.
In what circumstances would it be beneficial to choose InnoDB page level ...
- 1,367
4
votes
2
answers
5k
views
How to fix high memory usage of MariaDB?
I am using MariaDB(10.1.21) with following storage engines:
InnoDB
TokuDB
Spider Engine
System Configuration is :
3GB RAM
Dual Core Processor
I have tried pt-mysql-summary tool to identify the ...
- 141
4
votes
0
answers
1k
views
DROP TRIGGER locks up database with Waiting for table metadata lock
I've tried to read MySQL's documentation on metadata locking to figure out what's going on here, but our scenario just doesn't seem to fit.
We're using MySQL 5.5.35. All tables are InnoDB
First, our ...
- 459
4
votes
0
answers
7k
views
Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave
I am trying to setup multi source replication. In my slave I have defined following two properties in its configuration file.
master-info-repository=table
relay-log-info-repository=table
These ...
- 441
4
votes
1
answer
4k
views
Values of created_tmp_disk_tables increases
I noticed that the values of Created_tmp_disk_tables increased.
At a certain time, MYD files are created in /tmp directory and my site run very slowly.
I am forced to restart the mysql service to ...
4
votes
2
answers
2k
views
change character_set_connection utf8mb4 mysql
I want to change character_set_connection utf8mb4. Its showing me utf8. I have follow this https://mathiasbynens.be/notes/mysql-utf8mb4 article and changed
[client]
default-character-set = utf8mb4
[...
- 141
4
votes
1
answer
2k
views
SymmetricDS Setup - SQL Server 2008 to MySQL
I need to replicate just a couple of tables from a Microsoft SQL Server 2008 database to a MySQL 5.7 database (one-way only). I've been struggling to find a simple solution.
I've ultimately decided ...
- 41
4
votes
0
answers
5k
views
Collation in variables
After having many issues with ISO-8859-1 and reading a lot about collations I finally understand that I need to use charset UTF8 collate utf8_unicode_ci. So I configure my whole application with these ...
- 163
4
votes
0
answers
2k
views
InnoDB crashes on trx rollback
I have InnoDB assertion failure during the trx rollback
160316 10:47:31 InnoDB: Rolling back trx with id 5ADBE4173, 1 rows to undo
160316 10:47:31 InnoDB: Assertion failure in thread ...
- 141
4
votes
2
answers
2k
views
Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output
I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...
- 71
4
votes
0
answers
245
views
Identifying table causing runaway rollback in MySQL 5.5 (InnoDB / Windows)
I have a large database (180 GB), running under Windows, with a runaway rollback that starts running as soon as the database is started up.
I have started the database with innodb_force_recovery=3 ...
- 41
4
votes
0
answers
224
views
Flexible and robust way to cull older data?
I am no DBA and that is becoming more apparent daily, as such I am having trouble keeping my query times reasonable. I have about 3.57 Million records ( at about 3.43 GB ) and it takes about 2 minutes ...
- 141
4
votes
1
answer
3k
views
Update all foreign keys to a different primary key
In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. Let'...
- 928
4
votes
1
answer
529
views
Write-lock a whole table during transaction
I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
- 141
4
votes
0
answers
119
views
creating deadlock for distributed database in mysql-(Testing purpose)
I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
- 99
4
votes
2
answers
2k
views
Update query in a trigger
I have a configuration table:
Table: refdatecol
id int(11)
startdatecol varchar(100)
enddatecol varchar(100)
datecolTable varchar(100)
This table holds the user configured column names ...
- 141
4
votes
1
answer
1k
views
InnoDB internals - checkpoint, LSN, dirty pages
I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage.
However there isn't enough information to answer my question.
When I run show global status, I can get ...
- 49
4
votes
2
answers
405
views
Query multiple rooms with different conditionals (Adults, Children)
I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per ...
- 41
4
votes
1
answer
617
views
mysql replication missed quite a lot SQL statement
I have set up a master/slave with mysql-5.1.73
The master's binlog format is "statement".
The master and slave seemed running very well with slave status:
Slave_IO_Running: Yes
...
- 141
4
votes
0
answers
1k
views
Dynamic MySQL partitioning based on Unix Time
My DB design includes multiple MYISAM tables with measurements collected online,
Each row record contains auto-incremented id, some data and an integer representing Unix time.
I am designing an ...
- 173