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
2answers
197 views

Best Way to Partition By City / State in MySQL 5.5

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so: ALTER TABLE ...
3
votes
1answer
1k views

Unknown option '--no-beep'

I installed a new version of MySQL server (previously 5.0, now 5.5) and I copied the data folder in to the new one. Now I should perform mysql_upgrade. I opened the command prompt (server is located ...
2
votes
2answers
716 views

How to GROUP_CONCAT between given rows?

I have a table as id diary 1 breakfast 2 walk 3 start 4 office works 5 office projects 6 end 7 taxi 8 start 9 preparing for meeting 10 doing the meeting 11 end 12 night I want ...
1
vote
1answer
136 views

Server load discrepency in Slave with Master load being low and constant

We have MySQL Replication Master/Slave Setup db01 Master db02 Slave (ReadOnly) We are getting load discrepency on the Slave (db02), but the Master's (db01) server load is low. MySQL is the only ...
2
votes
1answer
1k views

Issue with connecting to the mysql database

We have an outstanding issue with VCC in chicago, there is an issue connecting to the mysql database If an agent does a refresh of the monitor they will be logged out. This is the error we ...
3
votes
2answers
534 views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
1
vote
2answers
840 views

Trouble with triggers and insert .. on duplicate key update

I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
1
vote
1answer
49 views

How are these two user drops different?

Other than the obvious, what is the difference between: drop user user_01@host01; and drop user 'user_01'@'host01'; In a sample test, they both achieved the same result. From Account Names docs ...
1
vote
1answer
115 views

restricting users to rows they created

To educate myself in PHP and mysql, I am making a browser based rpg game. This game will allow any number of users to register an account and, once registered, they can add, edit or delete champions ...
3
votes
3answers
2k views

InnoDB insertion faster

I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
2
votes
1answer
993 views

MySQL Slave Relay Logging but not logging Binary Log

I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
3
votes
2answers
529 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
3
votes
1answer
129 views

Backup and off-site restore setup

I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one. Our current situation is ~100 GB of InnoDB and MySAM data in ...
4
votes
2answers
2k views

Large files in wamp\bin\mysql\mysql5.5.24\data directory

I have discovered these files located in the wamp\bin\mysql\mysql5.5.24\data folder on my Windows 2008 server. They are consuming about 22 GB on a 40 GB drive. I am using a LMS called moodle, that ...
3
votes
1answer
131 views

how to add removed partition?

This is the structure of my table: -- Table: t_keyword_conversion_ga -- Create Table: CREATE TABLE `t_keyword_conversion_ga` ( `id` int(11) NOT NULL AUTO_INCREMENT, `own_domain_id` int(11) DEFAULT ...
4
votes
2answers
2k views

Is it bad to create many mysql temporary tables simultaneously?

I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
3
votes
1answer
2k views

Enabling “log_queries_not_using_indexes” disables “long_query_time”?

I noticed that, in Mysql, when enabling log_queries_not_using_indexes, slow_query_log and long_query_time, the generated slow query log file will contain a lot of queries even if the query time is far ...
2
votes
1answer
354 views

Does mysqld wait for init-script code to complete before opening the instance?

I'm trying to automate the execution of an application code at MySQL startup. The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete ...
2
votes
3answers
6k views

How to Restore data from binary log

I am trying to restore some lost data , but I am not having its backup .So I am trying to restore from the binary log .Please explain how to restore from the binary log in mysql .
3
votes
1answer
209 views

What's the most unsafe MySQL configuration?

I have MySQL 5.5.27-29.0-log instance running specifically for Jenkins. Since tests data is always recreated during setup, ACID is unnecessary and I'm hoping to sacrifice it to gain more performance. ...
0
votes
1answer
301 views

Unable to take backup of mysql database using Xtrabackup

I am trying to take mysql backup without any downtime but unable to take the backup using Xtrabackup . Actual Database is almost about 40+ GB in size. I am trying to first satisfy myself. Hence, I ...
1
vote
0answers
451 views

MySQL cluster stuck in start phase 4, how to proceed?

Over the weekend our cluster we set up for evaluation had one of its data nodes lose network connectivity briefly. This was while another data node was in the middle of a restart (which had been going ...
0
votes
0answers
135 views

Please help configure my Mysql for 12GB 4cores server

I use a dedicated server with 12GB memory and 4 core CPU with Ubuntu 12.04 LTS (x64). This not just a database server. MySQL appears to be restarting frequently, I think I can tune the config to ...
0
votes
1answer
37 views

Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

I am trying to create a new table new_table from t1 by comparing primary key values of another table t2 ie, 1) created table new_table 2) gave the query insert into new_table select a.* ...
2
votes
1answer
257 views

Restriction on self referencing on insert in MYSQL?

How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself). mysql> SELECT * FROM Employee; ...
3
votes
1answer
327 views

