Questions tagged [mysql-5.6]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

how can we check the ca cert enabled for the mysql 5.6 version

We have used --ssl-cert=path --ssl-mode=VERIFY_CA while login but unable to login as it is throwing ssl-error "ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is ...
0
votes
1answer
25 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
18 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 ...
0
votes
0answers
10 views

Optimize table on large table online DDL Feature

I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB. mysql> show variables like '%tmp%'; +----------------------------+------------------+ | Variable_name ...
0
votes
1answer
25 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
35 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 )...
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 ...
0
votes
1answer
9 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 ...
0
votes
1answer
22 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 ...
-2
votes
2answers
19 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
2answers
23 views

How to select Overdue Rows with Date Frequencies?

+------------------------+--------+ | Invoice_id | due_date | amount | +-------------+----------+--------+ | 20 |2020-01-18| 1250 | +-------------+----------+--------+ | 21 |2020-...
0
votes
2answers
35 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 ...
1
vote
1answer
25 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 ...
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
52 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 ...
0
votes
2answers
47 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
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 ...
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 ...
1
vote
1answer
93 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 ...
-2
votes
2answers
32 views

How to extract desired results from different columns

In order to conserve space and eliminate duplicates, I have the following table of distances between locations: patientIDa | patientIDb | distance | 1 | 2 | 4.5 | 1 ...
0
votes
1answer
25 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
55 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 ...
0
votes
0answers
34 views

Long Running Transaction in MySQL

I have very Long Running Transactions in MySQL (RDS Aurora) select * from information_schema.innodb_trx where trx_id = 45638005; +----------+-----------+---------------------+-----------------------+-...
0
votes
1answer
31 views

MySQL Lock Wait Time Out Exceeded upon Delete

I have a Delete Statement which fails with Below Exception com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction It waits for 50 ...
0
votes
1answer
74 views

mysqldump vs mysqlbackup

I am using MySQL v5.6.x. and using mysqldump takes a long time to dump and restore the db and does not seem practical for DB size larger than 50GB. It looks like the mysqlbackup utility that comes ...
-1
votes
2answers
48 views

MySQL Select Query not using Required Index

I have a Table which has 2 Million Records and No primary or Unique Keys. I added Two Indexes and I'm expecting an Index to be used for my Select Query. When I saw the Explain Plan , Optimizer is not ...
1
vote
2answers
117 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
83 views

MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin

I am a developer so was hoping someone with DBA experience could help me out here. I am using a MySQL database (MYSQL 5.6). I came across a situation where a particular column stores Ids generated ...
-2
votes
1answer
27 views

What alternative sql method can I use to see more records, GROUP BY id only shows one row per unique id

