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
4 views

Will redirecting queries to dev/null warm up indexes?

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice? e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt Thanks
0
votes
1answer
1k views

Error “#1118 - row size too large” on the first row of the table only

I ran into a problem using a mySQL database. I have some columns as type text but when I try to enter data into the first row I get the error code "#1118 - Row size too large. The maximum row size for ...
1
vote
2answers
19 views

Mysql Master Offline

In a unidirectional Master-Slave setup, should the slave be manually stopped in cases where the Master is offline due to Maintenance, Crash etc. ? Or should one just leave it so that it keeps trying ...
0
votes
2answers
25 views

MYSQL: Join with many to many

I'm trying to create a view and trying to see what can be done, I have a main table a table I wanna join to and also a junction table. Event table (it's very big so it's just dummy data): ...
0
votes
2answers
33 views

Mysql 5.5.25 must be restarted to connect

We have MySQL v5.5.25 running on Windows Server 2008 R2. Occasionally people are unable to connect, and the only way we have found to fix this is to restart the mysql service. This happens every 2-3 ...
0
votes
1answer
23 views

What is the difference between uniqe index/unique/unique key/unique constraint keywords?

I am using mysql 5.5.16 What is the difference between the following DDL statements? 1) create table sql_query ( id int not null , query_text varchar(21826) not null , query_md5 ...
0
votes
2answers
33 views

Fastest way to create local copy of live InnoDB MySQL database?

We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+). Every month or so we need to copy this database to our development machines to work on locally. ...
0
votes
1answer
250 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) ...
0
votes
2answers
29 views

MySQL Slave not updated

This might be a duplicate of questions on the same problem asked before. But all the suggestions to the previous questions do not seem to work in my case. I have set up Replication using SSH Tunnel. ...
2
votes
1answer
26 views

Space not reclaimed after dropping MyISAM table

I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I ...
0
votes
1answer
9 views

How to create two same name databases in two different MySQL users?

I am using MySQL 5.5 running on Ubuntu 12.04. I have created two different users as 'root' and 'abc' and I want to create 'Items' database in this both user accounts but I want this two databases ...
1
vote
1answer
197 views

MySQL users corrupt

I have a strange situation here: From time to time I cannot log in with any of my mysql users. I even cannot make a mysql dump. So I started searching in mysql files and I found that users.MYD and ...
3
votes
1answer
54 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
1
vote
2answers
70 views

What happens when we take a timediff between timestamp and datetime

Say I have following table in mysql (5.5+): CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ...
2
votes
3answers
42 views

How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