How to Update same table on deletion in MYSQL?

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee. Following is query I used to create table: CREATE TABLE IF NOT EXISTS `Employee` ...
1
vote
0answers
97 views

How to create multi volume file backup using symantec backup.exec

I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
1
vote
1answer
257 views

mysql session logging tables

I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites. Our table ...
2
votes
3answers
738 views

how to install mysql on mac?

My Mac runs Mac OS X, version 10.7.5., so it's a 64 bit system. First, I downloaded mysql-5.5.28-osx10.6-x86_64.dmg. Then I double clicked mysql-5.5.28-osx10.6-x86_64.dmg, it's mounted, and shows ...
1
vote
1answer
475 views

Query to take partial backup in MYSQL

I tried to take full backup in mysql using enterprise backup by using the command mysqlbackup -u root -p --backup-dir=g:\full_bck backup But I could not take backup of a single database , I ...
1
vote
1answer
279 views

How to removing or reduce the size of redo logs in MySQL cluster

Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we ...
1
vote
1answer
1k views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
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: ...
2
votes
1answer
183 views

innobackupex is failing while exporting backup

I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex. I have following ...
1
vote
1answer
213 views

How can I change the timezone in MySQL?

In a MySQL DB, I have two varchar fields. One is for date and one is for time. I need to change the time from CST to GMT. In MySQL how do we convert the combination of these date and time fields from ...
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 ...
1
vote
1answer
153 views

MySql - selecting into file and re-loading it with special characters

I have a MySql table called twitter_statuses which as the title suggests holds twitter statuses. here is the table structure. this table contains all sort of characters in the text column, and some ...
1
vote
1answer
140 views

MySQL Cluster Steps To Recover Data If Catstrophic Failure Occurs

I want to know if a catstrophic failure occurs (like a power outage), and all nodes in MySQL Cluster are shutdown in cluster then data will be lost even though a backup was taken? Or it possible that ...
1
vote
1answer
672 views

`show procedure status` doesn't list all of the procedures?

mysql-server-5.5.28-1.el5.remi I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
0
votes
1answer
121 views

Handling error of foreign key

How to set a FK value NULL upon receiving error for foreign key constraint? Consider a simple table a CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT ex_key int(11), FOREIGN ...
3
votes
3answers
1k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
0
votes
1answer
876 views

How can I enable my-huge file on windows server?

I installed Windows Server 2008 and MySQL 5.5. Now I need configure MySQL for huge InnoDB tables and a lot of connections. I see that on my Windows Server, there is a my-huge file(configs files that ...
1
vote
1answer
299 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 ...
4
votes
1answer
3k views

MySql - Changing expire_logs_days without restarting the server

I'm using MySql 5.5. Is it possible to change expire_logs_days and have the changes take effect without restarting the server?
1
vote
1answer
284 views

Mysql 5.0 on Windows 2008

I have a problem with MySQL on Windows Server 2008 x64. This is the first time I've installed MySQL 5.5 with performance problems. I decided to go back to the older version that worked well with ...
2
votes
4answers
283 views

Does my table violate normalization rules?

I am developing an emulator for a game. My item table looks like this: -- ---------------------------- -- Table structure for `items` -- ---------------------------- DROP TABLE IF EXISTS `items`; ...
2
votes
2answers
465 views

Does MySQL 5.5 support shared database with separate schema?

I am developing a SaaS based product using MySQL 5.5 as the backend database. I want to keep shared database for all the tenants, but separate a database schema of each tenant. There would be a single ...
2
votes
1answer
260 views

Null Values and Performance

I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.) But still there are times when saving ...
2
votes
1answer
303 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
5
votes
3answers
355 views

MySQL varchar(255) limitation & Anomaly

I have defined a column in my MySQL database as : ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL However, when I enter text ...