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,409 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
9
votes
1answer
177 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: ------...
9
votes
2answers
25k views

Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example: $ mysql -u root -p --column-type-info Enter password: --- Copyright and help message snipped for brevity --- ...
8
votes
0answers
13k views

Conceptual data model for a billing system

I am working on 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): ...
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
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....
6
votes
0answers
10k 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 ...
6
votes
1answer
2k 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....
6
votes
0answers
621 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
1answer
897 views

MySQL Replication : Duplicate entries - SQL_SLAVE_SKIP_COUNTER or delete row?

I have a system which replicates one database to four slave servers. Every now and then, when traffic is high, one or more of the slave servers hits a duplicate insert error and the slave stops ...
6
votes
1answer
818 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 ...
5
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 ...
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
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 ...
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
494 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
476 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
646 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
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
11k 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
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
251 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
2k 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
156 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
2k 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
1k 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
11k 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
983 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
4k 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
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
2k views

Two ways to define may-have-a (1..0-1) relationship

Today I have discovered for myself that may-have-a, one-to-zero-or-one, (1-0..1) relationship can be implemented in two different ways: Let A be the "main" table (left side of ..) and B be the "...
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
748 views

How do I restore a mysql dump that was created in xml format?

I have a rather big XML formatted MySQL dump It was created with the --xml param (-X) param of mysqldump. I need to import it in. This article from 2010 indicates that there was no "built in" way ...
4
votes
2answers
108 views

Missing DB entries

I have run into a problematic situation. I oversee the administration of 5 php web pages on one server, each having their own database. All pages work like interactive forms drawing and storing data ...
4
votes
1answer
494 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
1answer
409 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
4
votes
1answer
2k views

MySQL server: Unable to connect with root because of skip-name-resolve option

We had our MySQL server on an old server. We decided to move all of the databases to our new server. Old MySQL Server engine 5.5.31 New MySQL Server engine 5.5.38 We created one general dump file ...
4
votes
1answer
93 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
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
499 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
1answer
385 views

MySQL won't use an index on an int, if I provide a string

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log: # Time: 130904 12:19:59 # User@Host: cron[cron] @ [10.71.41.38] # Query_time: 58.488760 ...
4
votes
1answer
6k 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 ...
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 ...
4
votes
1answer
91 views

Sample a Mysql table to increase performance

we are storing all our clients traffic data in a MySQL 5.1.73 database (MyISAM) and then we use it to generate user and internal statistics/graphs, always searchinng for a client id and a date/hour. ...
4
votes
2answers
448 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...

1
2 3 4 5
89