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

learn more… | top users | synonyms

0
votes
1answer
606 views

ndbcluster: Error: 1114, The table is full, when using table

I have searched for a while about this error. I have a MySQL cluster running with 2 data nodes. The cluster houses a few other databases. I have a dump of a database in --tab format from another ...
2
votes
1answer
23 views

Logical condition when declaring handler procedure

I have a procedure in MySQL that executes a handler for SQLEXCEPTION to read the errno code and insert it to a table. DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 ...
1
vote
0answers
32 views

MySQL - Find bookings free time slot

I am trying to find free time blocks on a day between the business hours (08:00 to 18:00) of a service station. The schema of the database is as below, CREATE TABLE IF NOT EXISTS `Bookings` ( `id` ...
6
votes
2answers
178 views

How can I copy the MySQL EXPLAIN result into a table?

I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis. I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how ...
0
votes
1answer
15 views

Chained MySQL replication from Master->Master replicated pair

This is a pictorial view of what I have: Master 1<----->Master 2 | | | | V V Slave 1 Slave 2 The masters are never written ...
0
votes
2answers
23 views

MySQL create index with LOCK=NONE still locks a table

I have the following MySQL RoR Migrations: class ReindexRpushNotification < ActiveRecord::Migration def up execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;") ...
0
votes
1answer
28 views

How to use FULLTEXT in mysql innoDB?

Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance. I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference ...
1
vote
2answers
2k views

Mysql closing immediately after new installation

I am using mysql as an backend for my webapp ,the software ran without any glitch but today it gave unexpectedly ERROR:2003 i tried many solution on the internet but none worked. so reinstalled the ...
0
votes
0answers
20 views

Searching a text with space in mySql match against Query

I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result. My MySql Query is: SELECT count(id) as total , MIN(product_price) as ...
1
vote
1answer
27 views

Alter a large mysql table 700 gb

The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log. I found following options: pt-online-schema-change: alters a table’s ...
0
votes
1answer
26 views

Error: INSERT INTO client [closed]

I'm trying to convert my client database from MySQL to MySQLi and being a noob I'm having issues with it. Here's the code: <?php $servername = "localhost"; $username = "root"; $password = ""; ...
2
votes
1answer
210 views

Services don't have MySQL Server. How to run it?

I need to run MySQL Server on a new for me laptop. There's Windows 7. And MySQL have been installed on it. In order to verify this, I opened the Programs and Features window and saw it there: ...
0
votes
1answer
302 views

Slave not getting replicated SERVER_UUID error

Unable to start replication Master-Slave, I have searched a lot for this but unable to get the Server-UUID of both the server. Do we have any solution for this. Scenario: I have created new slave ...
0
votes
1answer
22 views

mysql Relay log file not found error while changing mysql data directory

I want to change data directory of my mysql server running 5.6.28 on centos 5.11. Its a slave to some master server. i have edited the config file ,moved the data contents from old directory to new ...
0
votes
0answers
12 views

Using MySQL session variables in a SSRS Report DataSet Query

I am creating a a SSRS report that connects to a MySQL database. In the DataSet I need to run a number of queries that load pieces of data in @SessionVar's that I can then piece together in the final ...
0
votes
1answer
243 views

SET sql_log_bin = 0 in RDS MYSQL 5.6

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master. I am trying to alter a table and want to change a column value and also on some tables I want to add a ...
2
votes
1answer
192 views

MySQL slave server getting stopped after each replication request from Master

Basic master-slave MySQL configuration has been done on Windows machine. Master and slave servers are running on localhost with different ports. Now when executing update or insert in master server, ...
0
votes
1answer
172 views

Subquery on timestampdiff only allows for 1 result, how do I scale this for all results?

I'm currently writing up a query which is meant to output data to show curves of power profiles, and I'm facing a slight problem. So far, I've managed to do this: select datetime, PLANT_STATUS, ...
0
votes
1answer
123 views

Not all MySQL processes are killed when restarting

We have a MySQL 5.6 server running on Ubuntu 12.04 using the MySQL 5.6 PPA. The service comes up fine when rebooting the box, but whenever we restart the MySQL process via /etc/init.d/mysql restart, ...
0
votes
1answer
1k views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
4
votes
2answers
109 views

find the beginning and ending values of sequential runs

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set? Given the sample data they used: mysql> CREATE TABLE wp_blogs -> ( -> ...
0
votes
1answer
119 views

replication filtering replicate-rewrite and do-table Mysql 5.6 (innodb engine )

I have a master/slave replication setup based on statement based replication. On master database named DB1, I have 5 tables and I want to replicate only two table on slave's database named DB2. Also, ...
0
votes
0answers
24 views

MySQL Design for bulk inserts

Consider the following table structures as below, these tables can go beyond millions records received_goods rg_id int(10) PK (auto_increment), name varchar(128), ... storage_details rg_id ...
1
vote
1answer
208 views

Slave Waiting to reconnect

I have added new slave host to master for replication, but missing some of the parameters. mysql> show slave status\G *************************** 1. row *************************** ...
1
vote
1answer
1k views

Can't Delete Orphaned MySQL Temp Table

One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes). ls -l sql -rw-rw---- 1 mysql mysql 8570 Mar 13 12:05 #sql-ib32694.frm ...
0
votes
0answers
25 views

mysql crashed and stuck on transaction roolback

Yesterday night I had an power outage which caused mysql reboot and after that it is stuck on waiting for 39 transactions to finish, the error is as follwed. [Note] InnoDB: Waiting for 39 active ...
0
votes
1answer
48 views

InnoDB files created even innodb_file_per_table is OFF in mysql-5.7

We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0. We decided to change ...
0
votes
2answers
37 views

Getting multiple out when using NOT EQUAL operator

How to display name of the department managers (fname, lname) who have no dependent? Tried this: Select DISTINCT EMPLOYEE.Fname,EMPLOYEE.Lname from EMPLOYEE INNER JOIN DEPENDENT ON ...
1
vote
1answer
110 views

Which field should be the candidate for index in this case?

You're tracking visits already in another table that contains quite some information about the visit. In order to block duplicate visits after a certain amount (Let's say 5) you think its best to use ...
0
votes
1answer
27 views

