MySQL 5.1 : The Second Major Release of MySQL 5

learn more… | top users | synonyms

0
votes
1answer
240 views

Mysql query is very slow

I am developing a website like any other social networking site using mysql. I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is ...
0
votes
2answers
736 views

How to run a cold backup with Linux/tar without shutting down MySQL slave?

I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
0
votes
1answer
509 views

How to configure my.cnf for Mysql 5.1, which is using too much CPU?

I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system ...
1
vote
1answer
327 views

Different thread states in query execution

My MySQL 5.1 based Intranet system is facing very bad performance during high load. When observed through MonYog, I found below thread states: We can simply understand the different thread state by ...
0
votes
1answer
74 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 ...
3
votes
2answers
168 views

Problem in Fetching Result set in Desired Format

I have 2 tables having the following columns Person ------ person_id, first_name, middle_name, last_name Items ----- person_item_id, person_id, item_type, status, ordered_date The query is ...
0
votes
1answer
168 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have 150GB database and my ibdata1 file size is 240GB so i want shrink the ibdata1 file so for that i have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
1
vote
2answers
48 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
3k views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
0
votes
1answer
42 views

ALTER TABLE blocking in MySQL 5.6

I have a question with a MySQL 5.6 master-slave setup. If I were to modify the master's schema with an ALTER TABLE SQL query, then when the slave tries to execute that ALTER TABLE statement too, would ...
0
votes
0answers
10 views

mysqldbcompare showing AUTO_INCREMENT differences but there are no missing records

I am encountering a strange issue when using mysqldbcompare to compare two MySQL DBs(one is a master, the other its slave). I noticed that the tool notes that the AUTO_INCREMENT values of this ...
0
votes
1answer
100 views

How to avoid/prevent Aborted Connections in mysql

We are using MySQL Version 5.1.73. Database is getting huge number of connections around 600 at one time. I observed Aborted Connections are getting increased from Global Status variables. So I ...
0
votes
1answer
159 views

Insert into a view derived from a table with a discriminator field

I am in a situation in which I have a base table with some fields and a discriminator field which could have one out of a specific set of values. From that base table, I have derived many views, one ...
0
votes
1answer
116 views

Help interpreting a MySQL execution plan

I'm trying to understand a slow query. The SELECT has been running (at this moment) for almost 18000 seconds (output from a SELECT on PROCESSLIST): id: 22 time: 17870 ...
0
votes
1answer
717 views

Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard?

MySQL 5.1.x | Windows Server 2003 Can someone please clarify why max_connections in my.ini is much larger than the manual value I specify in the Concurrent Connections Dialogue? For example, if I ...
0
votes
1answer
93 views

Extract one MySQL database from a FULL MYSQL Server backup file

I have a small shared windows server on which i'have installed MySQL 5.1 engine for hosting my user's mysql databases. I backup the complete MySQL databases each day by running this script as batch ...
0
votes
1answer
26 views

WordPress MySQL writes during “Flush Tables With Read Lock” are rejected instead of being queued

I'm running into an odd issue when setting MySQL into a brief read-only mode for backup. Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to ...
1
vote
0answers
14 views

Convert a list into one row with dynamic column in MySQL [duplicate]

I have a list of row to be converted into a dynamic column that depends on the record in one particular column. From this statement FID |Steps | Action ---- |------|-------- 1416 | 1 | Sit ...
0
votes
1answer
386 views

MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Can someone tell me how to prevent the following commands DROP TABLE <tbl-name>; TRUNCATE TABLE <tbl-name>; from being replicated to slaves?
1
vote
3answers
53 views

Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB. I know the risks involved based on resources such as ...
1
vote
2answers
61 views

return my.conf values to sane levels (total memory footprint = 220GB)

I recently took over administration responsibilities for one of our databases. The existing my.cnf has some crazy values. I should mention that one table has 1.8 billion rows of mostly unneeded ...
1
vote
3answers
223 views

MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux?

I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and ...
1
vote
1answer
24 views

Find MySQL Patch Level

What command do you use to find the patch level of MySQL? I am currently on 5.1.73. I need to find out what patches have been applied to this database. Thanks...
0
votes
1answer
44 views

Steps or evaluation order of the SQL statement

How the SQL statement containing simple statement like Select x From y Where z Having, order by , group by etc. are evaluated.( In which order they are evaluated.?)
0
votes
0answers
21 views

Intermittent MySQL 5.17 issues - extremely slow queries

I am experiencing major issues with intermittent mySQL issues. To give some background the website runs optimally for the majority of the time, however 3-4 times a day queries that should take a split ...
0
votes
0answers
27 views

Differing AUTO_INCREMENT values between 5.1 vs 5.6 slaves

