Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
1,079
questions
0
votes
0
answers
76
views
Search from text file contain string and replace row
My database table named VmIpAddress has the following 12 columns
id, hosting_id, server_id, vm_id, ip, mac_address, subnet_mask, gateway, cidr, trunks, tag, net
A row might contain for example
"...
- 113
0
votes
0
answers
76
views
optmizing inner join of two table with million of rows
SO I have two tables - reports (~30million rows) and reportsresponse (~333million row).
I am trying to join this
SELECT
rr.ReportsResponseId,
rr.Text,
rr.type
FROM
Reports r
JOIN ...
- 103
0
votes
1
answer
155
views
Migration MySQL to Oracle 19C error ORA-00942
I'm trying to migrate a simple MySQL test database to Oracle 19c using the migration wizard available in Oracle SQL Developer, the result is that the procedure create the user and the tables but the ...
- 1
0
votes
0
answers
79
views
Can we use AWS SCT (Schema Conversion Tool) with AWS DMS for ETL and migrating data from MySQL to Amazon Aurora for MySQL?
I want to migrate data from on-premises MySQL/MySQL RDS to amazon Aurora for MySQL. While migrating, I need to convert schema as well as do some ETL to get required schema and data.
In ETL I need to ...
- 21
0
votes
0
answers
29
views
Pre-aggregating the events due to resource constraints
I've this table in the main database
create table sales (
`updated_date_key` INT(10) UNSIGNED NOT NULL,
`store_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NOT NULL,
`category` INT(10) ...
- 53
2
votes
0
answers
323
views
Why AWS RDS Mysql is not keeping binlog files for "binlog retention hours" value?
I have an AWS RDS Mysql master with 1 AWS RDS slave and 1 ec2 mysql slave replicating this master instance. I already set the binlog retention hours to 7 days with the command:
CALL mysql....
- 21
0
votes
1
answer
855
views
I get a syntax error whenever I use `CHANGE REPLICATION SOURCE TO` in mysql
This is the MYSQL query:
CHANGE REPLICATION SOURCE TO SOURCE_HOST='3.94.185.28', SOURCE_USER='replica_user', SOURCE_PASSWORD='password', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_L
OG_POS=154
ERROR ...
2
votes
3
answers
446
views
Is it safe to enable Fast DDL in Production
We have a Database running Aurora MySql V5.7. We would like to enable Fast DDL to improve the speed of Adding a Column to a large database. According to the documentation, this feature is Enabled in ...
- 21
1
vote
1
answer
269
views
effectiveness of creating index for ORDER BY column when used with many WHERE clauses
Say I am trying to build a pagination for a simple e-commerce app where user can search, filter, and sort items, and the result is displayed in an infinite scroll UI. I'm planning to use the cursor ...
- 11
5
votes
1
answer
2k
views
MySql Json Replace An array Value
MySql Json Replace An array Value at Specific index if the value matches some condition
Example:
{
"deploy": [
"Infrastructure",
"API Security"
],
"...
- 53
0
votes
0
answers
26
views
Get a set number of rows for each date between a timestamp usng mysql 5.7
Need an sql query which gets two events on each date for a set number of days like 1 week, 1 month.
The result should be sorted in scheduled_date order.
I am using mysql 5.7
Sample Table :
Event_Name
...
- 1
2
votes
0
answers
93
views
how can I check when was my db(my sql db) last restored successfully in aws
how can I check when was my db(my sql db) last restored successfully in aws?
I want to know how old db we are using , like for ec2 instances we can see the launch time of server, but how can i do that ...
- 21
0
votes
1
answer
114
views
How to reliably restore slave to master in MySQL Multi Source Replication?
I have multi source mysql replication setup configured, so I have this big server which act as a slave to other smaller server. The setup looks similar to below.
Everything is working fine— until one ...
- 103
3
votes
0
answers
178
views
Slower delete performance in MySQL8 compared to MySQL5.7
I'm looking for some insights into why deletes from a fairly simple table seem to be much slower in MySQL8 than in MySQL5.7
Apologies in advance if this is a little fluffy at this stage and I've ...
2
votes
1
answer
69
views
How to calculate latlong difference from last 15 min ago and last 1 min ago without using correlation subqueries
Problem
I would like to find logs that are active and do not hit a distance quota of 12.5km, comparing from last 15 min ago and latest (within last 1 min ago)
Sample data
select version();
version()
...
- 79
0
votes
0
answers
87
views
I want to simplify my sum query using loop function
I have a query to summerize my data based on month between year 2021 - next years (depend on the real date)
i used simple query like this to summerize the amount monthly
SUM(IF(pt.description like '%O-...
4
votes
2
answers
312
views
Group BY DESC being ignored after join - Mysql 5
The problem I wish to solve
I am trying to select the trucks that are on duty. I tried using sub queries. However, I heard that it has bad performance. I also tried adding a is_onduty flag to the ...
- 79
2
votes
0
answers
245
views
MySQL 5.7- vs 8.0 referenced table update performance difference
I recently upgraded my MySQL server from 5.1 > 5.7 > 8.0. The server has over 3k databases and more than 1TB of data. All tables are InnoDB.
After upgrading, I noticed huge performance ...
- 21
1
vote
1
answer
27
views
Syntax issues when uploading backup from mysql5.7 to percona xtradb 5.7
Greetings to those readying the thread and thank you for devoting your time to answering the question.
I have encountered a problem when uploading a MySQL 5.7 backup database to a Percona xtradb 5.7 ...
2
votes
1
answer
56
views
Can I edit the binlog retention period on just one replica?
Can I just set the binlog retention period for the final replica in my chain differently than it is for all the others? It is set at a week for all replicas but for the one I am using I need it to be ...
- 133
0
votes
0
answers
135
views
Mysql innodb - Crashed recovery help
It is really painful our mysql wp db crashed!
I backed up /var/lib/mysql and tried innodb_force_recovery up to value 4. But no luck
When I try backing up using mysqldump it loses connection while ...
- 101
0
votes
0
answers
138
views
MySQL 5.7.x: password expiry set, user table column `password_expired` shows "N" when password is expired. Why?
This seems like a bug, but I can't find any reports of this anywhere and given the age of this server, I'm curious if I'm just misunderstanding the purpose of this flag.
The following example (real) ...
- 103
0
votes
1
answer
85
views
mysql -V shows a different version than mysqld -V
I'm running centos 7 with mySQL 5.7.xxx. mysql -V shows Ver 14.14 Distrib 5.7.26-29, but mysqld -V shows Ver 5.7.11-4. systemctl status mysql and systemctl status mysqld both show the same running ...
- 101
0
votes
0
answers
24
views
Mysql Query need to optimize
I have below query having proper indexes (all columns in where clause), But still query is very slow.
Can anyone help me to optimize this mysql query?
SELECT
centerID,
compID,
...
- 1
0
votes
0
answers
140
views
How can I speed up INSERT INTO ... SELECT ... ORDER BY queries?
I recently learned that it is advisable to have deterministic queries when using a replication instance. Therefore, we started adding ORDER BY to our INSERT INTO (...) SELECT (...) FROM X statements. ...
- 111
0
votes
1
answer
232
views
Normalizing an existing table with strings data
I have a table that has two string columns that repeat a lot, and in the next stage, some counts and conditions need to be done, lets say they are called company_name and person_name.
The table has ...
- 111
4
votes
2
answers
154
views
Incorrect database name `` after mysql@8 upgrade
After MySQL upgrade we ran into a breaking change as we have some really strange queries like this one:
use somedatabase;
SELECT * FROM `Table` where ``.`Table`.`id` = 1
With MySQL@5 this query runs ...
- 143
1
vote
1
answer
1k
views
Set into multiple rows [duplicate]
I have the following data
SQL> SELECT id, relatedIdInASet from tab;
+----+------------------+
| id | relatedIdInASet |
+----+------------------+
| 1 | [12,34,56] |
| 2 | [11,12,22,34] |...
- 513
1
vote
1
answer
453
views
Why is MySQL suddenly getting a lot of queries in a "Waiting for table flush" state? How can this be fixed?
Recently my database started, several times a day, to get stuck with a lot of queries in a Waiting for table flush state.
Usually there's a very long query in execution that when killed unlock the ...
- 121
0
votes
1
answer
100
views
MySQL add key to a table that is in use
I am running MySQL server v5.7.18 on CentOS 7.
One of the tables is used as operations log for a web API, and has grown to 20 million rows.
Users search this table from a front end app which runs a ...
- 103
0
votes
0
answers
170
views
mysqldump extreme export slowness
I'm using MySql 5.7.33 on VmWare Vsphere Vm with 20GB RAM and 8 cores.
These are my MySql settins
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
...
0
votes
2
answers
529
views
What is the definition of latin1 character set in MySQL 8.0 and MySQL 5.7?
I am trying to fix some corruption to character fields in my database after a migration. I suspect that part of the problem is that fields with UTF-8 data were interpreted with the MySQL latin1 ...
- 191
0
votes
0
answers
28
views
Is there any need of log table
I am developing an application that has database of mysql having orders and orderdetails table. order table having column orderStatus. My question is whether it is necessary to create another order ...
- 1
0
votes
1
answer
33
views
How to break GTID Based replication without disabling read only on replicas
How can we break GTID based replication OR cause replication error on MySQL 5.7 without disabling read only on replicas / slaves.
2
votes
1
answer
28
views
Struggling to create an efficient index for this MYSQL query with JOIN and grou
I'm running a MySQL query on a dataset that currently has approx 26M rows and is growing by 5-10M rows per year.
This is the primary table:
CREATE TABLE `ledger_entries` (
`id` int(11) NOT NULL ...
- 21
1
vote
1
answer
92
views
Match binlog position in 2 different MySQL replicas
I'm trying implement MySQL upgrade approach that would require either no downtime at all or it would be counted in seconds. One of the problems is that these databases are RDS MySQL databases so there ...
- 121
1
vote
1
answer
2k
views
MySQL remove quotes from middle of a string whilst maintaining the rest of the string intact
I have some fields in a table that contain data similar to the following:
{"item1":"stuff","item2":"12345","item3":0,"item4":"this ...
- 2,086
0
votes
1
answer
122
views
Optimize fetching many rows from table
Problem
I have the following table definition:
id BIGINT
resource_id INT
timestamp DATETIME
data JSON
The primary key is id and I have an index on (resource_id, timestamp). The data ...
0
votes
0
answers
96
views
Deadlock because of fulltext index - FTS_DOC_ID_INDEX in MySQL
We are having an issue with one of our innodb tables. This table has primary key on integer column (and this one is not auto increment column ) and fulltext key on one of the text columns, Application ...
- 47
1
vote
2
answers
117
views
MySQL - archiving data in tables
I am a developer for an insurance app. We are using AWS RDS with a MySQL implementation. The scenario we would want is to automatically archive data from one table to another. Something like this:
Let'...
1
vote
0
answers
654
views
How to debug a huge MySQL replication lag?
We have an AWS RDS and a corresponding Replica. Once or twice a month the replication lags and does not recover from its own. Our "solution" is to create a new replica and delete the old one....
- 111
1
vote
1
answer
1k
views
What is MySQL wait state 'wait/synch/mutex/innodb/lock_mutex'
We have an application (on Amazon RDS MySQL 5.7; db.m5.24xlarge; 96 cores) that takes in a lot of data, in parallel processors+queues. It mostly goes to one table, with primary keys not spreading over ...
- 191
3
votes
2
answers
4k
views
Why MySQL 5.7 timestamp not null requires default value?
When I try to create a table with non null timestamp, it requires me to add a default timestamp to it or use the implicit default. But I am not sure why this is necessary.
Have a look at https://...
- 131
0
votes
0
answers
249
views
Best way to duplicate an huge Amazon Aurora 2 (MySQL 5.7) DB into an Amazon Aurora 3 (MySQL 8.0)?
I would like to upgrade to Amazon Aurora 3 (MySQL 8.0) an Amazon Aurora 2 (MySQL 5.7).
In-place upgrade isn't available, Snapshot upgrade isn't possible neither because the production machine has the ...
- 209
3
votes
0
answers
72
views
Is there any way to optimize this query (generated by Drupal)?
I have an older system built on drupal, and there is a particular (generated) query that takes a long time - never completing in some cases.
The query:
SELECT
node.nid,
node.created,
node....
- 1,202
0
votes
0
answers
140
views
MySQL Creating Unnecessary Sort Index When an Aggregate Query is a Subquery
I have a complicated aggregate query, using the functions SUM and GROUP_CONCAT, that starts returning results in a reasonable amount of time by itself. But when this query is wrapped in another query, ...
0
votes
1
answer
122
views
Get combined checksum of first n rows in mysql
I want to get a checksum of supposing the first 100 rows combined checksum.
Saw this mysql-rows-checksum, but this gives a checksum for every row. I want only one checksum for given n rows combined.
- 101
1
vote
0
answers
381
views
How to recover corrupted innodb table from .ibd file?
I forgot to dump the databases before upgrading mysql from 5.5 to 5.7. After upgrade, I ran mysql_upgrade, which ended with the error:
Error occurred: Error during call to mysql_check.
It seems one ...
- 111
1
vote
0
answers
507
views
Is there a way to get AWS Aurora Mysql 5.7 compatible to cause timestamps to be updated to current timestamp when null is explicitly passed in?
All examples are technically from AWS RDS Aurora Mysql compatible, however I believe the issue is the same for non-aws mysql.
In Mysql 5.6 and 5.7 with the exact same values for sql_mode (the 5.6 ...
- 111
0
votes
0
answers
200
views
Writing a ledger with high concurrency
I'm trying to achieve a high level of writes on a single account for a ledger, however, if there is a high level of concurrency, things start to slow down significantly.
My current setup is basically ...
- 1