MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

1
vote
1answer
45 views

MySQL Latest Deadlock Explanation

Can someone explain this deadlock to me? It seems as if there are 2 inserts happening within milliseconds of each other with the same Primary Key. I'm not sure how this can cause a deadlock. At most a ...
0
votes
1answer
49 views

SQL INSERT and UPDATE queries run far to slow

I have two queries that run far too slow and I have no idea why. Both tables contain about 500000 rows. Each call needs ~20 minutes and the they have a really high CPU usage (~90% on Mac). I have ...
1
vote
1answer
312 views

Why mysqldump is returning with error 1143: Couldn't execute 'show table status like

I ran the following command to create a mysqldump mysqldump -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz I got following error after 60% (estimating checking ...
0
votes
1answer
124 views

Many mysql queries stuck at sending data

I am using opencart and I can se many queries blocking the mysql server. All queries are the same except the p.manufacturer_id. Attached there is the query and the explain of the query. The query is ...
1
vote
4answers
98 views

How to backup live mysql database?

I have 12 GB MySQL InnoDB Database with around 800 queries per second. No sooner I start 'mysqldump' over it to make backup I start getting '503' on my website. How do I make backup of my live ...
0
votes
0answers
198 views

How to solve MySql Slave Error 1452 Foreign key constraint fails?

I am site administrator. We use to have a mysql 5.5.40 master(db01) - slave(db02) setup. One day our master server (db01) got down due to hardware failure. So we switched our site to slave (db02) ...
3
votes
2answers
98 views

Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing. There is an index on search_id column in search_criteria table and on pricing_id column in pricing table. But running this nested query does not use ...
1
vote
0answers
158 views

How do I recover from MySQL InnoDB table/index corruption?

While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible. ...
0
votes
0answers
15 views

Trying to create a trigger to fill two columns (MySQL)

I am trying to create a trigger to update columns on a table when a new record is added to another table. This is the code I am using CREATE TRIGGER `hire` AFTER INSERT ON `hire` FOR EACH ROW BEGIN ...
0
votes
1answer
57 views

Adding a primary key to an existing table in mysql is taking time

22 hours ago I started a script that executes an alter table to add a primary key to a table which has 225 millions of records. MSISDN is the only column. In showproceslist its process status is ...
2
votes
1answer
78 views

How much space is needed to reclaim space from a table in INNODB

I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the ...
2
votes
3answers
147 views

Bulk Delete for Large Table in MySQL

I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month. If I do DELETE FROM NOTIFICATION WHERE CreatedAt ...
2
votes
2answers
105 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
0
votes
0answers
16 views

Daemon Waiting for activation in mysql event

When I do, SHOW PROCESSLIST; on mysql, for my event, I see the following result: Q1 )Why does it shows Waiting for next activation all the time? Q2) At times my scheduler turns off which causes my ...
0
votes
0answers
72 views

Configuration help my.cnf Mysql

Is the following my.cnf file OK for a dedicated server with 56GB of RAM (on ubuntu server and 8 processors)? I'm running just one website on the server and there is a high demand on my cpu from ...
1
vote
1answer
25 views

Failing to get multiple values using join

