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,620 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
11
votes
0answers
14k 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): ##...
11
votes
1answer
480 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: ------...
8
votes
1answer
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....
7
votes
0answers
2k views

Why is Insert-Ignore so expensive in MySQL?

TL;DR: Inserting a large number of rows with no collisions is MUCH faster than (not) re-inserting the same rows, BOTH using the INSERT IGNORE syntax. Why is this? I would assume that the index lookup ...
7
votes
0answers
12k 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 ...
7
votes
1answer
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....
7
votes
1answer
99 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 ...
7
votes
1answer
900 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 ...
6
votes
0answers
2k 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 ...
6
votes
0answers
1k 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 ...
6
votes
0answers
630 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/...
6
votes
2answers
12k 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 ...
5
votes
0answers
254 views

How can MySQL's data types with 4GB limits be fully used since max_allowed_packet is hard limited to 1GB?

The MySQL types LONGTEXT and LONGBLOB are documented with data limits of L + 4 bytes, where L < 2^32 (~4GB). But max_allowed_packet is only configurable up to 1GB. The largest possible packet ...
5
votes
1answer
1k 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 ...
5
votes
1answer
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 ...
5
votes
0answers
2k 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 ...
5
votes
1answer
522 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 ...
5
votes
1answer
487 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....
5
votes
1answer
723 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 ...
5
votes
1answer
7k 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 ...
5
votes
0answers
5k 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 ...
5
votes
2answers
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 ...
5
votes
1answer
298 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
1answer
3k 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 ...
4
votes
0answers
513 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-...
4
votes
0answers
174 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 ...
4
votes
0answers
3k 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 ...
4
votes
1answer
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 ...
4
votes
3answers
2k views

Optimise Large Record Deletion Procedure

I'm creating a database that mirrors our production database, but is lighter and anonymised - for local development purposes. Ensuring we've enough data to operate on as an engineering team, I'm ...
4
votes
3answers
15k views

Grouping data into 5 minute intervals within a time range

I am trying to prepare data for a candle stick chart. I have some records on MySQL database as shown below: | Rate | Date | |----------|---------------------| | 0.001111 | 2017-11-...
4
votes
1answer
1k 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, ...
4
votes
2answers
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 ...
4
votes
0answers
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 ...
4
votes
0answers
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 ...
4
votes
1answer
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 ...
4
votes
0answers
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 ...
4
votes
2answers
364 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 ...
4
votes
2answers
1k 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 ...
4
votes
1answer
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 ...
4
votes
0answers
182 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 ...
4
votes
1answer
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'...
4
votes
1answer
505 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 ...
4
votes
0answers
115 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. ...
4
votes
1answer
102 views

Best practices for relating NoSQL data to RDMBS

I am looking to use both a NoSQL solution (Redis, Mongo, etc) with a mySQL/Laravel application. My reasons for this is that I need a schemaless key value storage to go with my relational data. I could ...
4
votes
2answers
366 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 ...
4
votes
2answers
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 ...
4
votes
0answers
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` (`...
4
votes
1answer
521 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 ...
4
votes
0answers
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 ...
4
votes
0answers
1k 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 ...

1
2 3 4 5
93