Questions tagged [mysql-8.0]

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

Filter by
Sorted by
Tagged with
25 votes
1 answer
12k views

What happened to MySQL 6 & 7?

With the recent release of MySQL 8, I can't seem to find any information about why version numbers 6 and 7 were skipped. Anyone know?
user avatar
24 votes
3 answers
20k views

Why did MySQL remove the query cache feature after version 8.0?

Why did MySQL remove the query cache feature after version 8.0?
user avatar
  • 377
23 votes
4 answers
36k views

Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

In mysqld.log I have thousands of lines like: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade So, according to virtually every post on the ...
user avatar
10 votes
5 answers
10k views

Disable Binary Logs in MySQL 8.0

How can I disable binary logging on MySQL 8.0? I am needing to import a large MySQLDump file, and don't want to fill the Binary logs with many GB of data. In the past I would have set log_bin=0 in ...
user avatar
  • 1,920
8 votes
2 answers
5k 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 ...
user avatar
  • 295
7 votes
2 answers
2k views

Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]

I am trying the following insert query on mysql 8. Insert Into fence Set fenceName='aa', radius=2, fenceGeometry=ST_GeomFromText('POINT(...
user avatar
  • 177
7 votes
1 answer
10k views

I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7

I've installed mysql 8 on centos 7 and when i want to start the mysqld service , it says : Job for mysqld.service failed because the control process exited with error code. See "systemctl status ...
user avatar
  • 173
7 votes
3 answers
11k views

Extreme performance loss due to moving from Mysql 5.7.23 to Mysql 8.0 (status, config included) [closed]

Been sitting on this for 12 hours straight (it's noon now, so I will read/respond when I woke up again). I have made the big fault of recommending a database upgrade of our productive environment to ...
user avatar
  • 362
6 votes
2 answers
3k views

Why is this MySQL query taking so long?

I'm looking to do a relatively simply filter from one table called prices. prices is a very large table (~2G records). Sample data, query and indexes included below. I realize the table being ...
user avatar
  • 181
5 votes
1 answer
6k views

How do I create a point with an SRID in MySQL?

MySQL supports using ST_PointFromText to construct a point with an SRID, ST_PointFromText(wkt[, srid [, options]]) Constructs a Point value using its WKT representation and SRID. This ...
user avatar
  • 56.3k
5 votes
2 answers
2k views

MySQL crashing at about 70k open files

Server running community 8.0.14, we hit the number of open files a few days ago, so I increased the limit but after a while the server crashed with this in the error log: InnoDB: We intentionally ...
user avatar
  • 331
5 votes
2 answers
99 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 ...
user avatar
5 votes
1 answer
6k views

MySQL restore lost data after update from 5.7 -> 8.0.11

I have a docker-compose file where I use the official mysql:latest image as my database server. After a docker pull mysql and a docker-compose up the image updated from 5.7 -> 8.0.11 This update ...
user avatar
  • 191
4 votes
2 answers
687 views

Is using strings as keys for reference to other tables bad in terms of memory usage?

So i have to deal with the following scenario: I have a table , users , which holds all of the users of my application . Each user has an email which is unique and thus could be the primary key of ...
user avatar
4 votes
1 answer
487 views

Why is the Innodb Memcached plugin being deprecated in MySQL 8.0.22?

I originally used "the handlersocket" plugin for NoSQL access to InnoDB tables, then it eventually couldn't be built on newer versions so I migrated to the "Memcached" plugin which ...
user avatar
  • 506
4 votes
1 answer
9k views

MySQL event scheduler waiting on empty queue since server restarted 12 days ago

I noticed a process on the server which has been running for more than 12 days, which I think coincides with the last time MySQL was restarted. mysql> SHOW FULL PROCESSLIST; +---------+------------...
user avatar
  • 143
4 votes
2 answers
542 views

Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?

If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?
user avatar
  • 177
4 votes
2 answers
3k views

Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4

I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER ...
user avatar
4 votes
0 answers
491 views

How to get the Slave commit timestamp using MySql replication?

I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that. By reading its ...
user avatar
3 votes
5 answers
9k views

mysql 8.0, updates are sometimes stalling

Update: The scope of this question does not cover basic performance tweaks and considers these are done already. So if you are happy to share some evident variables like innodb_io_capacity, ...
user avatar
  • 197
3 votes
1 answer
606 views

Why 0/false returns true when using JSON_EXTRACT in MySQL

I have a condition to select a JSON document value that is falsy. My rows: 1. {"my_value": false} 2. {"my_value": 0} 3. {"my_value": "0"} 4. {"my_value&...
user avatar
3 votes
2 answers
1k views

Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes?

I have a VARCHAR(80) utf8mb4 column, and I'm testing adding some ASCII & Emoji characters, and use LENGTH() and CHAR_LENGTH() to understand the differences. From reading in different places, my ...
user avatar
  • 609
3 votes
2 answers
3k views

Can't set log_bin_trust_function_creators=1 on machine startup - MySQL 8.0

I'm trying to put the log_bin_trust_function_creators=1 parameter into the my.cnf configuration file to set the variable correctly on startup on a machine with Ubuntu 20.04 LTS. But every time I save ...
user avatar
  • 31
3 votes
2 answers
139 views

How to store values that include numbers and strings? Example: 1.25, trace, not applicable, etc

I created a database for nutritional table information. This database have values like carbohydrates, protein and fats of a food (there is a total of 28 fields). The problem is that there are special ...
user avatar
3 votes
1 answer
347 views

Why does REGEXP_REPLACE concatenate with a previous result?

This looks like strange behaviour, and I get the same on the MySQL command line as via db<>fiddle: create table t(foo varchar(255)); insert into t (foo) values ('the quick brown fox'), ('jumped ...
user avatar
  • 37.5k
3 votes
1 answer
256 views

MySQL 8.0 Replication filters lost between restarts

I don't know if I fell on a bug or a feature, I read the documentation several times but haven't found the information. I have two MySQL servers, both on 8.0.15 and hosted on a Windows Server (x64). ...
user avatar
3 votes
1 answer
385 views

Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3. CREATE TABLE test ( `id` bigint NOT NULL AUTO_INCREMENT, created_date ...
user avatar
  • 239
3 votes
1 answer
247 views

Testing MySQL adaptive_hash_index option

We recently migrated our application to an entirely new infrastructure, going from a single server managing everything to a kubernetes cluster and dedicated database server running on AWS RDS. On ...
user avatar
3 votes
2 answers
691 views

MySQL incorrectly saying serverID non unique when adding an instance to the InnoDB cluster

I Get the following error when adding the second instance to a MYSQL Innodb Cluster ERROR: Cannot add instance 'XXX' to the cluster because it has the same server ID of a member of the cluster. ...
user avatar
  • 135
3 votes
1 answer
169 views

Increased disk consumption after upgrading to MySQL 8

I have a MySQL 8 DB in AWS RDS, and after upgrading from MySQL 5.7 to 8 a couple months ago, we have noticed free disk space being consumed at a very rapid rate, at least double the previous rate. ...
user avatar
  • 33
3 votes
2 answers
3k views

MySQL Error seems unfixable: Index column size too large

I'm getting: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. There are many questions about this here, I tried many of them. No luck. My context: MySQL 8.0.13 ...
user avatar
3 votes
2 answers
5k views

My disk is full of binlog files

I just realized that my server is growing by 2 GB per day, which is not normal. I looked at my folder and the folder / var / lib / mysql reached 26 GB Normally it should be about 4 GB A month ago I ...
user avatar
  • 43
3 votes
2 answers
3k views

Mysql temp table insert from json can't handle null values

The MySql-8 JSON_TABLE function allows you to handle JSON data as if it was a table. I'm trying to use it to populate a temporary table using the generated table but I believe I'm hitting a bug. ...
user avatar
  • 1,284
3 votes
1 answer
611 views

Query works manually, not from stored procedure - Safe update error

I have a relatively simple update query that works fine when I manually execute it from a Mysql Workbench query window, but will not work when I call it as a stored procedure. The stored procedure in ...
user avatar
3 votes
1 answer
10k views

DBeaver: Access denied for user 'root'@'localhost' (using password: YES)

Context: I am creating a new Schema and looking to import .sql file generated from mysql dump. I have server running: MySQL 8.0.19. I am using DBeaver client Version 7.0.2 and have setup a new MySQL ...
user avatar
  • 129
3 votes
3 answers
993 views

RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table

On aws RDS I have an mysql 8.0.16 instance running. My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty. This is a problem for me because I would like to create ...
user avatar
3 votes
2 answers
1k views

MySQL 8.0 Tuning Advice Needed - Slow Locking & Various Issues

I recently upgraded my database to MySQL 8.0 and I'm using phpmyadmin to get status information. I also upgraded my virtual server to 4GB RAM and 2 vCPUs, intended to be a MySQL-only server for my ...
user avatar
  • 33
3 votes
2 answers
139 views

Why max(id) with join not working or how to optimize it with join?

Below is my MESSAGES_THREAD table: CREATE TABLE `messages_thread` ( `id` varchar(64) NOT NULL, `creator` mediumint(9) NOT NULL, `other` mediumint(9) NOT NULL, `time` datetime DEFAULT ...
user avatar
  • 101
3 votes
1 answer
2k views

Install mysql-shell from apt repository

I want to install and configure a high availability cluster for Mysql 8. To automate that, I want to use use apt repository (via ansible). My servers are debian jessy (8) distribution. I succesfully ...
user avatar
  • 141
3 votes
3 answers
844 views

High disk IO with MySQL master-master replication

We've been evaluating an active-passive master-master replication setup for our MySQL database, running on version 8.0. While diagnosing some general slowness issues, we were surprised to see very ...
user avatar
  • 151
3 votes
0 answers
521 views

Mysql memory usage increasing after upgrading to 8 and converting some tables from MyISAM to InnoDB

I am having an issue with MySQL gradually consuming more memory every day which seems to have coincided with converting some tables from MyISAM to InnoDB two weeks ago. You can see the trend in memory ...
user avatar
  • 31
3 votes
0 answers
906 views

Cannot access MYSQL safe mode using skip-grant-tables on Windows

I am running a MySQL server 8.0.21 on a Windows Server system. Recently I ran into an issue where my root user lost all its privileges. I am trying to gain access to the MySQL server using: mysqld ...
user avatar
3 votes
0 answers
2k views

MySQL Group Replication - Peer address is not valid

I have been trying to set up an MySQL Innodb Cluster, initially with two databases, both MySQL 8.0.19. But I keep getting the same errors. On the first one (username@1.2.3.4:3306), I have created ...
user avatar
  • 1,920
3 votes
0 answers
152 views

check if MySQL is using the Henry Spencer implementation for REGEX

Prior to MySQL 8.0.4, MySQL used the Henry Spencer implementation for REGEX and you could use this for word boundaries: '[[:<:]]' and '[[:>:]]' From 8.0.4 MySQL is using the ICU implementation ...
user avatar
  • 131
3 votes
1 answer
826 views

MySQL creates tables very slow

On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), ...
user avatar
  • 31
2 votes
2 answers
24k views

How to set lowercase_table_name

I've been trying to setup lower_case_table_names for ages in MySQL 8.0 without success. What is the procedure to set this up in 8.0? In previous versions of mysql I was able to override the variable ...
user avatar
  • 71
2 votes
3 answers
2k views

Performance issues after upgrading to MySQL 8

UPDATE (tl;dr;): I filed a bug report here: https://bugs.mysql.com/bug.php?id=99593 which has since been acknowledged and a workaround provided. See answer below for details. Certain queries seem to ...
user avatar
2 votes
1 answer
3k views

Why am I not able to add a user?

I'm running command grant all privileges on *.* to 'username'@localhost identified by 'strong password';, but I get the error: ERROR 1064 (42000): You have an error in your SQL syntax; check the ...
user avatar
2 votes
2 answers
8k views

How to select all columns plus one alias column in MySQL

It is a simple question, but i could not find the answer. I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ...
user avatar
2 votes
1 answer
5k views

Server quit without updating PID file when trying to start MySQL server?

So I created my .bash_profile, opened it in a text editor and added: export PATH=$PATH:/usr/local/mysql/bin and then I saved it. Next, I tried running sudo /usr/local/mysql/support-files/mysql.server ...
user avatar

1
2 3 4 5
9