Questions tagged [mysql-5.6]

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

Filter by
Sorted by
Tagged with
1
vote
2answers
50 views

Index creation in extremely slow in Mysql Aurora 5.7

I have a table in mysql Aurora 5.7 version . The db instance has very good configuration 488 GB ram (db.r4.16xlarge). Data base is static data base no application is running on this . I have 1.5 ...
0
votes
1answer
726 views

“Lock wait timeout exceeded; try restarting transaction” for my delete query

I'm using RDS Aurora with MySQL 5.6. I have a delete query on a table which runs once in a day and it fails with lock wait time outexception, try restarting transaction. This issue is not happening ...
0
votes
1answer
609 views

Mysql Remote IP blocked

I have a peculiar issue with some database connections: I've got this (test) script on 2 different servers: $link = mysql_connect($db_server, $db_user, $db_passwd); if (!$link) { die('Could not ...
1
vote
1answer
127 views

Aurora Serverless alter table Error 1114 table is full

Using Aurora Serverless, when attempting to alter table mytable add column mynewcolumn decimal(10,3) after myoldcolumn I receive "ERROR 1114 (HY000): The Table 'mytable' is full". The table has ...
0
votes
2answers
240 views

Setting up MySQL master-slave replication

I am trying to setup master-slave replication on MySQL 5.6. When I update my database in the master, the change is not reflected on the slave. When I show the process list on the slave it shows this ...
0
votes
2answers
283 views

Mysql 5.6 Replication Lag fluctuating between 0 and n

