Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
0
votes
1answer
39 views
MySQL Crashed, innodb trying to access page number which is outside the tablespace bounds
I am running MySQL (5.7) on a Windows 2012 server. One of the table (> 1 million records) seems to be corrupted. The mysql error log shows
2019-01-16T09:41:09.256990Z 0 [Note] InnoDB: Buffer pool(s) ...
3
votes
1answer
24 views
Lock database in time
I'm currently working with prototyping some stuff with a dump of a MySQL (5.7) database where much of the data is time dependent. Most of the queries I write use the NOW() function and the results ...
0
votes
1answer
17 views
How to transfer data from mysql 5.5.24 to 5.1.36
I've had a server that is not booting up, it had mysql 5.5.24. I was able to copy the whole /data directory.
I will be temporarily using an older backup server that has mysql 5.1.36 and cannot be ...
0
votes
1answer
24 views
How to optimize a delete query with a subselect? [on hold]
This query needs to delete over 17 million rows, from a table containing 20 million.
DELETE FROM statements
where agreement_id IN (
select id FROM agreements
WHERE created < DATE_SUB(...
0
votes
0answers
12 views
Still getting values for performance_schema.global_status after setting 'Performance_Schema' to OFF
In mysql version 5.7, I have turned off performance schema (performance_schema=OFF in /etc/my.cnf), but still getting values incremented for variables in 'performance_schema.global_status' table. Why ...
0
votes
1answer
24 views
Adding where condition with index make query slower(use filesort and temporary)
I'm trying to implement soft-delete so I added deleted_at (datetime) column to users table.
Also there is a query i need to run:
SELECT
COUNT(*) AS count_all,
`users`.`category_id` AS ...
1
vote
2answers
62 views
MySQL Select Union; Filter by Shared Relation?
Currently, I have 3 tables (see structure below), speakers, venues, and events. I'm trying to do a relevance search on the names of the speakers and venues. However, on the Search Results page, if the ...
0
votes
0answers
39 views
How to increase Mysql 5.7 Performance?
We are running production slave database with 3 TB data in MySQL running on Azure VM with server configuration of 16 core and 32 GB. The major challenge is regarding over all MySQL performance. please ...
0
votes
0answers
8 views
Bugs of derived merge resolved in mysql8
All bugs of derived_merged resolved in mysql8? Is it optimal if i use derived_merged=on in mysql 8.0.11?
By derived_merged=on it is giving invalid output of query when it will uaed with order by or ...
0
votes
1answer
18 views
MySql Not Logging Slow Queries
I've set up slow query logging for MySQL 5.7:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query_log.log
long_query_time = 10
log_queries_not_using_indexes = 0
Then to test ...
0
votes
2answers
28 views
query to replace text with sequential numbers
I have an images database, the default sort is the "name" column, and the "description" column content is the exact same in all files in the table. what I need is to replace certain text in the "...
0
votes
0answers
29 views
Confused mysql index priority
Situation
I have the following query that finds 3 candidates whose total point are larger than a random number.
SELECT * FROM ( ...
1
vote
1answer
32 views
Downgrading from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6
We are trying to downgrade from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6 due to some issues. But AWS RDS doesn't allow Aurora MySQL 5.7 snapshot restore to Aurora MySQL 5.6 instance/cluster. Does ...
0
votes
1answer
29 views
Best mysql config for my server
I have dedicated server, CPU is too high (80%-100%) and memory only use 9gb
RAM 32 gb and CPU Dual Xeon E5-2670(8core and 16 thread)
I have 25000 users that use my android application
My database ...
0
votes
0answers
13 views
mysql cluster with 1 master 1 node and 2 slave
My Query is I want to set up MySQL cluster with 1 Master 1 Node and 2 Slave with Auto failover. If my master is down node should assign slave as a master.
Can anyone suggest me an idea to do that? As ...
0
votes
0answers
15 views
Aurora MySQL 5.7 DDL statements hangs intermittently
We are using AWS Aurora MySQL and we recently moved to MySQL 5.7 version. After moving to this version, we started seeing issues with DDL statements. The processes which are running these statements ...
0
votes
0answers
12 views
Aurora MySql 5.7 killed processes doesn't die
We are using AWS Aurora MySQL and we recently moved to MySQL 5.7. After moving to this version, we started seeing issues with DDL statements. The processes which are running these statements just ...
0
votes
0answers
26 views
Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active
I am trying to setup mysql 5.7 GTID replication but when I try to reset
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
I get the following error:
ERROR 1776 (HY000): ...
2
votes
1answer
36 views
Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 “Using index condition”
My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).
EXPLAIN SELECT
*
FROM
`...
1
vote
1answer
19 views
Error while restoring the table dump
ERROR 1067 (42000) at line 25: Invalid default value for 'created_on'
I'm getting this error while restoring a table after taking the dump(remotely). I have used
mysqldump -h (hostname) -u -p (...
0
votes
1answer
62 views
Percona Xtrabackup fails to backup InnoDB table with fulltext index
I'm trying to make a backup of a large InnoDB table with a fulltext index using Percona Xtrabackup (2.4.12).
I have innodb_file_per_table set.
To create the backup, I'm running:
innobackupex --...
1
vote
1answer
29 views
Import Windows MySQL dump on a Debian Linux server
I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records.
Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, ...
0
votes
0answers
11 views
DataMemory and disk usage in MySQL NDBCLUSTER
Environment: mysql:5.7.23-ndb-7.6.7, CentOS 7.5.1804.
There are 2 data node, 2 mgmd, and 2 mysqld.
CREATE TABLE IF NOT EXISTS table_name_today (
id MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY ...
1
vote
0answers
20 views
Unable to LOAD from XML: The used command is not allowed with this MySQL version
I'm having difficulty loading records from XML on OSX using the command
load xml local infile 'records.xml'
into table Badges
rows identified by '<row>';
and I keep getting the error:
Error ...
0
votes
1answer
27 views
MySQL vs. MariaDB Binary Logging Difference
I've been doing some testing with both MySQL and MariaDB and I've noticed a big difference with regards to the growth of binary logs - I can't find out much about this online.
I have a test database, ...
0
votes
1answer
22 views
config file and parameters for ndb_desc
I'm trying to execute ndb_desc from API client. The config and parameters are as below:
my.cnf in mysqld:
[mysql_cluster]
ndb-connectstring=192.168.1.1,192.168.1.2
server-id=5
log-bin=mysql-bin
...
1
vote
2answers
51 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
votes
0answers
17 views
is it possible to call a stored procedure within a json_object function in mysql?
I am trying to execute something like this:
select json_object ('key', call new_procedure(in id integer(45),out json)))
However it gives an error. Is there any workaround to solve this situation?
...
0
votes
1answer
10 views
mysqlsh rebootClusterFromCompleteOutage force prompt
From mysqlsh I can run this instruction:
...
shell.connect(host, pass);
dba.rebootClusterFromCompleteOutage(clusterName);
But then it's asking me (y|N answer) for each instance it finds offline ...
-1
votes
0answers
19 views
Recursive into SQL
I need to use recursive var into sql
CREATE DEFINER = 'root'@'%'
PROCEDURE dev_comsevilla.procedure1(id_userA int, dateE date, rc int, limite int, total Int)
BEGIN
SELECT @id := c.collegiate_id, c....
0
votes
1answer
36 views
Upgrade MySQL Master Slave Replication Cluster from 5.7.22 to 5.7.24
I have a two node MySQL cluster with Master Slave replication.
The MySQL version is 5.7.22 and the OS is CentOS.
I need to upgrade the MySQL version to 5.7.24 due to some security reasons.
However, ...
1
vote
1answer
77 views
MySQL Replication Slave Status OK but Data inconsistent
We are using MySQL 5.7, and have a Master and 3 Slave servers replication setup. All the 4 servers are dedicated for MySQL use only, and belong to the same data-center, connected over Private Network. ...
1
vote
2answers
43 views
Reset global MySQL status variables
I'm currently looking into an issue where when running MySQL prepared statement I get an exception:
SQL Exception: Can't create more than max_prepared_stmt_count statements (current value: 16382)
...
1
vote
1answer
61 views
Mysql master/master replication, what is the problems
I tried to set up Master <-> Master replication on production and some people they tell me that is not a good option.
I want to know why is not a good option and what is the problem that may find?...
-1
votes
0answers
7 views
Cluster Control don't import mysql replication
I tried to import four mysql node on Cluster Control.
My structure is like this :
A <-> B -> C -> D
mysql version : 5.7.23
Linux version : Ubuntu 18.04 LTS
Cluster Control: Controller: 1.7.0....
0
votes
1answer
33 views
Can i control access end point When some table data changed In Mysql Semisynchronous Replication
I'm trying to set up One master Multi slave in MySQL 5.7
My architecture is like this.
Master - First slave(sync)
Second slave, Third slave, Fourth slave, Fifth slave(async)
My question is, If ...
0
votes
1answer
22 views
MySQL Range Optimization and Multipart index
I'm new to the MySQL world.
When I read about MySQL Range Optimization some questions were raised related to multipart indices.
MySQL 5.7 Doc
(http://jorgenloland.blogspot.com/2011/08/mysql-range-...
0
votes
1answer
25 views
Missing wsrep variables in mysql / percona / galera cluster
I set up a small mysql/percona/galera cluster using the percona cluster docker image, with 3 nodes.
I followed the documentation written here
After starting up the whole cluster, I'm missing some of ...
0
votes
2answers
29 views
Max DATAFILE size in NDB engine
My database setup is using NDB cluster with 2-3 nodes, on CentOS. While trying to test out the performance, i tried to store ~7mil records (around 2GB size) into one of the table. at around ~200k, it ...
3
votes
1answer
50 views
How to use Limit in query on my index column without scan all rows?
Here is my table :
In my table
Clustering_key (Primary key and auto incremental)
ID (Index Column)
Data (Text datatype column)
Position(Index column) maintain the order of Data
My table have 90,...
0
votes
1answer
58 views
InnoDB Buffer causing a lot of deadlocks and connection/client timeouts
I have a PXC cluster (5.7) with three nodes running on CentOS 7 VMs, each with the following system info:
mysql_db# free
total used free shared buff/cache ...
1
vote
1answer
23 views
Mysql Regex on where clause
I need a regular expression which will give me only those records which match a pattern.Below is table structure
auto_id (Auto-increment) | json_string (Text)
Sample records are:
auto_id | ...
0
votes
1answer
24 views
partition in ndbcluster
My environment will use 2-3 cluster nodes. The db schema at first was designed without knowing the limitation/rules with NDBCLUSTER on (BIG mistake! I know). Some things I notice and want to ...
0
votes
0answers
13 views
How to get the latest LastUpdate ,LocReqNo of each locationID of MySQL queryautoricardo.ch - Fahrzeugmarkt – Apps bei Google Play
As I want to retrieve the LastUpdate , LocReqNo of every location.
I mean to say that in the mention table there is 3 locationId , i.e., A1,A2,A3.
I want the output of the above table as follows:
I ...
0
votes
1answer
55 views
Performing wildcard search using the json_contains function in mysql 5.7 and above
I have some nested json data that i get using the json_object() function. Now I need to to check whether a certain value exists for a particular key which I am using as follows in my query:-
select ...
0
votes
0answers
18 views
mysql got connection timeout vs error tineout
I have Percona XtraDB Cluster with 3 nodes, and noticed a lot of the following timeout messages in my MySQL error log:
2018-11-13T08:34:13.724411-00:00 411826 [Note] Aborted connection 411826 to db: '...
0
votes
1answer
58 views
DROP TABLE IF EXISTS hangs on Waiting for table metadata lock
I'm using a dockerized MySQL 5.7.13 as part of an integration test of an application. The way the system works is as follows:
I start up the main application. It opens up a connection to MySQL, but ...
0
votes
1answer
105 views
Performance difference between Text and Varchar in Mysql
I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below CREATE TABLE small_table (
pk int(11) NOT NULL AUTO_INCREMENT,
varc varchar(255) DEFAULT NULL,
txt ...
0
votes
2answers
33 views
No 'Copying to tmp table' state in profiling a mysql query
I have a table with 100k rows
CREATE TABLE `small_table` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`varc` varchar(255) DEFAULT NULL,
`txt` text,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB ...
0
votes
1answer
45 views
SQL: get product stock of all product types in a single query
I want to get stock information for all the products depending upon the product type.
The tables are products, product_stocks, complex1_childs & complex2_childs.
products
| product_id | type ...
