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.

learn more… | top users | synonyms

1
vote
1answer
48 views

Am I using the ORDER BY clause incorrectly

I am trying to enumerate a database, created specifically for the purpose of learning SQL injections. I am trying to discover the number of columns a particular select statement might be using. I do ...
5
votes
4answers
4k views

How to check for existence of an INDEX on a column independent of used SQL database

Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used? For MySQL one could for instance check for the existance ...
1
vote
1answer
441 views

MySQL: Access denied messages

I have just installed Ubuntu on my server and MySQL. I can't make any change in MySQL (seeting password, creating database, etc.) as I ALWAYS get an Access denied error message. I have followed the ...
0
votes
1answer
800 views

MySQL 5.7.4 Startup Times Out

I recently upgraded the MySQL Server installation on my Windows 8.1 Pro x64 laptop from 5.7.3 to 5.7.4 using the MySQL Installer, and after doing so, attempting to start the MySQL Server service, ...
2
votes
2answers
36 views

Can creating indexes colide with future database upgrades?

I have a mysql Database with very slow querys. I am using the slow query log to monitor those. This is a database created by a third party software that we're using. Their developers didn't do any ...
3
votes
3answers
929 views

A little clarification on how 'LIMIT' works

Not being a full blooded member of the DBA clan, but more of a dev clan, I find myself trying to figure out some slow running queries. The subject of my question however is not 'Why my queries are ...
1
vote
1answer
2k views

Can I use a Percona Xtrabackup backup to restore a single table?

I've been playing around with the Xtrabackup tool for a month now, and I got to admit : it is really powerful ! I'm still testing it to backup database servers between 5 and 150 GB and the ...
0
votes
2answers
374 views

MySQL row does not persist, but primary key with auto-increment is incremented

I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, ...
0
votes
0answers
72 views

MySql query keep running for a long time

Following query keep running for a long time and I'm using MySql 5.5.34 on XAMPP on Windows SELECT id_package, `pck`.`fk_sales_order`, `pck`.`fk_picklist`, count(pci.id_package_item) as items, ...
0
votes
1answer
2k views

Select row with MAX(), or choose one if no MAX() exists

