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
3answers
1k views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
1
vote
1answer
53 views

Tuning MySQL query from explain

I am working on a search query in MySQL. I am getting this from explain: Original screenshot These are my indexes: Table definition: Original screenshot Query: SELECT `t`.`id` AS ...
0
votes
1answer
13 views

mysqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca

I am trying to recover binlog from a remote server using mysqlbinlog client program, however it appears that mysqlbinlog doesn't recognize the SSL options that's required to establish SSL connect to ...
0
votes
1answer
157 views

How to restore data after MySQL Upgrade?

I upgraded from MySQL 5.5 to 5.6 by uninstalling 5.5 and then installing MySQL 5.6. Now that MySQL 5.6 is running, none of my DBs or data is present/visible in MySQL Workbench. I also see that I ...
3
votes
1answer
865 views

Convert Oracle Query for Date to Mysql queries

Need to convert the following oracle queries to MYSQL queries. 1) select to_char(to_date(?,'DD-MM-YY')) from dual; 2) select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY')) from dual; 3) ...
0
votes
1answer
463 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
20 views

Config file location in MySQL

I have MySQL 5.5 running in RHEL. I have checked for my.cnf file in my filesystem and its available in two destinations. One in the user's home directory and the other is in /data/etc_ORIG directory. ...
0
votes
0answers
20 views

Issues in enabling binary logging

I am using MySQL 5.5 RHEL 6 I have made these changes in my.cnf file. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent ...
0
votes
1answer
47 views

Use of log_bin_trust_function_creators in MySQL

What is the use of log_bin_trust_function_creators? Also, Is the value of the parameter dynamic? Will it change if I restart my MySQL service?
2
votes
2answers
164 views

MySQL login after secure installation

I have installed mysql5.5 via secure installation procedure by using: sudo mysql_secure_installation Now when I am trying to login in MySQL from the terminal by using: sudo -u root -p The ...
1
vote
2answers
413 views

Mysql improve Self join performance large table?

I have a task to help identify conflicting IP ranges , I want to highlight when one companies IP range falls within another's ... below is the sample table, I have converted the IP ranges from .dot ...
0
votes
1answer
13 views

MySQL - using result of Procedure in Trigger to Insert in new table

I'm guessing I'm doing something that's prohibited by MySQL, but can't see what. Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to ...
2
votes
1answer
451 views

Unable to run update_mysql in MySQL 5.5

I have recently taken over managing a MySQL database running on Windows Server 2008 R2 . It is running 5.5.37 Community Edition. When it is started, it includes the following lines: Thread, Type, ...
1
vote
2answers
59 views

mysqldump error on common_schema

I tried to take the backup of common_schema in Server version: 5.5.44 MySQL Community Server (GPL). mysqldump -u root -p common_schema > common_schema_bkup.sql I got the following error: ...
0
votes
0answers
44 views

MariaDB error on alter table - table removed - constraint name is blocked

Today, we experienced a strange behavior in 5.5.41-MariaDB (InnoDB): An ALTER TABLE (add constraint) statement failed with error (mysql server has gone away after that). Then the database table ...
0
votes
1answer
491 views

Change MAMP to use a lower Mysql 5.1 version

I need to have the same development environment for my PHP program. My clients test server is running on Mysql 5.1 while my local is using MAMP running Mysql 5.5. How do downgrade my mysql database ...
0
votes
1answer
16 views

MySQL - Enabling Scheduled Event on Master and Slave Simultaneously

This may seem a bit strange, but I am trying to get a Scheduled Event to execute on both Master and Slave. I have two databases set up in a Master (A) to Master (B) replication environment. Master A ...
1
vote
2answers
210 views

Is possible to optimize this query or it's already optimized?

