Stack Exchange Network

Stack Exchange network consists of 175 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
1answer
19 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 ...
0
votes
2answers
38 views

How to group multiple ROW as ARRAY

This is my current Query: SELECT innings_no, Player_Name, sum(NoBalls) as NB FROM ( SELECT A.innings_no, A.bowler, ...
0
votes
0answers
17 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 ...
0
votes
2answers
20 views

How to set all auto increments to 1 in a Schema

Is it possible to reset all auto increment in a schema in one query? if not even just by per table will do. btw I am using MySQL server 8 thanks in advance :)
0
votes
0answers
36 views

MySQL how to GROUP SUM based on JOIN TABLE

I have Three Table, One Master Table and Other Two Table have relation with it. Below is the Three Table: 1. ball_by_ball table, this table holds full information by ball by ball: CREATE TABLE `...
1
vote
1answer
36 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
44 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 ...
0
votes
1answer
26 views

Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8

I am looking into upgrading some major components of a webserver (php, apache, mysql). I've done the update to 5.7 from 5.6. I've updated to php7.3. What I wonder now is: I see that a mysql ...
2
votes
1answer
28 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 ...
0
votes
0answers
7 views

MySql slaves on Amazon EC2 RHEL 7.6 server not getting connected automatically after mysql service restart on master

I have setup MySQL Master - slave replication on Amazon EC2 instance. OS : RHEL 7.6 MySQL Version: 8.0.13 Master server IP : 192.168.1.128 Slave server IP : 192.168.1.129 I have two EC2 instances on ...
0
votes
0answers
20 views

Reverse the order in the table derived from lateral join

Example table: CREATE TABLE `level_2` ( `id` int(11) NOT NULL, `comment_id` int(11) DEFAULT NULL, `comment_parent` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `user_name` varchar(...
1
vote
1answer
26 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 ...
0
votes
1answer
31 views

MySQL How to calculate Partnership Scores from Cricket ball by ball database [Solved]

I'm trying to create a simple cricket application, where i will store all cricket records from a database. I collect a sample database with data from online and i successfully did some query but I'm ...
0
votes
1answer
30 views

Limit groups of results based on a column value

I'm not sure I formulated the question properly, but anyway. I have a table with level-2 comments, some of which, expectedly, have the same comment_parent column value. How do I make a SELECT query ...
1
vote
0answers
23 views

Why is my InnoDB table size so much larger than the data?

I have a MySQL table (version 8.0.13). One table is unreasonably large and doesn't correspond to the current data. I rebuilt the table by running ALTER TABLE users FORCE and OPTIMIZE TABLE users, but ...
0
votes
0answers
23 views

Why sum with previous and current column do't working

I want to update my specific row with that row previous value, after searching i found some post about this but those solution not working with me. So what is wrong with my query or how can i ...
0
votes
1answer
19 views

which one is faster: “insert on duplicate update” OR “update with case”

Table: CREATE TABLE t5 ( col1 VARCHAR(36) NOT NULL PRIMARY KEY, col2 int(11) DEFAULT NULL, col3 int(11) DEFAULT NULL, UNIQUE (col2) ); Records already inside: insert into t5(col1, col2, col3)...
0
votes
1answer
16 views

Does MySQL or MariaDB support a LineString constructor that takes points?

It seems with MySQL and MariaDB I'm getting the following when I try to use the LineString constructor. In PostGIS I can do this with ST_MakeLine(pt1,pt2) > SELECT ST_MakeLine(Point(0,0), Point(0,...
1
vote
1answer
21 views

Logs from Syslog-ng to MySQL

Syslog-NG ver 3.18 MySQL ver 8.0.15 I configured my Syslog-ng as follows: source s_network { udp(); tcp(); }; destination d_file { file("/var/log/app_network.log"); }; destination ...
0
votes
0answers
28 views

Restoration in MySQL 8 is slower than MySQL 5.6

Recently I have installed mysql 8.0.12 version and started observing that its taking too long to restore database. So I verified the same with using a backup file which I restored to MySQL 8.0.12 as ...
0
votes
1answer
39 views

Query with HAVING clause is taking too long

I have query like this: SELECT msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at', category, msg.`uid`,name, user_name, profile_pic, STATUS, isNotificationOn, ...
0
votes
1answer
25 views

Can MySQL show the respective timestamps in each INSERT output message?

For example, after an INSERT, I see: Query OK, 296 rows affected (0.02 sec) Records: 296 Duplicates: 0 Warnings: 0 I would like to see timestamps with these lines, if possible, e.g.: 2019-...
2
votes
1answer
29 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 ...
0
votes
1answer
45 views

Regex replace with empty string results in NULL characters in between

Replacing a character with regexp_replace results in NUL characters in between. SELECT REGEXP_REPLACE(aColumn,'c', '') INTO OUTFILE 'Replaced.csv'; for abcd results in: ab d (infact: abNULd) This ...
1
vote
2answers
22 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 ...
0
votes
3answers
45 views

mysql 8 - rds - 1022 Can't write; duplicate key in table

Documenting this error and solution here in case it could help anyone. We have a MYSQL 8 database on Amazon RDS. The below group by used to work just fine on Mysql 5.7 but it started giving 1022 Can'...
3
votes
2answers
260 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 ...
0
votes
1answer
886 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 ...
0
votes
0answers
63 views

Mysql 8.0:Innodb index performance loss until server is dead. Performance perfect after index is re-created?

Important Update Spent 15 hours on this problem, rebuilt and changed indexes and the table many times. Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue. The "...
0
votes
1answer
31 views

How can I append a new datafile in an existing InnoDB tablespace?

I want to add a new data file in an existing InnoDB tablespace so that entry of data is smooth in case it hits its 4GB limit.
0
votes
0answers
24 views

Rank users by different periods and overall

I'm trying to create a ranking system for a game which should have 2 different rankings. Below you will find the Model (or the result after querying the database) which should be presented to the ...
0
votes
1answer
33 views

mysql 8 information_schema.processlist -> correct approach to cache it?

I have a high concurrency mysql 8.0 server. Thousands of tasks happen per second (up to a million rows per second read in average) In some of the tasks that can be very performance intense I have ...
0
votes
0answers
28 views

I'm new to MySQL and can't load data due to secure-file-priv and local_infile variable settings

I've tried just about everything I can find out by searching Google. I'm running a local install of MySQL community server 8.xx on macos 10.14. I installed from the dmg, and Workbench and created a ...
1
vote
3answers
205 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 ...
0
votes
1answer
42 views

Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE

I upgraded to MySQL 8.0.13 and tried to add a new field using ALGORITHM=INSTANT but got the following error: Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=...
1
vote
1answer
53 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
0answers
115 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
26 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
63 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
46 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
1answer
204 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
35 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
88 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
40 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
30 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
20 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
46 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 ...
0
votes
1answer
78 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
73 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
31 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 | ...