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,909 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
11 votes
1 answer
15k 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): ##...
user avatar
11 votes
1 answer
826 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: ------...
user avatar
8 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....
user avatar
7 votes
0 answers
13k 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 ...
user avatar
7 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....
user avatar
  • 171
7 votes
1 answer
109 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 ...
user avatar
7 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 ...
user avatar
6 votes
0 answers
3k views

How to control how JSON_ARRAYAGG orders results

I am testing MySQL JSON_ARRAY and JSON_ARRAYAGG functions in order to move some application expensive processes to SQL stored functions. In the following e-commerce application example, I am trying ...
user avatar
6 votes
0 answers
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 ...
user avatar
  • 161
6 votes
0 answers
649 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/...
user avatar
  • 115
6 votes
2 answers
13k 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 ...
user avatar
  • 161
5 votes
0 answers
201 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 ...
user avatar
  • 167
5 votes
0 answers
4k 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 ...
user avatar
5 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, ...
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 ...
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 ...
user avatar
5 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 ...
user avatar
5 votes
1 answer
559 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 ...
user avatar
5 votes
1 answer
496 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....
user avatar
  • 51
5 votes
1 answer
835 views

how to cache a subset for cascading select queries in mysql

Here is another database problem I stumbled upon. I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
user avatar
  • 51
5 votes
2 answers
384 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 ...
user avatar
5 votes
1 answer
8k 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 ...
user avatar
  • 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 ...
user avatar
  • 151
5 votes
2 answers
1k views

Multi-threaded replay of master activity for load/performance testing

Does anyone know of any existing tools/products that accomplish what I'm trying to do? After searching around for a bit the only thing I could find surrounding what I'm trying to accomplish is ...
user avatar
  • 5,153
5 votes
1 answer
359 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 ...
user avatar
4 votes
0 answers
545 views

Why does PhpMyAdmin put the idexes at the end of a dump?

When I export a MySQL database with Adminer or with mysqldump, it produces a .sql file with each table definition followed by (if the table is non-empty) an INSERT statement (or multiple statements ...
user avatar
  • 169
4 votes
1 answer
4k views

AWS Aurora running out of freeable memory and crashing

We have an issue with our AWS Aurora MySQL instance running out of freeable memory and crashing as a result. AWS's response was to upgrade to a bigger instance, but I don't feel that will necessarily ...
user avatar
4 votes
0 answers
723 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-...
user avatar
4 votes
0 answers
488 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 ...
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 ...
user avatar
  • 141
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 ...
user avatar
4 votes
1 answer
2k views

Speed slow down on a UNION ALL view

This is my DB architecture: a 3.000.000 records product (memory) table that must be shared by all databases a x (say 1) records product table specific for each single database a view in each ...
user avatar
  • 51
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 ...
user avatar
  • 439
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 ...
user avatar
4 votes
1 answer
1k 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 ...
user avatar
  • 41
4 votes
0 answers
3k 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 ...
user avatar
4 votes
0 answers
1k 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 ...
user avatar
  • 141
4 votes
2 answers
447 views

MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct ...
user avatar
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 ...
user avatar
  • 71
4 votes
1 answer
3k views

compare results between two select queries MySQL

I'm making test application where users will input their select queries in input box and the application have to compare them if they are identical with "correct answer" query. "Correct ...
user avatar
  • 141
4 votes
0 answers
206 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 ...
user avatar
  • 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'...
user avatar
4 votes
1 answer
515 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 ...
user avatar
  • 141
4 votes
0 answers
118 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. ...
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 ...
user avatar
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 ...
user avatar
4 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` (`...
user avatar
4 votes
1 answer
594 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 ...
user avatar
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 ...
user avatar
  • 173
4 votes
0 answers
2k views

Why is loading data into MySQL cluster taking so long?

I have a mysql-5.5.22 ndb-7.2.6 cluster 64bit running on Centos 6 with each node installed on the same Citrix Xen Server communicating via a virtual private network. Each node has 4GB of RAM, 4 vCPUs ...
user avatar
  • 9,478

1
2 3 4 5
99