MySQL version 5.5 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

5
votes
2answers
137 views

How can I display a mysql bit(64) column as a string of ones and zeros?

We have a MySQL 5.5 database with a table that holds a number of 64-bit values which are used for calculating the hamming distance. The columns for these are defined as BIT(64) and are populated ...
0
votes
2answers
46 views

How to run update query without interrupt other processes

I want to update a table and what I want to do is when other processes want to use this table, It will pause my update query and when other processes complete, it will continue to update.Is there any ...
0
votes
1answer
70 views

Copying MySQL schema, some tables not getting recognized!

From the 'data' folder of my colleague I copied the entire 'MyDatabase' folder and pasted that in my own 'data' folder.Now, some tables are getting recognized while others are not.**All the tables are ...
1
vote
1answer
31 views

What could cause a record to appear in the MySQL binary log but not the original table?

I have a web application (CiviCRM) that has the option to enable binary logging, which I've done. We use this software to record donations, including automatic recurring donations. Our payment ...
1
vote
1answer
449 views

Set sql_mode “blank” after upgrading to MySQL 5.6

After upgrading MySQL from 5.5 to 5.6, some our app face to problem and need set sql_mode to blank to solve this issue. I added sql_mode = '' to my.cnf but there was no effect on the mysql setting. ...
1
vote
1answer
205 views

MySQL SELECT blocking an INSERT?

Is it possible for a SELECT query to block INSERT / ALTER queries? I have the following (taken from ENGINE INNODB STATUS) on my slave database: ---TRANSACTION 26DA7060, not started mysql tables in ...
0
votes
0answers
37 views

MySQL - Seconds shown for query run time is incorrect

I came into work this morning, and first thing I noticed is that one of my Slave databases was running behind. I took a look at the processlist and could see that I had the following running. SELECT ...
0
votes
0answers
51 views

Trigger for different database host and remote server in MYSQL

I have two database server running on different host(Old host server,new host server). I want to create trigger i.e. If I insert any new record in new host server's table then that record is also ...
1
vote
1answer
76 views

Speed up COUNT(*) when using an index

I have a table similar to this (simplified): CREATE TABLE books ( id INT AUTO_INCREMENT, category INT NOT NULL, PRIMARY KEY (id), KEY (category) ); This table has over 10,000,000 ...
0
votes
1answer
71 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 ...
1
vote
1answer
54 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
0answers
23 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
2answers
21 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
1answer
526 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?
0
votes
1answer
54 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 ...
0
votes
0answers
63 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 ...
1
vote
2answers
613 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
1answer
127 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 ...
3
votes
2answers
518 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
21 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
74 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
101 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
133 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
88 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 ...
0
votes
0answers
10 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
1answer
55 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
93 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. ...
5
votes
1answer
1k 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 ...
2
votes
3answers
77 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
1answer
129 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 ...
1
vote
2answers
165 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 ...
1
vote
1answer
624 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
70 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 ...
1
vote
0answers
41 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
39 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 ...
0
votes
0answers
80 views

pt-online-schema-change with statement based replication in MYSQL

Is it possible/safe to use pt-online-schema-change in a Master slave environment where as the way replication is setup is statement based(SBR). What is the better way of doing it as currently I have ...
1
vote
3answers
181 views

Index optimization and cardinality

I'm by no means a DBA expert. Rather, a Magento developer having to troubleshoot some slow queries. I'll jump right in -- here's my attempt to EXPLAIN the troublesome query: EXPLAIN SELECT ...
1
vote
1answer
183 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 ...
0
votes
0answers
46 views

Index pages in innodb file when innodb_file_per_table = ON

I have MySQL 5.5, and innodb_file_per_table was always in config file. After some time of using MySQL DB was produced big ibdata1 file, but i don't know how explain why this file is so big, i try to ...
1
vote
0answers
486 views

Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

I know it is often asking question. But I can't find answer for my scenario with specific condition. We use MySQL 5.5 We have always(I am sure) innodb_file_per_table = ON option in our mysql ...
1
vote
2answers
105 views

Difference between Handler_write and Innodb_data_writes

Handler write is the number of requests to insert a row in a table. InnoDB data writes is the total number of data writes. Questions are Does inserting a row count as a disk write? If I do an ...
0
votes
2answers
146 views

Fixing MyISAM database without losing data

My old server was crashed but I was able to recover the databases from MySQL through testdrive. I'm trying to dump a database using mysqldump but it gives error for certain tables are crashed and ...
0
votes
0answers
45 views

MYSQL taking 17.5 GB of RAM

This is what top shows how MySQL is taking a large amount of memory. VIRT RES SHR S 3967 mysql 20 0 17.5g 1.4g 7008 S 41.2 2.3 17426:16 mysqld this is ...
1
vote
1answer
98 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
0
votes
0answers
56 views

Got an error from unknown thread, /pb2/build/sb_0-13157587-1410170252.03/rpm/BUILD/mysql-5.5.40/mysql-5.5.40/storage/myisam/mi_write.c:226

I am using mysql 5.5.40-log and my default storage engine is Innodb, I found this error in my error log "[ERROR] Got an error from unknown thread, ...
0
votes
0answers
44 views

Does MySQL InnoDB locks multiple rows with READ_COMMITTED isolation level?

I am in trouble while resolving LOCK WAIT TIMEOUT EXCEED error with MySQL InnoDB. I have gone through this article and it says if we use isolation level READ_COMMITTED then my update query should ...
0
votes
1answer
63 views

Backup a huge database 200gb+

i have a mysql database where i need to take a backup of the log database. the problem is the log database i over 200gb and contains over 280 millions of rows, i need right now to remove a kind of ...
1
vote
1answer
212 views

“Remote” connection to a local MYSQL database on LAN

I have a mySQL (5.5.38) server running on my desktop (windows 7), I can connect just fine into any of the servers Users, and skip-networking is off when checked via show variables. I am trying to give ...
1
vote
1answer
64 views

MySQL Drop User Fails for Remote Host

We're clearing out some old MySQL users that connected from remote hosts during a database transfer. Why is this DROP USER query failing for the user asdfasdf_imac4? I'm logged in as root. mysql> ...
0
votes
1answer
75 views

Working with variables in stored proc

I want to create a meta table on the fly using a stored pdocedure and passing a table prefix as "in" parameter and then build the final table name somehow. My code looks like this: SET @table_ = ...