Tagged Questions

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

learn more… | top users | synonyms

1
vote
1answer
431 views

Is it okay to have different lower_case_table_names value on master and slave?

I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1. I haven't started replication on the slave, but everything looks ...
7
votes
2answers
14k views

What is the difference between optimize table and analyze table in mysql

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
4
votes
1answer
6k 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 ...
1
vote
1answer
254 views

Create a mysql slave database from master database

I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files ...
2
votes
1answer
76 views

System to track what Zip Codes my vendors service

I am designing a table configuration that will contain all of the zip codes in the United States. I have a .csv file of all the zip codes in the U.S., so thats at least a start. This will end up as a ...
1
vote
2answers
261 views

Should master and slave have same mysql version?

I have a master database running mysql 5.5.11, and I am planning to setup a new slave. I am planning to use the 5.5.30 as it is the latest version. For setting up new slave, I am copying files from ...
0
votes
3answers
813 views

Mysql optimization help

I've tried optimizing mysql as good as I could, but apparently I'm not to good at it :-) So I come to you guys for help. Server specs are: AMD Opteron CPU 8 cores 16 GB RAM 2x2.000 GB HDD 7.200 RPM ...
0
votes
1answer
1k 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 ...
1
vote
2answers
6k views

MySQL Installation error: Unable to update security settings

I tried to configure MySQL server 5.5 on Windows and this is what I get mysql-server-5.5-winx64:56 - Attempting to update security settings. mysql-server-5.5-winx64:100 - Unable to update security ...
2
votes
1answer
215 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
108 views

In Mysql 5.5, why is the upper bound of a character range not inclusive?

So I'm trying to query on a character range, i.e. all manufacturers with names starting with a or b: SELECT manufacturer FROM warehouse WHERE manufacturer >= 'a' AND manufacturer <= 'b' or: ...
1
vote
0answers
42 views

Is there a canonical list of MySQL defaults which should be changed

I'm setting up a new mysql server as we're building a new app at work. Most of the developers/operations guys are familiar with mysql. Many of mysql's settings (eg strict mode) are set badly by ...
1
vote
1answer
633 views

MySQL Tables crashing randomly

This is one of the many random Tables that get corrupted. Any ideas why and what would be causing this? How do I keep MySQL tables from crashing and MySQL from crashing? Repairing USR_wp537 ...
3
votes
1answer
551 views

Which is more efficient, trigger or PHP code?

My requirement is to move EMR data for a patient to a set of history tables when the patient has been discharged. I proposed to use an after trigger on the table housing the discharge flag, checking ...
1
vote
1answer
5k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
1
vote
1answer
2k views

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default ...
2
votes
1answer
5k views

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set ...
0
votes
1answer
4k views

Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?

I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation. Would it be useful to run -check with this option or does the -optimize already take care of that? I am only ...
1
vote
1answer
1k views

How to Log IP Address for Inbound Connections in MySQL

Is there a way in MySQL 5.5 (on Redhat 6.x) to log the IP address from where a particular query has been run? And have that appear in the general log or even relay and bin logs? I am trying to track ...
0
votes
1answer
65 views

What would be the first things you do to find out about an MYSQL environment you are taking over?

I am new to DBA role, and wanted to know if there was any commands (or free software) that come with Mysql 5.5 (master/slave/slave) replication to quickly get familiar with the database, and also ...
1
vote
3answers
69 views

Contains statement Query

I need to check the string with the column if the column contains that string or not. example:String 'how to make pizza' and the column if has 'pizza' i need to fetch that.. like SELECT * FROM ...
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 ...
3
votes
2answers
4k views

Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find.. [mysqld] max_allowed_packet = 1M [mysqldump] max_allowed_packet = 16M What is the point? Because when you ...
3
votes
1answer
880 views

Query not using index when it should

I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table? ...
1
vote
2answers
1k views

Mysql replication - Error No. 1146&1062

short version In master master replication, How to have one master write data with odd IDs and the other one with even IDs? Is it the way to avoid conflict? detailed version I wanted to setup a ...
0
votes
1answer
126 views

Storing user search keywords

I need to store search keywords in a database to analyse user needs. How efficient should my schema be? I'm thinking like this: CREATE TABLE SearchKeyword ( ID INTEGER PRIMARY KEY ...
0
votes
2answers
219 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 ...
4
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
809 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
153 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
679 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
1k 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
51 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
129 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 ...
3
votes
1answer
1k 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
624 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
139 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
196 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
3k 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 ...
4
votes
1answer
3k 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
472 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
8k 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
236 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
357 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
529 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
149 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
38 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.* ...