MySQL 5.1 : The Second Major Release of MySQL 5

learn more… | top users | synonyms

1
vote
1answer
110 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
votes
2answers
440 views

Infinite Loop While Running Query

When I run this query from the command line, it continuously loops. I'm not sure why this is occurring. Could anyone explain this to me, and please offer a fix. I will be using a query similar to ...
0
votes
0answers
65 views

InnoDB data back in 3 months after OS shutdown

I've got an interesting issue last week that after shutting down an instance of mine, all data were back in 3 months (like a big rollback), even though there is no sign of crashing, recovery crash ...
3
votes
1answer
5k views

How to solve “The table … is full” with “innodb_file_per_table”?

I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
3
votes
1answer
83 views

How can I get this query to use it's index?

Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime). As I don't really know what I'm doing I tried adding indexes to all columns in ...
3
votes
2answers
140 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 ...
1
vote
1answer
2k views

Mysql Forcing close of thread 946 user

My mysql box keeping shutting down and up. Below is snippet of the log file. There is quite a number of places I notice this Forcing close of thread 946 user: 130426 12:36:28 [Note] Event Scheduler: ...
1
vote
1answer
139 views

Is there a way to recover this database (MySQL)

I did a "cold" (db offline, all filesystem files) backup of a MySQL db. I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default. I didn't realize that until attempting to ...
2
votes
3answers
1k views

Can I make sure two columns dont have the same value

If I have a table that looks like this CREATE TABLE foo ( id INT NOT NULL AUTO_INCREMENT, aa INT NOT NULL, bb INT NOT NULL, PRIMARY KEY (id), UNIQUE KEY (aa, bb), CONSTRAINT aa_ref ...
0
votes
1answer
190 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
1answer
84 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 ...
2
votes
1answer
969 views

Innodb Slow queries since convert from MyISAM

a few days ago we converted some write intensive tables from MyISAM to InnoDB hoping to have a better performance due the better locking system of InnoDB, but instead of gain performance, we start to ...
-4
votes
1answer
279 views

where can I get mysql-5.1.7.tar.gz or mysql-5.1.9.tar.gz? [closed]

I need mysql-5.1.7.tar.gz or mysql-5.1.9.tar.gz, but I can not find them anywhere, who can give me a link to find them, thank you very much!!!
0
votes
1answer
3k views

Adding a user to MySQL with 'name'@'%' fails with ERROR 1396

I just tried to add a new user to MySQL using CREATE USER 'name'@'%' IDENTIFIED BY '...' However, it fails with the following error: ERROR 1396 (HY000): Operation CREATE USER failed for ...
2
votes
1answer
188 views

How can I disable utf8mb4 entirely on MySQL 5.5?

I know you're not supposed to do this, but I have a MySQL 5.5 master and a MySQL 5.1 replication slave. That's supposed to be okay, unless I do something stupid like use utf8mb4 that isn't supported ...
1
vote
1answer
358 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.
0
votes
1answer
1k views

MySQL: Can't find record in tablename

After crash: mysql> repair table lot_not_sold; +------------------------+--------+----------+---------------------------------------------------------------+ | Table | Op | ...
1
vote
3answers
3k views

Automatic MySQL Replication Fail-over Strategy

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the ...
2
votes
1answer
2k views

How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted?

MySQL 5.1.x | InnoDB | Windows My mysql data directory is starting to fill up with bin logs. I currently have the following settings configured in my windows mysql server: [mysqld] log-bin ...
2
votes
1answer
2k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
1
vote
1answer
704 views

How to properly secure MySQL database?

We have a web application based on the famous triad apache + php + mysql which we sell to our customers and gets installed on their servers. Currently, we are using MySQL 5.1.41 which has only a ...
1
vote
1answer
190 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
158 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 ...
3
votes
1answer
1k views

Take individual MySQL database offline

MySQL does not have any SQL commands or internal mechanisms for making an individual database unavailable / offline moving an individual database Thtis being the case, how can you take an ...
0
votes
2answers
635 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 ...
1
vote
1answer
380 views

My SQL server showing different result with same query execution at different time

When I execute below query from MySQL server, it shows me different output for each time even though database is stable and not used by anyone except me. SELECT TABLE_NAME , table_rows FROM ...
1
vote
1answer
3k views

Why does max_used_connections status not get refreshed automatically once it reaches max_connections, even though the Threads_connected falls down

I always wonder, why does the max_used_connections value not come down, once it reaches the peak value of max_connections? I always flush the status and bring down this value to avoid DB connection ...
3
votes
2answers
1k views

Can I do anything about a very long running KILL command?

I've got a table with 103M+ records in it and takes up 70GB on the disk. I decided to DELETE (in one statement) 70% of the records. As expected, this took an awfully long time to run. I decided to ...
1
vote
1answer
1k 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: ...
5
votes
1answer
266 views

Migrating MySQL to new server

We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B). We have a new MySQL 5.5 instance on ...
4
votes
1answer
283 views