I have a MySQL 5.6.21 slave and a MySQL 5.1.73 slave that are both replicating from the same MySQL 5.1.73 master. I noticed that for several of the tables ( I said several because when I try to rerun ...
0
votes
1answer
60 views

MySQL replication working, but data is out of date

I'm trying to figure out why even though SHOW SLAVE STATUS isn't reporting any errors, nothing is replicating (see below): both nodes are running: mysql> SELECT VERSION(); +------------+ | ...
3
votes
1answer
1k views

Does `Seconds_Behind_Master` show exact Slave Lag from Master?

Does Seconds_Behind_Master always show the correct number of seconds the slave is behind the master? I have also Implemented the mk-heartbeat replication monitoring. It was showing the other result ...
0
votes
0answers
37 views

mysqldbcompare shows checksum fail but row differences passes between MySQL 5.1 and 5.6 databases

I have started to try using mysqldbcompare to help me compare two databases(one is MySQL 5.1.73 and the other MySQL 5.6.21) that are each about 5 gigs with lots of tables, but the databases are ...
0
votes
1answer
41 views

Accuracy of mysqldbcompare vs other tools like pt-table-checksum

I was curious to hear what everyone's experiences are with using mysqldbcompare vs pt-table-checksum( from Percona Toolkit ) to find the differences between two MySQL databases. I have heard that ...
0
votes
0answers
5 views

MySQL Master Slave replication seems to affect whole solution

We are running 2 master slave pairs on a web application MySQL 5.1. The concept is to keep all data in sync so that we can via a config file point all customers at one master slave pair to do ...
1
vote
1answer
46 views

Using pt-table-checksum to compare a slave's DB to another slave

I understand that pt-table-checksum can do checksum compares between a master's DB and a slave's DB easily. However, let's say your master has two slaves replicating from it for the same DB, i.e. ...
0
votes
1answer
26 views

InnoDB error: exceeded log capacity…but only during import

I see this error in mysql.log: InnoDb error: the age of last checkpoint is...which exceeds the log group capacity etc.. I think I understand it, but the fact is that it's happening only during a ...
1
vote
2answers
76 views

Fourth inner join to the same table increases query duration significantly

As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec. The table in question is deployments d4. The SQL Query: select a.id as ...
1
vote
1answer
443 views

Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21. I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
0
votes
1answer
46 views

I noticed that my MySQL slave's DB size is smaller than the Master's DB

I have a very simple setup of a master MySQL DB with one slave MySQL DB replicating from it. Initially, when setting up the slave, I used mysqldump to get an initial DB dump from the master like ...
0
votes
0answers
827 views

The total number of locks exceeds the lock table size, even after increasing buffer pool size

I am trying to partition a table with 200 million records in it. This table already exists: CREATE TABLE linkmap_ntoken ( target_nToken varchar(200) NOT NULL, ntoken_hashcode int(15) NOT NULL, ...
0
votes
1answer
45 views

Upgrading from MySQL 5.1.37 to MySQL 5.1.73

We have a fairly advanced website that runs on a cloud system. We have one master DB server and 2 Slave DBs that read from the Master. We're currently using 5.1.37 (I know...its old and from 2009), ...
0
votes
1answer
47 views

MySQL: What do we mean by 0 value in in_use column of show open tables statement?

If I run show open tables command, system return 73 records. But, in_use column value for every record is 0. What does this mean? Are these tables available in cache but not used by any thread?
0
votes
0answers
28 views

is there any limit of MySQL status variable value?

As we know MySQL status variable is a counter variable since the service start. MySQL service in my DB server restart every week (on sunday). Below is my observation since last few week: Suddenly ...
0
votes
0answers
31 views

Intermittent My SQL error: high cpu wio and cannot connect

I have a new deployed MySQL server with following specs: Server: 2CPU x 8 cores, 16GB RAM, 300GB hdd SAS RAID 1 OS: CentOS 5.11 MySQL: 5.1.73 Now I have a very strange and intermittent problem: ...
0
votes
0answers
26 views

How to handle too many instances of multiple tables?

If there is a table accessed by multiple DB threads ( for example 100) at a time then how many instances of that table will be opened? If I run show open tables and see that in_use column value of a ...
0
votes
0answers
100 views

error 1298 (hy000) unknown or incorrect timezone mysql

I'm following MySQL :: MySQL 5.1 Reference Manual :: 16.1.1 How to Set Up Replication guide. I'm using: # cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.5 (Santiago) # rpm -q ...
0
votes
2answers
362 views

MySQL 5.1: change datadir variable value in my.cnf

Below is my system configuration: OS: RHEL5 MySQL: 5.1.73 Default datadir=/var/lib/mysql/ But, the above directory has space crunch. So, I decided to change the path. I found /u000 directory has ...
0
votes
1answer
266 views

Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

In one Database table, created_at & updated_at are defined as: `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `updated_at` timestamp NOT NULL DEFAULT ...
0
votes
0answers
65 views

MySQL: What should I do if too many tables opened during peak hours?

In Peak hours: When I run below statement, I found around 100 tables out of 250 have opened. show open tables where in_use>0; There are around 10 tables for which in_use value are >50. Storage ...
1
vote
1answer
26 views

Do threads in cache still use memory?

I was studying about how and how much memory allocated to each connection thread and found below MySQL tutorial: http://dev.mysql.com/doc/refman/5.1/en/memory-use.html There is a point mentioned as ...
1
vote
1answer
12k views

Error Code: 1205 Lock wait timeout exceeded in MySQL

I have set InnoDB lock wait time-out to 120 seconds. When I run the query below for first time, it works fine. However, the second time, the same query with a different value gives me: Error ...
0
votes
1answer
36 views

MySQL: Adding innodb_log_file_size in my.cnf causes not starting MySQL service [duplicate]

I checked and found that InnoDB pluginis active. By default, Innodb_log_file_size=5MB. As per the MySQL Documentation, its ideal values range from 1MB to 1/N-th of the size of the buffer pool, where ...
0
votes
2answers
45 views

Using subqueries in delete query

I have a table candidate. It contains two columns name and roll. The values in all three rows of data are AA and 10. Now I want to delete only the second row. How can I do it?