I have two tables 1. Customer_details and 2. Order_details. 1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name, 2.Order_details table --> fields-- Ord_Id(Primary ...
0
votes
0answers
11 views

#2002 Cannot log in to the MySQL server / Connection for controluser as defined in your configuration failed

I use PHP/MySQL in Ubuntu 13. To reduce the size of ibdata1, i followed this post What is the best way to reduce the size of ibdata in mysql? and make necessary changes as told in the answer. I did ...
0
votes
0answers
12 views

How to acheive Concurrency in mysql when there is 500+ request simultaneously to the server

I am using Mysql (InnoDb), in this I am facing problem when we checked the curl request upto 500 connection at a time (simultaneously) but when we try more than that then my servers goes to idle ...
0
votes
0answers
7 views

MySQL record export and Import [on hold]

I want to export the record(s) of individuals from a table in secured format and later import the same. Please let me know how to do it in java. I am using MySQL. Best Regards,
0
votes
1answer
214 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
121 views

Repeated values in group_concat

I have two tables, first the table food and Second is Activity: INSERT INTO food (`id`, `foodName`) VALUES (1, 'food1'), (2, 'food2'), (3, 'food3'), (4, 'food4'), (5, ...
0
votes
2answers
27 views

How can I execute multiple statements at once in MySQL Workbench?

I'm trying to execute multiple LOAD DATA INFILE statements at once using MySQL Workbench. However, they execute one at a time. Anyway of forcing them to run in parallel? Thanks for the help!
0
votes
1answer
1k views

In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

On Windows, I upgraded from 5.1 to 5.5 no problem. Copied my 5.1 data folder into my 5.5 instance Started mysqld skipping grants Ran mysql_upgrade All good, but going from 5.5 to 5.6: Copied ...
0
votes
2answers
39 views

How to verify data dumps between two MS SQL and MySQL

I'm trying to verify data exported from MSSQL and imported into MySQL is 100% accurate using MD5 hash. On Linux, I'm using md5sum and WinMD5 on Windows. The encoding is the same: UTF8. I tried both ...
0
votes
4answers
806 views

MySQL gives me:“Can't open and lock privilege tables: Table 'host' is read only”

I am facing problem restoring a MySQL database. My primary database was MySQL 5.1 and now I am trying to copy it to MySQL 5.5. The database was backed up by using Xtrabackup. I am using Ubuntu ...
0
votes
0answers
34 views

MySQL: How to calculate potential wait time?

I'm trying understand the relationship between WAIT_MS and COUNT_STAR. Is this saying the average wait time is 3.2s and occurred 11,543 times for a total of approximately 10.5hrs of aggregate wait ...
1
vote
2answers
31 views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
3
votes
2answers
266 views

Finding differences between a master and slave when pt-table-sync fails

I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC). I've found some verifying differences beyond that. Certain tables in the mix have composite ...
13
votes
3answers
1k views

effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

I am looking for advice on table/index design for the following situation: I have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
2
votes
1answer
168 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 ...
0
votes
1answer
23 views

How to overwrite csv file of MySQL result set?

My SQL query is given below, select company.Company_name as COMPANY_NAME, IFNULL(followup.contact_date, ' ') AS FOLLOW_UP_DATE, IFNULL((select Full_name from user where user_id = followup.user_id), ...
0
votes
1answer
41 views

update variable from multiple tables in one row

I have two databases, and I need to UPDATE variable 'birthday' from one row to another checking 'name' (from db1 to db2). The problem is, that 'name' variable in second db is located in another table ...
0
votes
1answer
40 views

Uninstalling MySQL on Solaris

I have installed MySQL 5.5.31 in Opensolaris. When I tried starting the MySQL server, it says bash: mysql: command not found So, I decided to uninstall the whole lot and try doing again. But I could ...
0
votes
1answer
30 views

Migrating database with views trough phpMyAdmin causes error in second host

I'm trying to migrate a database which contains some views and I'm doing trough phpMyAdmin export feature. MySQL version on servers are: Server to migrate: Server version: 5.5.34-0ubuntu0.12.04.1 ...
0
votes
0answers
23 views

How to update Status using IF Condition in Mysql trigger

I'm trying to write an update trigger that will Design_Update_Status to Customized when a date is entered in Custom_Design_Update_Date field and if nothing is entered that field it should update ...
0
votes
2answers
24 views

Mysql replication from existing slave to new slave

I'm hoping you experts can help advise me, I've got this topology: Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB Backup server is a slave of Live DB, and Test DB is a slave of ...
-1
votes
0answers
27 views

Every 30 minutes mysql stuck for 20-30 seconds [duplicate]

I have very strong mysql 5.5 computer (Windows server / AWS) . My CPU ~8% ,Disk IO - 0.5% , ~Connection - 800 binlog_format=MIXED, innodb_read_io_threads=64, innodb_write_io_threads=64, ...
2
votes
1answer
47 views

how i can upgrade mysql (5.5) production DB to 5.6 with minimizing downtime

our existing Database size around 200Gb,if we can use consistent,parallel backups methods. for testing i migrated development Db from 5.5 to 5.6 by taking mysqldump with option ...
1
vote
1answer
35 views

ALTER TABLE - Rename a column

This got me nuts! I want to rename a column from read-more to read_more in blog table Tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE COLUMN ...
0
votes
1answer
140 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
53 views

How should I config the my.ini(or my.cnf) file for mysql?

Thanks for taking your time to look at my question, and I hope that you can help me with this. I have a Windows Server 2012 server, it has: Memory : 3840MB Ram Processor: Intel(R) Xeon(R) CPU ...
2
votes
2answers
769 views

MySQL information_schema doesn't update

I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true. After I run the query delete from abc.test, I want ...
0
votes
1answer
104 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
-3
votes
1answer
57 views

Need help on query optimization in MySQL

Below is the query, I need to optimize it. It is using subquery, I need to re-write it using join how to do this SELECT Client_ID FROM SQL_AuditPro.clients c WHERE (SELECT ...
3
votes
1answer
3k views

SUPER privilege not defined for master user in Amazon MySQL RDS

I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on ...
0
votes
1answer
16 views

MySQL is not logging queries into log file

I am using MySQL 5.5 with Amazon Linux 2013. Following is my configuration for log file. slow_query_log = 1 long_query_time=0 log-error = /var/log/mysqld.log slow_query_log_file = ...
0
votes
1answer
126 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
0
votes
0answers
14 views

Dynamic chart data from MySQL

I'm building a chart with JS, where i'm doing a query with PHP and pass it to chart in JSON format. The problem is the whole table exported in json file gets up to 10mb, and it takes too much time ...
2
votes
2answers
50 views

Query only for the first n results of like

What I'm trying to accomplish is a huge speedup (read: incremental results) when searching a database with a like operator which is kind of a last resort, if everything else already failed, to come up ...
3
votes
2answers
132 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 ...
0
votes
1answer
66 views

wordpress table is crashed and should be repaired

I run a wordprees website and use a managed VPS server with cPanel/WHM. Got bellow error log and check the table. I repaired the table with repair table command from phpmyadmin. But this problem occur ...