What I want is per map id I want to be able to see the top highest scoring gangs (also I should be able to say from which date or a specific single day), (i thought of using GROUP BY ID, but it would ...
1
vote
2answers
43 views

Alternative to slow [LEFT JOIN + OR] without UNION?

I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables. The query using only left joins ...
0
votes
0answers
21 views

What is the best way to modeling my database

I would have your advice to modeling my database. There are 2 main categories that have the same pattern: services, patient rights. For each service or patient rights, there are many main ...
0
votes
1answer
56 views

ORDER from 2 tables to achieve a specific order returned by an API

There's an external API that returns image similarities, based on an "image ID" and a "image version" (a same image can have multiple versions). The similarity ORDER is given by the API. The API ...
0
votes
0answers
261 views

InnoDB: Could not find a valid tablespace file for ' table '?

i have some project hosted by wampp server, which used to work without any issue. this week , it can't serve anymore , examining the mysql logs i got this lines: 2019-12-05 08:23:00 1464 InnoDB: ...
0
votes
0answers
12 views

Upgrading MySQL takes forever

I am upgrading a MySQL database from 5.6 to 5.7; it contains a few tables that are obscenely big, >500m rows in one case. I started the mysql_upgrade yesterday, and it has been chewing its way through ...
0
votes
1answer
17 views

Does range improve match operation and which runs first, range or match

I am using MySQL version 5.6.10 I have a very very large table large_table and a relatively small table small_table The small_table has only the records of the date 2019-11-20 There is a column I ...
0
votes
0answers
15 views

Schema design for products with different sizes?

I have My Products Table With Many to many relations with variants table ------------------------------ | ID | variant name | |----------------------------| | 1 | XL | | ...
0
votes
0answers
17 views

MySQL replication seconds behind master is not coming down

I tried looking up for this kind of issue, but did not find anything, hence posting this question. I have a MySQL master-slave replication (Row Format) setup, we had some issues on the slave node, ...
0
votes
1answer
29 views

Stored Procedure taking longer than a normal query

I have a stored procedure which returning single but taking 16+ seconds when I call that but only .5 second, if I run that by normal query. Procedure DELIMITER $$ DROP PROCEDURE IF EXISTS `...
2
votes
1answer
52 views

Incredibly long execution time for a update query?

I am using MySQL 5.6. In my table invoices I added two datetime columns that I will be setting, based on if another column is a certain value. UPDATE invoices SET twoWeekAlert = DATE_ADD(now(), ...
0
votes
0answers
99 views

Removing foreign keys to reduce downtime

In an attempt to reduce downtime on our production servers we are considering using one of the following tools to do schema migrations without any downtime: Percona Toolkit — pt-online-schema-change ...
0
votes
0answers
32 views

Apply a DDL statement with zero downtime with Aurora Mysql

I have an Aurora Mysql 5.6.10a db cluster with one master and one read, that I need to apply a migration to with near zero-downtime. I'm trying to get a comments field to accept emojis by updating ...
0
votes
0answers
7 views

Can mixing InnoDB and MyISAM slow down MySQL response?

I have been facing an issue from last 5 or 6 months. When we send a push notification using firebase to a topic All subscribed by all users. Server doesn't respond to other MySQL queries. I just ...
0
votes
0answers
31 views

Long Running Transactions with Active Status

I could see long running transactions in my Innodb Status, I'm using RDS Aurora Mysql 5.6 Version. ---TRANSACTION 977075315, ACTIVE 70113 sec 2 lock struct(s), heap size 376, 0 row lock(s), undo log ...
0
votes
1answer
244 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
117 views

How to convert a single MySql table to utf8mb4 (from latin1)

MySql version 5.6.39 i have one table that now needs to support chinese characters. the other tables in the database do not need utf8mb4 encoding. what have i done thus far: 1) backed up database! ...
0
votes
1answer
51 views

How to Get the First in and Last out in night shift?

I already have the Query for Getting the Day shift but I'm Having a problem in the night shift, Which is this query. SELECT user_id, Min(verify_date) as First_IN, Max(verify_date) as Last_Out from ...
0
votes
0answers
16 views

How to apply a condition based on values of a particular ID in a table?

I have a feedback table with FeedbackID. I have an action table with ActionID and FeedbackID and Follow-up. This implies that a particular FeedbackID can be related to several ActionID. In action ...
-4
votes
1answer
24 views

MySQL returns more than one row with AND operator match

I have two tables 1:n relationship, customer to orders the schema is in the http://sqlfiddle.com/#!9/a6842e/1 I have created. The key in customer is composite id and type. I am trying to get the ...
0
votes
1answer
55 views

Dates values displayed 0000-00-00

I have imported a csv file into my database created in phpmyadmin using sql. Server version: 5.6.37 - MySQL Community Server (GPL I have a column for dates which has a datatype of date. The data ...
0
votes
1answer
23 views

MySQL query is taking a long time to execute

I have a performance issue with a MySQL query that takes a long time. I'm trying to figure out why and what is Block nested loop? MySQL Version is 5.6 and Running on Cloud The query that works but ...

1 2 3 4 5 20