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

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