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

learn more… | top users | synonyms

2
votes
3answers
2k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
1answer
38 views

Changing Default Values of Percona Audit Log

Have I missed Something here? I am installing the Percona Audit Log Plugin (MySQL 5.5). I can install it fine using: INSTALL PLUGIN audit_log SONAME 'audit_log.so'; I can then see all the default ...
1
vote
0answers
28 views

MySQL/Percona Audit Log Plugin Not Working

I've been looking to implemement the Percona Audit Log plugin. So far I have installed it and configured it, and it seems to be working ok. Except, I have installed it on a slave Database as I don't ...
0
votes
1answer
164 views

Fulltext Search Doesn't Match a Hashtag

We maintain a table that looks like this to perform search queries on our forums database. I'll draw your attention to the FULLTEXT columns: CREATE TABLE `forums_posts_search` ( `post_id` ...
1
vote
1answer
125 views

Diffrences between master and slave replication in mysql?

In a database named asterisk, there is a table named cdr which was partitioned on the time field. mysql> show create table cdr; Create Table: CREATE TABLE `cdr` ( `gid` bigint(20) NOT NULL ...
1
vote
0answers
76 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...
4
votes
1answer
717 views

How to removing or reduce the size of redo logs in MySQL cluster

Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we ...
6
votes
2answers
491 views

MySQL: Show list of tables with paging in Terminal

I am using MySQL Ver 14.14 Distrib 5.5.44 for debian-linux-gnu. How can I show my tables page wise in a terminal?
2
votes
1answer
1k views

Mysql error “#1030 - Got error -1 from storage engine” when doing insert/update queries

I am starting to learn manipulating some long list of data from datalogs in mysql, around 300k entries, and it is being added by about 1k off entries everyday, it was working fine in a week then ...
2
votes
1answer
71 views

Why does coalescing make this query quicker?

I have the following query SELECT SQL_NO_CACHE `table1`.* FROM `table1` LEFT JOIN `table2` ON table2.id = table1.table2_id WHERE (`table2`.`date_assigned` >= '2015-06-21') AND ( ...
0
votes
1answer
197 views

MySQL Trigger On UPDATE “Unknown Column” Error

I want to log a table when its updating a row which is column "insertDate" or "updateDate" value is before than 3 month. I wrote this trigger: DELIMITER /// CREATE TRIGGER ...
0
votes
1answer
37 views

Percona / MySQL audit log plugin - limiting the content?

I've been looking to experiment with Perconas Audit Plugin, as it sounds like it could be potentially very useful. However it appears that it is a very blunt (ALL or NOTHING) tool. From my ...
19
votes
4answers
10k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
3
votes
1answer
132 views

Any risks in updating the system time on a mysql 5.5 server?

We just discovered one of our mysql systems has not been running ntp, with the result that the time has drifted a bit. If I enable ntp, and synchronize the time while the system is running are ...
1
vote
1answer
51 views

enable log_bin_use_v1_row_events in mysql 5.6

I have a MYSQL master with two slaves also running MYSQL 5.6. I want to create another slave and that needs to be MYSQL 5.5. I was wondering if replication will work smoothly. I have ...
5
votes
2answers
172 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
48 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 ...
2
votes
3answers
6k views

How to import table's data in MySql from Sql Server?

I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error. Here this error may be ...
0
votes
1answer
74 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 ...
0
votes
1answer
34 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 ...
2
votes
1answer
2k 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
306 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
44 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
66 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
228 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
1
vote
1answer
101 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 ...
5
votes
1answer
3k 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 ...
1
vote
3answers
1k views

Table Design for Student Attendance hourly for each day

I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate ...
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
57 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
2answers
22 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
25 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
1k 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
323 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 ...
0
votes
1answer
98 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
684 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
1k 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
69 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
218 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
889 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
22 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
97 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
114 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
168 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 ...
2
votes
1answer
138 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
2k 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
11 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, ...
2
votes
0answers
477 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
64 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 ?
1
vote
1answer
427 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 ...