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

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

0
votes
1answer
17 views

MySQL 5.6 - Query Performance Issue. Searching for Slug

I've a case where I don't know how to better the performance of this query. This is a simple query to the Geonames.org database. The query: SELECT name, latitude, longitude FROM geoname WHERE ...
0
votes
0answers
11 views

Unable to select the table result in linked Server

As I have successfully created the Linked Server in SQL Server 2012 SP4 with MYSQL Server. Some of the small tables record able to fetch from the MySQL Server. And some of the big tables records when ...
1
vote
0answers
21 views

mysql 5.6 Unknown system variable 'optimizer_trace'

I'm trying to follow this tutorial to trace optimizer that is used by mysql for certain query. But when I do SET optimizer_trace="enabled=on"; It said ERROR 1193 (HY000): Unknown system variable '...
1
vote
1answer
16 views

Proper use of FULLTEXT index for a column storing unique hash value

We have an InnoDB table called user, where we have a column called token (varchar(128)) where a unique md5-like value that represents whether a user is authenticated or not, is stored. This column can ...
0
votes
1answer
28 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 ...
-2
votes
4answers
65 views

Get product wise maximum level category [closed]

I have category table as: | category_id | path | level | |-------------|--------------|-------| | 100 | /100 | 1 | | 101 | /101 | 1 | | 103 | /...
0
votes
1answer
19 views

drop big table freeze server

I am using amazon rds mysql 5.6 master instance and 4 slaves. there is big table with 800 GIB size that i want to drop it for releasing space. But when i execute drop command, the server entering to "...
1
vote
2answers
39 views

MySQL HA_ERR_LOCK_WAIT_TIMEOUT ignoring innodb_lock_wait_timeout (deleting most of a table)

I am issuing some long-running delete queries to some databases, and I am getting the following error: Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout ...
2
votes
2answers
67 views

Is it possible to make mysql effective in low cardinality if I may need a majority of the rows?

I currently have a table with 3.2kk records, index cardinality is around 1000. It's data from google search console, so 5000~ records per day for 5000~ different queries, and I need to run reports ...
0
votes
2answers
58 views

mySQL query optimizer not using indexes

In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment ...
0
votes
1answer
32 views

Remove Characters & Sum Numeric Values

