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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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

-1
votes
1answer
29 views

MySQL ZERO warnings

I'm getting the error below in my MySQL log file. [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with ...
0
votes
1answer
15 views

Want to output post title with meta-tags at same time on MySQL of WordPress

I want to edit meta-tags of a blog page of my WordPress blog using SQL editting tool such as MySQL workbench. I want SQL output such as below. Can not? # post_title, metatag 'metadev', 'hoge' '...
1
vote
1answer
21 views

SIZE is no valid input at this position error in MySQL 5.7

I want to create user-defined function including SELECT. I tried below SQL script. DELIMITER // CREATE FUNCTION SEARCH(TEXT CHAR(250)) RETURNS VARCHAR(SIZE) # => ERROR: SIZE is no valid input ...
1
vote
2answers
37 views

Best Indexing for Performance - MySQL

I have a question related performance DB MySQL. I have querying like this with big data inside : SELECT * FROM tableA a INNER JOIN tableB b ON a.id = b.id AND a.code = b.code ...
1
vote
1answer
42 views

Innodb updates records with delay

I have two tables in my database like these: CREATE TABLE `main` ( `id` int(11) NOT NULL AUTO_INCREMENT, `type` int(11) NOT NULL, `status` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB ...
1
vote
3answers
851 views

How to delete duplicate records in MySQL, in a table without IDs?

I need to delete the duplicate records in this table. However, there is no id for each row. Example Data +---------+--------+----------+ | product | amount | quantity | +---------+--------+----------...
1
vote
1answer
15 views

Wrong sort order using utf8_unicode_ci and swedish characters like ö

I am confused about what encoding and collation I should use so my queries returns the correct sort order. Right now my encoding is UTF-8 Unicode and collation utf8_unicode_ci for both firstname and ...
0
votes
1answer
15 views

Cant update the student table in mysql with grade and total using procedure

Please help me ....!! I am just learning to use the mysql database.Now what i am trying to do is creating a table that contains roll number, name, Marks in 3 subjects as three columns, and total and ...
0
votes
1answer
15 views

Zero Date Values & Converting Entire DB From MyISAM to InnoDB

I'm trying to update the MYSQL DB for an older wordpress/woocommerce site that's currently using MyISAM by switching the engine type to InnoDB. The issue I'm having using Alter Table is that there's ...
0
votes
0answers
7 views

Add ID and Type from supplemental tables to parent table using SQL (MySQL & Laravel Polymorphic relations)

Note. Copied from StackOverflow I have one table that records all sales and fourteen supplemental tables which contain extra information about a sale. The fourteen supplemental tables are for all ...
0
votes
0answers
19 views

Adding 4th node in MySQL group replication stuck at RECOVERING

Environment: OS: Debian 9 MySQL: 5.7.23 (Running in group replication cluster) Issue: The cluster is running fine with 3 mysql nodes in group replication and all are working fine. While adding a ...
0
votes
2answers
39 views

Ubuntu cannot open shared library mysqlx.so

MySQL Server version: Ubuntu: 5.7.23-0ubuntu0.18.04.1-log (Ubuntu) MySQL installed using apt-get. When installing a plugin with the following command: mysql> INSTALL PLUGIN mysqlx SONAME '...
0
votes
0answers
30 views

Concurrent connections queue up queries too much causing MySQL has gone away error [closed]

I am using innoDB for MySQL 5.7 with 3.75GB of RAM and I have not done any optimization other than adding index to my tables. My problem is that everytime I have a high amount of traffic coming in (e....
1
vote
3answers
80 views

Returning 0 if value is NULL

SELECT p.id , ig.NumReceived , og.NumShipped , coalesce( (p.StartingInventory - og.NumShipped + ig.NumReceived), p.StartingInventory ) as OnHand , p.ProductName , p....
1
vote
1answer
24 views

Mysql Pivot rows into dynamic columns with unknown number of columns

I have an output of a query as follows: bid | code | amount | ----------------------------- 2915 | BF | -10700.00 | 2915 | YQ | -300.00 | 2915 | YR | 0.00 | 2915 | YM ...
1
vote
1answer
40 views

Why is MySQL slow on the Ubuntu dedicated machine while fast on the Ubuntu VM?

I have a dedicated machine with intel i5, 8GB RAM, 7200RPM HDD with Ubuntu 18.04 OS. I have installed only MySQL-5.7.22 in it, see the configuration file's content at the end of this question. I ...
2
votes
1answer
30 views

Migration of table from MySQL to Oracle using GoldenGate

I have a table of size 120 GB in MySQL 5.7 which I need to migrate to Oracle 12c. I am using Oracle GoldenGate for initial load of the table. I tried with initial load of full table but it consumed ...
0
votes
0answers
20 views

How to recover the database after mysqld --initialize?

I have a problem with my database, today I suddenly can't login using root. So I look for solution on the web and made a terrible decision, I used the command mysqld --intialize. Now I can login to ...
0
votes
1answer
36 views

select max value from 2 tables (mysql)

i have two identical tables: +-------------+------------+--------+--------+ | domain | date | score | source | +-------------+------------+--------+--------+ The table contains a domain ...
1
vote
1answer
27 views

Strange issue with mysql index cardiality

I am facing a weird issue when trying to understand (explain) a query. I have this table: CREATE TABLE `trk_track` ( `track_id` int(11) NOT NULL AUTO_INCREMENT, `track_date` date NOT NULL, `...
1
vote
1answer
33 views

Mysql 5.7 cannot start again

For the first time in my life, I have Ubuntu, so I do not know much how to manage and what should I be looking for; I'm using version 18.04. I installed MySQL 5.7, it worked great for a couple of days ...
0
votes
1answer
28 views

MySQL 5.7 UPDATE slow due to not using index

We have an issue similar to this. Only, the solution there won't work for us as: We don't use multi-table JOINS, but UPDATE … WHERE `userid` IN (<list of 10k ids>); We currently "only" have the ...
1
vote
3answers
63 views

What can be best database schema for multiple project and one database? [closed]

I am working on multiple projects but the back-end will remain the same. I have a different server for auth, data, etc. and one database to store all the details. If one projects are creating any ...
0
votes
0answers
20 views

mysql-binlog-connector-java - find first log file name in binary log index file ERROR

I am doing a POC on this library, and I am having some difficulties setting it up. I have an extremly simple MySQL setup: a single VM (no master-slaves) centos 7 MySQL version 5.7 Here is the code ...
1
vote
0answers
19 views

Long INSERT on large table blocks INSERT on other tables

Server is MySQL 5.7 and the tables are InnoDB. The large table consists of 20+ GB of data. The large table has several indexes on it and also full_text_indexes! Use-Case explained: There are ...
1
vote
1answer
16 views

Is it possible for MySQL v5.7.6 to to implement mesh replication with multi-source replication

As I know starting from MySQL v5.7.6 the multi-source replication is possible. So If we can have a slave that can get data from multiple masters. Can we have mesh replication, where each node act as a ...
3
votes
1answer
16 views

MySQL 5.7.21 not releasing memory after writing partition to disk

I've been testing extracting with SELECT * from a partition in a partitioned table in MySQL 5.7.21 and 5.7.22 I'm out of ideas and hitting my head on a wall. The goal is to write the data to S3 for ...
0
votes
0answers
35 views

Dynamic MySQL events implementation with triggers

I have a case in which MySQL event will get fired on time and date stored in the table with calculating from the CURRENT_TIMESTAMP and will automatically update the values using trigger. I have ...
0
votes
1answer
23 views

Update value in table based on condition in other table with trigger

I have 2 tables, first stores the details of all the races 1: and second the users who have joined which races 2: I want to update the race_status to Full and record_status to 2 in race table, if ...
1
vote
0answers
19 views

How to fix fragmentation issue with INFORMATION SCHEMA tables? [closed]

An instance in Amazon RDS (innodb_file_per_table=1) has a database of size 1G, however, the instance is continuously taking up space and we had to grow its size to 250G. All other tables except the ...
1
vote
0answers
27 views

Microsoft Azure - Azure Database for MySQL Server - Connection Security & Server Parameters not showing in Azure Portal and Azure CLI [closed]

I deployed one Memory Optimized instance with 2vCores CPU, 600GB Premium SSD and it is locally redundant. I restored almost 250GB of SQL dumps into the new instance. I noticed halfway through, the ...
1
vote
0answers
19 views

Exception for grant create user

I'm trying to create an account (tenant) which has full privileges on all databases called test_... It should also be able to create other users with also only privileges on those databases. As root: ...
0
votes
1answer
24 views

Permanent solution for Fixing buffer size

I have installed Mysql 5.7 on windows server 2016 and set the buffer pool size to 4 GB through sql query but when the server is restarted buffer pool size getting reduced resulting in the slowness of ...
3
votes
2answers
113 views

“Create Unique Index” on big table taking too long time [closed]

I am trying to restore GHTorrent's database dumps (CSV files containing GitHub metadata). The commits table has more than 891 Million rows and the project_commits has more than 5.4 Billion rows. Since ...
1
vote
0answers
10 views

X-devAPI not working inside of Google cloud SQL

I am tryng to use X-devAPI inside of Google cloud SQL but getting the following error from MySQL client comp@user:~$ sudo mysqlsh -u root -h XX.XXX.XX.XXX --mysql --dba enableXProtocol [sudo] ...
0
votes
0answers
10 views

Does MySQL 5.7 support multi-source replication with GTID enabled?

The plan was to retrieve data to one MySQL db and keep replicating from different sources. The problem is all the upstream DBs are base on GTID, since GTID is globally identical, is this even possible ...
0
votes
1answer
24 views

Insert a sequence of number where some numbers could exist already / fill gaps?

So before I make this completely in my main programming language I wanted to know, if there is any way to accomplish this with pure SQL? Also I think it would be a lot more performant. MySQL Version ...
4
votes
1answer
31 views

Will increase in Innodb_buffer_pool_size increases insert query performance?

We have 3 identical database boxes(M1<->M2->S1) with Mysql 5.7.17 installed and having memory 96GB RAM. In these boxes nearly 42GB occupied by Index and data length of Innodb Tables and 38G ...
2
votes
1answer
28 views

Upgrading from single server to new GTID-replicated master and slave MySQL 5.7

I'm going from a single, non-GTID MySQL 5.6 server to a master-slave replicated setup on 5.7 (could be 8.0 too) with GTID on CentOS 7.x. Some details: format = ROW, minimal logging, file_per_table ...
1
vote
0answers
27 views

mysqldump generates incorrect create view queries

I use mysqlpump to backup database: mysqlpump --single-transaction --add-drop-database --skip-definer \ --databases mydatabase --result-file /opt/myservice/backup/export.sql My DB contains several ...
1
vote
0answers
14 views

Inserts locking mysql 5.7

We currently are seeing record locks during insert after we upgraded mysql from 5.6 to 5.7. I have disabled the deadlock detection and logging everything but still can't determine why the locks are ...
0
votes
1answer
33 views

Mysql search varchar column strategy

I can't find decent strategy for searching titles. I have table for movies. Each movie has 5-10 alternative names that are stored in movie_names (id, name, name_clean, movie_id) table. To normalize ...
1
vote
1answer
582 views

What is mysql_native_password?

I was trying to set password for root. When I run: mysql> SELECT * from mysql.user where User="root"; It shows: +-----------+------+-------------+-------------+-------------+-------------+-------...
3
votes
2answers
62 views

MySQL v5.7 shutting down automatically [duplicate]

Every 5~ days MySQL just shuts down. I turned on error and general logging and I haven't found anything that would suggest why it's happening. The MySQL server was installed from a generic binary and ...
2
votes
1answer
326 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 ...
0
votes
2answers
60 views

Worse performance after create empty table

I'm working with MySQL 5.7.10 and I have this issue. I have this table to trace requests: CREATE TABLE `invoice_requests` ( `REQUEST_ID` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_spanish_ci',...
0
votes
0answers
16 views

After upgrading to MySQL 5.7 queries on Views not using underlying indexes of tables

select final.grad_year_flag , final.registration_id , final.first_name , final.last_name , final.grad_year , final.photo_id , final....
1
vote
1answer
31 views

What does MySQL actually use to decide which index should be used?

I know there is already similar question here like How does the database decide which Index to use. But it seems that those answers are not cool and useless. In my development and use of MySQL, I ...
0
votes
0answers
81 views

Create temporary tables in read only transaction - MYSQL 5.7

I am currently assisting with integration testing for migrating an application that currently runs against a mysql 5.5 DB to mysql 5.7. We have run into issues related to functionality that was ...
3
votes
2answers
74 views

Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column?

I am using MySQL 5.7 and have a table that has the following columns that are being used to store some data that is source from an Apache common log formatted access log; details extracted from a ...