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
0answers
20 views

Rolling back transaction with trx_mysql_thread_id of 0 create deadlocks on update

I'm using MySQL 5.6.41 on AWS RDS. I have seen, recently, lot of transactions ending as deadlocks. Using SELECT * FROM information_schema.innodb_trx; I found that a transaction was always there. +-----...
0
votes
1answer
12 views

MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly

I have a table in Mysql with userid, status and other columns. I keep updating the status of the row as and when the job is complete. Periodically these rows are deleted from the table. JobTable (...
0
votes
0answers
13 views

AWS RDS is showing very high wait/synch/mutex/sql/ values and EXPLAIN statements in performance insights

I'm running a CRON script which checks the database for work and executes anything that needs to be done. It does this across ~500 customers per minute, but we are using AWS RDS with a 16 vCPU ...
0
votes
0answers
3 views

Switching the OS from WSE2012 to WSE2019 on new drive, but Mysql database still on same data drive - Can I just install MySql on new OS and connect?

Current setup - Server Essentials 2012 on Drive 0 partitioned into OS and DB partitions. Mysql innnodb database on the DB partition. New clean install of ServerEssentials 2019 on Drive 1 (the New C: ...
1
vote
0answers
27 views

Mysql “memory usage” keeps increasing

I have 32core CPU 204GB RAM Mysql 5.6 server, hosted on GCP VM. I keep getting RAM high usage issues. I have around 200+ tables, most of which are Innodb. At any time, i see arnd 1k+ active ...
0
votes
0answers
8 views

Aurora RDS MySqlException: Unknown database

We have a .NET Core 3.1 microservice running in an EC2 auto-scaling group (7 instances) which uses MySql.Data NuGet package 8.0.18. It connects to an Aurora MySQL RDS cluster in the same account. This ...
-1
votes
2answers
28 views

Is the foreign key automatically indexed in MySQL?

Do we need to create an index for a foreign key while creating the table in InnoDB of MySQL?
-1
votes
2answers
32 views

RDS MySQL top CPU queries

I'd like to know if there is any way to get the top queries in my instance per CPU. I would see which query has more impact in my instance based on CPU usage. Anyone have any idea if it possible in ...
0
votes
1answer
11 views

Com_commit and Com_rollback count are almost same. What doest it mean?

SHOW GLOBAL STATUS has Com_* variables: The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. When I compare Com_commit to Com_rollback, both are ...
0
votes
1answer
27 views

How can I get rows after a specific row, from a resultset of a specific sort order?

I have a table that has an ID column of type varchar(255) as the primary key. The rest of the columns comprise of text, datetime and varchar(255) types. Let's consider the given query: SELECT ID from `...
0
votes
1answer
34 views

How to efficiently store impressions in db?

For my flask app I need to track impressions, clicks on images. The current implementation that I have is impressions table where I store impression ip, useragent, geo location and all the ...
0
votes
1answer
99 views

ERROR 1064 (42000) You have an error in your SQL syntax during import

New to MySql. I have an export file from server version 5.6.34. I am importing this on a server with the same version. I get an error like: ERROR 1064 (42000) at line 4343: You have an error in your ...
0
votes
0answers
16 views

Best way to restore a MySQL 5.6 database from just its folder and file data? [duplicate]

I had a 5.6 MySQL Server instance that was hosted on a computer that suffered a very bad lightning related power outage. For whatever reason, MySQL 5.6 Server does not want to start/stay running now. ...
0
votes
1answer
31 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
37 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
13 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
29 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 ...
-2
votes
2answers
44 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
33 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
34 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
19 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
52 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
14 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
7 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
11 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
1answer
32 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
42 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
78 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
15 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
50 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
35 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
1answer
14 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
1answer
19 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
38 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
25 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
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
15 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
27 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
325 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
1answer
33 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
56 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 ...

1
2 3 4 5
21