Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
0
votes
0answers
10 views
MySQL 5.7 Replication Lag
I am facing a wierd situation regarding Replicatio between two 5.7 , my replication is lagging by hours and the Exec_Master_Log_Pos and Relay_Log_Pos won't change and got struck , the issue is with ...
0
votes
0answers
21 views
When does autocommit take place when many rows are inserted as a single statement?
I have some problems with performance of insertion of about 50000 rows into a table. As an optimization it's advised to start a transaction explicitly to avoid committing on each row. However, I ...
0
votes
0answers
20 views
Mysql: Insert into JSON column type with GROUP_CONCAT
I'm trying to insert a list of words into a column with the JSON type:
create table my_table (
...
synonyms JSON
...
)
My query looks like this:
INSERT into my_table (id, synonyms)
...
0
votes
1answer
23 views
Mysql 5.7 - select where json
I have some JSON data stored in a filters column. An example from one row:
name | filters
---------+----------------------------------------------------------
John Doe | [{"Year":2004, "SportID":...
1
vote
0answers
38 views
The GET_LOCK() and RELEASE_LOCK() functions are taking too long
We are trying to use the MySQL's locking functions GET_LOCK() and RELEASE_LOCK() in our Spring Java application. The code we are using is defined in a procedure for both seperately which simply invoke ...
0
votes
0answers
38 views
buffer overflow detected in mariadb 10.3.11
We are getting Assertion failure every time memory usage of MySQL spikes up to the overall system's memory.
Example:
InnoDB: Assertion failure in file
/home/buildbot/buildbot/build/mariadb-10.3....
0
votes
1answer
31 views
Selecting N values from each group
I have 2 tables which have to be joined to get needed data.
Table1 is articles, Table2 is for channels/categories.
For the sake of simplicity, both tables only have 2 fields.
Basically, I need ...
0
votes
1answer
48 views
Trigger not setting DATETIME correctly
I have the following table:
CREATE TABLE assignment (
id INT NOT NULL AUTO_INCREMENT,
course_id INT NOT NULL,
title VARCHAR(128),
assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
due ...
0
votes
0answers
6 views
Mysql Shell doesn't change the configuration of my database for group replication
I am attempting to use a preexisting database on multiple nodes in a group replication setting. I am using mysqlsh 8.0.16, mysql server 5.7.24 and ubuntu 16.04 as the host.
When I attempt to ...
0
votes
0answers
13 views
MYSQL 5.7 Online DDL (Alter) Statement causing Lock_Wait_Timeout : AWS RDS
We are not able to do updated code publish from application on MySQL 5.7 as it contains ALTER statement on the table which has more than 50 million records and application team escalated that most of ...
0
votes
1answer
31 views
MySQL: columns with mixed character encodings, and finding columns which have multibyte data
I have some rather large CSV files that I am loading into my MySQL 5.7 database. The files are several gigabytes in size, several million lines long, and have large column widths that must be used in ...
0
votes
0answers
26 views
MySQL: High CPU/Load after linux OOM killer hit
We have a single VM running MySQL 5.7.20 with 64GB memory and 4 vCPUs (Xeon E5-2690 v3 @ 2.6GHz). This MySQL node is dedicated to our Atlassian Confluence application so it's not having any other apps ...
0
votes
0answers
15 views
Restoring backup with a MySQL Database downgrade
I want to migrate a MySQL 5.7 database to a MySQL 5.1 version because the MySQL version of the server I’m migrating my WordPress webpage is 5.1. I tried to do it in the usual way but it was not ...
0
votes
0answers
20 views
innodb buffer pool size not changing
I have Percona fork of MySQL 5.7 installed where I have defined innodb_buffer_pool_size in /etc/percona-server.conf.d/mysqld.cnf as follows
innodb_buffer_pool_size = 10G
But, when I login to MySQL ...
0
votes
1answer
42 views
Master Master replication rows not sync properly
I have a wordpress website that run on 3 machines
Machine1 | Machine 2 {nginx+lsyncd+mysql}
visitor > cloudflare > haproxy ------
...
2
votes
2answers
92 views
Maximum possible memory usage: 3.2G (163.34% of installed RAM)
I get frequent database crashes... explainable since mysqltuner tells me mysql has an appetite for memory. Since I'm not a database expert I hope you can point me in the right direction where to look ...
0
votes
2answers
44 views
Load big file in MySQL table
I've read some posts related to issues similar to this, but without success, so I'm trying to post my specific problem.
I have to load a big CSV file (25Gb) into a MySQL table. I'm doing this with ...
1
vote
1answer
24 views
Is it possible to change between SELECT column based on an “IF” statement? and use INNER JOIN variables?
I have a table of 'mapped_products':
product_id | price
A table of 'product_id_to_name':
product_id | product_name
(please don't ask me why, this is an already built web-app i am working on)...
1
vote
1answer
52 views
Select most recent event for each plate on a property unless the most recent is “Out” - self-join?
My cameras are positioned on the entry and exit gates of a parking garages. As cars pass the cameras, they capture the license plate and other attributes and load it into a database in real time. I ...
0
votes
1answer
21 views
Is Audit plugin available for Community edition?
MySQL version 5.7.26 MySQL Community Server (GPL)
OS : CentOS 7.6
I was trying to install audit plugin for MySQL database. But, I encountered the following error
mysql> INSTALL PLUGIN audit_log ...
0
votes
0answers
9 views
mysql-5.7 and php5.6 - error on LONGTEXT Field type saying a 387 character long string cant fit in
Ok Here is my frustration cause:
I've got this table structure:
CREATE TABLE `blog_post` (
`bp_id` INT(11) NOT NULL AUTO_INCREMENT,
`user_fid` INT(11) NULL DEFAULT NULL,
`cat_fid` INT(11)...
0
votes
1answer
52 views
Why a certain field causes a slow query?
I have 6 almost identical queries on a page. I am loading logs by keyname.
Lattitude and longitude take over 4s each, where as 4 other values take 0.09s How could just the one text value in where ...
0
votes
1answer
31 views
Partitioning with AUTO_INCREMENT primary key
I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table ...
0
votes
0answers
28 views
Storing the way a field value(s) is(are) stored in the database
Context
I have tables in which some fields can be edited by a user through a form.
The form is presented as an information sheet about a person : some fields are read-only, others are editable, some ...
0
votes
1answer
9 views
DATE_SUB with configurable value
In MySQL you can use the DATE_SUB function to get a new date based on the values passed in. For example I can use the following to get the a date of 30 days ago from when I run the function
SELECT ...
0
votes
0answers
42 views
INSERT INTO SELECT for a large table
I have a large table (200 million rows, 40GB). I tried to create a new table out of it by INSERT INTO SELECT. The process took 5 hours and consumed over 60GB of RAM. The size of the ibd file of the ...
1
vote
1answer
28 views
How to sum of column by depending other column in my sql?
how to calculate / SUM of column by the latest data of updateid column ?
I try this :
SELECT * FROM yourTable;
SELECT
ANY_VALUE(code) AS code,
COUNT(*) AS total_rows,
SUM(data->'$.chart."21"') ...
1
vote
1answer
60 views
DBeaver's Auto Increment field max is 2147483647 - why?
In DBeaver, when right-clicking the 'Foreign Keys' to select 'View Foreign Keys', the following informational page is displayed:
I would think that the 'Auto Increment' field value would read 1 (or ...
4
votes
6answers
227 views
MySQL join has drastically worse performance after upgrading from 5.6 to 5.7
I have a fairly straightforward query that has become very slow after upgrading from MySQL 5.6.35 to MySQL 5.7.12 (running on AWS RDS).
SELECT DISTINCT
Name,d.id,deviceType,issuedBy, description,...
0
votes
1answer
29 views
How to restore mysqldump of one database in different databaseName with content of data in the same host machine
Let's start from the beginning there is a MySQL database with name DB1. As I want to restore the mysqldump backup of DB1 database in DB2. The DB2 database is another database in same Host Server ...
4
votes
1answer
74 views
Is there a way to improve performance of this simple SELECT query?
I'm still relatively new to MySQL so please forgive any ignorance on my part. I've tried looking through other posts on here and StackExchange, but haven't had much luck in improving the performance ...
4
votes
1answer
66 views
How to avoid deadlock for this query?
I am running a MySql 5.7 database and I have a query which results in a deadlock as it is running in multiple threads.
I am trying to figure out how to avoid this deadlock, but so far I haven't been ...
3
votes
2answers
49 views
Query is faster with additional JOIN
SELECT COUNT(DISTINCT User_id) FROM Session
GROUP BY Floor_id;
SELECT COUNT(DISTINCT User_id) FROM Session
JOIN Floor ON Floor.id = Floor_id
GROUP BY Floor_id;
The second query runs 20x faster ...
1
vote
2answers
65 views
MySQL: need help making a query faster
I wrote a query that finds me records in discovery table that contain a needle from whitelisted (LIKE %needle%) and don't have a record in logs table (and some other easy to understand filtering):
...
0
votes
1answer
84 views
MySQL failed to handshake due to LOCK_thread_cache not released?
issue description
MySQL failed to connect, and too many connections expcetion is thrown. But after that, port is open, mysqld failed to respond packets including version and plugins of password,mysql ...
1
vote
2answers
78 views
MySQL using all RAM
I am trying to restore SQL dump file of around 750 MB on Ubuntu 16.04 VM with 1GB RAM and MySQL 5.7.21.
I have configured innodb_buffer_pool_size = 768MB, innodb_buffer_pool_chunk_size = 128M and ...
1
vote
0answers
14 views
AWS DMS - How to select different databases for source and target points for a task
Does aws DMS(Data Migration service) allow to select different databases for source and target points for a task in a replication instance? My source engine is MySQL.
example: I want to move table A ...
0
votes
0answers
9 views
Huge difference between data_length and index_length for a MySQL table
When i pulled up my top 5 mysql tables in terms of size, i could see the the index_length is higher compared to data_length.
I have a same table(same schema) for the same application in SQL Server ...
2
votes
1answer
110 views
MySQL 5.7 Community Server End of Life/Support
Is there any official document that outlines the date till which MySQL Community Server 5.7 will receive security updates/patches? I see that page 20 of this document from Oracle outlines Premier and ...
1
vote
2answers
54 views
Parallel replication is not working on a 5.7 slave from a 5.7 master
Note: Following are the parameters that we turned ON on the 5.7 replica for parallel replication to happen:
slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers =...
2
votes
2answers
58 views
Change user password on slave
Our users can only access the slave database.
How can I let them change their passwords on their own or when their passwords expire?
0
votes
0answers
39 views
Joining Multiple Tables with 2 aggregated tables
I'm using mySQL 5.7
I have seen this question a couple times and it seems like the answer was followed exactly but only my first row of data is correct.
I'm trying to join 3 tables 2 of them are ...
0
votes
0answers
28 views
mysql run out of memory during mview refresh
I want to create mview from mysql to Oracle using DBLink via odbc driver with Linux 64 bit os. but due to large number of Records around 2.5 cr records it raise the error
ERROR at line 1:
ORA-28500: ...
0
votes
2answers
26 views
Query Error when trying to run query
Please bear with me as I am relatively new to SQL. I am using MySQL and trying to run a query against a DB with two tables ('Network', and 'Server'). When I trying a portion of the query it executes ...
0
votes
1answer
23 views
MySQL Conditional Counter Based Other Columns?
I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is ...
0
votes
0answers
8 views
Stand by Percona 5.7 RAM utilization reach 95%
I'm new to MySQL and Linux OS. I'm more an MS SQL Server Developer, not even a DBA, but I've been assigned this role os MySQL DBA.
We have an instance of Percona 5.7 in a virtual node with 12Gb of ...
0
votes
1answer
167 views
MySQL `where…in` statement - does it remove duplicates?
I'm curious about if MySQL's where ... in (...) statement removes duplicates as an optimization.
For example, if I use a subquery, is it important that I use DISTINCT to remove duplicates?
An ...
2
votes
1answer
33 views
In MySQL, how can I have non-nullable timestamp column without a default value?
I want a non-nullable timestamp column where the inserter is required to specify what the value should be.
The following configuration
CREATE TABLE test (
t timestamp not null
);
results in the ...
2
votes
2answers
34 views
UPDATE with CAST(meta->>“$.field”) failing due to “null”
This issue is affecting MySQL 5.7.25.
So, in a migration I'm moving legacy fields from the table into a JSON column. This part of the process works fine. But, the rollback is failing miserably! At ...
1
vote
2answers
37 views
Count results after filtering out
I am using MySQL 5.7 and need to count results after filtering out the data.
My table structure:
+----+---------+---------+---------+
| id | metakey | metaval | tour_id |
+----+---------+---------+--...
