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