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

learn more… | top users | synonyms

1
vote
1answer
428 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 pt-...
1
vote
1answer
145 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. ...
1
vote
1answer
48 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
45 views

MySQL Replication Between Three Hosts?

We have a need to move our Moodle server to a different host, and our current host runs a LAMP stack that likely runs on a VM. It is accessed most of the time. In the event that the current host ...
1
vote
1answer
2k views

MySQL replication error 1594

Is there a way to restart replication without going over MySQL :: MySQL 5.1 Reference Manual :: 16 Replication. I'm NOT seeing ANY errors on master and I'm not entirely sure why slave is ...
1
vote
1answer
158 views

What does this mean - ~/.my.cnf

When I run $ mysqld --verbose --help | less I got information like Default options are read from the following files in the given order: /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf As per my ...
1
vote
2answers
4k views

Does MySQL have a version of Change Data Capture?

We're in the process of phasing out an old system and migrating onto a new one. The last time that we phased out an old system, we ran both systems in parallel and integrated data between both until ...
1
vote
1answer
3k views

MySQL master master replication, safe to delete rows on both masters?

We have a MySQL (v5.1.61) cluster with two masters (and two slaves). We have a table logs with a primary key column logID. This log table grows rather huge, and we'd like to delete data from it. So I'...
1
vote
1answer
336 views

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

I have a 150GB database and my ibdata1 file size is 240GB so I want to shrink the ibdata1 file because I have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
1
vote
1answer
712 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 ...
1
vote
1answer
175 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 ...
1
vote
1answer
12k views

Selecting multiple columns from multiple rows in one column (subquery?)

I'm working with MySQL 5.1.68. I have a situation where I'd like to select rows from another table into a column. I'd like to output this: id # name # citizen_name (multiple citizen names) 1 # ...
1
vote
1answer
2k views

UPDATE SET REPLACE() matches but does not change

I've seen another post about this exact thing but it's 1-1/2 years old so I thought I'd make my own...besides, the other post did not help me. What I am trying to do is pretty straight-forward. To ...
1
vote
1answer
176 views

Is there any query to fetch the a single row data in chunks?

The data stored in a row is just like "Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call ...
1
vote
1answer
478 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 ...
1
vote
1answer
2k views

What would cause Mysql database entries to be truncated to 253 characters?

Randomly seeing database entries truncated to 253 characters on a php site and can't figure out why or if it could be malicious or a corruption issue. Any thoughts would be appreciated.
1
vote
1answer
3k views

Retrieve double data type value in non exponential form

Is it possible to retrieve non-exponential value in double data type column? After the width of the column is increased to 15 it shows the values in exponential form. Now I get the following: mysql&...
1
vote
1answer
408 views

How do I restore 3 tables into a MySQL database from a single Windows dump file?

I want to import 3 specific tables from a dump file that contains 270 tables. The dump file, naveen_ifs_db.sql, is 20 GB. How do I restore 3 tables from that dump-file using the command line in ...
1
vote
1answer
1k views

MySQL Master-Slave Replication Cross Database

I am setting up Replication for cross Database as i have set 2 variables in Slave' s my.cnf as replicate-do-db=database_name and Replicate-wild-do-table=database_name.% ,But i am not able to get ...
1
vote
1answer
361 views

MySQL lost records in a master slave scheme

We have developed a web application that uses two MySQL servers set up with replication (master-slave scheme). The application was to be used for a couple of days only, when ~ 50-60 users would enter ...
1
vote
1answer
249 views

time executing inserts + slow SSH

_ Hello, my setup is a slow SSH to some linux machine. From there I start a mysql terminal client and execute about 200k inserts (in a while loop inside a stored procedure). This takes about 3 hours ...
1
vote
1answer
2k views

MySQL - MySqlDump uses \n instead of null

Using Windows with MySQL 5.1. I am running a script that uses mysqldump to backup a database. The mysqldump syntax used is: mysqldump --user=root --port=3306 --password=topsecret some_database --...
1
vote
2answers
26 views

MySQL Front - Workbench

We have MySQL Front installed in our server, I can connect to it with no problems, there is a db with tables, everything fine. Now I installed MySQL workbench (client) in the same server, but can ...
1
vote
0answers
14 views

Unable to get anything other than 'Remote scan' when selecting from linked MySql server

I have a linked server in SQL Server 2012 (SP3) that points to a MySql 5.1.61 server on another machine. Whatever I do, I don't seem to be able to get any where filtering to occur on the remote side. ...
1
vote
0answers
38 views

MySQL 5.1 - Display the error-message returned inside a stored precedure

I'm trying to find an alternative to display the error-message returned from MySQL-5.1/5.5 inside a stored procedure. The example below is what I'm looking for, however it's seems to be only allowed ...
1
vote
0answers
45 views

Database has thousands of randomly named tables

