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
2k views

MySQL - Performing an IF statement for each row in a query

I have a hurdle I'm trying to overcome with a website I'm building. I know what I want to achieve but I'm still pretty new to mysql so I'm not exactly sure how it's supposed to fit together. Setup: ...
3
votes
1answer
4k views

What happens when changing column type DATETIME to TIMESTAMP in MySQL?

I want to convert a column data type from DATETIME to TIMESTAMP in a table with more than one million of lines. Is it possible? Is it slow? Will I lose the old information or MySQL can convert this ...
3
votes
1answer
3k views

What is the actual difference between innodb_autoinc_lock_mode 0,1,2

I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given. I see in "0" option, it locks the entire table and does the ...
1
vote
1answer
2k views

mysql fine tuning: open_tables, opened_tables

I'm a novice to database administration and have been depending on forum suggestions and answers to help resolve my database hang ups and too many connections errors; I've managed to reduce the ...
4
votes
3answers
4k views

Strange MySQL database that I can't select

I am investigating a web site server (CentOS release 6.3) that runs the traditional lamp stack; I am getting the following output my MySQL; mysql> show databases; +--------------------+ | ...
5
votes
2answers
232 views

MySQL | Complex (for me) SQL Statement, how to?

I have a table that contains posts and another table that contains meta options for each post in first table. The meta options table is a key value pare table. Lets say I have the posts tables that ...
1
vote
1answer
271 views

Efficient query plan for selecting all data duplicated on several columns

The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well. The purpose of the query is to show all ...
0
votes
1answer
87 views

Where can I read about views for MySQL?

I really don't know nothing about SQL.. I know how to insert and select data from database using PHP but besides that, nothing. I found this view And I am sure that 5 minutes for you to answer this ...
1
vote
1answer
2k views

declare keyword error mysql

This is the code I've written: delimiter // create procedure `update_pts`() begin set @ctr=1; set @cnt=(select count(*) from participant); declare cs1 cursor for select sap from participant; open ...
1
vote
0answers
43 views

Is there a canonical list of MySQL defaults which should be changed

I'm setting up a new mysql server as we're building a new app at work. Most of the developers/operations guys are familiar with mysql. Many of mysql's settings (eg strict mode) are set badly by ...
2
votes
1answer
938 views

SQL INSERT Query takes 2 seconds and sometimes much longer to execute

I know the question's title is bad, But I have an hard time figure it out my self... sometimes this query runs in 2-3 seconds and sometimes it's takes even 30 seconds I have a table named ...
0
votes
0answers
524 views

MySQL Left Join Select all rows

Hi guys I have a query which is pulling data from database : SELECT sandwich_table.id, `sandwich_name`, salad, COUNT(salad), salad_type.type, ...
3
votes
4answers
4k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
3
votes
1answer
281 views

MySQL partitioned tables?

I have a database that supports a web application with several large tables. I'm wondering if partitioned tables will help speed up certain queries. Each of these tables has a colum called ...
1
vote
1answer
605 views

Automatic MySQL Sync from master, where Slave was offline for long period of time

Does anyone here know if there are any tools, not using mysql's built in replication that will sync up two databases when the slave has been inaccessible from the internet for extended periods of ...
1
vote
2answers
3k views

Using MySQL EXPLAIN to analyze the performance of a function

You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as ...
2
votes
1answer
2k views

Monitor user to query processlist

We have a corporate monitoring system based in Zabbix. Its MySQL monitoring is quite basic, so I want to add more info. To do so, I've done a script that retrieves this info from MySQL and sends it to ...
3
votes
2answers
10k views

How do I dump only the data for all tables using mysqldump

I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the ...
0
votes
0answers
34 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
1
vote
1answer
422 views

MySQL selects/updates only using 1 core

I have a server with 4 physical Xeon processors, each with 8 cores and each core is capable of 2 threads. When I run MySQL queries I'm only seeing 1 CPU go to 100% in top and the total CPU usage at ...
2
votes
2answers
730 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
2
votes
2answers
4k views

MySQL case sensitivity for table names

Why is it that different platforms (linux vs windows) does not support the same naming conventions? I'm stunned by the fact that the documentation uses file names as an excuse for this. Yes, windows ...
0
votes
1answer
782 views

How do I check if a stored procedure is defined in MySQL

When I do : select routine_definition from information_schema.routines where routine_name = 'sp_name'; it returns a row, but the routine_definition column is NULL. Does this mean that this Stored ...
1
vote
1answer
1k views

MySQL Point in Time Recovery For Just a Single Schema?

Is it possible to simply restore a single schema and bring it to a point in time (Say three hours ago) without bringing the entire database back? I can think of a lengthy method of backing up each ...
0
votes
1answer
54 views

é being converted from database to site

I have a MySQL database that is storing business data... In the database, when running a select against a business name that contains an accented e (é), it comes up correctly (showing me that it is ...
2
votes
1answer
2k views

creating table syntax error 1064

I'm using workbench to create a table. This script is giving me a problem. It is saying that there is a syntax error new '). DROP TABLE IF EXISTS `waitronmain`.`managers` ; CREATE TABLE IF NOT ...
1
vote
1answer
4k views

can't create schema for mysql database

I am trying to create a schema for a mySQL database through my sites phpMyAdmin section. However when I try to run the following script: CREATE SCHEMA `mydb` I get this error and I am unsure what ...
1
vote
1answer
70 views

About GRANT statement for user creation

