MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
0
votes
1answer
729 views
mysql closing tables taking double the time of process
I have a delete query that is taking a long time. When I check in processlist that status is showing as closing table.
It is taking a long time to close tables.
ex: I run a query and the total time ...
0
votes
1answer
392 views
MySQL INSERTs waiting for UPDATEs (MyISAM)?
In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables.
We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. ...
1
vote
1answer
233 views
Does replication with SSL encryption work on MySQL 5.5?
I am trying to set up replication with slave on Amazon EC2 and master in our local environment. The setup went fine without SSL encryption. But when I set up the replication to be SSL encrypted, it is ...
1
vote
1answer
1k views
Some MySQL slave errors?
Master version: mysql-server-5.5.14-1.el5.remi
Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340
Binlog format: ROW based
I'm using PRM to setup HA for MySQL.
Sometime, MySQL slave ...
2
votes
2answers
422 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 ...
2
votes
2answers
87 views
How to see what was executed in the database via binlogs for specific date?
I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had ...
2
votes
1answer
74 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
56 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:
...
0
votes
3answers
623 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 .
1
vote
0answers
33 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 ...
2
votes
1answer
1k views
Ubuntu Multiple MySQL instances and Apparmor
I am trying to set up multiple instances of MySQL on an Ubuntu dev machine and running into AppArmor problems.
I get this error in the syslogs:
kernel: [227631.776360] type=1400 ...
1
vote
1answer
214 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
...
0
votes
1answer
692 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);
...
4
votes
2answers
8k views
Grant table privileges to a user connecting from any host
I have a user account - let's call it 'wordpress' - that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host ...
2
votes
1answer
96 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 ...
9
votes
3answers
10k views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
0
votes
0answers
180 views
What to do to reduce first byte time after removing slow queries?
I've set long_query_time to 1 sec and found only 2 query which took 1 sec to execute at 2 different points of time since yesterday. So I don't suppose such 2 queries cause my first byte time to be 3 ...
3
votes
1answer
385 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 ...
0
votes
2answers
87 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 ...
1
vote
1answer
156 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 ...
1
vote
1answer
168 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 ...
2
votes
1answer
523 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
689 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 ...
2
votes
1answer
178 views
Innodb mutex locking ?!? Inserts start stacking after 18-24hours, restart required
We have a fairly high end box running mysqld 5.5.20.
The cpu configuration is dual quad core xeons.
It has 32 gb ram.
It has a quad intel ssd configuration running raid 0+1.
On a fairly busy ...
0
votes
1answer
60 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 ...
3
votes
2answers
467 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 ...
1
vote
0answers
143 views
mysql server is not sending result set back after query execution to client and staying in sending data state
When I pass a query from my Java application to mysql server, it stays in Sending Data state and not returning result set back to application.
When I execute the same query in SQLYog directly, it ...
1
vote
2answers
45 views
Contains statement Query
I need to check the string with the cloumn if the cloumn contains that string or not.
example:String 'how to make pizza' and the cloumn if has 'pizza' i need to fetch that..
like
SELECT * FROM ...
3
votes
1answer
123 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?
...
0
votes
3answers
424 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 ...
1
vote
2answers
541 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 ...
1
vote
2answers
164 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
43 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 ...
2
votes
1answer
190 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
247 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
56 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 ...
1
vote
1answer
322 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
215 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
0answers
197 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 ...
3
votes
3answers
269 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 ...
1
vote
1answer
156 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 ...
1
vote
1answer
47 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
72 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 ...
1
vote
1answer
202 views
MySQL Replication Error, can't stop slave
When I run command SHOW SLAVE STATUS\G, I see
Slave_IO_Running : 'Yes'
Slave_SQL_Running : 'No'
What I've usually done to fix this is to run
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
...
3
votes
1answer
83 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 ...
1
vote
1answer
173 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
151 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
67 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
3answers
3k views
How to change a MySQL previous slave to be a master and remove slave status information?
I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine.
What I can't seem to do is to remove ...
2
votes
1answer
112 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 ...
