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

learn more… | top users | synonyms

1
vote
1answer
1k 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
152 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
2k 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 ...
1
vote
1answer
323 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
676 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
173 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
11k 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
170 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
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: ...
1
vote
1answer
401 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
353 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
247 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
0answers
40 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
121 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
0answers
26 views

Alter a large mysql table 700 gb

The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log. I found following options: pt-online-schema-change: alters a table’s ...
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
102 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
165 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
334 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
124 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
114 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
99 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
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
298 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
324 views

why is this query taking so long?

+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | ...
1
vote
0answers
320 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
454 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
383 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
278 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
38 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
168 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
949 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
243 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 ...
0
votes
1answer
108 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 ...
0
votes
1answer
813 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 ...
0
votes
1answer
117 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
177 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
632 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
85 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
43 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 ...