MySQL 5.6 performance_schema not showing queries from application

I'm seeing a strange problem with perfromance_schema... it seems queries are not getting logged as I expect them to be. For example, this is what I get: mysql> SELECT `SCHEMA_NAME`, `DIGEST_TEXT`, ...
1
vote
1answer
17 views

mysql restore specific date data from a dump file

I have mysql [version 5.6] database dump file. I want to restore only one day data from that dump file. Anyone has any clue ?
1
vote
1answer
42 views

mysql - merge two large instances to one server (40GB of binaries)

I'm need to merge two mysql instances (both mysql V5.6) onto one server. The binaries of 12 databases are approx. 40GB. I read other posts about mysqldump but as I've previously had many issues ...
2
votes
1answer
253 views

How to suppress specific warnings in mysql error log when ignoring a table to replicate

Is there a way to make the mysql error log less chatty with certain warnings? I just threw in the replicate-ignore-table option in my.cnf so our health check table doesn't get replicated. However now ...
0
votes
0answers
39 views

Table data is not getting dumped

This is just for test purpose. I'm taking backup (INNODB DB) with --single-transaction and from another terminal I'm running alter table on one of the table of same DB on which dump command is ...
0
votes
1answer
140 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
5answers
333 views

Replication in same server within different databases. Mysql 5.6

What I have : a windows machine with one instance of MySQL 5.6 Server. two database named test and test2. Test database has a table called activity with columns id and class What I need : ...
0
votes
0answers
7 views

Is it mandate to turn off huge pages in Mysql 5.6

Folks, Is it required to turn off huge pages in Mysql 5.6 server under /sys/kernel/mm/transparent_hugepage/enabled -> To never? We have a heavy transactional database and calling the data segments ...
0
votes
1answer
50 views

Maximum number of TEXT/BLOB columns per table/performance of table with many of them?

I've created a system that generically imports CSVs into a database table with metadata columns for keeping track of when a CSV record has been changed, and it allows filtering on CSV columns instead ...
1
vote
2answers
3k views

add column without metadata lock in mysql 5.6

I am using MySQL Server version: 5.6.15 Homebrew. I need to add new null column. alter table requires no metadata lock. In order to avoid this MySQL 5.6 introduced algorithm=inplace. Here is the ...
2
votes
1answer
59 views

Query with Indexes slower than without Indexes

I'm using MySQL 5.6 and my storage engine is InnoDB. I have a table with 1 million rows containing with the columns: ID (primary key) FirstName LastName foreign_key_id (foreign key, NOT NULL) ...
0
votes
0answers
32 views

Do Queries on a MySQL Federated table lock the remote table?

We are trying to use Federated tables as an experiment where a remote server copies from the Federated tables to a local db with OLAP designed tables. My question is whether the SELECT queries on the ...
2
votes
2answers
170 views

Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries

I'm systematically (1/2 times per day) experiencing a freeze in the DB server. Suddenly, all the COMMITs get stuck in 'init' state; they pile up (up to 20/30), an almost no other queries are executed ...
0
votes
0answers
7 views

Is it advisable to configure huge pages on SLES for MySQL

What is a general opinion on using huge pages with MySQL server? As described in here: https://dev.mysql.com/doc/refman/5.7/en/large-page-support.html Or is it best to leave things as is and not ...
8
votes
1answer
709 views

Issues with MySQL installation

First of all, excuse the length, this post is very long, and needs some background, second; it has been hard for me discerning the signal from the noise, please bear if some of this is not relevant: ...
2
votes
0answers
35 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
0
votes
0answers
5 views

How would I script standing up a MySQL instance with certain specs?

Context: I seem to be unable to find in MySQL Workbench schema creation process where I can adjust server specs like max memory (2 GB); CPU usage, 1x 4 core; 20 GB SSD/Flash storage; OLTP; OS (RHEL ...
2
votes
1answer
192 views

Mysql high cpu usage - cpanel

After an update from whm, I see a high CPU usage from MySQL (5.6). my.cfg: [mysqld] slow_query_log=1 slow_query_log_file="/var/lib/mysql/slow-query.log" long_query_time=1 skip_name_resolve ...
2
votes
1answer
89 views

mysql trigger on a federated table

I have two MySQL(5.6) databases D1 and D2 on two different machines. There is a table T1 on D1 and there are two tables T2A and T2B on D2. I want to update T2B whenever T1 is updated. T2A is a ...
0
votes
1answer
151 views

how to Use old mysql database after reinstall mysql in RHEL 6?

I was getting problem in starting and login mysql server so I backuped /var/lib/mysql folder and reinstalled mysql server. Now I want to use old database files like: ibdata1 and database folder ...
0
votes
1answer
111 views

Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...