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