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

4
votes
2answers
14k views

MySQL replication: 'Duplicated entry for PRIMARY key'

Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync. Basically 'mysqldump' was running almost whole night and then the ...
1
vote
1answer
402 views

I have accidently deleted ibdata1 on an ubuntu server

I have accidently deleted the 'ibdata1' file on the server having innodb, Is there any way I can recover it? the mysql service has restarted since the deletion.
4
votes
1answer
34k views

How do I select items from a table where a single column must contain two (or more) values?

I have a MySQL database table that references different words and their locations in documents. I want to return the IDs of documents that contain all of the words. Here is an example table. docid ...
2
votes
2answers
691 views

MySQL Master-Slave setup: master is not indexed and slave is indexed

Is it possible to have a MySQL master-slave setup where the master has no indexes (for faster insertion) and the slave will be indexed? Thanks in advance!
1
vote
0answers
314 views

innobackupex: What causes Slave_open_temp_tables did not become zero after waiting 15 minutes?

As you might know, in order to assure a consistent replication state, the slave should be backup only when there are no open temporary tables: http://dev.mysql.com/doc/refman/5.5/en/replication-...
1
vote
1answer
650 views

monitoring mongodb and mysql replications using nagios

I have set replication for mongoDB and mySQL. Now I have to manage them using nagios. I have to write some php-script that will read server status. I am good with nagios setup. Now I want to know ...
1
vote
1answer
111 views

Recover Wrong encoded string values in MySQL database

I have a database in which I store the alt and title values of the images. Unfortunately the program which inserts these values hasn't been connecting to the MySQL database with a UTF-8 link and the ...
3
votes
3answers
7k views

What is the best way to recover from a mysql replication fail?

Today, the replication between our master mysql db server and the two replication servers dropped. I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to ...
2
votes
1answer
4k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
1
vote
0answers
85 views

Is there a way to dynamically monitor database tables creation per day? [closed]

We have a huge MySql database and I have a task to monitor tables creation per day, is there some kind of an open source utility or any other solution which could help me achieve this goal?
0
votes
0answers
1k views

MySQL always crash on start, ibdata1 bigger than 4GB

I'm having a problem with MySQL when testing my production data. Whenever I started MySQL (mysqld), it always crash. My suspect is on my InnoDB data file (ibdata1) since the size is already bigger ...
2
votes
1answer
340 views

Huge 0.000001 and 0.000002 files and so on, in mysql data folder

In mysql data folder unknown files were created like this: This is my.ini content: [client] host= . port= 3306 socket= "MySQL" [mysql] no-auto-rehash [mysqld] max_allowed_packet = 800M ...
0
votes
0answers
417 views

“Mysql2::Error: Table doesn't exist” after power outtage

The power went out while my site was running and now when trying to access my site running locally on my mac I'm getting this error: ActiveRecord::StatementInvalid: Mysql2::Error: Table 'mysite.users'...
4
votes
2answers
12k views

Get the Time of Last update of a column

This command gives the date of the last update for a table SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'MyDB' AND TABLE_NAME = 'MyTable' But I want to find the time of ...
0
votes
1answer
1k views

Can't change root password: “The system cannot find the file specified.”

I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message: ...
1
vote
1answer
501 views

Implementing a Record Keeper/Rollback System for a MySQL Database

What's the best way to create a record keeping table for all actions in my database? I have a number of tables inside one database and I want to create another that records all movements in every ...
1
vote
1answer
81 views

Mysql is larger than wamp

Wamp which includes mysql,php,apache is only 24.7 MB but the only Mysql is 207 MB here.Why?
2
votes
1answer
2k views

prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary

I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index. MySQL accepts this query: CREATE TABLE IF NOT EXISTS `...
3
votes
1answer
441 views

restrict user host settings to socket connection only

Is there a way to restrict a user's host setting so that (s)he can only connect by socket and not TCP/IP? I'd like to have this setting for the root account. edit: As Abdul Manaf pointed out skip-...
2
votes
2answers
287 views

Changing ORDER BY direction causes performance issue

I use MySQL 5.6, and I have two tables each with 16M rows: CREATE TABLE IF NOT EXISTS `newsstudios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
0
votes
1answer
134 views

Maintain stable data in a database

I am doing Performance evaluation of my application. The size of database is around 20GB. I am using jMeter as a performance evaluation tool. Here each performance test (ie., Each jMeter run) there is ...
0
votes
1answer
610 views

Does this error message indicate a SQL injection vulnerability?

I just took over management for a webservice and today I got a user report about this error. Now I'm no sql injection expert but does this error make this attack possible? Microsoft OLE DB Provider ...
0
votes
1answer
490 views

MySQL system date change impact

