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

Database and query optimizacion

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
0
votes
1answer
11k views

How to get only month from MySQL date/time?

In my MySQL server, I have rows that have the date of my created content. Example: 2013-03-17 02:53:47 For now, I am using: $date = $fetch[thedaterownamegoeshere]; <?php echo $date; ?> But ...
0
votes
0answers
48 views

mysqlbinlog (mysql5.5) output errors out (char length)

wondering why an invalid insert (exceeds char length varchar(100)) breaks when piping mysqlbinlog output to RDS-- when pasted into a client and run separately-- it inserts, truncates, and warns ...
0
votes
2answers
12k views

1286 - Unknown storage engine 'InnoDB'

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
1
vote
2answers
167 views

Breaking Semisynchronous Replication in MySQL 5.5

I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7. My application is running and updating the database of the master server and same is being updated in the ...
4
votes
2answers
6k views

Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?

My Hardware Spec Technology: Ivy Bridge CPU: Intel Xeon E3 1245v2 Intel Smart Cache: 8MB Cores / Threads: 4 / 8 Frequency: 3.4GHz+ / 3.8GHz Turbo Boost RAM: 32 GB DDR3 Hard disk: 2x 2TB SATA3 Raid 1 ...
3
votes
4answers
857 views

Stress Test to Saturate Memory for MySQL InnoDB

I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of ...
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 | ...
0
votes
1answer
32 views

Why isn't my includeThreadNamesAsStatementComment JDBC parameter working?

The JDBC API lists a includeThreadNamesAsStatementComment parameter that seems to indicate it will prefix all queries with the Java thread ID. I thought this would be useful in tying back DB activity ...
3
votes
2answers
2k 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
2answers
331 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 ...
7
votes
5answers
19k views

How to properly kill MySQL?

I have CentOS 64bit with CPanel installed and I use service mysql stop However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
0
votes
0answers
56 views

How to properly kill MySQL? [duplicate]

I have CentOS 64bit with CPanel installed and I use service mysql stop However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
0
votes
2answers
2k views

MySQL Table not repairing

Table info: Database name: user_motiva Table name: wp_options.frm wp_options.MYD wp_options.MYI wp_options.TMD when I do a mysqlcheck -r --all-databases it gets hung on that table even if you ...
0
votes
1answer
4k views

Error 1044 Access denied to user

This is driving me crazy. When I try to create a few tables from my Workbench model I get this error 1044. I've been trying to find a solution but nothing works for me. Curiously when I run ...
0
votes
2answers
589 views

How to restore mysql dump file? [closed]

We have created a dump file (.sql) using the command mysqldump in mysql5.5. Now we want to restore the same. We are using the command :- -u[username] -p[pwd] [dbname] < [.sql file] But we are not ...
1
vote
1answer
174 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 ...
2
votes
1answer
509 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 ...
1
vote
1answer
449 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
15k 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
259 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
276 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
838 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
225 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
110 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
644 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
577 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); ...
2
votes
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
5k 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
916 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
129 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
223 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
815 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
154 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
727 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: ...