If MAX(id) != COUNT(id), does that mean rows have been deleted?

The question pretty much says it all. We're talking MySQL here, version 5.1. What I'm wondering is whether this would strictly means rows have been deleted, or if there are other possible scenarios ...
1
vote
1answer
159 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
607 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 ...
3
votes
1answer
6k views

Change existing datadir path

I want to change the datadir path in the my.ini file. The existing datadir path is C:/ProgramData/MySQL/MySQL Server 5.1/Data/ The size of my C: drive is 30 GB and the MySQL data folder is occupying ...
4
votes
2answers
9k views

innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster. We have 125G main memory with 24G InnoDB buffer pool ...
1
vote
2answers
575 views

Does pt-table-checksum point out rows that are different between Master and Slave?

Trying to verify replication integrity of my replica. And I found that pt-table-checksum is one of the best ways of doing it. I have a simple Master->Slave set up. Have created a sample table with 10 ...
1
vote
2answers
322 views

SHOW SLAVE HOSTS shows incorrect output

I had used --report-host option in one of my slaves, so that I could see that under SHOW SLAVE HOSTS. Now that server is no longer in use. I have uninstalled that server as well. Unfortunately I still ...
4
votes
1answer
249 views

Clarification about master slave configuration in mysql

We are planning to migrate an existing MySQL database to a new server using the MySQL replication master slave procedure. Unfortunately this will take a lot of time so we were thinking of doing a ...
1
vote
0answers
36 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
10k 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 ...
1
vote
1answer
1k views

Solutions to 4-byte utf-8 characters in mysql 5.1

I'm running into a problem that seems to be an issue w/ mysql 5.1s limitations on UTF8 characters. From http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-utf8.html Currently, MySQL support for ...
2
votes
1answer
2k views

log_warnings doesn't seem to work for aborted connections in MySQL 5.1

The docs for 5.1 say that I can set log_warnings = 2 to capture Aborted_connects in the error log. This bug report suggests that this was only merged into 5.5 and 6.0 (and maybe 5.2) but not into ...
3
votes
2answers
1k views

Can we assign mysql Key_buffer_size value for particular table?

In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps: SET GLOBAL myisam_table.key_buffer_size = 4194304; CACHE INDEX test.myisam_table ...
1
vote
1answer
123 views

Help Optimizing Query

For the following table structure in MySQL 5.1.49: CREATE TABLE `leads` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `institution_id` int(10) unsigned NOT NULL, `lender_id` int(10) unsigned ...
1
vote
2answers
413 views

Lost connection to MySQL server during query

Here is a part of my query SELECT * INTO OUTFILE 'Target_File_Name' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '' FROM (SELECT 'Start time','Calling no','Called ...
2
votes
1answer
1k views

How to optimise read/write performance a MySQL table with many rows?

I am developing a django application where I use a MySQL database. In my application there is one table for domains information which has only 2 coloumns id domain_name but I have a problem when I ...
1
vote
1answer
256 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 ...
0
votes
1answer
375 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 ...
1
vote
2answers
338 views

why can I only install MySQL 5.1 on cent OS 6.2?

I had Ubuntu 10.10 on my server and MySQL 5.1 version. But since i wanted to update the system, I installed a fresh version of Cent OS 6 only so I could install the latest version of MySQL. But after ...
3
votes
1answer
10k views

MySQL case sensitive table names on Linux

Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't ...