I am using mysql 5.6 and want to remove all characters from json output of db column and after that required sum of numeric values Input: {"across_back":"0","bustd":"0","waiscvvt":"50","hisdp":"50"...
2
votes
0answers
17 views

missing ibd files

I'm running mysql 5.6. For reasons entirely my own fault, I've got some missing ibd that make up partitions for several tables. When I try to mysql I get the following messages for the missing ...
0
votes
1answer
36 views

mysql query to get time difference

I have query like this SELECT user_ID, tanggal, kode_layanan, nomor_antrian, aksi_ID,jam FROM `transaksi` WHERE tanggal = '2018-10-11' AND kode_layanan = 'A' AND aksi_ID BETWEEN 4 AND 9 and ...
0
votes
0answers
23 views

How to convert the SQL query from MariaDB 10.3 to MariaDB 10.1/MySQL 5.6

Since I need to convert the below query which should work on MariaDB 10.1/MySQL 5.6. When I tried to run the below query I used to get the error as #1064 - You have an error in your SQL syntax; ...
0
votes
1answer
16 views

How to query when one ID has more than one name and find the total count of unique names, id

Same id having more than one name and the count of the unique names which are associated with the unique id
2
votes
2answers
59 views

Lost connection to server executing ALTER TABLE

I'm trying to execute an ALTER TABLE statement in MySQL 5.6. Every time I run the query, it's creating a file with a name like #sql2-503-918bf.ibd taking full table size. I don't have enough disk ...
0
votes
1answer
17 views

Analyze general log

Motivation : understand the highest CPU\usage\long_query of historic queries running on production. example :10 minute ago on server x graph showed high CPU usage. I thougth for next steps : ...
0
votes
1answer
61 views

How to insert data to database by using checkbox in PHP MySQL?

I have this output: a problem while I inserting data the time value for all users inserted to the database, I want when I checked the checkbox send the time value to the database for every student I ...
0
votes
0answers
21 views

MySQL INNER JOIN issue

I have this query that updates my table: UPDATE mancanti_java a JOIN from_generale_java b ON a.commessa=b.commessa AND a.modello=b.modello AND a.stagione=b.stagione SET a.fabrica=...
0
votes
0answers
22 views

What is the proper way to add a constraint to multiple existing tables?

If I have a database that is temporarily offline can I take a SQL dump of it and then just add "ON UPDATE CASCADE" after the foreign key references in each table. Then clear the database and import ...
-1
votes
1answer
41 views

Reference table to same table in another database

I'm currently working on a bigger project with a few friends of mine. However, at the moment, we have a ... pretty bad development setup. Here an - extremely simplified - database: -------------- | ...
1
vote
2answers
32 views

For each value in field A, select the row with some special value in field B, while respecting further criteria

Disclaimer: While the basis of my question—the greatest-n-per-group question—has been asked and also answered so many times before, I am facing a seemingly hard issue due to extra conditions that I ...
1
vote
1answer
29 views

Delete from table using FK columns, JOIN and LIMIT

This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to ...
0
votes
0answers
22 views

MYSQL 5.6 Replication broken in mixed base binlog format

I am using MySQL replication with binlog_format=mixed on master, but still Im getting error 1032 many times. Last_SQL_Error: Could not execute Update_rows event on table mydb.mytable; Can't find ...
0
votes
0answers
28 views

Mysql query sorting based on record id and expire date

I have an list of items with primary key id and expire_date a datetime field. I want to sort them based on id if the expire_date is greater than or equal to today, and if the expire_date is passed, I ...
0
votes
1answer
31 views

Mysql Slave Claims to be Running but New Records Not Appearing and No Delay

I've created a new mysql master and a slave. My master is also a slave of another running instance. The replication between those two works fine so I don't believe my issue is there. I created a dump ...
0
votes
2answers
27 views

Mysql Percona 5.6 Configuration File

I've just installed a Percona instance of Mysql on an Amazon server and can not find the my.cnf file anywhere. I found auto.cnf in /var/lib/mysql but there is nothing in that file like what I have ...
0
votes
1answer
45 views

in MySQL what is the query to show these student's that each marks less than 50?

I have this table marks in MySQL database I used a query to output this table that shown as below: it's my output: I want use a query to show these student's that each marks less than 50, output ...
0
votes
2answers
65 views

How to use variable into SQL in MySQL trigger

I'm trying to write trigger. I have encountered an one problem. My Code is like. DELIMITER // CREATE TRIGGER upd_check BEFORE UPDATE ON user FOR EACH ROW BEGIN DECLARE tempVar text; IF(OLD.name ...
1
vote
0answers
82 views

MySQL memory usage is too high - OOM

Im using 32vCPU and 120GB VM for Mysql 5.6. it's a dedicated MySQL server. 15000 IOPS SSD disk. Total database size 40G This server was working fine for a couple of months. Memory usage is ...
2
votes
2answers
43 views

Retrieve parent and child data using child id

I have a parent and child table, I am able to retrieve all data using UNION SELECT. Parent Table: id | parent_name 1 | Parent A 2 | Parent B Child Table: id | parent_id| child_name 1 | 1 ...
0
votes
2answers
56 views

MySQL - Does using an explicit transaction affect query performance?

In MySQL 5.6, using InnoDB, I have a rather large table, call it A; (about 4 million rows) in which I need to update a field. The condition regarding what to update is held in another table, B; and ...
2
votes
1answer
182 views

MySQL 5.6.34 RDS Warning: a long semaphore wait causes crash — log included

I had my RDS MySQL 5.6.34 server crash under normal load with nothing out of the ordinary today (That I could find). Amazon support pointed me the the error log. MySQL config: https://gist.github....
2
votes
0answers
12 views

MySQL Queries Truncate Causing Connection Spikes

We have a MySQL Master Instance & Read (failover only) on AWS. MySQL 5.6 Aurora running Magento. Performance is great, and better than standard MySQL (we have high traffic site so see the benefits)...
2
votes
1answer
26 views

Can I stop replication of create/drop index statement on one slave of Mysql 5.6?

I have this cluster where master and slave uses same indexes but one of the slaves has some extra indexes (large machine which runs some analytics queries). So I have to maintain two sets of indexes. ...
0
votes
1answer
40 views

MySQL MSI appears to be installing wrong (latest) MySQL version

For compatibility reasons with other software, I'm trying to install MySQL 5.6.40 on Windows 10. Any version from 5.7 onward is incompatible with my other programs. I've been trying to use the MSI ...
0
votes
0answers
30 views

Sudden Hard Disk Crash , No Backup to Recover INNODB Tables

I am facing this isssue , suddenly one of my server hard disk crashed and we dont have any backup to recover , all the tables are INNODB and i used Innodb-force-recovery to recover the tables , but it ...
0
votes
1answer
26 views

Other way to disconnect user/client in mySql 5.6?

Hello everyone is there another way of disconnecting user/client aside from: kill id I have a close button which when i click the button it will close the connection on the selected user... but the ...
1
vote
1answer
67 views

Getting Master Current Binlog and Binlog Position in Replication Backup

I want to take the current binlog file and binlog position of the master server in a mysqldump on the slave server. I cannot use master-data=2 on slave - how can I do this nevertheless?
0
votes
0answers
39 views

Create database in separate partition or new drive in mysql

We are using MySQL 5.6 having all databases in 1 partition /usr/local/mysql. Now we want to create a new database which is of large size. But the existing partition is not having enough space and one ...
0
votes
1answer
30 views

How does MySQL rotate binary log files when number extension reaches its maximum?

Assume the bin-log file format is: something-bin-log.000122. With each file has a maximum of 1GB size before rotating, and with cap on the number of old binary log files - say 14 days. What will be ...
2
votes
1answer
72 views

How to read mysqld.log files? [closed]

can someone tell me how to read the mysqld.log? I'm curious about the third column. Because every time i start/stop the mysql it give different output. I hope someone can explain to me :). Below is ...
2
votes
2answers
41 views

Do MySQL (5.6) triggers support 4 byte emojis?

I'm using MySQL 5.6.34 with the following settings: "character_set_client","utf8mb4" "character_set_connection","utf8mb4" "character_set_database","utf8mb4" "character_set_filesystem","utf8mb4" "...
1
vote
2answers
24 views

How to store user selected value, single choice or all choices

I have a form where a user is able to select a single value from a dropdown. The first option in the dropdown is "All", followed by all of the individual options that they can pick. So, they can ...
1
vote
0answers
28 views

Can't add new node to Galera Cluster

I have a 5 node Galera 3 Cluster running MySQL 5.6 on Ubuntu 16.04 (I tried running 5.7 but ran into the AppArmor issue). I'm trying to add another node to the cluster but it continually fails. The ...
-1
votes
1answer
43 views

In MySQL how can I make all possible set / groups of different status values in a table [closed]

Can anyone help me to sort out this problem using query/procedure/functions. id Price Status -- ----- ------ 1 10 1 2 12 1 3 15 2 4 21 3 5 25 1 6 27 ...
0
votes
0answers
17 views

Is it possible to execute a stored procedure available in MySQL server A in database A from MySQL server B in database B?

I want to know the scope of remote procedure calls in MySQL, which is similar to the one available in Sybase or MSSQL server? I have a stored procedure in MySQL Server A in database A. Is is possible ...
1
vote
2answers
118 views

MySql 5.6: Ignores innodb_page_size setting in my.ini

So I tried to increase the innodb_page_size to 32k, by setting this value in my.ini: innodb_page_size=32k I then stopped the MySql service, and restarted it. The service in Win looks like this: "C:\...
0
votes
1answer
19 views

Slave Missing Data From Master, Manual Insertion Issues?

My transaction logs became corrupted and I had to skip 50 insert records from master. I've exported the data and am planning to update the slaves manually, with the slave stopped. The auto-increment ...
-1
votes
3answers
102 views

How to insert autoincrement value inside varchar field

I have table users in which id field in varchar(36) datatype. I want to insert into id with primary key and auto-increment Field structure `id` varchar(36) NOT NULL PRIMARY KEY