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
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 ...
