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

learn more… | top users | synonyms

1
vote
2answers
2k 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 ...
0
votes
1answer
329 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
177 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`) ...
0
votes
1answer
126 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 ...
3
votes
1answer
907 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 ...
0
votes
1answer
1k 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) ) ...
5
votes
1answer
291 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 ...
2
votes
1answer
913 views

Database Design - Creating Multiple databases to avoid the headache of limit on table size

I need to design database to store information contained in millions of log files generated by devices on trial on network. My first approach was storing all the information in tables contained in a ...
0
votes
0answers
99 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 ...
2
votes
1answer
280 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 ...
7
votes
2answers
36k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
2
votes
1answer
2k views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...
1
vote
0answers
1k views

“Lost connection to MySQL server during query” error

I have MySQL 5.5.20 and this table: mysql> desc country; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
74 views

ORDER BY optimization issue

I have a query: SELECT user_details.id , user_details.first_name , user_details.last_name, user_accounts.name account_name, jtl0.account_id account_id, user_details.title , ...
0
votes
1answer
251 views

Do certain string patterns affect query performance on a VARCHAR column?

I am a beginner on MySQL Administration and performance problems. Now this is confusing me. I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, ...
1
vote
1answer
3k views

ERROR 13 (HY000) When Creating Database

I'm trying to create a database and when I type in CREATE DATABASE epik; I get the error ERROR 13 (HY000): Can't get stat of './epik' (Errcode: 13) I am running kubuntu 13.04 with mysql 5.5.31.
0
votes
0answers
31 views

Should store procedures be cached?

Should store procedures be cached in MySQL? If so, How long it is stay in cache? In my case, When I call one stored procedure first time, it is giving me results in 1 second, after that it gives me ...
3
votes
1answer
1k views

skip columns while using LOAD command mysql

I have a csv file having 5 columns and i want to load only one out of that, is there a way i can do it. I want to use LOAD statement of MySQL 5.5 Any pointers are welcomed, help would be much ...
2
votes
1answer
549 views

datetime vs timestamp in mysql 5.5

What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this? I have a situation where I am trying ...
2
votes
1answer
3k views

Dealing with empty strings while loading a table from a CSV

I am trying to load a CSV file into a table. The CSV file contains some empty strings for example: 1,2,,4 I want to load the empty field as null. One way I know is to use \N i.e: 1,2,\N,4 but it ...
1
vote
1answer
409 views

MySQL-5.5 InnoDB memory issue

Version in use is mysql-5.5.24. In the Enterprise version of MySQL innodb_buffer_pool space is released after a while, however this does not appear to be happening in the community version. I am ...
2
votes
1answer
1k views

Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows?

Having a corrupted Mysql 5.5.31 (Windows) database, my question relates to the the top solution provided in How do you identify InnoDB table corruption? , more precisely to the following script that ...
0
votes
2answers
554 views

Using MySQL InnoDB as an Archive

My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
1
vote
1answer
446 views

MySQL localhost vs Amazon RDS instance

I am seeing unexpected behavior with some MySQL performance. When I run a simple query SELECT 1; on my local host (MySQL 5.6.x) using workbench, it executes in 0.000s, but the same query run on ...
1
vote
1answer
343 views

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' not working

I'm trying to install semi synchronous replication in my mysql 5.5 and trying to execute the below command: INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' but this command is ...
1
vote
2answers
466 views

How to avoid lagging when enabling log-slave-updates?

Master: 48GB RAM 16-core my.cnf: https://clbin.com/hlkUo Slave: 64GB RAM 24-core my.cnf: https://clbin.com/viLm0 Both are running 5.5.28. For the incremental backup purpose, I need to enable ...
1
vote
1answer
64 views

Identical subquery optimisation in an update

update activitybooking set `submitted`='1' where id='958' and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and (id='958' or ...
3
votes
1answer
1k views

MySQL specific database configuration file

In MySQL's configuration file I've globally disabled autocommit as so. [mysqld] autocommit=0 I need to turn MySQL's autocommit on for a specific Ruby on Rails database though. It could be for the ...
0
votes
1answer
57 views

Optimizing Query

Can anyone help me tuning this query..... explain extended SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a LEFT OUTER JOIN (SELECT msisdn FROM as_treat_pre_usage_30days GROUP BY ...
2
votes
1answer
898 views

Creating a partial unique constraint for MySQL

I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL. My ...
0
votes
0answers
82 views

MySQL replication is not happening when piping sql into mysql client

Today I noticed a very strange issue where if I cat a SQL file and then pipe it to MySQL client, replication doesn't happen. These MySQL statements aren't also written to the binlog. But replication ...
1
vote
1answer
1k views

MySQL replication — issue with mysql.proc during replication from 5.0 to 5.5 host

We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication. "'Column count of mysql.proc is ...
0
votes
1answer
45 views

Misunderstanding statement in MySQL documentation

I can't understand this statement in Optimizing Data Size: Declare columns with identical information in different tables with identical data types, to speed up joins based on the corresponding ...
1
vote
1answer
3k views

MySQL General log not working on Mac OS X [duplicate]

I want to log all queries to the database running on my MacBook Pro (Mac OS X 10.8.1). I created a file /etc/my.cnf with the following content: [mysqld] general_log=1 ...
0
votes
2answers
2k views

Steps to upgrade MySQL from version 5.0 to version 5.5

I have been developing under MySQL v5.0 for a while now and need to upgrade to v5.5 to be in step with the hosted production server. After having searched for precise steps to do this migration, I am ...
0
votes
1answer
600 views

Redo Logs Mysql Cluster

The mysql cluster has 4 directories namely D8 D9 D10 D11 for redo logfiles. But while inserting into table only one redo directory is used. I ran following command to get redo information "all dump ...
0
votes
2answers
87 views

Error log - What does this mean and what can I do about it?

I have the following repeated errors in my error.log and I don't know what it means or what I should/can do about it: Access denied for user 'root'@'[some IP]' (using password: YES) There are ...
3
votes
1answer
283 views

MySQL Stress testing issues?

I had gone through MySQL High Performance book Chapter 2 for MySQL Bench marking and Profiling,I have some questions about benchmarking How can we test an existing database and queries against it ...
2
votes
1answer
810 views

when will Innodb support activation of triggers on cascade foreign key? [closed]

The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html Currently, cascaded foreign key actions do not activate triggers. Any Idea when will this be implemented. Is ...
1
vote
0answers
214 views

Error in alter table reorganize partition for MySQL Cluster

My cluster contains 48 million rows and cluster contains 4 storage nodes . After that I add online storage node and executed alter table 'customer_tbl' reorganize partition. Some parameters of ...
1
vote
1answer
190 views

Changing TEXT to VARCHAR

In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text. Currently the longest entry has length 360 (found using SELECT MAX(LENGTH(value)) ...
1
vote
1answer
2k views

Should I disable triggers, timestamps in a slave in mysql?

We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in ...
0
votes
1answer
50 views

Reinsert data in new table with new IDs

I have the following two tables: buynsell CREATE TABLE `buynsell` ( `id` VARCHAR(20) NULL DEFAULT NULL, `msg` VARCHAR(255) NULL DEFAULT NULL, `date` VARCHAR(25) NULL DEFAULT NULL, ...
1
vote
1answer
725 views

Limit Access To mysql information_schema

In case of mysql attacks, many times user extracts information from information_schema. can i do something in mysql that can restricts user to xtracts information from information_schema while all ...
1
vote
1answer
193 views

Data usage increases initially when reorganize partition mysql cluster

In my MySQL cluster, the data usage of 4 data nodes is approximately 77%. I added a new data node online, and attempted to reorganize partition to redistribute data among all data nodes. But before ...
-4
votes
1answer
55 views

How to create Mysql Replication on mysql 5.5 master-slave [duplicate]

Guyz please tell me how to replicate master-slave in mysql 5.5
0
votes
1answer
522 views

MySQL inserts/deletes slowing down after a while after Start/Reboot

The Setup here is: OS: Ubuntu 12.04 (1 Core and 1.75 GB of RAMO) MySQL Version: 5.5 There are about 8 to 9 tables in the database. On initially starting mysql the queries are fast at around 0.05 - ...
0
votes
1answer
588 views

MySQL not allowing text columns?

I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE ...
0
votes
1answer
448 views

MySQL Slave Lagging Almost Daily -Ver 5.5.17 ( Master & Slave )

I am observing this lag in a continuous manner. Investigating in the bin log events, i found the following information. Slave executing the relay Logs slowly. 1. Changing the Innodb File Format ...
3
votes
1answer
791 views

How this simple Stored Procedure ended up in Deadlock

Thanks for all and this forum is really helpful. After few minutes of system running we get “Try restarting the transaction.” And its end up in deadlock. We use MySQL 5.5.28 and the Java code ...