Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
559
questions
1
vote
1
answer
315
views
Update rows recursively based on previous rows by individual
I am struggling in finding a faster way to update the data I am currently working with. More precisely, I have workers' contracts data with starting and ending date for each contract. However, ...
1
vote
1
answer
874
views
I can't properly set permissions to MySQL config files
Yesterday I ran out of hard drive space for the database in the middle of a dump; Luckily this was in development and I had my backups... but I like to see these cases in detail and document them in ...
user237107
0
votes
0
answers
331
views
Error 2020: Got packet bigger than max_allowed_packet' when dumping one row from the table
I got the error message when I ran mysqldump:
Error 2020: Got packet bigger than max_allowed_packet' bytes when dumping table `my_table` at row: 17223\n"
According to the MySQL official user ...
- 111
0
votes
1
answer
1k
views
Unable to connect with JDBC after MySQL upgrade from 5.7 to MySQL 8.0
We use java application client to transfer database table data to a third party server. With MySQL 5.7 it was working fine. After upgrade to MySQL 8, it started to show errors. First thing we did was ...
- 13
0
votes
0
answers
211
views
How do I get the correct permissions to run 'mysqld --upgrade=FORCE'?
I'm attempting to run MySQL 8.0.30 on Ubuntu 22.04.
I'm trying to run the command mysqld --upgrade=FORCE as suggested in the answers to this post. I can't tell if I'm doing it wrong or if there are ...
- 101
0
votes
0
answers
16
views
Cannot connect to server after installation
I am new in MySQL.
I installed Windows 10 MySQL 8.
Installation was failed on the communication setup.
Server is running but I cannot connect through Workbench.
The problem was sold!
0
votes
2
answers
406
views
How to speed up SHOW BINARY LOGS
I am working on migration from mysql 5.7 to mysql 8 (AWS Aurora). As part of the migration I set up AWS DMS tasks and I came to notice each task takes more and more CPU from the writer.
The underline ...
- 473
0
votes
2
answers
284
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 ...
- 181
1
vote
1
answer
91
views
Join with certain tables causes execution time to skyrocket
Database Application: Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
Server: Ubuntu 18.04.6
RAM: 32GB
CPUs: 8 core
The underlying application framework is Drupal and it uses a query ...
- 13
0
votes
3
answers
171
views
Create user on Master does not create it on Slave
We have mysql replication set between two aurora clusters.
Everything looks good and the replication is working well.
However, I created a few hours ago a user on the master and I still don't see it ...
- 473
1
vote
1
answer
528
views
Optimal way to SELECT all rows in a table that are not referenced by a foreign key?
In MySQL I have a table "resources" that represents a list of resources.
eg:
CREATE TABLE resources (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
...etc.....
- 139
0
votes
2
answers
289
views
Optimizing IN clauses with thousands of integers
I have a search engine that outputs the id's of the elements to show in the search results, or to use in an "analytics" module.
In this one, I perform a lot of different queries to show ...
- 101
1
vote
0
answers
82
views
MySQL - Too many nested sub-expressions in a full-text search
I have a full-text search query with many sub-expressions like this:
SELECT DISTINCT id
FROM search_table s
WHERE MATCH (s.search) AGAINST ('+(failure* protection*) +(math* police*) +(insect* hall*) +(...
- 644
3
votes
2
answers
718
views
Why MySQL 8 doesn't return any rows when using a table with a descending PRIMARY KEY, 2 indexes, and valid join conditions with existing rows
Using 2 tables (A & B), joinable by their PK, with the PRIMARY KEY of A being sorted DESC (PK in descending order as it's allowed by MySQL 8), and A having 2 indexes in ASC ordering.
When I ...
- 209
0
votes
0
answers
97
views
Duration different between mysql workbench and command line
When profiling a simple insert statement, I'm seeing a massive difference in duration reported between mysql workbench and the mysql command line.
Test Setup
CREATE TABLE test (
id int
)
...
- 121
0
votes
0
answers
21
views
MySQL server is not executing drop command
Whenever I use 'drop' in a command it doesn't execute it. For rest of the commands it is working fine.
1
vote
1
answer
161
views
Understanding MySQL Locks and Deadlocks
I was just checking the articles regarding the MySQL table locking system. I wanted to learn about how we can avoid the deadlocks in MySQL while UPDATE/DELETE operations.
What I have done so far:
...
0
votes
0
answers
630
views
MYSQL 8 | Ubuntu 20 | Failed to start error
Here is the error I get. Can't start MySQL server 8 on ubuntu 20. I haven't backed up my db so can't purge all data as well. I tried reinstalling MySQL, and didn't work.
>/var/log/mysql/error.log ...
- 1
0
votes
1
answer
200
views
Is it safe to manually delete binary logs after disabling it?
After disabling log-bin by skip-log-bin option, I cannot use PURGE command to remove binary logs.
Refer to the documentation
PURGE BINARY LOGS requires the BINLOG_ADMIN privilege. This statement has ...
- 101
1
vote
1
answer
165
views
MySQL - Nested loops count too high resulting in query taking too long
I am seeing high response time for a MySQL query. I have two similar queries:
SELECT `orders`.`id` FROM `orders` WHERE (`orders`.line_count = 0 OR
`orders`.account_type_id IS NULL OR
...
- 153
0
votes
0
answers
160
views
mysql Trigger to prevent entry being written to a table AFTER writing to another table
Is there a way to use SIGNAL in a trigger to prevent a query being written to a table, but only AFTER you have logged the details in another table?
for example:
CREATE TABLE `test2` (
`txtvals` ...
- 2,046
1
vote
1
answer
212
views
Cannot assign local variable using ":=" inside subquery
I have a query which counts the total of users by month or by day. So what I have to do is to accumulate in a variable that total.
My code is:
select * from (select createdDate,newUsers, ( csum := ...
- 123
1
vote
1
answer
989
views
MySql 8 nested json aggregation functions
I'm struggling trying to aggregate results in a table into a nested json.
this is the table:
+----+------+--------+------+------+-------+-----------+--------------+
| id | area | userId | game | step |...
- 21
0
votes
2
answers
93
views
Product alternatives query performance slow - Many to many tables and ROW_NUM(OVER PARTITION...) optimisation needed?
When the user selects a product in my web app, I run a query to search for related alternatives based on product categories linked to the product via a many to many relationship with a category ...
- 101
3
votes
0
answers
2k
views
Replication instantly fails with 1032 error; Coordinator stopped because there were error(s) in the worker(s)
I have two databases, which I'm attempting to configure in a master-slave relationship (the eventual goal is to set them as master-master, but I'll settle for getting master-slave working at this ...
0
votes
0
answers
57
views
How does mysqlbinlog restore work with gtid_mode=ON?
It doesn't seem to work at all for me if gtid_mode is on.
Is this the reason?
"In addition, once a transaction with a given GTID is committed on a
given server, any subsequent transaction having ...
- 101
1
vote
1
answer
2k
views
MySQL does not use index, does full table scan instead
EDIT: See further developments below.
I have an InnoDB table with the following schema:
CREATE TABLE `data` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_id` CHAR(27) NULL DEFAULT NULL,...
- 155
0
votes
0
answers
113
views
MySql query not using spatial index
We have the below query that we are calling to find all rows within another table's row geometry. When I do a explain, it shows the spatial index spidx_table2 as a potential index, but says it is ...
- 235
1
vote
1
answer
185
views
Is `innodb_table_locks=0` a good idea?
I have noticed several times that triggers in mysql cause table locks on each and every table referenced in the trigger. Even if not actually touched, like when it's inside an IF statement.
If I ...
- 113
0
votes
0
answers
343
views
Waiting for table level lock innodb
We had a table which in empty. But due to some business logic the table gets queried anyway. This was working fine but today suddenly all the queries on that table were taking a lot of time due to ...
1
vote
1
answer
361
views
Is it safe to use user defined variables inside stored procedures in MySQL?
Currently I'm working with Stored Procedures in MySQL and I'm using in some procedures user-defined variables and I've seen that type of variables are initialized in the current session and keep their ...
- 123
0
votes
2
answers
31
views
Mysql server running as replica doesn't record the slow queries in the slow query log, even though it's enabled
I am running a MySql 8.0.29 server serving as a replica for another server on an ubuntu 20.04.4 LTS box.
'slow_query_log','ON'
'slow_query_log_file','/var/log/mysql/mysql-slow.log'
'long_query_time', ...
- 417
0
votes
1
answer
281
views
Columns that can be more than 255 characters but 99% of times will be less than 255 characters. TEXT or VARCHAR(5000)? (performance)
I have two columns that can be long, but 99% of times will be short (less than 255 characters). This means that I can not use VARCHAR(255) because 1% of times I could not save data. I thought to make ...
0
votes
0
answers
38
views
MySQL Replication with Data Transformation and Replicant-specific Data
I have two MySQL 8.0.23 RDS's - one "Production" RDS that actual users use, and one "Performance" RDS that is used for performance testing.
These databases have the same schema and ...
- 101
0
votes
1
answer
460
views
Specify range of Json array from different column
Mysql 8 makes it possible to extract part of an array saved in a JSON column as shown below:
SELECT jcol->>'$[1 to 5]' FROM `db`.`table` which 1 and 5 are boundaries of the desired range of ...
- 149
0
votes
1
answer
111
views
Mysql choosing really poor index without IGNORE INDEX hint
Problem
The following query produces a really inefficient query plan:
select `event`.`id` as `eventId`
from `alert`
inner join `event` on `event`.`id` = `alert`.`eventId`
inner join `property` on `...
- 121
1
vote
0
answers
113
views
Aurora Serverless 2 MySQL 8.0 doing a mysqlpump to dump users
Recently i ask about using roles in MySQL 8.0 using aurora 3.0 in a Serverless 2 RDS cluster.
They answered me that the correct way to do that is using mysqlpump with the --users parameters to dump ...
1
vote
1
answer
900
views
What are the possible downsides or consequences of using ALGORITHM=INPLACE, LOCK=NONE when running alters on a table?
I've been trying to find the answer to this online for a while with no luck.
What are possible downsides or consequences to running an alter (such as add index, add column, drop index, drop column) ...
- 41
-3
votes
1
answer
369
views
Import 5gb sql file into MYSQL database [duplicate]
i have exported sql file using mysql dump from a database and i am trying to import this big 5gb file into anohter mysql database. I am using mysql -u username -p -h hostname <file.sql command ...
- 1
7
votes
2
answers
1k
views
Are enums still evil (in MySQL 8.0)?
So I have a disagreement with a coworker about using enums vs lookup tables.
This very old article from 2011 and MyQL5.5 still gets referenced again and again by those not liking enums.
But we are ...
- 173
1
vote
2
answers
676
views
MySQL information.schema.tables shows create_time later than update_time?
I've checked the documentation, but couldn't find anything that could explain this.
mysql> SELECT * FROM information_schema.`TABLES` WHERE table_schema='myschema' AND TABLE_NAME ='mytable' \G;
*****...
- 155
5
votes
1
answer
3k
views
MySQL Workbench mysqldump: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
When using workbench on Mysql 8, I get the following error:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
I'...
- 151
1
vote
2
answers
866
views
Mysql 8.0 "ERROR 1030 (HY000): Got error 100" when adding index to very large table
I'm attempting to add an index to a very large table (2B rows) and the command fails after a few hours with:
ERROR 1030 (HY000): Got error 100 - 'InnoDB error' from storage engine
I'm finding the ...
- 33
1
vote
2
answers
568
views
how to downgrade mysql on Debian 10 from 8.029 to 8.02x
I have MySQL installed on my test server (Debian 10). I recently updated it to the newest version (8.0.29), but there are some issues with it so I want to go back to an older version (8.0.28). But I ...
- 2,046
2
votes
1
answer
1k
views
mysql: setting default default_collation_for_utf8mb4?
How to set the value for "default_collation_for_utf8mb4" to "utf8mb4_general_ci", globally , or when the mysql 8.0 server started ?
I tried
[mysqld]
init_connect='SET ...
- 131
0
votes
0
answers
62
views
MYSQL8 unknown runaway memory
First thank you, I am not very knowledgeable in MYSQL and am purely self taught.
I moved from metal to AWS(EC2, with maxed out IOPS:3000) and in the process we also upgrade from MYSQL5.6 to MYSQL8.0....
1
vote
2
answers
368
views
Change location of innodb_temporary
I want to change the path where the ibtmp1 is stored in mysql 8.0.25 on debian 11. How to do this? I see:
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
***...
- 121
1
vote
0
answers
38
views
How to create a comprehensively auditable database that is updated from multiple concurrent sources?
I have various different Python scripts running concurrently updating a MySQL database from a circa 15 API and web scraping sources. In another script a continuous loop runs that queries batches of ...
- 131
1
vote
0
answers
2k
views
Join JSON column using an index
I have two tables:
definition (7,000 rows):
+--------------+----------+
| definitionId | nameRef |
+--------------+----------+
| 1 | [1] |
| 2 | [1,2] |
+--------------+-...
- 155
0
votes
1
answer
302
views
Error while running mysql_upgrade
Recently upgraded Mysql from 8.0.20 to 8.0.28 ( removed the older rpms and installed newer ones)
and started Mysqld in RHEL7. All seems good except that I see below error when trying to run ...