I have one master and 7 slaves. During high load on my master, I see replication lag and it keeps fluctuating between 0 and n (where n keeps increasing with time and I have seen n grow more than 1 ...
1
vote
2answers
35 views

MySQL Server 5.6 Using all or very little of my resources trouble shooting?

I have an application that uses a MySQL 5.6 Server. This server is hosted on a Windows 8 VM on a server in our office closet. A coworker of mine sent me a picture of the mysqlId process consistantly ...
1
vote
2answers
1k views

Restoring MySQL dump ERROR 1449

I took hot backup (dump) from my Master MySQL DB with below command mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql Master ...
8
votes
1answer
13k views

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared I have a Stored Procedure which contains a Prepared Statement and a view DROP PROCEDURE IF EXISTS `sampleProc`...
0
votes
0answers
6 views

on slave repication filter --replicate-do-db= store; give syntax error 1064 on mysql 5.6

--replicate-do-db=store; CHANGE REPLICATION FILTER replicate_do_db = (store); //also by using this query in mysql 5.6 that option on server side give syntax error 1064 how to fix it
0
votes
1answer
2k views

How to Recover My database From Old WampServer?

Previously I am using wampserver2 with "mysql5.6.17" version.I have many database in that version. I uninstall the wampserver and install new latest version wampserver3.1.0_x64. Before uninstalling ...
2
votes
1answer
38 views

Large MySQL database dump/restore [closed]

Got a quite interesting task: Have to dump mysql databases from AWS and restore on a private cloud. Databases are quite big: 160GB, 120GB and 21GB each db.sql dump - all 3 in the same aurora rds ...
3
votes
2answers
760 views

Query with Indexes slower than without Indexes

I'm using MySQL 5.6 and my storage engine is InnoDB. I have a table with 1 million rows containing with the columns: ID (primary key) FirstName LastName foreign_key_id (foreign key, NOT NULL) ...
0
votes
1answer
25 views

Select data at row number in warning

When running show warnings in mysql, sometimes the results give a row number, such as "Warning | 1265 | Data truncated for column 'identifier' at row 12343". I'm interested in doing something like: ...
0
votes
0answers
11 views

Error : mysql goes away when upload File in MBs [duplicate]

I have a table that store the file content of uploaded file. The datatype of the column is LONGBLOB. when i try to upload file of 9 MB in size show mysql error "mysql goes away". what i am doing in my ...
1
vote
0answers
15 views

Create SCD type 2 table from change log table using SQL

I have a change log table, which captures the transition of a state with its time, for example: user_id,user_status_from,user_status_to, created_date 7cc4d A2 A1 2019-11-03 ...
3
votes
1answer
4k views

How to make a FULLTEXT search with ORDER BY fast?

I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. The basis is one table with a fulltext on two columns and I want to ...
0
votes
1answer
325 views

Prevent mysql process being killed by OOM killer

I need to protect mysql process so that it won't be killed when system runs out of memory, we have a bunch of adhoc job run on the server from time to time, so I would prefer the adhoc jobs being ...
11
votes
8answers
18k views

How can I make MySQL client read password from mylogin.cnf?

I'm trying to make the mysql client connect to a mysql server without requiring the password to be given interactively. Steps taken: 1) First create a mylogin.cnf file $ mysql_config_editor set --...
0
votes
2answers
636 views

MySQL - Restore 1TB database using myloader

Im trying to restore a database which is 1.3TB and its in AWS Aurora. I want to restore a copy of this database to an EC2 instance(32 vCPU, 244GB MEMORY). I used mydumper to take backup from AWS RDS ...
4
votes
1answer
4k views

How to get row count from all tables of a schema ( without using information schema )

The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
0
votes
1answer
325 views

MySQL Difference between BEGIN and START TRANSACTION

I'm using RDS Aurora (MySQL 5.6) By default Auto Commit flag is enabled. I have a transaction as follows BEGIN; INSERT INTO .....;// STATEMENT 1 INSERT INTO ......;//STATEMENT 2 COMMIT; I'm ...
0
votes
1answer
56 views

MySQLdump Exclude master-data

Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static ...
1
vote
0answers
26 views

Pair records by grouping on foreign key

I am stuck on a question I got in a coding challenge. Essentially I have 3 tables; ratings, reviewers, and books. The ratings contain a list of book reviews from reviewers. I need to output a query ...
0
votes
2answers
73 views

mysql 5.6 flushing all dirty pages on ib_logfile rotation creating checkpoint stall

Server version: 5.6.23-72.1-log Percona I have been trying to track down why our mysql server is sharp checkpointing and it seems to correlate with rotating of the ib_logfile. I inherited this ...
4
votes
2answers
1k views

Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries

I'm systematically (1/2 times per day) experiencing a freeze in the DB server. Suddenly, all the COMMITs get stuck in 'init' state; they pile up (up to 20/30), an almost no other queries are executed ...
0
votes
1answer
101 views

Get Master Server Information from Slave instance

We need to get Master server information such as server IP and Port, we know this information is dumped into master.info file by default but what if it required within MySQL prompt as we are using ...
0
votes
1answer
30 views

how to show combined results of two tables?

I want to show the result as combination of two. Table 1 SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID` FROM au.`invoice` WHERE au.`invoice`.`InvoiceID` > 11126 -- total results 757 ...
0
votes
1answer
20 views

i have error in query [closed]

i am not getting what is the issue in the query probably i am not following the correct way to put the string and char sign , i am inserting the data in c# to local host with where clause please check ...
4
votes
2answers
7k views

Can I use table aliases in a MySQL DELETE statement?

This does not work: DELETE FROM topics AS t , posts AS p USING t INNER JOIN p WHERE t.id=p.topic_id AND t.id = '5599'; whereas this does: DELETE FROM topics, posts USING topics INNER JOIN posts ...
1
vote
2answers
229 views

MySQL 5.6 — How do I tell if I need to increase innodb io thread setting?

I am trying to tune my mysql server for the high end system. We are using 16--32 core hosts with memory > 100GB. I have looked into a bunch of existing setting, but not sure if I need to tune up the ...
0
votes
1answer
55 views

Users Export From Mysql Version 8.0 to 5.7

I want to exports all users from mysql version 8.0 to 5.7. Can you guide me for this activity. I want same password and privs should migrate.
1
vote
1answer
38 views

SELECT gives different results with/without FOR UPDATE in same transaction

I'm sorry but I havent't found a better wording for the title. Given the table a: CREATE TABLE a ( id INT NOT NULL auto_increment PRIMARY KEY, ref VARCHAR(10) NOT NULL, val INT NOT NULL )...
0
votes
2answers
112 views

MySQL replica is lagging without resource saturation

I am running MySQL 5.6.41 on AWS RDS. I have one multi-AZ master and one multi-AZ read replica. It is a vanilla configuration MySQL configuration, so the binlog is in MIXED format and the replication ...
0
votes
1answer
33 views

How to better backup MySQL 5.6 database?

Today I had an issue where I accidentally deleted a whole table (which cascaded to a few other tables) due to a funky piece of MySQL syntax, see here. Luckily I had a backup from midnight, however ...
0
votes
1answer
61 views

MySQL database not loading table data

My centOS 7 server had an unexpected shutdown. For some reason one of the mysql database shows that all tables are empty, but if I browse the files in /var/lib/mysq/database I can see the files(.frm ...
1
vote
1answer
15 views

Master-Master Replication: Missing PK Values [duplicate]

I have configured master-master replication in mysql 5.7.22 and its working fine. I am using below settings for PK values. I found issue in their ID sequence and pk values missing their sequence ...
1
vote
2answers
8k views

MySQL does not start - Ubuntu 14.04 LTS after migration to new disk

I have installed in a Virtualbox VM a 64-bit desktop version of Ubuntu 14.04 LTS and MySQL Server 5.6. I had some databases in the machine's main virtual hard disk but then I was going to open a SQL ...
1
vote
2answers
2k views

mysql no longer defaulting to /tmp/mysql.sock

I was testing some software to see if it could connect to mysql correctly when mysql is not using /tmp/mysql.sock. I stopped mysql, editted /etc/my.cnf to add socket=/var/lib/mysql/mysql.sock ...
0
votes
2answers
51 views

How to select Overdue Rows with Date Frequencies?

+------------------------+--------+ | Invoice_id | due_date | amount | +-------------+----------+--------+ | 20 |2020-01-18| 1250 | +-------------+----------+--------+ | 21 |2020-...
0
votes
1answer
13 views

MySql8 Trailing String comparison

I am planning an upgrade of a legacy MySQL5.6 Database to MYSQL8.0.19 database. All my testing has been successful but have one issue preventing me from going live. By default in MYSQL5.6 the ...
1
vote
1answer
27 views

Optimizing a query that gets the opening balance

I have a query like this select org, `Balance` from oss_collection_history och2 where och2.`DateFrom` = (select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org) It ...
-2
votes
2answers
30 views

i want fetch json data in mysql [closed]

I am using MySQL version 5.6.41-84.1-log, I want to fetch single column of JSON data example: I want to fetch date only id | amount_detail ---------------- 1 | {"1":{"amount":"6000","...
0
votes
1answer
41 views

calling mysql procedure return empty result

when i call procedure it gives me empty result call hussian('prstate','table_1','column1,column2,column3',1) it run but gives empty result, please help me... DELIMITER // CREATE PROCEDURE ...
1
vote
1answer
361 views

Replication Hung - Seconds_Behind_Master Increasing

One of my slave is no longer replicating. The seconds_behind_master continue to increase, Exec_Master_Log_Pos does not increase, and Relay_Log_Space does increase. Slave_IO_Running and ...
0
votes
1answer
20 views

Identify when Table Schemas were last Modified

I'm trying to identify all tables that have had indices added/altered since January 15. Is this possible? I've run: select * from INFORMATION_SCHEMA.PARTITIONS where UPDATE_TIME >= '2019-01-15' ...
0
votes
1answer
17 views

mismatch between server schema content and exported sql rows [duplicate]

I am using MySQL 5.6.44 within phpMyAdmin on Go Daddy shared hosting and experimenting with some duplicate table data. I am trying to figure out, when I export my table from phpMyAdmin the exported ...
0
votes
0answers
14 views

Tracing what went wrong with MySQL server?

I have a local in-house client application that relies on steel server MySQL 5.6 database in our closet. This morning, people suddenly couldn't log in. The client server said it couldn't connect ...
14
votes
4answers
69k views

MySQL: How to create Column if not exists?

I am trying to create a Column for my table only if it does not exist. I have researched a lot but I could not find any solution yet. Is this really possible to conditionally create Column ?
0
votes
1answer
314 views

Show Processlist state “Statistics”

Please note that it's not a duplicate thread because I already set variable optimizer_search_depth and didn't have any impact. Sometimes, our database/application services crashed due to max ...

1 2
3
4 5
21