Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
1,078
questions
1
vote
1
answer
465
views
Mysql row-based binary logging behaving different in some versions of mysql (rows grouping)
Mysql row-based binary logging seems to have a different behaviour when grouping rows changes in some versions of mysql.
Let's assume the following statement update three rows:
UPDATE table_name ...
CommunityBot
- 1
0
votes
1
answer
458
views
Options for archiving old MySQL 5.7data
I have an web application that has a 1TB MySQL 5.7 production database.
We are running into performance issues due to table data size. We are planning to archive old data from different tables. ...
CommunityBot
- 1
0
votes
1
answer
7k
views
MySQL ZERO warnings
I'm getting the error below in my MySQL log file.
[Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with ...
CommunityBot
- 1
1
vote
1
answer
456
views
MySQL Database on RDS with memory issues
I am facing high memory utilization issue on RDS MySQL server. We are having other issues too, can someone give me a help?
My database has 120GB, including indexes.
Can anyone help me out to ...
CommunityBot
- 1
0
votes
2
answers
377
views
Why does mysql binlog use different isolation level?
Default isolation level:
mysql> SELECT @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
Binlog:
...
- 13.9k
0
votes
1
answer
948
views
Very slow large stored procedure
I have a rather large stored procedure that processes over a hundred thousand records. This SP has been running beautifully until last week when the database server was hit with a ransomware attack. ...
CommunityBot
- 1
1
vote
1
answer
631
views
MySQL Indexes on all column combinations
I have a table that basically represents the log of records
CREATE TABLE records
(
id varchar(36), -- uuid
text blob,
user_id bigint,
cluster_id bigint,
status_id ...
CommunityBot
- 1
1
vote
1
answer
405
views
MySQL 5.7 Can slave be ahead of master during replication
From my understanding, once commit() is received from client, transactions are written to binlog first before any commit is done to InnoDB.
In this case, is it possible for slaves to be ahead of ...
CommunityBot
- 1
0
votes
2
answers
730
views
If I need to restrict access to certain databases?
So I've just created a MySQL server. I also created some users only having specific access to certain databases, but do I need to restrict access to default databases to the users I've created?
If so, ...
CommunityBot
- 1
0
votes
1
answer
433
views
Can't load local file into mysql database
So I will preface this by saying that I'm not all that skilled in MySQL (or regular SQL for that matter), but I'm trying to load data from a .txt file into my table that I keep for ticket sales. When ...
CommunityBot
- 1
2
votes
2
answers
6k
views
Disable InnoDB deadlock detection
I would like to disable InnoDB dead-lock detection and automatic rollback, as per the requirement we like to rely on innodb_lock_wait_timeout (because of heavy concurrency).
The lock wait timeout ...
- 1,663
0
votes
1
answer
487
views
Sync of Master Master replication when Primary node comes up MySQL 5.7 Enterprise
I have a Master (say Node A) - Master (Say Node B) asynchronous replication setup with ROW based logging method.
Node A has been setup as Write/Read node and Node B has been setup as Read Node Only.
...
CommunityBot
- 1
0
votes
1
answer
585
views
Exporting mysql query result to .txt file
I want to export query result into .txt file
The path of .txt file and query is dynamic
DELIMITER //
create procedure test(filepath text , _query text)
BEGIN
SET @sql = CONCAT(_query, ' into ...
CommunityBot
- 1
2
votes
2
answers
1k
views
Nested loop on primary key
CREATE TABLE `request` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`created_by` int(11) unsigned DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
...
CommunityBot
- 1
1
vote
3
answers
159
views
Sudden spike in BinLogDiskUsage
Two days ago our MySQL RDS instance saw a sudden spike in BinLogDiskUsage, CPUUtilization, NetworkReceiveThroughput and NetworkTransmitThroughput.
The result was a database that was extremely slow to ...
- 101
0
votes
0
answers
30
views
Upgrade from MySQL 5.6 and 5.7 to MariaDB
We have several servers with MySQL 5.6.51 and several with MySQL 5.7.39. What are the right steps to upgrade to latest MariaDB LTS 10.11? I think we can't go directly to 10.11 or?
MySQL 5.6.51 > ...
- 69
1
vote
1
answer
2k
views
DROP TABLE IF EXISTS hangs on Waiting for table metadata lock
I'm using a dockerized MySQL 5.7.13 as part of an integration test of an application. The way the system works is as follows:
I start up the main application. It opens up a connection to MySQL, but ...
CommunityBot
- 1
0
votes
1
answer
859
views
Changes between Mysql version 5.1 TO 5.7
I have recently migrated Mysql 5.1 to Mysql 5.7. DB Migration was successful but when the application started firing few selected queries, Mysql started taking hours to execute them for which ...
CommunityBot
- 1
0
votes
1
answer
444
views
MySQL replication still failing after new import
I had an issue with my MySQL replication from Master to Slave. An App we had by mistake wrote to the Slave and deleted some data as well, causing errors during the replication (delete errors, primary ...
CommunityBot
- 1
1
vote
1
answer
958
views
How to install keyring_aws plugin in MySQL?
I am looking for a way to install keyring_aws plugin in mysql community edition.
Is it possible?
I gone through couple of docs,
https://dev.mysql.com/doc/refman/5.7/en/keyring-aws-plugin.html
...
CommunityBot
- 1
1
vote
1
answer
47
views
Will Dropping a corrupt table free up space in MySQL
We have a MyISAM Table in our database which has around 97 Gb worth of data with resulted in 95% disk utilization. Due to this we started clearing the data from the table by deleting the rows but this ...
- 13.9k
0
votes
1
answer
3k
views
Import issue: Missing columns values from CSV file
I have a problem with the importing of a CSV file to my table in MySQL. The values (3) of the columns (var1, var2, dates) are not loading, as shown in the image below:
First I created the table,
...
CommunityBot
- 1
0
votes
0
answers
14
views
Need to build MySQL replication with TSL cert between onPremises server as Master and Azure flex server as Slave
I have a requirement
Need to build MySQL replication with TSL cert between onPremises server as Master and Azure flex server as Slave .
On Master & slave host ssl is enabled . also i have followed ...
0
votes
1
answer
526
views
mysql has two pid files
I just finished re-building my (5.7) slave from a hot backup. When I issue
systemctl start mysqld
it just "sat" there for over 20 minutes. It usually takes less than 10 seconds for the ...
CommunityBot
- 1
3
votes
2
answers
4k
views
CPU,Memory and disk I/O utilization for each query in mysql
How can we identify the exact CPU,Memory and disk I/O, when a query is executed. I am using mysql 5.7.20 community version. I am able to get only cpu details for session level by setting SET profiling=...
CommunityBot
- 1
3
votes
1
answer
2k
views
wait_timeout kills session in SLEEP MODE or all connection ?`
Do wait_timeout session clear all running session or only sessions which are in SLEEP mode for specified seconds ?
CommunityBot
- 1
0
votes
1
answer
646
views
MySQL Multi Master Replication
I have 4 servers running MySQL 5.7 in total , servers 1,2,3 each has single database on them For example:
Server 1 has the DB x_1
Server 2 has the DB x_2
Server 3 has the DB x_3
Server 4 has 3 ...
CommunityBot
- 1
1
vote
1
answer
7k
views
how do I connect to mysql database from visual studio without requiring SSL connection?
I have established a link between Mysql database and visual studio on my windows 10 machine.Unfortunately my application which is a desktop app can't open a connection to the DB. I keep getting this ...
CommunityBot
- 1
0
votes
2
answers
2k
views
MySql replication Failure
I have a mysql (Ver 14.14 Distrib 5.7.29, for Linux (x86_64)) primary -> secondary replication setup. Initially the replication was working successfully until we had to restart the primary database. ...
CommunityBot
- 1
0
votes
1
answer
464
views
config file and parameters for ndb_desc
I'm trying to execute ndb_desc from API client. The config and parameters are as below:
my.cnf in mysqld:
[mysql_cluster]
ndb-connectstring=192.168.1.1,192.168.1.2
server-id=5
log-bin=mysql-bin
...
216
votes
5
answers
246k
views
'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
I am trying to backup mysql using the command
mysqldump -u root -p database_name > backup.sql
, but it is throwing an error:
'Access denied; you need (at least one of) the PROCESS privilege(s)
...
0
votes
2
answers
291
views
The UPDATE queries is 50x slower than its SELECT query
The UPDATE queries is 50x slower than its SELECT query.
I have a table named sync_read and the have the following columns
id
club_id
created_at
user_id
queue_id
The table have the following Indexes:
...
CommunityBot
- 1
9
votes
4
answers
7k
views
How import mysqldump .sql file into MySQL 5.7 when exported from MySQL 8
I accidentally installed MySQL 8 and did a ton of work there, creating a lot of data. I then backed up all of the databases using mysqldump.
Later, I realized that I needed to downgrade back to MySQL ...
- 101
0
votes
1
answer
306
views
Mysql Replication trigger not executing on slave
Mixed replication is enabled.
MySQL master has a table Products that has triggers on update and delete. On slave database we have different trigger on same table on update and delete.
Master database ...
CommunityBot
- 1
0
votes
2
answers
1k
views
Error on updating a field with binary values
I have a problem on emoji showing on webpage after migration to new DB Server. But I have a workaround. I will get the binary values of a certain field of TABLE1 on our OLD DB Server. Then update the ...
CommunityBot
- 1
1
vote
1
answer
2k
views
Unicenta opos 4.6.1 can't connect to mysql 5.7.36
Basically I can't get Unicenta opos to connect to a fresh install of mysql server on Debian 11. I have set up a user and granted privileges in mysql, created a new database named MAINDB and for some ...
CommunityBot
- 1
0
votes
1
answer
1k
views
MySQL Group Replication multi-primary - add new member
I am configuring MySQL Group Replication in multi-primary mode to replicate all databases between two members and allowing writes on either.
I have my two members IP addresses configured in the ...
CommunityBot
- 1
3
votes
1
answer
774
views
MySQL using variable in SELECT command with view affecting performance
sorry if this has been asked already. I've just moved from MySQL 5.6 to 5.7.17 and I'm struggling to understand this issue.
I have a view "delete_me_test_" which has around 26k rows.
SELECT
...
CommunityBot
- 1
1
vote
1
answer
961
views
UPDATE random rows
I'm working on creating a more efficient way to UPDATE random rows in a ticket allocation website we have.
Currently, we pre populate the tickets table with ticket numbers in subsequent order, i.e 1,2,...
CommunityBot
- 1
1
vote
2
answers
52
views
How to Identify Tables with Index Size Issues during MySQL 5.7 to 8.0 Migration?
I'm trying to migrate from MySQL 5.7 to MySQL 8.0 for my application's database. However, the precheck failed with an incompatibility error "25," stating that tables with redundant row ...
- 76.9k
0
votes
2
answers
320
views
How mysql store data and their encoding
I have few databases in latin1 and i will migrate it to utf8. I have some characters like 'œ' that are utf8 characters.
I want to know how mysql store the encoding ?
Cause if it stores utf8 characters ...
CommunityBot
- 1
1
vote
1
answer
422
views
How to change directory of binlogs on Ubuntu 18.04
Current config:
[mysqld]
datadir=/data/mysql
log-bin=test
Updated config:
[mysqld]
datadir=/data/mysql
log-bin=/binlogdata/test
log-bin-index=/binlogdata/test.index
What I did was:
Stop mysql ...
CommunityBot
- 1
0
votes
0
answers
8
views
How Innodb Concurrency Tickets working in sorting by mysql?
Innodb concurrency tickets how reduced when the sorting performing query running? is tickets applied for sorting or not? If applied how it was calculated by mysql and is there any performance issue ...
- 1
0
votes
1
answer
474
views
Why does my JDBC keep setting session variables?
I'm looking at the Performance Insights for MySQL Aurora on AWS and among the top queries I see:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL READ ...
CommunityBot
- 1
2
votes
1
answer
4k
views
Restart event scheduler and alter event when slave being promoted to master
I am using innnodb cluster. It is good that every time a R/W node failed the R/O node will being promoted to R/W.
However I have to restart scheduler and alter event enabled (originally slave side ...
CommunityBot
- 1
0
votes
1
answer
550
views
resolve lock wait timeout delete innodb table
In an application I work with, DELETEs are rare, though they seldom happen. When they do, they remove few rows but from several related tables, like 8 to 10.
Most of the times, mainly when the ...
CommunityBot
- 1
0
votes
1
answer
4k
views
Partitioning with AUTO_INCREMENT primary key
I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table ...
CommunityBot
- 1
2
votes
2
answers
905
views
Optimising MySQL count() with INNER JOIN (550k rows)
I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the ...
CommunityBot
- 1
0
votes
0
answers
10
views
What is the Strategy for Rotating MySQL 5.7 certs with Active/Passive Pool?
Aside from migrating while we upgrade to 8.0, which is an option, what have people done workflow/strategy to rollout and rotate MySQL SSL credentials with zero downtime?
- 169
1
vote
0
answers
70
views
Moving from MySQL 5.7 to MySQL 8.0
I'm currently running MySQL 5.7 in one of the key servers. This version reaches end of life soon, so it's time to move to 8.0. Or give it a shot and jump on 8.1?
This one is also running on dated ...
- 13