I can't find an answer for this problem. If I have in my database data of datatypes like date and timestamp. Can the change of system date affect this saved data ? I mean, can these existing ...
1
vote
2answers
2k views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE (auth_user....
0
votes
0answers
110 views

(My)SQL: management and duplication of multiple sub-databases [closed]

I have a pretty simple question, but it seems that there are controversies as to how to solve it in the best possible way. Say I have a web app that makes online surveys. My challenge is that each of ...
1
vote
1answer
7k views

“Cannot add or update a child row” when deleting a record?

I have the two following tables: survey_main ---------- id name answers_main -------------- id survey_id I have the following foreign key constraint on the answers_main table: CONSTRAINT `...
2
votes
2answers
1k views

How to calculate total ON hours and Total OFF hours in day of a motor using PHP and MySQL [closed]

In my application I would need to calculate total ON hrs and OFF hrs in a day of motors in one tank. Every time he/she turn ON/OFF motors, data will go and store in motor_status_log table of my ...
2
votes
1answer
13k views

MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ...
2
votes
2answers
4k views

Not able to access stored procedure from my application

I have written few stored procedures and executed in my server that is having php myadmin now while calling this stored procedure from my application it is showing following exception ...
2
votes
1answer
750 views

Is it possible to convert mysql binlog from statement format to row format?

The mysql server online is of version 4.1, which doesn't support row-based binary log. Nevertheless, I need the row-based binlog. Can I use the binlog generated by old mysql and import it into another ...
5
votes
1answer
4k views

How can I speed up this slow simple query?

SELECT * FROM `game` WHERE (hometeam=29 OR awayteam=29) AND `sportid`=1 AND`date` BETWEEN '2012-07-01' AND '2013-06-30' AND deleted=0 This simple query takes 0.5-1.1 seconds (without ...
0
votes
1answer
486 views

Latin1 to UTF8 on large MySQL database, minimal downtime

Let me lay out the basic scenario for the problem I'm up against. I'm running MySQL 5.1 and have a single database with 2 large MyISAM tables (9M and 20M rows respectively). Each of these tables has ...
0
votes
1answer
462 views

How to profile a particularly slow or inefficient query

I'm currently writing queries for my database but this is the first time I've dealt with large data sets of any sort, and I'm now finding that I need to improve the performance of my queries. ...
1
vote
3answers
2k views

MySQL InnoDB Sorting Issue

I was surprised to see MySQL's InnoDB showing an interesting behavior, which I cannot fully explain. According to the official MySQL InnoDB Documentation: "All indexes other than the clustered ...
0
votes
0answers
25 views

Secure the jdbc and remote MySQL(windows) server connection [duplicate]

I'm a student doing a project which uses jdbc and mysql(on windows). I have an app with connects to mysql server on remote host. I just this connection to be a little secure. I found this : http://...
0
votes
0answers
463 views

How to enable SSL in MySQL on Windows?

I'm a student doing a project which uses JDBC and MySQL (on Windows). I have an application with connects to a MySQL server on a remote host. To have a secure connection, I found this : http://www....
1
vote
1answer
4k views

Analyse MySQL General Query Log in Real-time?

We want to use mysql general query log to do real-time monitoring and auditing. Currently our approach is: set general_log=on; sleep 15m; set general_log=off; scp & rm xxx.log; set general_log=...
0
votes
3answers
27k views

max_allowed_packet in mySQL

I tried all three method explained here to max_allowed_packet. But no one changes its value in my MySQL 5.6. I use show variables like '%max_allowed_packet%' to see its current value. But it always ...
0
votes
1answer
128 views

csv file handling

I am using an EAV like structure to store CSV files. There aren't any predetermined columns, so it needs to be able to expand the columns / data on demand. Is this a fairly good way of handling such a ...
1
vote
1answer
834 views

Mitigating MySQL replication delay in single master/multiple slave setup

For a particular web application that requires high availability I am thinking of scaling out by setting-up MySQL replication with one "write" master and multiple "read" slaves (as explained here http:...
0
votes
1answer
211 views

Chaining System Query in MySQL

I have a table which maintains a user chain Here is the table structure +------+--------+ | id | user | +------+--------+ | 2 | 4 | | 10 | 4 | | 11 | 4 | | 1 | 2 | |...
2
votes
1answer
90 views

Make sure slave has finished reading from master

I am planning to have a master-slave system in which occasionally the master and slave will reverse roles. To do this, I will stop further updates to master, and redirect traffic to the slave. But, ...
1
vote
2answers
868 views

MySQL bin log missing data?

I'm trying to make heads and tails of my binary logs and am coming up short. I have many entries similar to the following from mysqlbinlog but I'm missing log data that I know should be there. # at ...
1
vote
2answers
4k views

MYSQL matching one column to another in a different table via a FULLTEXT index

I current have two different tables. The first table has a list of titles and IDs associated to these titles, the second table is a list of random heading. What I would like to know is if there is a ...
40
votes
2answers
79k views

Good explanation of cascade (ON DELETE/UPDATE) behavior

I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is ...
1
vote
1answer
567 views

Any way to estimate the required space for a SQL dump file?

I want to import a Wikipedia XML dump file into MySQL. The uncompressed XML dump file is 65GB. Does this mean I should free up almost 64GB in the MySQL data path? Is there any way to estimate the ...
0
votes
1answer
829 views

MySQL query optimizer ignoring smaller scan on TIMESTAMP column, cardinality?

I've got a complex multi-join query where the optimizer won't pick an index on a TIMESTAMP column even though it results in a smaller index/table scan. If I force the index on the TIMESTAMP column, ...
0
votes
1answer
207 views

How we take dump with batch of records option?

we have 30 GB database.we want to take backup of this database. here the question is we want to take the backup batch of every 10k records. How we can take batch of records backup using mysqldump
0
votes
1answer
201 views

'mysql' execute statment returns 'NULL'

Could you please advise why this query returns 'NULL' on BASH shell, basically I need to script it so could you please also advise what's the best practices to execute queries in BASH script? MySQL ...
2
votes
1answer
1k views

MySQL exception after upgrading from Java 6 to Java 7

I've been trying, for a long time, to find a solution for the issue. Recently I've updated my JDK from 6 to 7. Now my grails app doesn't start up, I keep getting the follow exception: Caused by ...