Questions tagged [mysql-8.0]
MySQL version 8.0 - please also tag with mysql for search purposes.
184
questions
0
votes
2answers
35 views
How to select all columns plus one alias column in MySQL
It is a simple question, but i could not find the answer.
I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ...
0
votes
0answers
79 views
I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7
I've installed mysql 8 on centos 7
and when i want to start the mysqld service , it says :
Job for mysqld.service failed because the control process exited with error code. See "systemctl status ...
0
votes
2answers
57 views
Mysql 8: count(*) on INT primary is 13500 times slower than supposed to be. any innodb dev here?
I am aware there are many questions on this topic, mysql has not improved much on these single-query performance bugs in the past years but my case is more unusual.
If mysql would access the table ...
0
votes
1answer
23 views
MySQL CURDATE() and CURRENT_TIMESTAMP return different value?
I am facing a little confusion because of CURRENT_DATE value and CURRENT_TIMESTAMP value shows different date on MySQL.
Scenario :
1) User calls API from Lumen backend to insert data on a table. One ...
0
votes
1answer
19 views
how to enalbe MySQL 8 innodb_dedicated_server option
Initially, I was looking for tuning the MySql 8 instance then I found that there is something like autoconfiguration feature added with the 8th version. Then I visited the official MySQL website for ...
1
vote
2answers
47 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. ...
0
votes
1answer
34 views
Comparison and creating new column
Table A
> - Product#|Customer|state|Type
Glasses |mr. Xyz | MN |Instore
Glasses |mr. abc | MN |Instore
perishables|mr. abc12| TN |Instore
N-perishables|mr. abc122| ...
0
votes
1answer
30 views
Joining two select statements
Im using MySql 8.0 and I've been exercising a lot of quries but this one I cant seem to figure it out. I need to join somehow two select statements that I've written and they display the correct ...
0
votes
0answers
7 views
NDB cluster cant create new table. Error code:1005
While creating a new table i am getting Error code 1005.
Here is my query:
CREATE TABLE `ps_subscription_persistence_1`
( `id` varchar(40) NOT NULL,
`packet` varchar(204) DEFAULT NULL,
KEY `...
0
votes
2answers
63 views
MySQL Coalesce date missing results, why?
Running MySQL 8.0.16, settings left at defaults as far as I know.
I have a table, let's call it the_table, with columns
id: bigint primary key auto_increment
the_time: datetime
I have a record ...
0
votes
0answers
24 views
InnoDB Cluster Error
Is anyone encountered this error "Unable to join the group: peers not configured" in creating Innodb cluster? or I need to configure group replication first? I follow the documentation step by step ...
0
votes
0answers
7 views
MySQL 8.0 appending unknown character to column
I'm inserting a CSV files into MySQL 8.0 using Python 3.7:
bulk_insert_sql = "LOAD DATA LOCAL INFILE filename.csv INTO TABLE table_name FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 ...
0
votes
1answer
26 views
SSL Configuration fails with a warning MySQL 8.0
Hi I am trying to set up a MySQL server 8.0.16 on RHEL 7.5. The configuration I am willing to include for this server contains SSL related options.
The MySQL server is initialized but with a warning ...
0
votes
0answers
26 views
Error importing MySQL/Percon server tablespace for individual tables
I am using Percona-Server 8.0.x and I am following percona xtraback documentation.
https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/restoring_individual_tables.html
I am exporting ...
0
votes
0answers
10 views
Applying batch of row changes (delete) MySQL Replication Stuck
I am using Master Slave replication for master and slave .
Master Configuration
64 GB RAM 2 TB SSD
Slave Configuration :
16 GB RAM 2 TB SSD
My database size is 750 GB.
When i configured ...
1
vote
1answer
52 views
RDS MySQL 8.0.16 has empty “INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS” table
On aws RDS I have an mysql 8.0.16 instance running.
My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty.
This is a problem for me because I would like to create ...
-3
votes
1answer
52 views
From a backup strategy perspective, would it be efficient to use a relational DB (as MySql) and a document oriented DB (as MongoDB) in the same app?
I am developing an app where I need to store an undetermined number of properties for any given entity, for example:
Entity || # of properties**
Apples || 14
Oranges || 4
Pears |...
3
votes
2answers
80 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 ...
1
vote
1answer
264 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
votes
1answer
152 views
Every Attempt at Error Handling Fails with GET
UPDATED (12.3.2019)
I'm using MySQL 8.x and every attempt at putting any error handling that provides internal details fails on the "GET" (red highlight) stating:
GET is not valid at this position
...
3
votes
2answers
120 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. ...
0
votes
0answers
24 views
failure to create tablespace on external drive mysql 8 ubuntu 18.04
I am pretty new to how to configure MySQL and I have bug I can't figure out. I installed MySQL 8 and I am trying to create a table in a different data directory following the instructions provided in ...
0
votes
1answer
34 views
mysqldump waiting for table fush for long time
MySQL version: MySQL Commercial 8.0.16 OS: RHEL 7.6
Following is my mysqldump command
mysqldump --defaults-extra-file=/mysql_data/config/extra_options.cnf --single-transaction --flush-logs --master-...
0
votes
1answer
30 views
Alter on huge table consumes all disk space
I'm running MySQL 8 on Ubuntu 19. I have a table that is 543G and I have to add a new column and index. I've started with the new column:
alter table hugeTable add column newCol tinyint(1) after ...
-1
votes
1answer
49 views
Serializable Transactions vs SELECT FOR UPDATE in MySQL
I have a stock table with columns: id, available, sold, status fields. Before increasing sold field I have to check if sold + x is less and equal than available. To make sure every transaction uses ...
0
votes
1answer
31 views
Create trigger that executes maximum one time per day
I have a table big_table with a large number of daily INSERTs, and this table has a column interesting_val. For the first row, and first row only, which is inserted after 00:00 UTC on each date, I ...
0
votes
2answers
31 views
How can I find out if a user's password has expired in MySQL?
How can I find out if a user's password has expired in MySQL?
The MySQL version I'm using is 8.0.16.
0
votes
0answers
44 views
How to get attendance with Multiple Record?
I have this attendance with multiple record. i can't get the attendance with too much record how do i remove the unusable attendance ?
id user_id verify_date verify_type verify_state ...
1
vote
1answer
72 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 ...
3
votes
1answer
85 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.
...
1
vote
1answer
36 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 ...
0
votes
0answers
17 views
Mysql 8. Event call Stored Procedure with Transaction to Federated tables never finish ESTABLISHED connection until timeout
We have a Mysql server which connect to remote Mysql Federated tables and copy data to local tables.
For several databases it works fine, but the new one did not close connection after Transaction ...
0
votes
0answers
106 views
Error 2026: SSL Connection Error: Failed to verify the server certificate via X509 certificate matching functions
MySQL Server: Enterprise Edition 8.0.16-Commercial
OS: RHEL 7.5 (Maipo) 3.10.0-862.11.6.el7.x86_64
I am trying to understand SSL/TLS enabled connections for MySQL servers. I am trying to configure it ...
0
votes
1answer
26 views
Restoring MySQL 8.0 server from cold backup files
Recently, my database server running MySQL 8.0 crashed due to power outage. I have access to a cold backup (i.e., all physical files) of the server before the crash occurred. I do not have any other ...
0
votes
0answers
35 views
Why query mysql 8.0 response too slow with respect to mysql 5.5
I have the following query which in mysql 5.5, original server runs in approximately 3 seconds but all the opposite with respect to mysql 8.0, which runs in about 3 minutes, a big difference!!!. This ...
0
votes
0answers
27 views
innodb Page offset doesn't match file offset: page offset: 262144,
Our database MySQL8 crashed due to no space left on the data directory. After we arranged more space the database kept crashing with the error
I know that there a lot of threads with this kind of ...
0
votes
0answers
12 views
I'm trying to order a table with 3 columns as if the columns were merged into 1 such that it would sort as 3 separate entries
I've hit a somewhat unique obstacle with my current project. Below is a mock MySQL table to better describe the issue I'm trying to solve.
mysql> describe `minimized_event_instructions`;
+---------...
1
vote
1answer
24 views
LOAD DATA keeps CPU usage high after it finishes
I'm loading multiple files into the database using the LOAD DATA LOCAL INFILE command combined with Golang database/sql driver like this:
mysql.RegisterLocalFile(fileName)
_, err = dbConnection.Exec(...
0
votes
1answer
45 views
Import a large MySQL dump 68GB on RDS instance
I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM).
The import takes more than six hours:
More than 500 tables
I have 2 large tables: the first ...
0
votes
0answers
19 views
How to create a classic session in MySql 8?
I want to make use of the mysql 8 parallel import utility:
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-parallel-table.html
Therefore trying to establish a session as follows:
...
0
votes
0answers
23 views
How to change datetime system timezone to other zones in SQL Query using CONVERT_TZ() SQL Function
I have two times timeA & timeB stored in database, I am using SQL Query to extract data from DB as:
SELECT ID, timeA, timeB, CREATED_AT, IS_ACTIVE
FROM datatable
WHERE IS_ACTIVE = TRUE AND NOW(...
3
votes
2answers
97 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 ...
0
votes
0answers
26 views
Router Configuration for HA InnoDB Cluster
I've set up three Ubuntu MySQL servers (18.04 LTS / 8.0.17) in a single-primary InnoDB cluster and have installed mysqlrouter on an application server.
With the router’s configuration manually ...
0
votes
0answers
15 views
How does MySQL get its timezone information on Windows without the time zone tables package?
I setup MySQL deployments exclusively on Linux, but I have a development environment on Windows that has a MySQL server for testing purposes. To make things easier, MySQL runs with default-time-zone = ...
0
votes
1answer
27 views
Reclaim disk space after drop table from database in mysql
I have this version of MySQL server:
mysql Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)
I use a some large tables. When I truncate database with truncate database <name>, I ...
0
votes
1answer
19 views
Backed up MySql folder… upgraded to newer version and cant seem to recover data
I copied the entire MySql folder mysql-8.0.13-macos10.14-x86_64 and installed a newer version mysql-8.0.17-macos10.14-x86_64.
I should have researched but thought the installer would handle moving ...
0
votes
1answer
41 views
Relay log read fail, relay or master log currupt ,How to repair?
I am running mysql InnoDB cluster 8.0.17(group replication)
During the repication the slave disk gets full and no space left at all the thread waits for the disk space to be freed at the same time ...
1
vote
2answers
141 views
Mysql Daemon cannot start after upgrade from 5.1 to Mysql 8
I preinstalled Oracle Enteprise Linux and it automatically installed Mysql Community 5.1. But I wanna update it to 8.
After update the daemon won't start and the /var/log/mysqld.log shows that
[...
-1
votes
2answers
108 views
Mysql 8 picking wrong index for group by date function [closed]
I have a table as below.
CREATE TABLE `alarm` (
`alarmTypeID` tinyint(4) NOT NULL,
`vehicleID` mediumint(9) NOT NULL,
`alarmDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`...
1
vote
1answer
133 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 ...
