Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [mysql-8.0]

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

0
votes
0answers
5 views

MySQL 8 Geospatial Queries

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 ...
0
votes
0answers
8 views

mysql 8 is failure to start after upgrade from 5.7

I've upgraded one of the slave server from mysql 5.6 to 5.7 and now 5.7 to 8. But after up-gradation steps done when i use systemctl start mysqld.service or systemctl restart mysqld facing error and ...
0
votes
0answers
19 views

MySQL 8 using the wrong tmp directory

I have a query like so... INSERT INTO table3 SELECT DISTINCT * FROM table1 LEFT JOIN table2 on ... And it uses a temp table. The query runs for about 60 seconds, then starts filling /tmp even ...
0
votes
2answers
35 views

2018 of MySQL (InnoDB) CHAR vs VARCHAR

In 2018, does it still hold true that MySQL (InnoDB) CHAR performs better VARCHAR speed-wise? I've only found a performance comparison explanation that is 7 year old. I've checked the official MySQL ...
1
vote
0answers
20 views

MySQL 8 more than doubles the size of MyISAM table after optimization

I recently upgraded from MySQL 5.7 to 8.0. I have noticed some changes from 5.7 to 8. But I have not been able to find any discussion on following situation that I have been puzzling for a while. I ...
0
votes
0answers
11 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 ...
0
votes
1answer
15 views

innodb cache duplication due to os page cache and buffer pool

We have set our innodb buffer pool 80% of total memory. Mysql page allocation gets stalled sometimes when page cache is occupying memory. Now since mysql is caching table rows and indexes and follows ...
0
votes
1answer
34 views

Application considerations when using a multi-primary innodb cluster

I'm developing a scalable cloud application and I wish to use a multi-primary cluster to be better able to scale horizontally when needed. I have experience with a single primary cluster configuration....
-1
votes
1answer
15 views

Cannot find innodb_page_size option in [mysqld] section in my.ini file

Where can I find innodb_page_size option? Since I cannot find it in [mysqld] section in my.ini file. I want to change it to 32k or 64k since I am getting the below error: MySQL: Error Code: 1118 ...
0
votes
1answer
22 views

innodb_page_size is not present in [mysqld] section in my.ini file in MySQL Server 8.0?

I have to edit the innodb_page_size to 32K but I cannot find innodb_page_size in [mysqld] section in my.ini file. I am using MySQL Server 8.0.13. Where can I find it?
-1
votes
1answer
18 views

Retrieve a list of Ids from a table where all the records have a given status [closed]

I have a table with data related to books. Schema: BookKey PRIMARY KEY, BookID, Status The primary key of the table is a BookKey and each BookKey belongs to one BookId. Each row also has a status ...
0
votes
2answers
34 views

how do I insert timzone-pois.sql into MYSQL?

I have read about how to insert the time zone into MYSQL as below: I can't find time zone tables in sys database of MySQL However, after I type show databases, there was no response except ...
-1
votes
0answers
28 views

databases not showing anymore after an upgrade

I did an upgrade from MySQL 5.5 to 8.0, by doing intermediate upgrades to 5.6 and 5.7 as recommended, thus: 5.5 -> 5.6 -> 5.7 -> 8.0. I ran the mysql_upgrade utility with every step and never had any ...
-1
votes
0answers
22 views

Schema not showing up in MYSQL workbench

I created a schema on a newly installed MYSQL 8.0.13 Workbench on windows 10 system. But, it is not showing up. So, I thought I have to create a table in order for it to show up at the left panel of ...
-1
votes
0answers
70 views

ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

After removing the old MYSQL8.0.12 and upgraded to MYSQL8.0.13, I created a new schema but the schema is not showing in the workbench'sf left panel. So, I followed this https://stackoverflow.com/...
-1
votes
0answers
44 views

how to use GRANT ALL privileges to MYSQL 8.0.12

I have tried to use GRANT ALL PRIVILEGES without avail. Here's how the command prompt(with Administrator right looks like): After I entered the command, I just got the following(Note: I just copy ...
0
votes
0answers
15 views

MySQL: TMPDIR is ignored by temptable

I am using MySQL 8 Comm. Edition on an Ubuntu Server. I have a HD-RAID on a mount point besides the normal hard drive. So I want to point the "TMPDIR" variable in "/etc/mysql/mysql.conf.d/mysqld.cnf" ...
0
votes
1answer
21 views

MySQL - Blank fields in tab-delimited .txt file causing errors in LOAD DATA INFILE

