Questions tagged [mysql-5.6]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
23 views

How to select rows after a certain row using auto increment temporary columns

I have a table that has ID varchar(255) as PK. I have managed to create an auto increment temporary column rowNumber to use with a SELECT, as follows: SELECT (@cnt := @cnt + 1) AS rowNumber, ID ...
0
votes
1answer
27 views

Difference in MYSQL Master Slave DISK SPACE

I am using percona_mysql_5.6, both master and slave are in sync as terms of replication and table wise records are also same. However there is a difference is the disk space consumed. The slave is ...
0
votes
0answers
11 views

MySQL: is root a special user?

That may sound like a silly question, but let me explain - I wanted to log on to MySQL as user root from several other systems, so I created this: mysql> create user root@`10.254.%.%` identified by ...
1
vote
1answer
27 views

Why Am I Getting Inconsistent LEFT JOIN Results in MySQL

Background I have a database in 3 different locations with the same exact static data (nothing updates it, it was a one-time import from another system). In 2 of the 3 locations, a query similar to ...
-1
votes
0answers
9 views

mysql 5.6 can't open on redhat 6 [duplicate]

[root@master mysql]# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) and [root@master mysql]# /etc/init.d/mysql.server ...
-3
votes
1answer
35 views

How to get data from tables selected by reference from information_schema with some criteria in table name

