Questions tagged [mysql-8.0]

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

Filter by
Sorted by
Tagged with
17
votes
1answer
5k 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?
11
votes
2answers
13k 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 ...
9
votes
3answers
5k views

Why mysql remove query cache in 8.0 version?

Why mysql remove query cache in 8.0 version ?
5
votes
2answers
1k 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 ...
4
votes
3answers
3k 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 ...
4
votes
1answer
3k 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 ...
3
votes
2answers
82 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 ...
3
votes
1answer
309 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 ...
3
votes
2answers
137 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. ...
3
votes
1answer
91 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. ...
3
votes
1answer
235 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(...
3
votes
2answers
98 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 ...
3
votes
3answers
513 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 ...
3
votes
1answer
418 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), ...
2
votes
1answer
2k 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 ...
2
votes
1answer
69 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). ...
2
votes
2answers
65 views

unexpected additional columns

in most of my tables, command SELECT column_name FROM information_schema.columns WHERE table_name='table' worked as expected, but in one table i am getting additional tables that i was not expecting....
2
votes
2answers
17 views

Is there a way to construct SQL statement to return desired value only if there is a single row found, and return 0 otherwise?

Consider below: SELECT customer.contact.id FROM customer.contact WHERE customer_office_id = [INTEGER] If only a single row is returned, I want the result set to return ...
2
votes
3answers
64 views

How to use left join with a subquery