I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one: -- -- Table structure for table `default_users` -- CREATE TABLE IF NOT EXISTS `default_users` ( ...
3
votes
2answers
103 views

View with parameter IN

I'm creating a view in which I have a where with an in clause and I want it to be a parameter. Unfortunately views can't be created with parameters, so I tried replacing it with functions, but ...
0
votes
1answer
17 views

MySql DB from Ubuntu to RHEL

I would like to migrate my Mysql databases from Ubuntu to RHEL..what is the best way to proceed? I'm on community edition. I will be moving to entreprise edition of MySql on RHEL.
1
vote
1answer
35 views

Windows crashes on doing sql dump [closed]

Windows simply crashes and opens the blue screen for dumping physical memory on trying to import sql dump. Blue screen error: IRQL_NOT_LESS_OR_EQUAL So far I have tried 4 methods: Through workbench ...
1
vote
1answer
43 views

Migrating mysql.user table from MySQL 5.5 to MySQL 5.6

I have a tricky question. I am migrating from MySQL 5.5 to MySQL 5.6. I want to migrate 45 users in MySQL 5.5 to MySQL 5.6 Questions Should I just take a dump of the MySQL 5.5 user table and ...
1
vote
2answers
67 views

Should I stop an awfully inefficient MySQL DELETE statement?

I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode: DELETE table FROM ( SELECT MAX(id) id, field FROM table GROUP BY field ) temp_table INNER ...
1
vote
1answer
34 views

Mysql can't start?

Mysql can't start, here is the log : 150708 06:28:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150708 6:28:36 [Warning] Using unique option prefix key_buffer instead of ...
5
votes
1answer
223 views

Percona mysqld got signal 11

I've googled and searched stackexchange, but can't find anything relevant to this particular issue (there are other error 11 issues, just not for this as far as I can tell). We have a server that is ...
0
votes
0answers
8 views

Binlogs in MySQL are created too frequently, how to solve this issue?

We are using MYISAM storage engine in a Master-Master replication environment. The number of binlogs which is to be generated regularly is getting very high count despite setting binlog size of 1.6GB, ...
1
vote
0answers
263 views

How do I recover from MySQL InnoDB table/index corruption?

While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible. ...
1
vote
1answer
43 views

mysql replication and innodb_file_per_table

Is it possible for the master (5.1) to have innodb_file_per_table = 0 and for the slave (5.1 or 5.5) to have innodb_file_per_table = 1 ?
0
votes
1answer
36 views

How to Upgrade Master Slave MySQL on AWS RDS?

I already have Master Slave configuration on AWS RDS. We want to upgrade 5.5 to 5.6.23. ..What should be correct sequence to Upgrade ? 1. Upgrade Master ( Using Modify Instance ) --> Upgrade Slave 2. ...
1
vote
1answer
314 views

mysql fulltext query is very slow

I have one table for User detail in MySql with about 500000 records in it. I have also created fulltext index on firstname, lastname field on this table. but when I am trying to search any single ...
2
votes
2answers
617 views

Errors while creating multiple mysql-5.5 instances

I have installed 3rd mysql instance on my testing server. 2 instances already running without any issues. When I installed 3rd instance by mysql-5.5.30 zip source, it installed successfully but when ...
2
votes
3answers
55 views

How to show multiple names for one record but not duplicate the record?

I have a database structure like so: Car - id - carname - image - category - status Parts - partid - partname CarParts - carpartid - carid(fk) - partid(fk) - amountid(fk) Category - ...
1
vote
2answers
3k views

Hyperthreading & MySQL InnoDB Thread Concurrency Performance

I have a dedicated DB Server with the following specs" Ubuntu 12.04 LTS Server Intel(R) Xeon(R) CPU X5670 @ 2.93GHz 12 Cores MySQL 5.5.29 64 GB RAM RAID10 We are currently doing Hyperthreading ...
1
vote
1answer
43 views

MySQL Read/Write Benchmark

I'm trying to benchmark Read/Write speed in MySQL 5.5. Currently I have a stored procedure that insert rows with random numbers to a table and I'm measuring time of execution before and after ...
4
votes
2answers
199 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 ...
4
votes
5answers
899 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 ...
0
votes
1answer
79 views

Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

CRUD replicates just fine. But we noticed that DB structure altering commands such as ALTER TABLE and/or adding/updating columns to a table and even creating new ones do not replicate to the slave at ...
1
vote
2answers
81 views

mysql master slave replication scenario

We are creating a new mysql slave, but for dump we are taking from a backup server which is a slave of current master. Now to enable replication we want to connect it with the master so should I take ...
3
votes
3answers
13k views

MySQL using too much CPU

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: CentOS 5.9 24gb Ram 8 ...
0
votes
1answer
69 views

what could cause a mysql process to close (clean)

We are using mysql master-master (version 5.5.32) for an API manager called Layer7. The 2 hosts holding the instances are in the same ESX From time to time we have mysql doing a clean shutdown for ...
0
votes
2answers
51 views

Killing a process without login to MySQL

Here is the situation: I was running a very complex query on a database which was expected to execute within a few hours over night(server configuration is not good and data set is large). This ...
2
votes
2answers
952 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 ...
0
votes
0answers
39 views

MySQL - Get actual precision and scale of number

I would like to query the integer part and fractional part of all decimal(8,4) values in a column. By that I mean, I want to know the actual given values, and not the max allowed by the data type, ...
3
votes
2answers
1k 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
8k 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); ...
0
votes
1answer
26k 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 ...
1
vote
3answers
78 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
1answer
44 views

Mysql Master - Master Replication

i have established a master A and master B replication between two servers.Both are getting replicated.But it is limited with only one database. My questions 1.How to create a new database in ...
1
vote
0answers
31 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
1
vote
1answer
29 views

MySQL: Incorrect binary log format - OFF.000

I have a Percona MySQL 5.5.30 as a master(Debian 6.0.7). I got a weird issue that all my binary logs started saving in format OFF.00* instead of the specified format in .cnf file. mysql> show ...