I have a MySQL InnoDB table with the following columns: id: UNSIGNED bigint(20) AUTO_INCREMENT (PRIMARY KEY) name_id: UNSIGNED bigint(20) time_stamp: decimal(26,9) event: enum('add', 'init', ...
0
votes
1answer
2k views

Please explain how this query generates a calendar table in MySQL

This page has a pretty neat way of generating a robust calendar table. I've copy-pasted it below: CREATE TABLE calendar_table ( dt DATE NOT NULL PRIMARY KEY, y SMALLINT NULL, q tinyint ...
0
votes
1answer
880 views

exceeding 'max_updates' resource when importing large database

I need to import a large database on my shared server.I'm using the following statement in a shell to import my database mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME < ...
2
votes
1answer
493 views

Is there any difference between these two version of MySQL - 5.1.73-community-log Vs 5.1.73-community

I have installed MySQL 5.1.73 in Oracle LINUX 5 server. Just after RPM installation, I checked system variables and found that version is 5.1.73-community. When I copid ...
0
votes
1answer
1k views

“Display width out of range for column” error when importing mysql 5.1 dump into 5.5

I am migrating to a new server, and trying to move my database from a 5.1 to 5.5 server. I created a dump with mysqldump -u root -p --add-drop-database --triggers dbname > dbname.sql When I ...
0
votes
1answer
59 views

Mysql tar ball installation

I need to install two different versions of Mysql in one server OS RHEL6 and it will be separated by user called mysqlprod and mysqlpreprod. I need to separate their binaries, softwares,data ...
0
votes
1answer
192 views

How to check if several tables exist in database, and how to show it in Mysql

I would like to check if several tables exist in a database, and return a easy to understand return, I know you can check if a single table exists by running show tables like "foo"; but is there ...
0
votes
2answers
38 views

How can I genereate a string from the returns of a SELECT query in Mysql?

I need to create a string from data that is extracted from an SQL query, and wish to accomplish it within the SQL Query. The return is something like +---------------+ | foo_ids | ...
1
vote
2answers
162 views

Can counting rows be made any faster?

I need to calculate the total number of rows based on a set of criteria: SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a'; The above query takes 0.10 sec to run on a MyISAM table with ...
1
vote
1answer
32 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
2
votes
2answers
226 views

Why is this MySQL SELECT performing so slowly despite indexing?

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
1
vote
1answer
141 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
1
vote
2answers
4k views

Does MySQL have a version of Change Data Capture?

We're in the process of phasing out an old system and migrating onto a new one. The last time that we phased out an old system, we ran both systems in parallel and integrated data between both until ...
0
votes
1answer
236 views

How to structure renting and sale as type of purchase?

I'm currently planning a database which involves two different types of Products that can be bought outright, or rented for a weekly or monthly amount. I'm just trying to figure out how to structure ...
24
votes
2answers
41k views

Multi cores and MySQL Performance

Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
1
vote
0answers
26 views

Why is this MySQL query performing slowly despite correct indexing? [duplicate]

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
1
vote
1answer
632 views

InnoDB primary key efficiency

I've read a number of articles regarding efficiency of primary keys depending on the storage engine, and I am left confused. Given a simple many-to-many table with two fields, storeId and zoneId, ...
0
votes
1answer
3k views

MySQL group concat not showing distinct values when joined to another table

I have one view that gives me comma separated values and want to use that data in another view. In the first view, I am using group_concat( distinct ...) and when I run that view by itself, I can see ...
1
vote
1answer
167 views

MySQL database stops working after log enabled

I have this strange issue. I have MySql workbench, which I use to write queries for MySQL database (xampp 3.2.1). Everything works fine, but recently I found myself having to profile the queries my ...
0
votes
0answers
707 views

Mysql Insert taking too long. Table need optimzation

I have around 60 tables and a video table with over 600,000 rows. with 4 Btree and 2 FULLText Indexes and have around 30 Columns. Server Specs are high, over 30G of Ram, Intel Xeon Processor. Mysql ...
1
vote
1answer
96 views

Adding new tables — memory usage increases

I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema. Does adding more tables increase memory usage? I am using about ...
1
vote
0answers
330 views

High concurrency is even slower than low concurrency in mysql? [closed]

My application is supposed to handle high concurrency insert/update, now I have only one mysql instance setup for test. My application is full asynchronous, it should be able to handle 10,000 ...
0
votes
1answer
674 views

Unable to connect to Mysql at peak time

I have two servers one is application server and another is database server. Continuously I am firing query to mysql. At peak time I get error - Can\'t connect to MySQL server on \'IP Address\' (99) ...
1
vote
2answers
50 views

How can I store least used table files to a slower drive?

Is it possible to separate the least used and most frequently used data files on to separate disks when using InnoDB?
0
votes
1answer
52 views

How to find the rows from a table which has exactly two 'n' s in the employee name?

I want to find all the employees whose names have exactly two n's in their name. How can this be done?
2
votes
1answer
4k views

Mysql join two tables and limit by first input?

i am only starting to play with MySQL and i seem to have been stumped by one problem. I have one table containing a number of fields including a primary id field. I have a second table containing ...
-2
votes
0answers
97 views

Can this architecture supports 350 concurrent access? [closed]

This question is more about server capacity issue. I have a linux server with Apache 2.24 installed (12G Ram, 32 cores CPU, Red Hat 64Bit). Install 11 instances of Wordpress, all 3.8.1, with WP ...
1
vote
1answer
88 views

Any idea why this seemingly-simple query is really slow?

Here's the query SELECT count(*) FROM registration r LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id AND r2.team_id IS NULL WHERE r.event_id = "102577"; It seems pretty ...
2
votes
1answer
300 views

Need help tuning my.cnf file

I need help tuning mysql. Currently it is using a lot of CPU. I dont find any query that last longer than 3 seconds in SHOW PROCESSLIST and there are only around 20-25 running most of the time. Still ...
2
votes
1answer
115 views

Relational modeled design to store versioned files

The scenario I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the ...
2
votes
1answer
48 views

How to decipher the transaction message in innodb status log.

I have several transactions that seem have stalled. Looking at the innodb status log, I found several messages similar to the following: ---TRANSACTION 69DBBB, ACTIVE 947 sec starting index read, ...
4
votes
1answer
238 views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
0
votes
0answers
1k views

MySQL - Out of memory: Kill process 3046 (mysqld)

We run a MySQL (v5.6.17) dump backup every 4 hours, and today noticed: Apr 19 20:09:30 mysql1 kernel: Out of memory: Kill process 3046 (mysqld) score 970 or sacrifice child Apr 19 20:09:30 mysql1 ...
0
votes
1answer
578 views

Efficient way to write query for multiple table which depends on column value of a table in mysql?

In the given schema, there is a table A having column A1,A2,A3,A4 There are three more tables i.e. B,C,D If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup ...
1
vote
3answers
3k views

MySQL schema for recurring events - Query by specific date

I've just asked this exact same question on stackoverflow so apologies for cross posting but I only just noticed dba so thought I'd ask across here as well. I'm just about to start putting together a ...
0
votes
2answers
2k views

Massive MySQL delete from CSV file

To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file? If not, what would you see as the ...
0
votes
0answers
396 views

Can't Add Databases through PHPMyAdmin [closed]

EDIT (should've mentioned this sooner): I'm using a Mac, running OS X 10.9.2 Being fairly new to PHPMyAdmin, I thought I was removing settings/privileges for one single user through PHPMyAdmin, but ...
1
vote
1answer
2k views

Replicating data from Oracle to MySQL

I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has ...
1
vote
1answer
268 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...
-2
votes
1answer
198 views

MySQL ran out of space, now won't start

So the partition MySQL was mounted on ran out of space. As a quick fix, I decided to move mysql's data directory to /home/minecraft/mysql/. I then changed the datadir in the config to the correcy ...
2
votes
1answer
1k views

Replication from EC2 Master to RDS Slave - Connecting to master

I am migrating my company's database from an EC2 instance to an RDS instance. I have already migrated a snapshot of data and am now trying to set up replication to get the data that has been added ...