Questions tagged [mysql-5.6]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
243 views

MySQL data warehouse calculate difference over years in percentage

I'm trying to create a query to calculate the percentage difference based on a time range (e.g. WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months. My question is how to add the new ...
0
votes
1answer
582 views

When range partitioning, should I drop primary UUID-like key?

I'm aware of the limitation "every unique key on the table must use every column in the table's partitioning expression". So given that my primary key is a UUID, it's going to require a change. ...
0
votes
1answer
190 views

MySQL DB Migration from on-premises to AWS through replication

I am having a production MySQL server with set of 20 databases in on-premises server. Now I require to migrate few databases from production server to AWS RDS without disturbing other databases in one-...
1
vote
1answer
638 views

Replicating from MySQL 5.1 master to 5.6 slave failing because 'INSERT … VALUES (NOW())' results in 'Error_code: 1062'

I am migrating away from some old MySQL 5.1 servers to some new MySQL 5.6 servers. During this process, I'm creating a new MySQL 5.6 slave from an existing MySQL 5.1 slave, using the procedure in the ...
0
votes
1answer
451 views

MySQL - Turkey/Istanbul Daylight Saving Time Change

Until this year Turkey was using daylight saving time, staying UTC+2 on winter period, UTC+3 on summer. This year it is decided to stay only in UTC+3. Our DateTime's are persisted on MySQL (5.26.34) ...
1
vote
2answers
3k views

Query to retrieve index details for a particular column in all tables

There is a column created_at in most of tables. I would like to get the details if that field exists in the table and it is not indexed. Is there any thing can be done with the information_schema to ...
3
votes
2answers
72 views

Stored procedure getting delayed more

Stored procedure and it is getting delayed. Taking nearly more than half an hour if I call the stored procedure in MySQL command line client. This procedure is to update values in two tables drop ...
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
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
1answer
1k views

Meaning of 'In current row format, BLOB prefix of 768 bytes is stored inline.'

I encounter below issue on MySQL v5.6.16: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format,...
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
2answers
2k views

Slow query log is not storing slow queries

Using 5.6.35 MySQL Community Server (GPL). I have setup the local MySQL instance as follows show variables like '%slow%query%log%' slow_query_log ON slow_query_log_file /usr/local/mysql/data/...
0
votes
1answer
1k views

MySQL: Privileges to view Views/Stored Procedure for user Regardless of Host

I've created stored procedures on a (remote hosted - DreamHost) MySQL Database using the HeidiSQL client. One problem i seem to have is that viewing/editing the content of stored procedures is only ...
1
vote
1answer
145 views

Percona Server 5.6.40 restarting with singal 11

We recently migrated our mysql to new hardware and it was running in slave mode for 15 days. We made it the master 11th June. On 13th June it restarted for the first time with signal 11. Stacktrace ...
2
votes
1answer
993 views

Flush Host Connection Blocked, but some connections allowed

We are running Percona MySQL 5.6.30. We've had an issue where a PHP script on a different host was reporting an issue connecting to the database: Database may be down : SQLSTATE[HY000] [1129] Host ...
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 ...
1
vote
2answers
391 views

MySQL Upgrade on Windows

I am having MySQL 5.1 installed on windows box. Now I would like to upgrade to latest MySQL 5.6 version. I am planning to install MySQL 5.6 in the same server with same port 3306. After installation, ...
0
votes
2answers
79 views

Trouble Enabling Query Caching on MySQL 5.6?

Recently I discovered our MySQL 5.6 database is not using any query caching and I wanted to change that. I added [mysqld] query_cache_type = 1 query_cache_size = 50M query_cache_limit = 2M to a ...
2
votes
1answer
2k views

Table partition in aws MYSQL RDS master slave setup

we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this ...
1
vote
1answer
5k views

Top CPU consuming MySQL processes

I am trying to find out the Query which is consuming high CPU, but can't do it so. This command top -H gives me the high CPU consuming thread PID, but i cannot match them with my processlist output. ...
1
vote
1answer
1k views

MySQL help do I use Where not exist or Join? - Laravel

I have two tables as follows: 1) urls id domain created_at ------------------------------------------------ 1 google.com 2016-11-30 00:00:00 2 test.com ...
1
vote
1answer
3k views

How change security group in AWS RDS to allow public access?

I am new to MySQL and AWS. I have set up a new (free trial) AWS account for the purpose of setting up a MySQL database server that I can access from multiple PCs. Per the AWS RDS tutorial, I set up ...
-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
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
1answer
26 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
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
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
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 ; * ...
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
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) ...
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
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
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
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 ...
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: ...
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
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
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
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: ...
1
vote
1answer
343 views

Issue with Time Zone while migrating MySQL on-premises DB to AWS RDS

I am having MySQL 5.6 database with PST time format in on-premises server. Now we are willing to migrate the database to RDS. For this, we have taken backup from physical server and restored in RDS ...
-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
114 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
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?
4
votes
1answer
2k views

Replication stops with GTID_NEXT error after creation/drop of memory table in mysql5.6

We have recently upgraded to mysql5.6.25 from mysql5.5.x/mysql5.1.x on our mysql-cluster. Below is a brief snapshot of our architecture. Since we have upgraded and enabled gtid-mode we have been ...
0
votes
2answers
712 views

High “Pending normal aio writes”

During daily batch operations (mass delete/upsert operations), regular throughput is sometimes affected, e.g. operations such as a single lookup on a PK or a small range (7 rows examined, 1 row ...
1
vote
1answer
132 views

Error when using general and specific GRANT

I'm having troubles with GRANTs when trying to use particular permissions on specific database. There's an user that needs SELECT on all schemas starting with some text; and INSERT, SELECT, UPDATE, ...
0
votes
1answer
122 views

How to Get the First in and Last out in night shift?

I already have the Query for Getting the Day shift but I'm Having a problem in the night shift, Which is this query. SELECT user_id, Min(verify_date) as First_IN, Max(verify_date) as Last_Out from ...
4
votes
2answers
1k views

Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...
5
votes
1answer
484 views

Running Triggers on Slave with Row-based Events in MySQL >=5.6

We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys If one uses statement-based ...

1
2 3 4 5
21