Questions tagged [mysql-5.6]

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

Filter by
Sorted by
Tagged with
-2
votes
2answers
33 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 ...
2
votes
1answer
3k views

Mysql Server 5.6 high memory consumption

I have a problem with my mySQL server. It consumes 9.7 GB ram. I have only a very limited amount of data on it (13 mb) I have been searching the internet and seval places a solution like this one ...
4
votes
1answer
4k views

How to restore corrupted InnoDB tables?

We have a data loss: the MySQL database is dropped and we have to restore the tables from ibd files. The backup is too old so we have to undelete the deleted database files. Most of the tables are ...
0
votes
1answer
48 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
0answers
93 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; +----------+-----------+---------------------+-----------------------+-...
-1
votes
2answers
60 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 ...
0
votes
1answer
287 views

What are the minimal permissions required for the “Users and Privileges” tab in MySQL Workbench to function in MySQL 5.6

My ultimate goal is to allow administration for the purposes of password resets with minimal possible permissions given. It seems to me that with the UserAdmin role, or even UserAdmin with ...
-2
votes
1answer
43 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 ...
2
votes
1answer
506 views

MySQL slave server getting stopped after each replication request from Master

Basic master-slave MySQL configuration has been done on Windows machine. Master and slave servers are running on localhost with different ports. Now when executing update or insert in master server, ...
1
vote
2answers
68 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
1answer
62 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
22 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
0answers
1k 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
1answer
220 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
170 views

Master DB Ran Out Of Space

My master server ran out of space today (at 20:22). My transactions logs ended up broken into multiple files: -rw-rw---- 1 mysql mysql 228M Jul 31 20:22 mysql-bin.000914 -rw-rw---- 1 mysql mysql ...
23
votes
3answers
42k views

Best way to create a materialized view in MySQL

I am using MySQL 5.6. I am not being able to a create materialized view like I can in Oracle. I have seen one or two solutions like Flexview. Can anybody tell me best way to create a materialized ...
1
vote
1answer
177 views

How to avoid row locks on both tables joined in an update? [closed]

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
0
votes
0answers
23 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
21 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
29 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
37 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
57 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(), ...
7
votes
3answers
55k views

How to do While Loops?

How can we use while loops in MySQL? My test script: BEGIN SELECT 0 INTO @n; WHILE @n < 10 DO SELECT @n; SET @n := @n +1; END WHILE; END; But it has syntax errors. I'm running ...
0
votes
0answers
102 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
84 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
95 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
2answers
168 views

Optimizer choose incorrect index for query according to rows number in mysql 5.6

I have mysql 5.6. The problematic query has the following format: SELECT field_1, field_2 FROM trace_ejb3 WHERE field_1 IN (...) AND field_2 BETWEEN ... AND ... GROUP BY field_1; Optimizer ...
2
votes
1answer
1k views

Issues with mysql enterprise backup, assertion failure on restore

I'm trying to restore a backup to a new server, but having some issues with corruption. The source server has a 2.5T table which is very active. I copied the latest backup over to a new server and ...
2
votes
1answer
90 views

Alter a large mysql table 700 gb

The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log. I found following options: pt-online-schema-change: alters a table’s ...
0
votes
1answer
332 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! ...
-1
votes
1answer
479 views

MySQL user export PASSWORD is showing up as <secret>

I am trying to export all users from old MySQL server to new. All the methods that I tried shows the PASSWORD as <secret> pt-show-grants phpMyAdmin 4.8.1 https://serverfault.com/questions/...
-1
votes
1answer
658 views

How to select multiple rows for one id or user in MySQL?

I have two tables: this is table info: ---------------- |s_id | s_name | ---------------- | 1 | Hevin | | 2 | Liza | --------------- This is table marks: ------------------------------------...
0
votes
0answers
20 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
37 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
278 views

Is there a routine in MySQL similar to fn_dblog in SQL-server?

I already search about fn_dblog and it is about transaction log and it is used in SQL SERVER. if there any way to do transaction log in MySQL like fn_dblog? i can't find one
0
votes
1answer
263 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 ...
1
vote
0answers
104 views

MySQL Password Validation - No previous passwords

I wonder if there is any possibility to use the password validation for MySQL 5.6 (https://dev.mysql.com/doc/refman/5.6/en/validate-password.html) to prevent for example the last 4 passwords. The ...
0
votes
2answers
25 views

Mysql - tablename.ibd file is huge in size

I'm using MySQL 5.6 and innodb file per table has been enabled. I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB. I have done a huge ...
0
votes
1answer
28 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 ...
0
votes
2answers
913 views

MySQL Union not showing 2nd query result set

I am doing a union of 2 queries and trying to fetch data on two different columns, however the union is not fetching the value from 2nd result set. Can you please guide me on this. select * From ...
0
votes
0answers
37 views

Get Server name as default value in MYSQL

Hi i have done the MYSQL database replication with 4 servers. And i would like to capture the server instance name. For an example if any records in 1st instance i would to save the MYSQL server name ...
0
votes
0answers
38 views

How to change datetime system timezone to other zones in SQL Query using CONVERT_TZ() SQL Function

I have two times timeA & timeB stored in database, I am using SQL Query to extract data from DB as: SELECT ID, timeA, timeB, CREATED_AT, IS_ACTIVE FROM datatable WHERE IS_ACTIVE = TRUE AND NOW(...
0
votes
1answer
82 views

Mysql binary logs are not deleted after expiration

I have 3 Mysql 5.6 nodes in one galera cluster in the first DC and 3 MariaDB 10.3 nodes in another galera cluster in the DR site. One node on the 1st DC is the master for one slave node on the DR site....
0
votes
1answer
127 views

Group_concat on IF Select MYSQL

I have a query as below: SELECT o.order_id, c.customer_name, Group_concat(p.product_name, ' x', p.product_quantity ORDER BY receipt_order ASC SEPARATOR '<br>')...
1
vote
0answers
38 views

How to enforce “at most N child rows” in a foreign key relationship without a trigger?

I have a table that I will be using to save "template" messages for my users. The schema is CREATE TABLE listemailtemplates ( id int primary key auto_increment, userId int not null, ...
0
votes
0answers
143 views

Implication in Increasing MySQL maximum keys allowed per table

We have a situation where we encounter a MySQL error ERROR 1069: Too many keys specified. Max 64 keys allowed In our database (using Magento E-commerce) we need to add 76 indexed key columns but ...
0
votes
1answer
309 views

how to install a second server of mariadb [closed]

I have a server where cpanel is installed (it includes mysql 5, php). What I need is to install a second database server mariadb 10.1.20. I tried through: yum install MariaDB-10.1.20-centos6-x86_64-...
0
votes
0answers
32 views

How to prevent users with Global Drop Privilege DROP MySQL System Databases

I have a very specific context: For security, my Infrastructure Team will only give the DBAs Team access to MySQL not full access to servers. Their MySQL Users will have almost full global ...

1 2 3
4
5
21