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

learn more… | top users | synonyms

0
votes
0answers
13 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`, ...
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
35 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 ...
0
votes
0answers
14 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
1answer
53 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
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 ...
2
votes
0answers
26 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
4 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 ...
-1
votes
1answer
30 views

Got error while reverting mysql 5.7 to mysql 5.6

Got error while doing mysql service restart. Googled a lot, but nothing fix my issue. [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format. 2016-04-23 11:10:38 22185 ...
0
votes
1answer
14 views

How to support custom fields for an entity and list them as columns?

I am currently working on a system which supports "custom fields" for users. I will illustrate this with a simplified example of this database schema: users -------- user_id name age custom_fields ...
1
vote
0answers
17 views

Mysql became unresponsive

All of the sudden mysql became unresponsive we are not sure of the reason, when we check mysql error log we find following, but for us it is hard to interpret it, can anybody help to understand it and ...
0
votes
0answers
11 views

load balancer in mysql cluster

Do MySQL cluster has its own inbuilt load balancer ? If yes then how can we use it ? I don't want to use third party tool like HA PROXY to set up LB because of financial constraint. As per MySQL DOC ...
1
vote
1answer
99 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 ...
3
votes
2answers
76 views

Better understand table indexing best practices

I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider: The tables are not that big, I think (1000 - 10,000 rows). The searches are made ...
0
votes
2answers
34 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 ...
3
votes
1answer
26 views

Performance schema: 'stage/sql/Sending data'

Background I am using MariaDB 10.0 but MySQL 5.6 answers are also of interest. Two machines cloned from the same image, same MariaDB configuration as far as I could tell, same dataset. One machine ...
0
votes
1answer
17 views

Table 'performance_schema.threads' doesn't exist. SQL Error: 1146)

I'm using MySQL '5.6.29-log' on Windows 2012 and performance_schema is missing few tables. How does I re create performance_schema? I'm looking at MySQL :: MySQL 5.6 Reference Manual :: 22.2.1 ...
0
votes
0answers
16 views

Boosting select queries during INSERT ON DUPLICATE KEY UPDATE into 150Gb innoDb table

I have a single server with 2 innoDb tables: 35Gb (partitioned, 24M rows), 150Gb (without partitioning, 220M rows). 25% data, 75% index. 99% of select queries are SUM + WHERE + GROUP BY. 100% of ...
1
vote
1answer
13 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 ?
0
votes
2answers
42 views

Relate few records in table to eachother

I need to relate groups of records, like I have 100 records in table, 3 of them should be known they are related, and other 5 are related to own group. I don't need any group IDs or anything like ...
0
votes
0answers
18 views

MySQL server v5.7 error 2013 and 2006 when system date is changed

I have a problem with my MySQL server v5.7 when I would perform a query and the system date is the current date everything works. But when I change the system date and perform the query again in MySQL ...
3
votes
1answer
46 views

mysqldump inconsistencies

I'm planning to take a dump of a table with the --single-transaction option enabled. I know that if we execute any DDL statements during the dump process we might get an inconsistent dump. Will the ...
1
vote
0answers
14 views

ERROR 1 (HY000): Can't create/write to file (Errcode: 13 - Permission denied)

I've been struggling for a couple of days with this now and have been trying to use this thread amongst other google searches to resolve it but can't seem to get it to work. When I run this SELECT ...
0
votes
1answer
32 views

Why does this SQL work on one version of MySql-Server but not another?

I'm trying to run the following very simple SQL command on two different MySQL databases: CREATE TABLE mytable ( created DATETIME(6) NOT NULL, updated DATETIME(6) NOT NULL, my_id ...
1
vote
2answers
29 views

MySQL Replication issues on server placed in different continents

I am running 2 cpanel servers with MySQL Replication set on them, one in france the other in US. Running a database hungary site. Replication was setup to load balance as well as for failover reason ...
1
vote
0answers
29 views

Missing DDL and table

I encounter the following errors after reinstalling MySQL Server. These seem to affect only one out of the several tables in the schema. The following errors are generated from MySQL Workbench. When ...
0
votes
0answers
16 views

Reusing domain specific queries

I'm trying to cut down on duplicate code on a project(queries) but due to constraints, I can't seem to grasp how. The constraints : I work on a slave DB from a vendor (I have only select ...
0
votes
1answer
36 views

Create Domain Fails to work in MySQL [closed]

I am trying to finish my homework. In almost all of the questions, I have to use "Create Domain". It seems like MySQL does not support that. What can I use instead?
2
votes
1answer
36 views

Stored procedure unable to compare

Hi I tried implementing the below stored procedure which always takes my if condition as true and implements . CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOpen`(in uid varchar(11),in S_ID ...
0
votes
2answers
45 views

MySQL row-based replication - Delete Fails

We have a Master - Master replication setup, MySQL 5.6. Only one Master is used, the other is for backup and failover (we'll call that the slave). The binlog_format is set to ROW, autoincrement ...
0
votes
1answer
42 views

Don't know why MySQL is not creating table with primary key constraint [closed]

I can't understand why MySQL showing this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...
3
votes
2answers
177 views

Different behavior between MySQL 5.5 and 5.6

I have this schema with data: CREATE TABLE `ticket` ( `id` INT NOT NULL, `name` VARCHAR(45) NULL, `type` INT(11) NULL, PRIMARY KEY (`id`)); CREATE TABLE `task` ( `id` int(11) NOT NULL, ...
1
vote
0answers
36 views

MySQL Event scheduler not generating CSV

The scheduler seems to be running from INFORMATION_SCHEMA.EVENTS but there is no CSV output. The event scheduler has to automatically produce a csv to pass on the data to another system. It's like ...
0
votes
0answers
8 views

How to create mail alerts in mysql replication environment?

if suppose In mysql replication environment Masterserver : @@readonlyflag has should be zero only,if it is changed as 1 means without notice.we need to set email alerts for that? is it possible to ...
0
votes
1answer
32 views

Why does this MySQL 5.6 stored procedure not compile with MySQL Workbench?

Why doesn't this MySQL 5.6 stored procedure compile with MySQL Workbench? CREATE PROCEDURE spDataLocationSetActive ( IN driveLetter varchar(1), IN active ...
0
votes
0answers
40 views

Install two instances of mysql 5.6 and 5.7

How to install two mysql servers on one Debian server? apt-get install libaio1 wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.11-1debian8_amd64.deb-bundle.tar tar -xvf ...
0
votes
0answers
14 views

MySql event scheduler skips events for several minutes: why?

I'm trying to diagnose some troubles on a couple of installations of a webapp that occasionally stop responding (Tomcat 7 and MySQL 5.6). Since the hangs only happen on one or two of these ...
0
votes
1answer
23 views

Mysql takes more tmp space than the table size

I have a mysql table with 2.8GB data+index size. I wanted to add a column to the table and it was failing due to insufficient space on /tmp. When I looked at the size available on /tmp it was 3.9GB. ...
1
vote
0answers
24 views

When are UPDATE / INSERT operations processed in Online DDL in MySQL 5.6?

MySQL 5.6 includes functionality called Online DDL which allows ALTER operations to proceed on tables without applying a LOCK to the table (technically referred to as allowing Concurrent DML). ...
0
votes
0answers
10 views

How to create Pivot table with dynamic columns in MySQL [duplicate]

I'm using the following table for storing dynamic form data data: Using the following query to select the records from my table SELECT response_no, ...
0
votes
0answers
25 views

mariadb can't import sql which contains \' in insert statement

i export a large db from mysql5.6, then i import into a mariadb (xampp) on my localhost by command line C:\Windows\System32>C:\xampp\mysql\bin\mysql -u root -p dbname < D:/folder/filename.sql ...
0
votes
0answers
11 views

MySQL disk usage due to failure on dump import

I've just SCPed a 400 MB database from a live server. I dumped it with mysqldump and gziped it as following: mysqldump --single-transaction=TRUE -u dbuser -p dbname | gzip> /home/user/database.gz ...
0
votes
1answer
39 views

Why do we need to compare keys from different table?

can you please help me make sense of this code? The question: is to find the pair of students who have registered for the same course in the same semester. why do I need to check the s1.sid = ...
1
vote
2answers
53 views

Contiunous rise of mysqld memory consumption

Mysqld memory consumption rises forever and never seems to be freed. It starts out about 6GB but gradually rises to around 10GB in a few weeks, and of the 10GB only 4GB and 50MB is being used by the ...
0
votes
0answers
20 views

How to enable load balancing on mysqlrouter for read/write queries?

I have installed a Mysqlrouter to be a load balancer for my databases. My databases are dual master MySQL with two slaves. I want to configure Mysqlrouter to redirect read/write queries on both ...
0
votes
1answer
30 views

Query not using indexes

I have a query which is not using the timestamp index and scanning 200 million records. I know that the query will not use the index if there is a function applied on top of it. However is there any ...
0
votes
1answer
47 views

Mysql Optimization Query

I'm looking for some expert advice on solving a few problems being reported by mysqltuner. We run a few Magento ecommerce sites. PX121-SSD server with Hetzner, 128gb RAM. Running Mysql (Percona) on ...
3
votes
1answer
52 views

InnoDB - Intermittent CPU spikes on a large database

I have a machine with a large MySQL 5.6 database (multiple tables with 10-100 million rows). It has a considerable amount of load, especially during the evening and is running on a 16 core machine. ...
0
votes
0answers
29 views

What is the difference between Microsoft SQL Server 2008 R2 and MySql Server 5.6 for handling Select ISNULL(MIN(StartDate, '1/1/1900 10:00'))?

I would like to know the difference between Microsoft SQL Server 2008 R2 and MySql Server 5.6 for parsing the following SQL statements. Microsoft SQL Server 2008 R2 SELECT ...
1
vote
1answer
32 views

Altering a large table to INNODB

I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master. I tried to use the ...