Preface: First of all excuse me for bringing forwarding this weird question, as it could be just me. Situation: We have a Centos 6.5 Cloud VPS hosting our website, running MySQL 5.1 and it is also ...
1
vote
1answer
186 views

Why do innodb tables get corrupted so easily?

I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been ...
1
vote
1answer
39 views

MySQL - LOCK IN SHARE MODE for EXISTS query

I am starting a transaction and I need to check that a record exists, I don't need any values, but I need it to exist for the duration of the transaction for everything to remain valid. It's VERY ...
1
vote
1answer
150 views

Automatic replication on slave node after service restart

I am new to mysql administration. we have master - slave replication environment. I added a 2nd slave node to existing env [master-slave] and replication is successful. Whenever I restart the mysqld ...
1
vote
2answers
205 views

What does MySQL mean by “Major Version”, and is MySQL replication supported between 5.1 and 5.6?

I am migrating from some MySQL 5.1 servers to MySQL 5.6 servers. The MySQL docs are a bit unclear if replication is supported between a 5.1 master and a 5.6 slave, and MySQL's documentation is unclear ...
1
vote
0answers
351 views

mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway ...
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 ...
1
vote
2answers
128 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
1answer
124 views

MySQL After Update Trigger apparently not running

I have the following After Update trigger on a MySQL table: CREATE DEFINER = 'root'@'localhost' TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD AFTER UPDATE ON ...
1
vote
0answers
100 views

Merge/combine two queries with different dates

I have one query that runs a SUM statement based on daterange 1, and worked fine, and another query that run same SUM statement, but on a different date range. I would like to merge/combine the two ...
1
vote
3answers
2k 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
0answers
102 views

MySQL: Sometimes query failed to use index

I have a complex query including join of 4-5 tables. 1 months back, I have optimized this query and it was using the index. Also, two months back, index of all tables had been rebuilt by just export ...
1
vote
0answers
253 views

MySQl: slow query is not using index in joining

Below is the configuration of my DB server: Apache version : Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8 PHP version : 5.3.13 MySQL version : mysqlnd 5.1.11 Windows Server 2008 Standard ...
1
vote
1answer
1k views

mysql 5.1 — unknown variable 'basedir=c:/mysql/'

I recently changed the default port(3306) to another for mysql. I did it by changing the my.ini file and did restart of the server. No other changes were made whatsoever. When connecting using a GUI ...
1
vote
1answer
312 views

Upgrading MySQL 5.1 to 5.5 - All tables gone [closed]

I'm trying to upgrade from MySQL 5.1 to MySQL 5.5. I followed the instructions here: http://dev.mysql.com/doc/refman/5.5/en/windows-upgrading.html I also adjusted the Datadir. But after the upgrade, ...
1
vote
0answers
325 views

why is this query taking so long?

+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | ...
1
vote
0answers
335 views

Fulltext search returning too many rows

Server: MySQL 5.1.66-0+squeeze1 My problem: I have two databases with the same tables. One is for the testing environment and one for production. When I perform a MATCH AGAINST on the prodution ...
1
vote
1answer
404 views

mysql freeze/crash on JOIN

here is query: SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as ...
1
vote
0answers
287 views

Database instance keeps restarting

From the past few days, our MySQL database instance has crashed and had to be restarted a few times. My MySQL version is 5.1.57-log. The CPU and memory usage was way below 50% during the crash time. I ...
1
vote
0answers
39 views

How does MySQL 5.1 use indexes?

I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly. Suppose having the following MySQL table: CREATE TABLE IF NOT EXISTS `...
1
vote
1answer
171 views

How big is a tmp table?

I've got an RDF table in MySQL 5.1 with the MyISAM engine. Here's the table definition: CREATE TABLE `node` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `graph` varchar(100) CHARACTER SET latin1 ...
0
votes
3answers
1k views

Changing MySQL auto_increment_offset

Sorry for the wall of text, but I don't know what's relevant and what's not. I have MySQL a MySQL database that it is being replicated from our production environment to our development/test ...
0
votes
2answers
271 views

MySQL: Moving Database from Windows to Linux

I have a Windows based dedicated server have below configuration: Windows edition: Windows Server 2008 R2 Standard Service Pack 1 Processor: Intel(R) Xeon(R) CPU X5460 @3.16GHZ 3.16 GHZ (2 Processors)...
0
votes
1answer
118 views

How can I move a MySQL database from 5.1 to 5.6?

I have pretty big database, like with 5 000 000 rows in total. It is located in a remote server. I want to copy this database to my personal computer, for testing purposes. However I don't know the ...
0
votes
1answer
1k views

MySQL: Use Sysbench on Windows

Sysbench is a tool to analyze the MySQL performance. I downloaded it and extracted but there is no executable file. Files having extensions like .sh or .c or .h are available. I gone through the ...