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,901 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
13 votes
1 answer
17k 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): ##...
Rathish Kumar B's user avatar
13 votes
1 answer
2k 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: ------...
Agniva De Sarker's user avatar
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....
Matt Suo's user avatar
8 votes
0 answers
15k 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 ...
Justin Howard's user avatar
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 ...
Bell's user avatar
  • 181
8 votes
1 answer
4k 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....
dimoss's user avatar
  • 181
8 votes
1 answer
134 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 ...
RickMeasham's user avatar
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 ...
Camden Narzt's user avatar
7 votes
0 answers
6k 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 ...
Alwin Kesler's user avatar
6 votes
0 answers
619 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 ...
jeremieca's user avatar
  • 177
6 votes
0 answers
7k 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 ...
Christopher McGowan's user avatar
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, ...
Gregory Pakosz's user avatar
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 ...
Arjun Komath's user avatar
6 votes
0 answers
698 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/...
TCulos's user avatar
  • 115
6 votes
2 answers
15k 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 ...
Fidel's user avatar
  • 161
5 votes
0 answers
658 views

When/how does removing an index cause a table to lock in MySQL?

Dropping an index in MySQL 8 is INPLACE, not INSTANT, per https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html. As I understand it, INPLACE means that there is potentially some ...
sandinmyjoints's user avatar
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 ...
John's user avatar
  • 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 ...
Arun Antony's user avatar
5 votes
1 answer
634 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 ...
Daniel Sachs's user avatar
5 votes
1 answer
511 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....
Aneesha's user avatar
  • 51
5 votes
2 answers
422 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 ...
RAJESH KUMAR's user avatar
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 ...
ck_'s user avatar
  • 271
5 votes
1 answer
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 ...
fortm's user avatar
  • 151
5 votes
1 answer
494 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 ...
Martijn - DMARC Analyzer's user avatar
4 votes
0 answers
203 views

MySQL: how to kill a user lock held by a terminated thread

We're using MySQL 8.0.32 hosted by AWS RDS. mysql> SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.32 | +-------...
Alex's user avatar
  • 161
4 votes
1 answer
732 views

AWS Aurora Mysql seemingly not picking PRIMARY or secondary index

Relevant System Info: Aurora Mysql 8.0.mysql_aurora.3.03 rg6.xl instances (1 writer, 2 read replicas) Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot) I have been ...
Randoneering's user avatar
4 votes
0 answers
773 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 ...
Sisir's user avatar
  • 141
4 votes
1 answer
405 views

Brew Upgrade Mysql: sdi_delete failed: Record Doesn't exist: tablespace_id: 1 Key: 1 4049

I ran the following commands from the cli in MacOS Monterey V 12.4: brew update brew upgrade Upon doing so, MySQL was upgraded from 8.0.30 to 8.0.31. MySQL was running in the background during the ...
Derrick's user avatar
  • 141
4 votes
1 answer
2k views

Restore MySQL dump to a container fails with ERROR 1114 "The table is full"

I am trying to restore a MySQL dump of size around 18GB to another MySQL server, which is running inside a container using this command: mysql -h example.com -u user -p matomo < dump.sql But it ...
undefined's user avatar
  • 151
4 votes
0 answers
1k 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-...
Mukul Mantosh's user avatar
4 votes
0 answers
709 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 ...
Wilson Neto's user avatar
4 votes
1 answer
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 ...
Sarath S Nair's user avatar
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 ...
πter's user avatar
  • 141
4 votes
0 answers
335 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 ...
Riedsio's user avatar
  • 1,377
4 votes
2 answers
6k 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 ...
Abhishek Ginani's user avatar
4 votes
0 answers
2k 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 ...
Safado's user avatar
  • 459
4 votes
0 answers
8k 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 ...
Frank Martin's user avatar
4 votes
1 answer
5k 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 ...
Mohamed Ben HEnda's user avatar
4 votes
2 answers
3k 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 [...
Bhavin Chauhan's user avatar
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 ...
Ryan's user avatar
  • 41
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 ...
gevial's user avatar
  • 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 ...
Cameron's user avatar
  • 71
4 votes
0 answers
254 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 ...
Joe Paul's user avatar
4 votes
0 answers
234 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 ...
stephenmm's user avatar
  • 141
4 votes
1 answer
4k 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'...
Jeremy Wadhams's user avatar
4 votes
1 answer
542 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 ...
davide's user avatar
  • 141
4 votes
0 answers
122 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. ...
Acube's user avatar
  • 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 ...
tempusfugit's user avatar
4 votes
2 answers
408 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 ...
user1906941's user avatar
4 votes
1 answer
640 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 ...
welkinwalker's user avatar

1
2 3 4 5
99