I have below scenario: create table t1 (id int, title varchar(20), description varchar(20)); create table t2 (id int, info1 varchar(20), info2 varchar(20)); create table t3 (id1 int ...
1
vote
0answers
233 views

Lost Connection to mysql server at 'reading authorization packet', system error 0"

OK, this has been bugging me all afternoon now, I have a new MySQL Database '5.5.40-0+wheezy1-log' running on Debian Linux. It is to all extents and purposes a clean install. I have created two ...
0
votes
0answers
232 views

Following mysqltuner suggestions: query_cache_size and join_buffer_size

So I am following the suggestions of mysqltuner, and so far I've had great results, the server is performing well (dedicated VPS running a large Magento eCommerce store) There is only two issues left ...
0
votes
0answers
61 views

MySQL Crash due to “InnoDB Assertion” after Query

Current setup: Ubuntu 12.04 LTS MySQL 5.5.31 Current Use: Slave - Only ETL queries 64 GB RAM Intel(R) Xeon(R) CPU X5670 @ 2.93GHz - 12 Cores - Hyperthreaded (24 Logical) MySQL Main Settings: ...
0
votes
0answers
37 views

Last synchronization time between master and slave (replication)

During replication process where a Master is connected with slave. Some times connection between these two goes down. Any options to get the details of the last synchronization time?
0
votes
0answers
90 views

MySQL “ERROR 1005 (HY000): Can't create table 'foo' (errno: 13)”

Windows Server 2008 MySQL 5.5.39 using the root user with full privileges granted to the DB. I know errno: 13 is a permissions issue. I've seen several answers to this for linux where setting ...
0
votes
2answers
940 views

Hyperthreading & MySQL InnoDB Thread Concurrency Performance

I have a dedicated DB Server with the following specs" Ubuntu 12.04 LTS Server Intel(R) Xeon(R) CPU X5670 @ 2.93GHz 12 Cores MySQL 5.5.29 64 GB RAM RAID10 We are currently doing Hyperthreading ...
0
votes
1answer
141 views

Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command

We need to optimize a large table (on a weekly basis) in order to reclaim disk space, since records are being deleted regularly from the table. The table in question is an InnoDB table and the ...
0
votes
1answer
62 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
0
votes
1answer
46 views

Filtering the results of a SELECT query to avoid certain words in the value of a column?

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the ...
1
vote
1answer
35 views

Is there any drawback of MIXED replication mode?

So, recent mysql server allows three types of replication mode: STATEMENT, ROW, MIXED. They all have pros/cons, and I pick the mode according to site characteristics. When I get a replication error I ...
0
votes
0answers
262 views

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

CREATE TABLE approvalreasonexemption ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `personId` INT(10) UNSIGNED NOT NULL, `reasonId` INT(10) UNSIGNED NOT NULL, `createdBy` INT(10) ...
0
votes
1answer
2k views

Executing try catch block in mysql stored procedure

In my mysql stored procedure I want try catch block but it gives error in my mysql stored procedure . I want my select query to be in try block and if it executes correctly I want other query to ...
0
votes
2answers
57 views

about giving same column name in every table of database

I have a database with 5 tables. In every table there is a column for checking status. If i give the name to that column which is same in all the 5 tables; What will happen? Is it correct according ...
0
votes
1answer
129 views

too many connection error mysql: how to calculate max_connections

how to calculate this values to get the best results to resolve this error "too many connection error mysql" and make my API faster in mysql wait_timeout = ?? max_connections = ?? ...
1
vote
1answer
157 views

How to take physical backup of MYSQL database

I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone ...
0
votes
1answer
48 views

Myloader command: Table doesn't exist

I took the backup of mysql database using mydumper command: mydumper -B mydb -o /backup/oct3 -s 500000 -c -m -S /tmp/mysql.sock -u root -p password@123 -v 3 Now, I am restoring mysql database ...
1
vote
1answer
36 views

How to cache query for re-use in following UNION of queries?

I need to perform query, save the result and re-use it multiple times in one session (something like WITH in Oracle DB). This is the query I want to save: SELECT `event`.id, event.name FROM ...
2
votes
1answer
727 views

Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dropped my UNIQUE KEY index 'myindex' on a table and I wanted to change the UNIQUE KEY to something else, so after dropping it I did: alter table mytable add unique key anykey (add1, city, state); ...
1
vote
1answer
50 views

Simple query hangs on a specific column condition

I have two servers, and I a dumped the database of the first to restore it on the second. Everything works great, except that on the second server, I can't do any selection based on a specific column ...
0
votes
1answer
145 views

insert row if table is empty, else update row (mysql)

i have 2 tables "table1" and "table2" both have TIMESTAMP cols for starttime and endtime. i want something like if (table2 is empty) insert into table2 (table2.starttime, table2.endtime) value ...
0
votes
1answer
39 views

Mysql remove binary log

Hello everyone we got situation over here. So, our mysql server is running out disk, after some investigation we found out that our mysql binary file have eat too many space. In order to purge the ...
2
votes
1answer
89 views

Why does MySQL increment the auto_increment value when there's a constraint violation?

I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns. I noticed that when I try to ...
0
votes
1answer
12 views

MYSQL variable in function

Can anybody tell why the value of @URlID is always -1 even when i know the query will return a result? I have tried any possible type of variables and any possible way of inserting into the variable ...
0
votes
1answer
410 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 ...
1
vote
2answers
177 views

Identifying cause of high I/O

I'm trying to diagnose the bottleneck for high-write I/O activity on a percona master (with replication to a slave). The pattern of behaviour is roughly cyclic and regular: 60 mins of 75-90% ...
1
vote
1answer
87 views

MySql - unique key not working

I have the following table: CREATE TABLE `timeline_lists` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `list_type` int(11) NOT NULL, `s_object_id` int(11) NOT NULL ...
2
votes
2answers
1k views

High MySQL “InnoDB Writes Per Second” - Can't see the queries?

On our live MySQL 5.5 database server, we use MySQL Workbench for running manual queries, checking running queries to see what's going on, etc. I noticed today that on the "Server Status" tab there's ...
1
vote
2answers
95 views

Monthly report by time [closed]

I work in a parking company. We need a report of monthly vehicle exits by time. select hour(datetimeout) as 'dateout',count(*) from to_leaveveh where datetimeout between '2014-09-01' and ...
0
votes
0answers
46 views

Best upgrade path for MySql 4.1 to 5.5?

Background Our production environment runs on a system that nobody wanted to update for a long period of time. With a new sherrif in town, we're going to update it. Production is MySql 4.1 with ...
3
votes
2answers
56 views

Fetch all words from column with like

I have a column with description and inside it can contain # value . what I want is all the words searched from that column with #tag This is my query SELECT description FROM photo where ...
1
vote
2answers
297 views

Using MySQL/PHP PDO, is there a 'simulate' or dry run option?

Does MySQL (or, ideally, PHP PDO) have some way of simulating transactions without actually committing them? (Can I get MySQL or PDO to act as if data is inserted/deleted/updated, without actually ...
1
vote
0answers
38 views

pt-online-schema-change with circular replication

So I have an alter table (adding 4 columns) to do on a fairly large table (74 million rows). The problem is this table has firstly to remain online and writable during the whole operation and ...
0
votes
0answers
8 views

Why pages not made young is zero?

In results of Show INNODB Status showed me very weird result: Pages made young 81821279, not young 0 The official statement says: Pages made young, not young: The number of old pages that were ...
0
votes
1answer
181 views

Change MAMP to use a lower Mysql 5.1 version

I need to have the same development environment for my PHP program. My clients test server is running on Mysql 5.1 while my local is using MAMP running Mysql 5.5. How do downgrade my mysql database ...