I am trying to get data like count or distinct from tables matching criteria in information_schema SELECT count(A.searchstring) FROM (SELECT table_name FROM ...
1
vote
0answers
31 views

Non-existent table that actually exists?

I've been tasked to solve a very difficult and interesting problem. Given is a MySQL database created on the AWS-Aurora service. The MySQL version reported when connecting is 5.6.10. We have an ...
1
vote
1answer
18 views

Query To Show YTD Total Summing Each Month Prior To Current

I need a query to give a YTD total that should sum each month before it. For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would ...
1
vote
1answer
33 views

How to check if column exists in case statement?

I think a case statement would be the best approach to this but am open to any other suggestions. What I am trying to do is this. The query is generated based on a users input regarding the type ...
0
votes
0answers
37 views

MySql Group By Query Taking More 5 seconds On An Indexed Table

I have a simple analytics table which has a little over 2 Million rows and currently growing at a pace of 20k rows per day. Please check the table CREATE TABLE IF NOT EXISTS `affiliate_analytics` ( `...
0
votes
2answers
18 views

mysql 5.6 FEDERATED table and mysql.servers correlation

I have no expirence with FEDERATED tables in MySQL. I have database with FEDERATED tables defined and I'm wonder if this tables are used in current env (I have no access to application/developers to ...
0
votes
2answers
39 views

How to remove references to performance_schema.user_variables_by_thread in mysql 5.6?

I have a Jira 6.4.7 instance running on MySQL 5.6.44. I'm in the progress of updating the Jira instance to 7.0.5, which still supports MySQL 5.6. I accidentally hooked up a newer Jira 7.0.5 to the ...
1
vote
1answer
17 views

How to find error rows in bulk insert?

MySQL 5.6: I'm experimenting with copying data from one table to another in different ways. The source table is on a remote server and has about 500,000 rows - I use the federated engine to connect. I ...
0
votes
1answer
11 views

Copying a MySQL 5.6 server settings for a new environment?

I have a MySQL 5.6 Server running on a Windows 8 VM on a steel case server. It's been having issues (The Windows 8, which is then causing the MySQL to have issues). Therefore I was tasked with ...
0
votes
1answer
34 views

SQL delete where not in (…) query not behaving as expected

We have the following query which returns the most recent fares from our database : SELECT f1.id FROM ( SELECT * FROM fares ) AS f1 INNER JOIN ( SELECT origin_id , destination_id ...
0
votes
1answer
14 views

Why mysql-noinstall5.6.x.zip includes ibdata file?

English is not my main language, please feel free to correct me or ask for clarification or a different explanation. I've been messing around with MySQL for Windows and trying to understand how it ...
0
votes
0answers
16 views

How to perform database archieving?

I am using mysql 5.6 in my project since last 10 years. The problem is, database size has reached to over 14GB and so system performance is getting lower day by day. Since I only need to keep only ...
0
votes
0answers
11 views

mysql < dump.sql or source dump.sql consume all system memory

I am trying to restore a large dump (~300GB MyISAM) into a Mysql server version 5.6.48, but if using: mysql < file.sql or mysql> source file.sql In both cases, after starting the process ...
0
votes
0answers
6 views

MySQL: A user cannot see insert to a table that happen after log in_

I have created a kind of primitive batch processor in MySQL, using scheduled events, which runs a stored procedure. Each time the procedure runs, it inserts a log message into a table. All this works ...
0
votes
1answer
10 views

DR For MySQL multi master nodes

I’ve planned to build MySQL master-master replication and need DR for this setup. I was thinking for replication from any of the node as a slave. Please help, which option is best and how to switch ...
0
votes
0answers
20 views

MySQL - How to optimize multiple OR conditions in a query on a large table

I have a table with the following structure: Configuration ( id: binary(16) Primary key, htId: binary(16), raId: binary(16), date, amount ); I have already created these indexes: ...
0
votes
0answers
38 views

Group by with Order by DESC not working using PHP MySQL codes

I have three tables, table one name (tbl_brands) with these columns: and second table name (tbl_loader_attachment) with these columns: and the third table name is (tbl_images) with these columns: I ...
0
votes
5answers
73 views

Why does this mysql query take so long?

I have a MySQL server (ver 5.6) that is well equipped with hardware (Linux, in AWS, 32GB RAM, 56 CPUs), and according to top and iotop, it isn't even feeling warm; yet this query takes something like ...
0
votes
0answers
14 views

MySQL BEFORE Trigger isn't working

MAIN TABLE CREATE TABLE `sec_role_access_m` ( `ROLEACCESS_ID` int(10) NOT NULL AUTO_INCREMENT, `ROLE_ID` varchar(10) DEFAULT NULL, `MODULE_ID` varchar(10) DEFAULT NULL, `FUNCTION_ID` int(10) ...
1
vote
2answers
47 views

Index creation in extremely slow in Mysql Aurora 5.7

I have a table in mysql Aurora 5.7 version . The db instance has very good configuration 488 GB ram (db.r4.16xlarge). Data base is static data base no application is running on this . I have 1.5 ...
1
vote
2answers
31 views

MySQL Server 5.6 Using all or very little of my resources trouble shooting?

I have an application that uses a MySQL 5.6 Server. This server is hosted on a Windows 8 VM on a server in our office closet. A coworker of mine sent me a picture of the mysqlId process consistantly ...
0
votes
0answers
11 views

MASTER-MASTER repliation

MySql in master A ceate user after using replicate-do-db filter on Master A, that user is not showing on Master B by this query-------select User from mysql.user; how to completely access both ...
0
votes
0answers
6 views

on slave repication filter --replicate-do-db= store; give syntax error 1064 on mysql 5.6

--replicate-do-db=store; CHANGE REPLICATION FILTER replicate_do_db = (store); //also by using this query in mysql 5.6 that option on server side give syntax error 1064 how to fix it
0
votes
0answers
17 views

Same server, same query, different response time

I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes. The setup is: * virtual server; * 4 virtual CPU; * 8 GB of virtual memory ; * ...
2
votes
1answer
37 views

Large MySQL database dump/restore [closed]

Got a quite interesting task: Have to dump mysql databases from AWS and restore on a private cloud. Databases are quite big: 160GB, 120GB and 21GB each db.sql dump - all 3 in the same aurora rds ...
0
votes
1answer
23 views

Select data at row number in warning

When running show warnings in mysql, sometimes the results give a row number, such as "Warning | 1265 | Data truncated for column 'identifier' at row 12343". I'm interested in doing something like: ...
0
votes
0answers
17 views

Why can I create this view in two different MySQL databases but not in MariaDB?

I'm copy / pasting code in SQLyog to make the same view in three different databases held on different servers (but which all have identical tables). On my local PC running MySQL version 5.6.12-log ...
0
votes
0answers
11 views

Error : mysql goes away when upload File in MBs [duplicate]

I have a table that store the file content of uploaded file. The datatype of the column is LONGBLOB. when i try to upload file of 9 MB in size show mysql error "mysql goes away". what i am doing in my ...
1
vote
0answers
14 views

Create SCD type 2 table from change log table using SQL

I have a change log table, which captures the transition of a state with its time, for example: user_id,user_status_from,user_status_to, created_date 7cc4d A2 A1 2019-11-03 ...
0
votes
2answers
25 views

Is it possible to set initial index number in a table?

My System database has got a very large amount of data and some tables are really getting very big. I was looking for a way such as when the system will query on those large tables, it will ignore the ...
0
votes
1answer
261 views

MySQL Difference between BEGIN and START TRANSACTION

I'm using RDS Aurora (MySQL 5.6) By default Auto Commit flag is enabled. I have a transaction as follows BEGIN; INSERT INTO .....;// STATEMENT 1 INSERT INTO ......;//STATEMENT 2 COMMIT; I'm ...
0
votes
0answers
26 views

Imported database very slow on a simple query

A production database has been exported to a separate location for various reasons, however the dumped/imported database is performing much slower on some very specific queries. Here is 2 queries on ...
0
votes
1answer
42 views

MySQLdump Exclude master-data

Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static ...
1
vote
0answers
26 views

Pair records by grouping on foreign key

I am stuck on a question I got in a coding challenge. Essentially I have 3 tables; ratings, reviewers, and books. The ratings contain a list of book reviews from reviewers. I need to output a query ...
0
votes
1answer
28 views

how to show combined results of two tables?

I want to show the result as combination of two. Table 1 SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID` FROM au.`invoice` WHERE au.`invoice`.`InvoiceID` > 11126 -- total results 757 ...
0
votes
1answer
20 views

i have error in query [closed]

i am not getting what is the issue in the query probably i am not following the correct way to put the string and char sign , i am inserting the data in c# to local host with where clause please check ...
0
votes
0answers
16 views

Optimize table on large table online DDL Feature

I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB. mysql> show variables like '%tmp%'; +----------------------------+------------------+ | Variable_name ...
0
votes
1answer
47 views

Users Export From Mysql Version 8.0 to 5.7

I want to exports all users from mysql version 8.0 to 5.7. Can you guide me for this activity. I want same password and privs should migrate.
1
vote
1answer
37 views

SELECT gives different results with/without FOR UPDATE in same transaction

I'm sorry but I havent't found a better wording for the title. Given the table a: CREATE TABLE a ( id INT NOT NULL auto_increment PRIMARY KEY, ref VARCHAR(10) NOT NULL, val INT NOT NULL )...
1
vote
1answer
15 views

Master-Master Replication: Missing PK Values [duplicate]

I have configured master-master replication in mysql 5.7.22 and its working fine. I am using below settings for PK values. I found issue in their ID sequence and pk values missing their sequence ...
0
votes
1answer
13 views

MySql8 Trailing String comparison

I am planning an upgrade of a legacy MySQL5.6 Database to MYSQL8.0.19 database. All my testing has been successful but have one issue preventing me from going live. By default in MYSQL5.6 the ...
0
votes
1answer
39 views

calling mysql procedure return empty result

when i call procedure it gives me empty result call hussian('prstate','table_1','column1,column2,column3',1) it run but gives empty result, please help me... DELIMITER // CREATE PROCEDURE ...
-2
votes
2answers
30 views

i want fetch json data in mysql [closed]

I am using MySQL version 5.6.41-84.1-log, I want to fetch single column of JSON data example: I want to fetch date only id | amount_detail ---------------- 1 | {"1":{"amount":"6000","...
0
votes
2answers
46 views

How to select Overdue Rows with Date Frequencies?

+------------------------+--------+ | Invoice_id | due_date | amount | +-------------+----------+--------+ | 20 |2020-01-18| 1250 | +-------------+----------+--------+ | 21 |2020-...
0
votes
2answers
67 views

mysql 5.6 flushing all dirty pages on ib_logfile rotation creating checkpoint stall

Server version: 5.6.23-72.1-log Percona I have been trying to track down why our mysql server is sharp checkpointing and it seems to correlate with rotating of the ib_logfile. I inherited this ...

1
2 3 4 5
21