I am working with MySQL 8.0 through MySQL Workbench on a Windows machine. I am having an enormous amount of problems importing an excel file into a MySQL database. I think the problem is that my data ...
1
vote
2answers
51 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
vote
2answers
27 views

Showing data that is not already in the data base

I have the following problem. I want to search a query in an existing data base and that the result shows me the entries of the query that are NOT already in the database. Minimal example: | ID | ...
1
vote
1answer
23 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
32 views

Statement Based Replication still giving duplicate Entry Error

New to the replication. I am configuring multi-master replication. I did the following steps: made one slave. configured two masters to it. Now i am performing following scenario: disconnect both ...
1
vote
0answers
16 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 ...
0
votes
1answer
49 views

Loading 400 Million Records into MySQL Hangs on “Sending Data”

I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. Super awesome. ...
0
votes
1answer
10 views

Problems Linking the max value of an attribute in a table to its primary Key

I need to extract the order_id with the max number of items, I've figured out how to extract the max value but I'm having problems linking it to the particular order_id. PS: I'm a uni student so ...
1
vote
1answer
31 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 ...
0
votes
1answer
44 views

What's your data ETL methodology? [closed]

I think this is more like an open question: How do you do the ETL for your company? We are using MySQL as our database management system and we have a production schema. Every month, when we received ...
0
votes
2answers
102 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 ...
0
votes
1answer
107 views

How to structure table in MySQL for group chat application? [closed]

I'm making a messaging application, and I need a way to organize and store messages. I was thinking something like a table that had all the group chat IDs and who was in which group chat, but MySQL ...
1
vote
1answer
49 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- ...
0
votes
1answer
42 views

SQL query does not recognise derived table

So I've made a simple SQL query: SELECT companyname FROM works w1, ( SELECT companyname,sum(salary) AS sumsal FROM works GROUP BY companyname ...
2
votes
1answer
71 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. ...
0
votes
3answers
53 views

MYSQL 8.0 server not found in Workbench directory in Windows 10?

I have upgraded my workbench to 8.0CE and also removed the previous 5.7 MYSQL server as well as Workbench 6.3. This is the directory: C:\Program Files\MySQL\MySQL Workbench 8.0 CE Also when I typed ...
0
votes
1answer
32 views

MySQL 8.0 Triggers Current_User not working

I have this trigger that inserts a row to a table CREATE DEFINER=`root`@`localhost` TRIGGER `user_log_AFTER_INSERT` AFTER INSERT ON `user_log` FOR EACH ROW BEGIN INSERT INTO ...
1
vote
1answer
57 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 ...
3
votes
1answer
80 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), ...
0
votes
0answers
54 views

MySQL creating database really slow

I had a problem on my MySQL server, where creating tables/databases is really slow (had to wait a couple of hours), so I reinstalled the server and MySQL from scratch, but the problem didn't go away. ...
0
votes
2answers
26 views

Where is the cache for the MySQL caching_sha2_password auth plugin stored?

Where is the cache for the new default authentication plugin caching_sha2_password in MySQL 8 stored? I want to know for future reference how to clear it in case I ever run into any problems using it.
4
votes
3answers
396 views

Why mysql remove query cache in 8.0 version?

Why mysql remove query cache in 8.0 version ?
2
votes
1answer
150 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 ...
0
votes
1answer
38 views

get row/value by primary key number

I'm writing my own python wrapper on top of official mysqldb connector. I'm trying to design method that would dynamically extract row data knowing KEY ID. SELECT * FROM cameras; +----------+--------...
0
votes
1answer
45 views

MySQL upgrading from 5.7 to 8 - Throwing strange error and won't start up

I followed the instructions on the MySQL web site and got MySQL server 8 mostly installed but it won't launch because it fails pretty quickly during the upgrade process. The apt-get portion installed ...
2
votes
2answers
32 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
1answer
18 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 ...
0
votes
1answer
49 views

MySQL MSI appears to be installing wrong (latest) MySQL version

For compatibility reasons with other software, I'm trying to install MySQL 5.6.40 on Windows 10. Any version from 5.7 onward is incompatible with my other programs. I've been trying to use the MSI ...
1
vote
1answer
319 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 ...
0
votes
0answers
602 views

MySQL: ROW_NUMBER() OVER PARTITION Question

I am looking to essentially recreate the ROW_NUMBER() OVER PARTITION function that I have used in SQL Server Management Studio previously to do the following in MySQL. MySQL doesn't seem to want to ...
3
votes
1answer
246 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 ...
4
votes
1answer
1k 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
1answer
1k 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 ...