MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

0
votes
0answers
57 views

Is using “read-committed” isolation level save on *slave*?

I saw a few articles which discuss how different transaction isolation levels affect replication. If I understand properly, all these related to transaction isolation settings on master, not on slave. ...
0
votes
0answers
63 views

Percona mysql server slow queries

Recently I bought new database server and installed percona mysql server 5.5.32-31 and transferred my /var/lib/mysql directory from old database server having Percona mysql server 5.5.28 to new ...
1
vote
3answers
705 views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
3
votes
3answers
206 views

Why set up static data in views vs. using tables in mysql?

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields. I have the following too: LDAPIMPORTANT - view that ...
1
vote
0answers
421 views

SELECT + INSERT + Query Cache = MySQL lock up

MySQL server seems to constantly lock up and stop responding on certain types of queries and eventually (after couple of minutes of not responding) give up with an error "MySQL server has gone away", ...
2
votes
2answers
598 views

changing TEMPORARY TABLE default collation

when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
2
votes
1answer
153 views

MySQL table architecture

Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; ...
1
vote
2answers
241 views

MySQL Synchronous Replication on Ubuntu 12.04 with DRBD

I have been looking around for some easy and open-source ways to handle database synchronous replication. DRDB on a Linux server seems to be one of the fastest and easiest methods to handle database ...
0
votes
2answers
78 views

Mysql Command required for getting values from my.cnf

I am new to MySQL DBA, i have a doubt, Is there any command for finding my.cnf configuration file variables and its respective value which is mention in my.cnf file in mysql prompt itself. like we ...
3
votes
2answers
902 views

Replication error

We have a slave server that has stopped replication due to the following error: Slave SQL: Query caused different errors on master and slave. What could be the cause of this error? And what would ...
3
votes
1answer
91 views

MySQL Replication not proceeding

I have a weird replication problem I have not seen before. It's basic mysql replication with single master and two slaves. One of the slaves is not executing replication events and seems just stuck ...
0
votes
0answers
106 views

Importing large .sql file stops before even midway and starts deleting

Okay so i'm trying to import my minecraft servers .sql file into database and after first 1GB of importing data it stops without error and starts deleting what already has been imported any ideas? ...
0
votes
0answers
28 views

Merge rows with common field value in MySQL [duplicate]

I have a table which was imported from MS-SQL, it has about 1.9 million rows. After importing I realized that a lot of the rows are duplicates and there are no primary key constraints. The rules to ...
2
votes
2answers
3k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
0
votes
2answers
1k views

What is the difference between NULL and \N while loading data from a csv using load data local infile

I have a table which has four fields create table test_hc1(cola int,colb int,colc varchar(20),cold int); Now, I am trying to load following data 1,2,\N,4 and the third field in table is coming ...
-3
votes
3answers
80 views

Listing databases that are not in use

I want to show databases that are not used. For that I tried this query, but the result set is empty: mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME NOT IN ...
1
vote
2answers
1k views

ESCAPED BY clause in LOAD DATA INFILE

Can somebody please help me understand what is ESCAPED BY clause means in LOAD DATA LOCAL INFILE statement MySQL 5.5. I am a newbie to MySQL so please bear with me.
1
vote
2answers
70 views

need help in Query optimization

I need help in query optimization,I am having table with structure Create Table: CREATE TABLE `ip_country_mapping` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `cron` bigint(20) NOT NULL, ...
0
votes
1answer
295 views

Strange Locking issues while playing back logs via percona playback

Problem while replaying logs I am in the process of benchmarking a new DB node (specs at the end) and have run across some strange behavior: As described here i: Created a dump (innobackupex ftw) ...
1
vote
2answers
60 views

Need some support on MySQL Query

I have a query SELECT leads.id, sfav.id is_favorite FROM leads INNER JOIN teams tst ON tst.team_set_id = leads.team_set_id INNER JOIN members members ON tst.team_id = ...
0
votes
1answer
226 views

Export just rows modified in last month (date defined by user)

I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?
0
votes
2answers
249 views

How can we check for data integrity when we upgrade from MySQL 4.1 to MySQL 5.5?

By data integrity, I mean: Schema integrity Data (record level) integrity Checksum was an option, but since the storage format for some data types in MySQL 5.5 has been changed, it won't be ...
1
vote
0answers
102 views

MySQL Replication - Used to work, now get error 2026

