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

learn more… | top users | synonyms

1
vote
0answers
29 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
14 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
112 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
99 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
38 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 (EMPLOYEE.Ssn !=...
3
votes
1answer
37 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
55 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
26 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
19 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 that,...
0
votes
0answers
19 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
56 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
37 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
41 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
31 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
62 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
17 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
48 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
38 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
3answers
74 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
52 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
223 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
39 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
12 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
34 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
50 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 mysql-...
0
votes
0answers
20 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
34 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
27 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). https:/...
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, GROUP_CONCAT(if(...
0
votes
0answers
32 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
41 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 = r1....
1
vote
2answers
54 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
31 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
34 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
50 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
70 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
32 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 ISNULL(MIN(StartDate),'1/1/...
1
vote
1answer
36 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 pt-online-...
0
votes
0answers
37 views

How to fix the MySQL 5.6 System.Security.Principal.WindowsIdentity.get_User () not implemented exception in Linux?

I would like to know if programmatic intervention is necessary to fix the following C# Runtime exception when running MySql 5.6 Connector.NET on Ubuntu Linux 15.10 instead of Windows 7. Error ...
0
votes
0answers
27 views

Query slow on 5 Mil fact table

I have a fact table with currently 5 mil rec. The record count will go up as ETL runs on daily basis. The below reporting dashboard query is taking 1.50 sec to execute. There are many number of ...
0
votes
2answers
32 views

MySQL Union not showing 2nd query result set

I am doing a union of 2 queries and trying to fetch data on two different columns, however the union is not fetching the value from 2nd result set. Can you please guide me on this. select * From ...
1
vote
0answers
31 views

Manually specifying candidate masters in Maxscale

Looking over the documentation, I haven't quite been able to find if its possible to manually set the candidate master status of a server in Maxscale. The reason I am looking for this is because some ...
1
vote
1answer
52 views

MaxScale not correctly recognizing new topology when we failover to a new master

So I currently have an architecture where we have one master and one slave and read/write splitting in Maxscale. When we fail the slave, Maxscale correctly recognizes it and displays the information ...
0
votes
0answers
87 views

Slow Insert in MySQL 5.6.29 Myisam Table

I am facing slow response times in MySQL 5.6.29 while Inserting/Updating MyISAM based tables. Queries are taking more then 40 sec and create "Waiting for table level lock" state. VM has 12 VCPU and 32 ...
0
votes
0answers
16 views

longblob insertion in MySQL

I use MYSQL 5.6.19-0ubuntu0.14.04.1 I insert a JSON String (for example {"a":"1","b":"2","c":"apple😀aeroplane"}) into a column in my table of UTF-8 encoding. Rough view of my table (i have changed ...
0
votes
0answers
30 views

Configuring my.cnf file and parameters calculations

Hi i am new in database administration and learning to configure my.cnf. Can anyone help me about parameters in my.cnf and what values should be set for each parameter, and what are the calculations ...
3
votes
1answer
59 views

How do we implement SQL Server IF EXISTS (SELECT) in MySQL 5.6?

We would like to convert the following SQL Server statement: IF EXISTS(SELECT OptionValue FROM Options WHERE OptionCatagory = 'General' AND OptionName = '...
6
votes
1answer
125 views

Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name

I'm finding that MySQL seems to have an issue when ONLY_FULL_GROUP_BY mode is enabled and the query includes a HAVING that refers to grouped columns from different tables that have the same name. ...