Questions tagged [mysql-8.0]

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

Filter by
Sorted by
Tagged with
2 votes
1 answer
32 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 database; SELECT * FROM `Table` where ``.`Table`.`id` = 1 With MySQL@5 this query runs ...
1 vote
1 answer
22 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, ...
0 votes
1 answer
12 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 ...
-2 votes
0 answers
13 views

Why does my Group Replication plugin fail in MySQL 8.0.27?

Why does my Group Replication plugin fail in MySQL 8.0.27 ? Can't open shared library '/usr/local/mysql/lib/plugin/group_replication.so' (errno: 0 /usr/local/mysql/lib/plugin/group_replication.so: ...
0 votes
0 answers
15 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
21 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 ...
0 votes
0 answers
12 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 ...
0 votes
0 answers
8 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
60 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 ...
  • 437
0 votes
2 answers
39 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 ...
1 vote
1 answer
59 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 ...
0 votes
3 answers
36 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 ...
  • 437
1 vote
1 answer
30 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.....
0 votes
2 answers
48 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 ...
1 vote
0 answers
37 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*) +(...
3 votes
2 answers
585 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 ...
0 votes
0 answers
23 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 ) ...
0 votes
0 answers
9 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
44 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
310 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 ...
0 votes
1 answer
20 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
0 votes
1 answer
25 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 ...
0 votes
0 answers
29 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` ...
  • 1,954
1 vote
1 answer
49 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 := ...
1 vote
1 answer
105 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 |...
0 votes
2 answers
35 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
1 vote
0 answers
731 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
26 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
286 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,...
0 votes
0 answers
43 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 ...
  • 156
1 vote
1 answer
43 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 ...
0 votes
0 answers
96 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
80 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 ...
0 votes
2 answers
22 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', ...
  • 407
0 votes
1 answer
62 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
1 answer
129 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 ...
0 votes
1 answer
43 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 `...
1 vote
0 answers
55 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
133 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) ...
-3 votes
1 answer
157 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 ...
5 votes
2 answers
266 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 ...
1 vote
2 answers
175 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; *****...
1 vote
0 answers
1k 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'...
1 vote
1 answer
176 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
212 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 ...
  • 1,954
0 votes
0 answers
46 views

How to create correct mysql update query using window function for grouped items

We have a table that you could label as a summary table that we need to update every so often from another table that contains dated sales rows. How do you create a query, along with the index for it, ...
  • 156
1 vote
1 answer
329 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 ...
  • 121
0 votes
0 answers
31 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
92 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 ***...
  • 111
1 vote
0 answers
33 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
2 3 4 5
10