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

learn more… | top users | synonyms

-1
votes
1answer
16 views

select columns if a column has a value

I have RegiNum and EmpNum columns in users table. If RegiNum column has a value it's considered as a student. If RegiNum is filled, I want to select Name and ID from that table. I used select ...
0
votes
0answers
4 views

mysqldbcompare giving Compare table checksum FAIL error

Running mysqldbcompare on two test databases on same server. There is no data discrepancy in any table but showing following message which I'm not able to understand. Any help is appreciated. TABLE ...
1
vote
0answers
35 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` ...
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
0answers
21 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 ...
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 ...
0
votes
1answer
24 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
14 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
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 = ""; ...
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 ...
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
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
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 ...
0
votes
1answer
52 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 ...
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 -> ( -> ...
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 ...
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
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`, ...
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 ...
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
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
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
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 ...
-1
votes
1answer
64 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
16 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
23 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
13 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
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 ...
3
votes
2answers
85 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
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 ...
3
votes
1answer
32 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
32 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
20 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
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 ?
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
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
51 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
23 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
39 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
30 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
40 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
43 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
37 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
53 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
50 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
183 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
38 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 ...