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

learn more… | top users | synonyms

0
votes
1answer
847 views

TCP sockets bottleneck with InnoDB/Debian?

We have a big mysql performance issue on a production environment with 18 "front" servers sending mysql queries to a unique mysql server. We are using Redis on each front server for a number of things,...
0
votes
1answer
119 views

mysql's query cache behaviour on LIMIT

Is it accurate to assume that MySQL's query cache gets checked on subsequent execution of queries in the form SELECT ... WHERE ... LIMIT x, y where the the only varying paramaters in the query are x ...
0
votes
2answers
43 views

Calculating the number of times a group occures

I'm trying to figure out how many times in my database a particular grouping has occurred. I have two relevant tables. mysql> describe logins; +-------------+-------------+------+-----+-----------...
0
votes
2answers
199 views

Best way to handle temporary data

Say I have a table prices. Users can see the prices of this table (I'm simplifying it). Users also can send prices to the server, so the server is going to collect these data in another table (...
0
votes
1answer
1k views

Migrating from SQL Server to MySQL using MySQL Workbench tool

I'm trying to migrate few tables from SQL Server to MySQL using MySQL Workbench migration wizard. All work fine for structure migrations but when I go to the data migration section it throws an error ...
0
votes
1answer
646 views

Mysql:Need to upload data to database, get duplicate key error - how can dup keys be ignored

I'm uploading data to a db (mysql 5.1.42) from a dump file using mysql command line and get duplicate key error. This is the format I use: /mnt/temp# mysql -u username -p -h url_of_db_master -D ...
0
votes
1answer
32 views

Is there any impact to changing IP address of MySQL Server?

We are changing the network and the IP addresses will be changing. Will there be any impact to MySQL running on the server? I don't use the IP address to reference the server. Instead I use the ...
0
votes
1answer
107 views

MySQL Tuning and optimization

How to Tune and Optimize MySQL Database, as a reference we are using mysqltuner.pl and we got the below output of slave host which we created recently. our future plan is the perform this same action ...
0
votes
1answer
53 views

How to reset slave without over write of master dump in mysql

My master having position is File Position mysql-bin.000046 430674598 I lost my data dir of mysql on master after that I have backup of slave which restored in master.Now Both ...
0
votes
1answer
27 views

Putting an order in the way how locks are aquired in MySQL

From MySQL documentation: A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether ...
0
votes
1answer
104 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
1answer
685 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
1answer
967 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 ...
0
votes
1answer
2k views

MySQL: How index speed up query execution

I have read multiple articles about MySQL indexes. Some of the important pages are below: http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html http://stackoverflow.com/questions/3567981/how-do-...
0
votes
1answer
285 views

MySQL: How turning off the logging affect the performance?

OS: RHEL5 DB: MySQL 5.1.73 community edition DB size: ~1GB I have setup a testing environment and turned off all logging - general logs, binary logs, slow query logs etc. I am using Oracle ...
0
votes
2answers
5k 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
768 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 '0000-...
0
votes
1answer
2k views

Difference between [mysql] and [mysqld] in MySQL configuration file

There are multiple sections in MySQL configuration file: [mysql] [mysqld] [myisamchk] [mysqld_safe] [mysqlhotcopy] [mysqldump] [client] I found that same variables with different values have ...
0
votes
2answers
4k views

How to trace a MySQL session like in Oracle

I have an application running in MySQL 5.1.58 community edition. My application is working fine, but suddenly any query stuck and then after all queries is getting logged in slow query log. I am ...
0
votes
1answer
971 views

MySQL 5.1.x: Performance improvement in Enterprise edition Vs community edition

My application is running on MySQL 5.1.58 community edition. I tried to search enterprise edition of MySQL 5.1.X but only MySQL 5.6.X version of enterprise edition is available. Is it possible to ...
0
votes
1answer
91 views

MySQL: How to find MySQL 5.1 (with built-in InnoDB) or MySQL 5.1 (with InnoDB-plugin)?

My DB server has MySQL 5.1.58-community-log and I am planning to upgrade it to MySQL 5.6 with specific purpose of improving my system performance and removing the bottleneck issue. So, I have gone ...
0
votes
1answer
52 views

MySQL: Algebra in Union operation

I have below query: select entity_id from table1 exc UNION select entity_id from tabl2 inc On execution, I got 24419 rows. When I execute select entity_id from table1 exc I got 16235 rows and on ...
0
votes
2answers
1k views

MySQL: why assign NULL as default value

When we define a table, multiple times we assign default value of a column as NULL. I have How NULL is different than empty value('') as well as 0? Is there any impact of NULL value in query ...
0
votes
2answers
121 views

MySQL using swap during conversion to InnoDB

I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions ...
0
votes
1answer
112 views

Tracking changes on a replication slave

I suspect the answer to this is "you're boned", but I'd like to see if there are any good options that I haven't explored. We're building a system that will integrate with a 3rd-party POS system and ...
0
votes
1answer
712 views

Out-of-Range and Overflow Handling for float values

am using mysql 5.5 log version with sqlmode='' .I have many columns with float(9,3) as the type. Say for ex one column taxvalue (9,3) Currently if the user enter the value above 999999.000 MYSQL ...
0
votes
1answer
696 views

Index for ORDER BY on aggregate in MySQL

I'm working with the following query: SELECT *, SUM(entries_count) AS entries_count FROM leads WHERE promo_id IN (1, 2, 3) GROUP BY user_id ORDER BY entries_count DESC LIMIT 25 OFFSET 100; It's ...
0
votes
1answer
124 views

Circular replication with servers in different locations

My service runs on single machine, and database (mysql 5.1.66) is replicated to 2 other identical slaves (with lesser system resources, mysql 5.1.66) via chained replication. Machines are located in ...
0
votes
1answer
3k views

MySQL: Can't find record in tablename

After crash: mysql> repair table lot_not_sold; +------------------------+--------+----------+---------------------------------------------------------------+ | Table | Op | ...
0
votes
2answers
1k 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
36 views

MySQL Commands out of sync error

I am new to MySQL, normally use SQL Server. I have a MySQL server (version 5.1.73) and I have two databases, syslog and syslog_archive. There are three tables: syslog.logs syslog_archive.logs ...
0
votes
2answers
27 views

Importing mysqlnd database to MySQL server

Importing a mysqlnd (5.0.12) database to MySQL server (5.1.66) causes error? Currently we are performing this task and encountering errors (like below): ERROR 1146 (42S02): Table 'xx-xxx-xxx-xxx' ...
0
votes
0answers
37 views

Issue with fetching data from mysql according to recent timestamp ASC ordering

I am having trouble with mysql query to fetch the data according to the recent timestamp in ASC order. it means the recent updated one record will be first shown. The Query is grouping with the ...
0
votes
1answer
304 views

Migrating/Upgrading a MySQL 5.1 schema to MySQL 5.7 schema

I'm bit green on MySQL upgrades, but my question has to do with single schemas (dbs). I have some hosted databases on a server using MySQL 5.1 Community, and I currently use mysqldump to back those ...
0
votes
1answer
23 views

Two mysql db's load the same data at two different rates

Any idea why this might be happening? We have the same mysql (*.sql file) data being imported into two virtually identical mysql databases every day, but one consistently takes 30% longer to load ...
0
votes
2answers
28 views

How to run mysqldump from windows command prompt

I'm using non-install version of mysql5.1 on Windows. I have placed "mysql" folder under c:\ and it seems to work fine. But when I try C:\>mysql\bin\mysqldump.exe -u root -p mydatabase > c:\>...
0
votes
0answers
28 views

MySQL import a sql file but login for which is different than localhost db

I have a SQL file which I want to import into MySQL using the command line but get an error saying that the username does not have access to it. The file is too big, like 35GB, so I can't open it and ...
0
votes
2answers
75 views

Upgrading the mysql server from 5.1 to 5.6?

My server have CentOS6. Currently I'm are using the Percona 5.1.47 version. Would like to upgrade to 5.6. What is the best way to do, and what are the prerequisites to keep in mind before doing ...
0
votes
1answer
1k views

GRANT EXECUTE ON PROCEDURE unable to USE database

I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance. The stored procedure is defined with SQL SECURITY as DEFINER. ...
0
votes
0answers
139 views

Performance Issue on Centos Server (Mysql)

I have performance issue on my centos server (vps). I asked about it in serverfault and I was said there is a problem with mysql and ask here. Here is serverfault topic, you can find details. Shortly;...
0
votes
1answer
37 views

Unable to show binary logs how to remove logs from mysql?

Server version: 5.1.50-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show binary logs ; PROBLEM Data directory is full I want to remove ...
0
votes
2answers
83 views

MySQL Upgrade on Windows

I am having MySQL 5.1 installed on windows box. Now I would like to upgrade to latest MySQL 5.6 version. I am planning to install MySQL 5.6 in the same server with same port 3306. After installation, ...
0
votes
0answers
112 views

Replicating from MySQL 5.1 master to 5.6 slave failing because 'INSERT … VALUES (NOW())' results in 'Error_code: 1062'

I am migrating away from some old MySQL 5.1 servers to some new MySQL 5.6 servers. During this process, I'm creating a new MySQL 5.6 slave from an existing MySQL 5.1 slave, using the procedure in the ...
0
votes
1answer
2k views

Error 1067: The process terminated unexpectedly [InnoDB]

I am facing the following error when i try to restart the MySQL service after an unexpected shutdown of the database server last night. Could not start the MySQL service on Local Computer. Error 1067:...
0
votes
2answers
48 views

Table partition should not be selected

I am looking to partition a table (nearly 30M rows) by date ranges (mysql version : 5.1.73) CREATE TABLE `TABLE_X` ( `name_x` varchar(50) NOT NULL, `type_x` varchar(5) NOT NULL, `status_x` ...
0
votes
2answers
243 views

How to create function using another functions mysql

I want to create a function in MySQL using another function but I am getting an error "Explicit or implicit commit is not allowed in stored function or trigger". This is my code: DELIMITER $$ ...
0
votes
1answer
93 views

merge multiple fields of csv file while running mysqlimport command

I have a mysql table like this +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---...
0
votes
0answers
51 views

The same timestamp in different MySQL versions

I read in mysql 5.6 SQL changes following text: TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not ...
0
votes
0answers
240 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
113 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 ...