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
1answer
50 views

How to get same column values to make multiple columns?

I don't know if my question is valid or not but I'm working on an attendance system, and I want to get some output for my project. I have an attendance table that holds three columns: emp_id, ...
1
vote
1answer
197 views

MySQL data warehouse calculate difference over years in percentage

I'm trying to create a query to calculate the percentage difference based on a time range (e.g. WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months. My question is how to add the new ...
1
vote
0answers
391 views

mysql RDS cpu utilization is 90 % above always for less than for 250 connections ( max conn 1236)

I'm using mysql RDS db.m3.xlarge( 4 vcpus, 15G Ram). max_connection limit is 1236 but cpu is used more than 90% for less than 250 connection. I don't know what I'm struggling to find reason. Please ...
0
votes
1answer
482 views

How to update a sequence column using single update statement

I have a table, lets call it foo which consist a column priority and has a unique constraint on it. lets assume below is the current state of foo table :- mysql> select * from foo; +-----+--------...
1
vote
1answer
462 views

Mysql connection pool set timezone

Service application is nodejs It is using mysql connection pool. Currently, we will plan to sell service to another country What is the best way to set timezone? timezone setting execute, and query ...
0
votes
1answer
36 views

SQL Query Optimization (Mysql 5.7)

I have following query to execute in live mysql 5.7, which takes around 7 sec and we want to reduce their time, please support SELECT COUNT( DISTINCT InternalInquiry.inquiry_id ) ...
0
votes
1answer
164 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
121 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 ...
1
vote
0answers
247 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
176 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
254 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 ...
0
votes
1answer
62 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
304 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
155 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
801 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
67 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
264 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
53 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
1answer
107 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
2k 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
113 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 : ...
-1
votes
1answer
156 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
42 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
53 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
1answer
209 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
320 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
916 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 ...
2
votes
0answers
733 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
1k 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
126 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 ...
3
votes
1answer
1k 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
45 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
141 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
180 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
1answer
53 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
651 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
218 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
262 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
293 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 ...
3
votes
2answers
185 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
49 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
156 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
57 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 ...
1
vote
2answers
2k 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
60 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
778 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
0
votes
1answer
146 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 ...
1
vote
0answers
41 views

I want to help data fetching query in MySQL 5.6

I'm fetching data from mysql. But, query is so complex. My case is like Table schema CREATE TABLE `mt_department` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '[ID]', `mt_company_id`...
0
votes
1answer
262 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
1
vote
2answers
589 views

MySQL tuning: INNODB_BUFFER_POOL_SIZE not found?

My replicator MySQL db, hosted by Amazon-RDS-Aurora is performing poorly. The DB is quite powerful so performance shouldn't be an issue. When I executed SHOW VARIABLES; I could not find ...

1 2 3
4
5
20