I've had replication running now for about 2 years, with 1 master and 2 slaves. Both are through an SSL connection. I'm using MySQL 5.5.15 on the master (CentOS), and MySQL 5.14.46 on the slaves (on a ...
1
vote
1answer
205 views

Large table delete in mysql

I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options: DELETE QUICK To set some ...
1
vote
2answers
565 views

Changing the mysql.proc table directly after upgrade

Our host upgraded our mysql install without us knowing from roughly 5.1 to 5.5. This caused a problem because the table definition of mysql.proc is different as discussed here: ...
1
vote
1answer
63 views

Get rows in a range of time

I have a table with two columns that stores TIME type values. This is the CREATE statement: CREATE TABLE `sdriving_turno` ( `idturno` bigint(20) NOT NULL AUTO_INCREMENT, `idempresa` int(10) ...
1
vote
1answer
1k views

MySQL equivalent of WITH in oracle

I need the mysql equivalent of the WITH clause in Oracle. Please help. Thank You
1
vote
0answers
35 views

MySQL - Randomly long DESCRIBE queries

I have several queries like this (running on MySQL 5.5): DESCRIBE `TABLE`; And it randomly takes 5-10 seconds on my production servers. We have an ORM that caches this data once per day (per web ...
4
votes
2answers
5k views

What max_allowed_packet is big enough, and why do I need to change it?

I have MySQL (5.5) in master-slave setup and created another slave server. I stopped the original slave, dumped the data, copied and reimported and it worked fine. I noted the master_log pos of the ...
-1
votes
1answer
46 views

How do I create a trigger that cascades new rows into another table?

I am having a table name link with two columns link1 and link2 and another table name hide with four columns hide1, link1,link2 and hide2. If i insert data into link table the data should insert into ...
1
vote
0answers
130 views

innobackupex: What causes Slave_open_temp_tables did not become zero after waiting 15 minutes?

As you might know, in order to assure a consistent replication state, the slave should be backup only when there are no open temporary tables: ...
2
votes
1answer
54 views

Make sure slave has finished reading from master

I am planning to have a master-slave system in which occasionally the master and slave will reverse roles. To do this, I will stop further updates to master, and redirect traffic to the slave. But, ...
0
votes
1answer
83 views

How we take dump with batch of records option?

we have 30 GB database.we want to take backup of this database. here the question is we want to take the backup batch of every 10k records. How we can take batch of records backup using mysqldump
1
vote
2answers
5k views

MySQL using too much CPU

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: CentOS 5.9 24gb Ram 8 ...
1
vote
1answer
107 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 ...
0
votes
0answers
21 views

mySql Event gets expired on closing of the command prompt

As I am quite new to server side, I tried to create a event using CREATE EVENT enetname ON SCHEDULE AT '2013-06-13 18:00:00'+INTERVAL 30 SECOND DO update tablname set status= 'Expired' where ...
0
votes
1answer
161 views

MySQL - What is the diffrence between ClearText authentication and PAM authentication plugins?

PAM Authentication: PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory. Cleartext Client-Side ...
1
vote
2answers
418 views

Multiple table rows in one row

I have a table from where want to get such classes where 3rd position candidate got more marks than difference of 1st and 2nd. class position name total_marks 10 1 a 100 10 2 ...
1
vote
1answer
4k views

BEFORE INSERT trigger in MySQL

I am new to MySQL and I'm having problems creating a BEFORE INSERT trigger. I get an unexpected END error. I have a table named verlof_aanvragen with a column datum (and 6 more columns). What I ...
0
votes
1answer
158 views

What's the practice to generate long user id like those on Facebook?

Now I am about to launch a site and I have trouble coming up with a way to create long user id(e.g. 4000002). Do I set id to auto-increment(Starts at 0) and then add something(e.g. 4000000) to this ...
0
votes
1answer
76 views

Select unique value whereas the time is highest in the most optimal way

Given a simple, with a text and time column, I want to select X unique values from the text column, whereas that row contains the highest value for time. INSERT INTO `test` (`id`, `text`, `time`) ...
1
vote
1answer
2k views

How to run a SELECT query within while loop in PHP?

Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as $result1 = $mysqli->query("SELECT * FROM table1"); while ( $row = $result->fetch_assoc() ) { if ( $row['X'] == ...
3
votes
1answer
359 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
1
vote
1answer
217 views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
0
votes
1answer
102 views

Am I wrong in table design or wrong in selected index when made the table?

I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
0
votes
1answer
415 views

HOW to “SUM” and “JOIN” for single query of a poll results?

I have a poll database as CREATE TABLE poll_answer answer_id, answer varchar(255), poll_id int(11), FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE, PRIMARY KEY(answer_id) ) ...
0
votes
2answers
276 views

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
0
votes
0answers
62 views

Replicate database without Internet connection

I'm working on a company where I have a central DB and several agency databases. See the diagram at here. All the databases are identical, just the data can change and in some cases. I've no internet ...
1
vote
2answers
434 views

MySQL+Web-App Performance Issues using different servers

We are having a performance issue with our MySQL servers that does not make any sense. I have read countless articles from many people (mostly Percona) and have made my.cnf tweaks. We have even manage ...
2
votes
1answer
108 views

MySQL Subquery hangs / terrible perfomance

I am using MySQL 5.5.16 @ CentOS. Please have a look at this query: SELECT inventory_ID, ( SELECT price_brutto FROM _inventory_prices ...