MySQL version 5.5 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

1
vote
0answers
1k views

Server version (5.5.40-MariaDB) does not match with the version of the server (10.0.15-MariaDB) with which this program was built/distributed

I just upgrade MariaDB (Mysql) 5.5.40 to MariaDB 10.0.15 by following this step by step. I have failed to enable the mysql and also mysql_upgrade with the following errors mysql_upgrade -u root -p --...
0
votes
0answers
63 views

Access Denied to users after upgrading mysql to 5.5.40

I recently upgraded mysql from 5.1.0 to 5.5.40 but suddenly some users seem to have lost the previlages. All the projects are live and those with issues are showing database connection error. How can ...
2
votes
1answer
494 views

MySQL - Get Minimum Value, Data From Multiple Tables

Good evening all, first post on the DBA forums. I am having real issues working out how to accomplish a seemingly simple task within MySQL as a model for PHP code later on. I am not exactly a MySQL ...
2
votes
3answers
758 views

mysql replication master 5.5 slave 5.1 error on create database

I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we ...
3
votes
1answer
2k views

View last several innodb deadlocks

I see that I can view the latest deadlock in mysql/innodb, but is there a way to view past deadlocks? We have two deadlock issues, one being important and the other not. The less important deadlock ...
1
vote
1answer
867 views

When try to do Mysqldump Backup from console then getting such as error [closed]

mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect when i am tring to do MySQLDump backup from console then getting such as above error. I am ...
1
vote
1answer
155 views

Finding out the exact size of the database after inserting records?

I have this query: SELECT table_schema "db.name" , Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; Does this ...
1
vote
1answer
17k views

MYSQL query to find records between two date range

I am working in a web application using PHP and MySQL. I have two tables Employee employee_id email_id 3 tl1@jasmine.com 4 agent1@jasmine.com 5 agent2@...
0
votes
1answer
98 views

Hierarcy Query Mysql

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ===============================================...
1
vote
0answers
64 views

Can MySQL Index Merge an Composite index with a single column index?

I am tuning a query and currently have an index as following : Index 1 : (col1, col2) An example query would be : SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2'; I have created an index on ...
0
votes
1answer
59 views

Mysql Master - Master Replication

i have established a master A and master B replication between two servers.Both are getting replicated.But it is limited with only one database. My questions 1.How to create a new database in ...
0
votes
0answers
227 views

Slow Query Performance - Can't Debug

I have a MySQL query that I am trying to debug the performance issue but cannot. I know that the majority of the query is reading/processing rows (Sending Data state). However, in the explain output, ...
1
vote
1answer
48 views

Queries that ran, and the number of times they ran

I want to profile a running web application in dev on a mysql 5.5 database. The web application uses hibernate and I am able to see that a rather large number of queries are run against the database. ...
1
vote
2answers
390 views

One to many join, Sum from both table

I have two tables: orders and ordersdetail...(partial table) orders- id(primary key), amount, date( index) orderdeails- id(primary key), skunitid, quantity, order_id Each order has multiple order ...
1
vote
1answer
227 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
287 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 ...
3
votes
1answer
5k 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 ...
1
vote
1answer
1k 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 ...
3
votes
4answers
369 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
906 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
552 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 ...
2
votes
0answers
577 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
53 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
447 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
684 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 ...
3
votes
3answers
2k 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
806 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
230 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 mysql....
1
vote
1answer
26 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
858 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
1answer
2k 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
101 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?
2
votes
2answers
7k 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
884 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 ...
1
vote
1answer
273 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
433 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
57 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
1k 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) ...
1
vote
1answer
8k 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 ...
1
vote
2answers
756 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
268 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
1k 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 ...
1
vote
1answer
414 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
76 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
3k 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
128 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 ...
1
vote
2answers
505 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
93 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
207 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
13 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 ...