I am new to databases, learning MySQL through a textbook. My doubts are regarding grant statement. I am learning on a localhost environment (both server and clieat are my own system). FIRST DOUBT ...
1
vote
1answer
940 views

MySQL InnoDB locking on combined UPDATE-JOIN statements

on querying statements which contain an UPDATE combined with JOIN, we are getting the following errors: Statements writing to a table with an auto-increment column after selecting from another table ...
-2
votes
1answer
92 views

How to efficiently model this simple data structure?

catId(INT) | userId(INT) | catName(VARCHAR) 0 1 Cat1 1 1 Cat2 2 2 U2Cat1 3 2 ...
0
votes
2answers
18k views

how to insert data from one table to another [closed]

SELECT a.nid, a.stock, b.qty, sum( b.qty ) FROM uc_product_stock a INNER JOIN uc_order_products b ON a.nid = b.nid GROUP BY a.nid the above query give me the following result: nid stock qty ...
1
vote
1answer
171 views

Master Slave Replication in Mysql

I have generated a dump from Master and I have to construct slave and give privileges and binary log position and file name of master for it to obsorb from master. Master is a high transaction OLTP ...
2
votes
6answers
3k views

MySQL join two table if second table is not empty

I have 2 tables like below table 1: users id name 1 John 2 Jane 3 Mery 4 Alpha table 2 : notes id note owner created 1 alpha alpha 1 2013-02-03 10:30 2 yeah ...
1
vote
1answer
181 views

With MyISAM is there any index size savings when using INT vs BIGINT?

There is obviously a four byte per row space savings in a data column when using INT vs a BIGINT type. But I have been trying to figure out if there is also any space savings for myisam indexes on ...
4
votes
2answers
17k views

Server requested authentication method unknown to the client [mysql_old_password]

Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password] SQLSTATE[HY000] [2054] The server requested authentication method unknown to the ...
5
votes
1answer
8k views

Which is better: many join conditions or many where conditions?

I am trying to compare two queries: Query 1: SELECT a,b,c,d,e FROM tableA LEFT JOIN tableB ON tableA.a=tableB.a WHERE tableA.b=tableB.b AND tableA.c=tableB.c AND tableA.d=tableB.d AND ...
0
votes
1answer
180 views

mysqlslap running very slow on a specific computer

I've recently discovered the tool mysqlslap and have run some simple tests with it. I currently have three test environments (2 Win 7, 1 Linux Ubuntu) where I can run these tests. However, running a ...
1
vote
2answers
3k views

“can't return a result set in the given context” error when calling stored procedure - differences between mysql 5.0 and 5.5?

I have a stored procedure to regenerate leafs (nleft, nright) in simple (id, id_parent) tree. (sample table) When I'am calling CALL EdgeListToNestedSet( 'category', 'id_category', 'id_parent'); ...
1
vote
1answer
70 views

MySql include partition key in index?

Please look at the following Mysql table: http://pastebin.com/b0NDSbdz I've partitioned the table by sent_at and I'm making sure that most of the queries pass sent_at. do I need to include sent_at in ...
0
votes
1answer
2k views

MySQL crashes with Signal 8 after “Table has no partition for value” error

We have a mysql database running well and suddenly it stops and below are the snippet of the log file. What is the best remedy in this scenario? 130202 1:08:59 [ERROR] Event Scheduler: ...
4
votes
1answer
909 views

Make sure MySQL is not accepting remote connections

I am currently doing development for a LAMP-based website. I do not know what configuration changes have been made to MySQL since it was installed on our server. The way that we use MySQL, there is no ...
0
votes
1answer
172 views

compare the same table

I am facing an issue with the following query. When I execute the query, it takes very long. I broke the query into two parts, compared with a shell script, but is there any chance to go with one ...
1
vote
1answer
806 views

MySQL Tables crashing randomly

This is one of the many random Tables that get corrupted. Any ideas why and what would be causing this? How do I keep MySQL tables from crashing and MySQL from crashing? Repairing USR_wp537 ...
1
vote
3answers
9k views

How can I copy data from one MySQL server to another based on a SELECT statement (then delete the data from the original)?

I have a very large log table from which I want to copy rows, putting them into the same table structure on a new server. I don't want to copy everything, only old rows, so that the table on the main ...
1
vote
1answer
360 views

How to select only the number from “show global status like 'Com_insert';”

The title says it all. How can I select only the number result from show global status like 'Com_insert' ?
1
vote
1answer
1k views

Master to Slave to Slave Configuration in MySQL

We currently have 3 MySQL Servers running; the first which contains production data and is due to be retired mysqld Ver 5.5.28-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu)) mysql> ...
1
vote
1answer
378 views

Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

I have a MySQL development server that uses InnoDB tables in some of the databases. It was originally setup with the defaults so all data went into the system tablespace (e.g the main ibdata1 file). ...
1
vote
2answers
185 views

How to Perform Two Simple Queries using Select Value from First Query

I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select ...
5
votes
1answer
3k views

What is the proper way to backup MySQL database with rsnapshot?

I want to backup my website that contains both user uploaded images and data stored in MySQL database such that they are always consistent with each other in any particular point of time. While ...
1
vote
2answers
2k views

MySQL slow queries - Fast using PHPMyAdmin , low CPU and RAM usage

I have a site running on Amazon EC2 , which runs fast and contains some reasonably complex MySQL queries. Part of that site is an API which serves out data to another site. These queries are very ...