This or this questions do not answer my doubt. I have the following set of tables in a database called order_mgmt_sys (Please note that database is static and I can't alter the tables) mysql> ...
2
votes
1answer
511 views

How to query all entities associated with all children (recursive) of a node in SQL?

This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary. Ok, I’m having trouble putting this into words… so bear with me. Say I have a tree of ...
2
votes
1answer
669 views

Longitude and Latitude order with ST_X, and ST_Y on SRID 4326

I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column. Nothing in the docs other than the primitive Point() suggests the ...
2
votes
1answer
1k 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. ...
2
votes
1answer
555 views

MySQL 8: Truncate table stuck in `checking permissions` state for minutes

Sometimes but not always truncate table statements get stuck in checking permissions state for several minutes (up to 7-8 minutes). I'm running MySQL 8.0.12 (for Linux on x86_64 (MySQL Community ...
2
votes
1answer
45 views

column_name ordered same as describe table

I was under impression that default order of columns is internal mysql order but for some reason describe table and selecting column_name gives me compleatly different order. How can I set ORDER BY ...
2
votes
0answers
70 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 ...
2
votes
1answer
122 views

Why Subquery do not filter the result in multiple Subquery

This is Query (skipped selection for simplicity and also it work perfect): SELECT * FROM (SELECT a.striker FROM ball_by_ball a LEFT JOIN batsman_scored b USING (...
2
votes
0answers
172 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 ...
2
votes
1answer
477 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 ...
1
vote
2answers
60 views

Why this query giving wrong output?

I want to find review group by ratings 1,2 etc and for doing so i write below query but this giving wrong info. This is the query: SELECT CASE ur.rating WHEN ur.rating between 0.5 and 1 ...
1
vote
1answer
578 views

MySQL Workbench, Windows Server 2019, MySQL not showing Performance, Information of MySQL Schemas!

This evening I installed MySQL on a new clean install of Windows 2019 server. When the install finished, the installer offered the possibility of opening Workbench so I said yes. Then I noticed that ...
1
vote
2answers
29 views

mysql innodb index: better one big or many compact?

I've a question for an expert in how innodb indexes are structured (mysql 8.0.18) Say I have 4 varchar columns on a table with one billion rows: country, state, city, attraction I have queries ...
1
vote
1answer
81 views

MySQL change user login from one plugin type to another

I'm in the process of testing MySQL 8.0. I have set up a test database, and the first thing I want to do is import my user account from MySQL 5.7. So to do this I run: /*ON MySQL 5.7*/ SHOW CREATE ...
1
vote
2answers
40 views

How to fill GROUP_CONCAT() with 0's when JOIN data missing

I'm using MySQL 8.0's date generation and am joining in data when/where available. This technique allows me to ensure this query returns zero values. This works perfectly when I'm pulling data for a ...
1
vote
2answers
60 views

mysql 8.0 in-memory table slow insert

Please help with following strange issue.I defined in memory table but insert of 600.000 rows takes 13 minutes and causes heavy write to disk. The CPU is underutilized and I have enough free memory. ...
1
vote
1answer
204 views

What should be innodb_buffer_pool_chunk_size for pool_size=2G, pool_instances=2?

I have 4G VPS with following config innodb_buffer_pool_size=2048M innodb_buffer_pool_instances=2 SELECT @@innodb_buffer_pool_chunk_size/1024/1024 128.00000000 Should I change ...
1
vote
1answer
2k views

mysql 8.0, updates are sometimes stalling

Recently I moved one of my production servers from MySQL 5.7 to 8.0. Immediately after that I noticed that update statements are sometimes (not always) taking more time (enormously) and tend to stack ...
1
vote
1answer
133 views

How does functional index in MySQL 8.0.13+ improve query performance?

Quoting from 13.1.15 CREATE INDEX Syntax: MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. See "Functional Key Parts" ...
1
vote
1answer
190 views

Cannot set innodb_buffer_pool_size above 1023M in my.ini

Windows server 2012 R2 with 64GB RAM, MySQL 8. The Database is about 5GB. I am using a buffer pool of 1023M, as shown below: innodb_buffer_pool_size=1023M If I enter anything above 1023M e.g. 1024M ...
1
vote
2answers
697 views

Using MySQL 8.0 “Upgrade Checker” on RDS

The MySQL 8.0 Shell came with an "Upgrade Checker". Useful to run before upgrading, as you may detect incompatible issues with your table structure / etc. Running its python version from the shell ...
1
vote
1answer
440 views

How to verify if given user@host with 'password' exists in MySQL 8.0+?

I write sanity check script for an application. One of the tests is supposed to verify that a given user@host with 'password' exists in MySQL. I'm using simple native hash passwords. In MySQL5.7- ...
1
vote
1answer
82 views

How to get First in and Last out with multiple records?

I have this records and it is attendance of each employee. I'd like to get their attendance, what i have tried Query is like this which is getting their attendance but if it has a multiple record it ...
1
vote
1answer
45 views

Dockerized Sysbench could not connect to Mysql

I'm trying to follow this tutorial. It does not give Mysql version requirements. So I'd tried to use Mysql 8.0.18 Community Server to connect to using those commands. I've created what have been ...
1
vote
1answer
57 views

Restoring a mysqldump loses user privileges for routines?

When I try and restore a mysqldump, the user privileges against some routines are missing. From what I can tell that is happeneing the routines that are in schemas that are loaded after the mysql ...
1
vote
2answers
138 views

Same column present on two or more foreign key constraints

A simple schema leading to my question is the following: CREATE TABLE A ( AID INT AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE A_COPY ( AID INT, CID INT, PRIMARY KEY (AID, CID), ...
1
vote
1answer
405 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 ...
1
vote
1answer
41 views

Why Output is wrong after doing the left join as subquery

I have TWO QUERY which work perfect, but when i join those two query as LEFT JOIN the output become wrong. So what is the problem.? Query 1: select Batsman.innings_no, bowler, sum(...
1
vote
1answer
175 views

Secondary nodes in mysql 8.0.15 innodb cluster are not reading group_replication_member_expel_timeout variable from my.cnf

I have setup a 3 node mysql 8.0.15 innodb cluster. I have been trying to set group_replication_member_expel_timeout variable to 60 via the /etc/my.cnf file. Although the primary node has read the ...
1
vote
1answer
417 views

How can I recover all the Stored procedures, Functions, Events from previous server version

Until recently I have been running MySQL5.5.8 - foolishly a couple of days ago I decided to upgrade to take advantage of new features to be found in version 8. Before the upgrade I did a backup, using ...
1
vote
1answer
397 views

MySQL 8 Geospatial Queries, MakeEnvelope Workaround?

I'm trying to work with some data from Google Maps and have a few locations along with their bounding boxs (northeast, southwest) data stored. Using that data, I have the following query... SELECT ...
1
vote
2answers
112 views

mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0

I'm struggeling the last two days with the following issue: Restoring a ~1GB database dump with only innodb tables on a mysql 5.7 server takes about 5 minutes. After upgrading the same server to ...

1 2 3 4 5