Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
1,079
questions
1
vote
1
answer
76
views
Very different performance between two similar queries
We are working on a view (multiple joins in this view). And I have these two queries:
Query 1:
select *
from v_investment_planning_ticket v
where v.ticket_id in
(...
- 11
-1
votes
1
answer
822
views
InnoDB: Assertion failure on executing select Query - MySQL 5.7.31
I am using pt-archiver for daily archiving of tables, but while selecting data from one tables I am getting following error and it restart mysql instance
2021-07-07 13:21:17 0x7fe0dffdc700 InnoDB: ...
- 149
0
votes
1
answer
309
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 ...
- 561
1
vote
3
answers
287
views
How to avoid deadlocks when generating consecutive ticket numbers?
I have implemented a ticketing system where when you join an event and placed an order depending on the quantity, a n of entries will be generated. Example: you place an order with 10 quantity, a 10 ...
- 11
3
votes
0
answers
1k
views
How to estimate table row size in MySQL InnoDB?
My particular problem that I'd like to solve is a table with very wide column (sometimes > 1000 columns) which requires me to split the table. In order to do so smartly, I'd like to split right ...
- 131
0
votes
0
answers
908
views
InnoDB deadlock with concurrent SELECT .. FOR UPDATE queries
I'm currently having deadlocks during high load scenarios.
These deadlocks occur even without any update, insert or delete statements
Also changing isolation level to read committed does not help ...
- 101
1
vote
1
answer
166
views
Inconsistent results between MySQL and MariaDB when using variables and order
I'm building an accounting table and I need to show the current balance on each transaction.
Everything is working fine in my local environment (Windows 10 + MySQL 5.7.19), but the same query gives me ...
- 113
0
votes
1
answer
95
views
auxiliary index table corrupted?
Mysql err log is kept throwing errors like this:
2021-06-30T23:29:32.795948Z 3018 [ERROR] InnoDB: Failed to find tablespace for table `my_db`.`FTS_00000000000022f5_00000000000030de_INDEX_1` in the ...
- 103
2
votes
1
answer
3k
views
MySQLdb._exceptions.OperationalError: (2006, '') when closing a SQL query. Is it because of the connection?
Salut les administrateurs ! I have a question (and an issue) When closing my SQL query in my ETL I get a connection error while it seemed I was able to get connected:
c.execute("""
...
0
votes
0
answers
92
views
Is it possible to use check_user_proxy on AWS RDS?
My top-level goal is to enable two-user (zero-downtime) secret rotation for RDS users. To recap/summarize the approach, you always have two accounts in your DB that have equivalent access. You switch ...
- 101
0
votes
2
answers
3k
views
Sum properties of an array of objects, conditionally
I have a JSON field where each entry is of the form
[
{date: '2021-06-28', value: 1},
{date: '2021-06-27', value: 2},
{date: '2021-06-26', value: 3},
...
]
I'm trying to sum the values ...
- 11
0
votes
0
answers
367
views
MySQL mass delete from large innodb table very slow
I have a innodb table which contains more than 120million rows including duplicates. Let's name it as big_table. I fetch all the duplicates into another table(temp_big_table) which contains nearly ...
- 101
2
votes
2
answers
823
views
How to compact/group by the results of a query by columns' values?
I have the following table (MySQL version >= 5.7.X, using DBeaver 21.1.0 to manage the db):
n_brand
population
actions_completed_by_unique_user
pepsico
pepsicoeur
1
pepsico
pepsicoeur
1
pepsico
...
- 123
0
votes
0
answers
29
views
Why doesn't mysqld produce output?
I have been upgrading Percona mysql to 5.7 on several servers recently, and often it goes like:
Stop the daemon and install new packages.
Start the daemon, which fails
su - mysql and run mysqld ...
- 231
0
votes
0
answers
452
views
Check elapsed time for mysql queries
I'm not much familier with mysql. We hit a strange scenario. There were lot of 'select' queries hitting database at the same time.All were same 'select' with some different parameters value and they ...
- 348
0
votes
3
answers
2k
views
Get the most recent 2 entries for each group based on timestamp
I am using MySQL 5.7 database with the following columns
item_symbol
source_date
price
I have composite primary keys which are a combination of item_symbol and source_date. I have around 100k ...
- 103
-2
votes
2
answers
89
views
MySQL Upgrade Procedure in a production environment [closed]
Am new to the role and employers have asked me to come up with a plan to upgrade the MySQL 5.6 on the production server to 5.7. It has partitions and MASTER-MASTER replication setup and has around ...
- 21
1
vote
1
answer
3k
views
Mysql Innodb Error Number 24 Means 'too Many Open Files'?
I was in the middle of restoring my database using mysqldump and it errored out this
ERROR 1030 (HY000) at line 637885: Got error 168 from storage engine
And when i check the logs, i am seeing this :-...
3
votes
1
answer
5k
views
Improve MySql query performance in large table
I have a MySql database that is used to store events from an app, since we created it, we only inserted and selected data, we've never deleted any rows. I'm not a DB admin and there's no DB admin in ...
- 183
0
votes
3
answers
166
views
Force MySQL index on OR query?
I have a query that could be resolved 99% of the time using an index but needs a table scan for a few queries:
SELECT * FROM mytable WHERE idx=123 OR name like "%foo%" limit 1
Logically ...
- 152
1
vote
1
answer
953
views
How to alter AWS RDS tables in `mysql` schema?
I want to alter tables like mysql.rds_sysinfo, mysql.rds_monitor, mysql.rds_heartbeat2 which belong to mysql schema.
The alter sql for that is:
ALTER TABLE mysql.rds_sysinfo ROW_FORMAT=DYNAMIC;
But ...
- 45
1
vote
1
answer
3k
views
ERROR 1180 (HY000): Got error 5 during COMMIT
We're using Galera cluster and I'm running below statement to make a new table:
create table tab2 select distinct id , report from my_table ;
It runs for sometime and after that it fails/cancel ...
- 348
2
votes
1
answer
292
views
MySQL 5.7 won't restart, even without Apparmor disabled on Ubuntu 18.04
After a server restart, while MySQL shows to be running, it's totally inaccessible and everything i've tried to do to rectify hasn't resolved the issue.
I'm going to try to make this has easy to read ...
- 133
0
votes
1
answer
118
views
MySQL In-place upgrade creates a new instance instead of upgrading the existing one
I am doing an in-place upgrade from mysql 5.7.26 to 5.7.33. I have done from 5.6 to 5.7 for serveral hosts and did not face this issue.
Followed below steps
Took a VM snapshot and mysqldump as well.
...
2
votes
1
answer
368
views
Data truncation on spatial query after updating from MySql 5.7 to 8.0
I have the following table:
create table places
(
id bigint auto_increment
primary key,
position point SRID 4326 not null
);
create spatial index position
on places (position);
--...
- 151
3
votes
1
answer
4k
views
Is TLS 1.2 supported in MySQL 5.7 community build
As a security best practice, I am exploring enforcing TLS 1.2 communication in MySQL 5.7.33 in my environment.
From what I searched in internet, I could see TLS 1.2 is supported in MySQL 5.7 builds ...
0
votes
0
answers
24
views
Remove tables intersection [duplicate]
Given two columns, t1 with distinct values, but a superset of t2, how is it possible to operate over t1 to subtract the elements of t2 of t1? I utilize MySQL 5.7
CREATE TABLE IF NOT EXISTS t1 (
id_ ...
- 117
0
votes
1
answer
476
views
How to Update Date in each row with different dates in MySQL
I want to update date column of the table with dates in the increment of 1 for all rows.
How do I do it in MySQL?
Table: tbl_question_of_the_day
Column: date
So the output that I want is:
Row 1: Today
...
- 101
0
votes
1
answer
331
views
MYSQL 5.7 Partition a table based on partition key of another related table
Please understand a little background before getting to know the problem.
TABLE transaction (
t_id: AUTO INCREMENT BIGINT
t_execute_on_date: DATE,
timestamp: DATETIME,
... other columns
)
The ...
0
votes
1
answer
262
views
Online schema changes for cloudsql DB
I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime.
What I have tried so far:
I tried with pt-online-schema-...
- 348
-1
votes
1
answer
253
views
Difference between rows of a table - MySQL 5.7
I try to decrease the rows of a table by its previous value when it exists. For the first row element, consider 0. Could you help me? Create the table below as example.
CREATE TABLE IF NOT EXISTS t1 (
...
- 117
1
vote
1
answer
476
views
Why is the execution time on Datagrip longer on MySQL 8 than MySQL 5.7 on a new Linux Server?
I am migrating from a 4 year old setup to a brand new high performance server and am experiencing slower performance than on the old machine.
The old setup is a Ubuntu 16.04 Server on bare metal Intel ...
- 293
1
vote
1
answer
6k
views
Storing emojis in MySQL
I'm new to MySQL database. I want to store emojis in my database. I already have data in my database, and I don't want to lose it.
I don't know the proper way of storing the utf8mb4 character-set ...
- 13
1
vote
0
answers
3k
views
How can I downgrade an AWS Aurora MySQL 5.7 2.09.2 to an earlier 5.7 version?
During a test, I applied a branch of our Terraform description that upgraded a couple Auroras from 5.7 2.04.x to 5.7 2.09.2 without losing the data (as expected). When attempting to apply the main ...
- 111
0
votes
0
answers
78
views
Unable to restore a big table in mysql
I'm new to mysql.I have a 127GB size ibd table file to restore. While running "alter table shiwangini_test2.mytable import tablespace ;" -it's just running out of memory and stopping mysql ...
- 348
0
votes
0
answers
772
views
Data truncated after importing a MySQL 5.7 dump into MySQL 8
I am trying to import a 12G MySQL 5.7 dump (on ubuntu 16.04) into a MySQL 8.0 database (on ubuntu 20.04).
Half way through the import exits and returns the following error:
ERROR 1265 (01000) at line ...
- 293
0
votes
1
answer
141
views
GCP Cloudsql Mysql Replica unresponsive after mass delete on Master
Directed here from S/O
We have a Master/Replica configuration for Mysql innodb(5.7.32) databases in Cloud SQL... We have a single Table (let's call it Master table) partitioned on Two keys having both ...
- 11
0
votes
0
answers
167
views
SELECT ALL FROM bogus_table LIMIT 0
I'm not a developer but a system administrator,
I found this query SELECT * FROM bogus_table LIMIT 0\G in the MySQL-slow-query.log in the production server.
I asked the developer about this query and ...
- 48
0
votes
1
answer
32
views
ERROR: Too many connectioons
I'm new to mysql. So, sorry if you find it an easy/silly question.
I have a quick question since I encountered a strange scenario. I want to make sure does it actually happens or did I do the right ...
- 348
3
votes
2
answers
3k
views
Performance issues from RDS Aurora 5.6 to Aurora 5.7
Recently upgraded one of my RDS instances to aurora mysql 5.7 from 5.6 and I've had to triple the instance class while I try to resolve this (cpu eventually pegs and never declines).
Most reads are ...
- 131
0
votes
3
answers
354
views
How can I filter query results based on whether they have ALL values from another table?
For example, some actions that users can do, require a set of permissions.
Users are stored in the Users table:
UserId
1
2
and permissions in the Permissions table. Users can have permission A, B, ...
- 101
0
votes
2
answers
796
views
Why is MySQL InnoDB table copied despite using ALGORITHM=INPLACE for ALTER TABLE
I run an ALTER STATEMENT on an InnoDB table on a MySQL server version 5.7 that:
drop some foreign key constraints
drop some indexes (non unique)
add some columns (without index or foreign key ...
- 103
0
votes
1
answer
32
views
Join with keys of both tables [duplicate]
I try to bring to tables together in a join-like way. Consider the column id_ as "on clause" key. In the tables below, there are more id_s in table 1 than 2. I would like to have all ids and ...
- 117
0
votes
0
answers
420
views
Problem with connect mysql databases of remote
I'm getting error with connect remote mysql database. Firstly;
1-) I use windows 10 machine and my remote server also windows 10 machine.
2-) My database at locale at remote windows machine with root.
...
- 1
0
votes
0
answers
200
views
MySQL Error 1251 on 5.7.33 - Can't log in
ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client
I have recently imported a backup into a test environment so I can review some ...
- 37
-1
votes
1
answer
1k
views
Match closest date from a table to a table with just dates
I am trying to feel some gaps in table with values from a previous date.
I have created a date table which looks like this:
date
2021-04-13
2021-04-14
2021-04-15
2021-04-16
2021-04-17
2021-04-...
- 101
0
votes
1
answer
34
views
MySQL return rows ONLY if ALL the rows in another table have a specified value
I have two tables
Table1 :
efd_details( id )
Table2 :
efd_detail_status( detail_id , status )
Now the data as following :
select * from efd_details;
Output :
ID
1
2
3
4
5
select * from ...
0
votes
1
answer
55
views
Slow SQL taking almost 1s to execute with only 24000 records! (EXPLAIN included)
Any ideas how to improve and what the actual culprit is?
records in product are only 24000
takes 0.85s to run
Mysql 5.7
select `product`.* from `product`
inner join `manufacturer` on `product`.`...
- 211
1
vote
2
answers
35
views
group by left(col, x) text column - possible to index?
I'm looking for a possible index for the following:
select left(name,10), count(*)
from tab
group by left(name,10)
This is the table config:
create table tab(
id int(11),
name text,
primary key(id),
...
- 513
0
votes
1
answer
508
views
MySQL on Windows Refuses to Start as a Service
I am trying to install MySQL 5.7.33 onto a windows 2012 server. A process I have done many many times.
I have copied the Zip file onto the server and expanded it into G:/mysql57/ (the same zip file I ...
- 2,086