MySQL 5.6 : Latest Release of MySQL with improvements that enhance every functional area of the database kernel.

learn more… | top users | synonyms

1
vote
1answer
27 views

HeidiSQL: Import CSV

A few lines of my CSV: "id","date","date_","pow","ene","reac" "ES0134000563024539AM0F","2013-05-02","2013-06-05","62","43","43" "ES0134000573024539AM0F","2013-05-02","2013-06-05","62","43","43" ...
11
votes
3answers
395 views
+100

MySQL instance stalling “doing SYNC index”

Problem An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, ...
0
votes
1answer
15 views

Replicate update statement as Insert in Mysql 5.6

I want to replicate all update statement from Master as a Insert at slave end. 1. Is it possible like in SQL Server ? 2 If so , how ? I want to do it because , if any record on master is ...
1
vote
2answers
36 views

group result of multiple table to get unique result after count operation

survey_categories +---------------+-------------------+ | survey_cat_id | survey_cat_status | +---------------+-------------------+ | 1 | 1 | | 2 | ...
0
votes
1answer
198 views

Accuracy of mysqldbcompare vs other tools like pt-table-checksum

I was curious to hear what everyone's experiences are with using mysqldbcompare vs pt-table-checksum( from Percona Toolkit ) to find the differences between two MySQL databases. I have heard that ...
1
vote
0answers
31 views

Master/Slave and Master/Master replication - MySQL

What is the difference between having a master-slave replication and having a master-master( where in the second master acting as hot-standby - active-passive) replication? Also, what are the issues ...
1
vote
1answer
39 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
69 views

how to minimize the data scanned on my database using index?

Is there anyone how to minimize the data scanned on my database?? please show me.. using index? query SELECT IF (e.channel LIKE 'IAX2%', e.calldate, IF(d.channel LIKE ...
1
vote
0answers
23 views

How to find where an unauthenticated user connects from

We are seeing some "unauthenticated users" when I run the command show full processlist in mysql (v5.6). | Id | User | Host | db | Command | Time ...
1
vote
0answers
15 views

Can I install php 5.6.10 and mysql 5.6 in flash drive [migrated]

I want to use the latest version of Apache PHP and MySQL in flash drive And then I'll use it for any computer
0
votes
1answer
117 views

mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway ...
0
votes
0answers
36 views

MYSQL 5.6 replication occasional Lag

We use Mixed replication in RDS mysql 5.6 and we are noticing replication lag every few minutes or so, the lag appears momentarily and then disappears. On slaves when we do show processlist we saw ...
1
vote
1answer
22 views

Calling a View from Procedure

I have a Large SQL Statement with several Join which need to be executed with different Where Conditions based on Procedure Input Parameters. Ex: CASE pSearchType WHEN 1 THEN SELECT Col1, ...
0
votes
3answers
27 views

What does MySQL mean by “Major Version”, and is MySQL replication supported between 5.1 and 5.6?

I am migrating from some MySQL 5.1 servers to MySQL 5.6 servers. The MySQL docs are a bit unclear if replication is supported between a 5.1 master and a 5.6 slave, and I'm a little confused what MySQL ...
2
votes
1answer
48 views

cannot delete db table [closed]

I tried to use a DROP statement to delete a table but I could not.Instead I tried the following....I deleted the .frm and .ibd files from of the respective table from the directory. Then I used a ...
1
vote
0answers
47 views

DatabaseRestart

I have strange problem, we are using circulation replication, between 2 different network with 5.6.25 and 5.6.22 version of MySQL and RAM is 64GB with 45GB innodb_buffer_pool Master Server is running ...
0
votes
1answer
450 views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
0
votes
1answer
679 views

Mysql closing immediately after new installation

I am using mysql as an backend for my webapp ,the software ran without any glitch but today it gave unexpectedly ERROR:2003 i tried many solution on the internet but none worked. so reinstalled the ...
0
votes
0answers
31 views

MySQL database and MySQL Client

My database version is 5.6.25 Community edition and my client version which is on a remote server is 5.6.24-2+deb.sury.org~vivid+2. First thing, is it okay to use a client lower version than ...
1
vote
1answer
15 views

Default directory for MySQL databases

Is it recommended to have the MySQL database location as the default one which is /varlib/mysql ? Are there any risks in having the databases in the specified location?
2
votes
3answers
258 views

Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB. I know the risks involved based on resources such as ...
0
votes
1answer
131 views

Myloader command: Table doesn't exist

I took the backup of mysql database using mydumper command: mydumper -B mydb -o /backup/oct3 -s 500000 -c -m -S /tmp/mysql.sock -u root -p password@123 -v 3 Now, I am restoring mysql database ...
2
votes
1answer
33 views

How to suppress specific warnings in mysql error log when ignoring a table to replicate

Is there a way to make the mysql error log less chatty with certain warnings? I just threw in the replicate-ignore-table option in my.cnf so our health check table doesn't get replicated. However now ...
1
vote
1answer
48 views

Very low table cache hit rates

I have been trying to tune my MysSQL databases, but always having a very low cache hit rate. I suspect partially because I run a sitemap crawler that is crawling 1.3 million pages. But, besides that, ...
1
vote
2answers
60 views

mysqld_safe mysqld from pid file /data/mysql/mysqld.pid ended

MySQL is restarting by itself, but it's crashing most of the time. I am unable to restart the instance due to a pid issue. Tried to restart MySQL moving original ib* files to _old - this time ...
2
votes
1answer
22 views

Importing CSV's into MySQL(5.6) tables leaves date fields as 0000-00-00

During the import from a CSV file created from an MS Access table into a MySQL table it converts all date fields in the csv into 0000-00-00. I use the following Load Data command for the import. ...
1
vote
1answer
28 views

Decimal or Point Data Type for storing Geo location data in MySQL

I want to store Geo location for each Address in my MySQL(Innodb) "tblAddress" table and I think I have 2 Options: 1) 2 columns for Each Address: Latitude DECIMAL(10, 8), Longitude DECIMAL(11, 8) ...
2
votes
2answers
56 views

Any way to speed up reorganize partition of a very large table in MySQL?

So we have this massive 1.5TB+ table with billions of records that has been neglected for the past few months and needs to have new partitions added. The partitions are based off a timestamp which are ...
1
vote
0answers
23 views

prioritize one query over the other in mysql

Is it possible to prioritize one thread over the other in mysql. The scenario that I'm trying to address is as follows :- 2 threads T1 and T2 are both trying to acquire a lock on a table. T1 is a ...
1
vote
1answer
54 views

Query or a New Column for Performance

I have below tables: ProductsTable (has product details), UsersTable (has all application users), RatingCategry (category for which user can rate a products like Quality, Cost etc.) and ...
3
votes
0answers
28 views

Running Triggers on Slave with Row-based Events in MySQL >=5.6

We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys If one uses statement-based ...
1
vote
1answer
46 views

import CSV into mysql table via command prompt (MySQL 5.6.25)

I'm trying to load CSV's into a mysql database with this command: LOAD DATA LOCAL INFILE '\Users\userName\Downloads\tableName.csv' INTO TABLE tableName FIELDS TERMINATED BY ',' LINES TERMINATED BY ...
1
vote
2answers
35 views

MySQL : How to restore mysql dump (.sql) ignoring DROP TABLE statement?

I am trying to restore multiple mysql dump (.sql) files. Data in each dump file are from the same table, but with different ID (primary key) range. For example, > 1.sql (0 < ID && ID ...
2
votes
2answers
19 views

Insert query in mysql

I am using MySQL. I have table like below: CREATE TABLE test_table ( first_name VARCHAR(20), last_name VARCHAR(20), full_name VARCHAR(40) ); Now I want to insert values like below: ...
0
votes
0answers
18 views

Unicode character replication MSSQL to MYSQL in Symmetric DS

I'm doing replication from MSSQL to MYSQL using symmetricds and it is working fine. Now , the problem is: In the SQL Server table , I have a column defined as NVARCHAR(20) which accepts unicode ...
1
vote
1answer
36 views

Migrating mysql.user table from MySQL 5.5 to MySQL 5.6

I have a tricky question. I am migrating from MySQL 5.5 to MySQL 5.6. I want to migrate 45 users in MySQL 5.5 to MySQL 5.6 Questions Should I just take a dump of the MySQL 5.5 user table and ...
0
votes
1answer
32 views

SET sql_log_bin = 0 in RDS MYSQL 5.6

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master. I am trying to alter a table and want to change a column value and also on some tables I want to add a ...
0
votes
1answer
178 views

Can't Delete Orphaned MySQL Temp Table

One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes). ls -l sql -rw-rw---- 1 mysql mysql 8570 Mar 13 12:05 #sql-ib32694.frm ...
2
votes
0answers
63 views

Mysql Match…against query performance in InnoDB Mysql 5.6

I need to search records in the table which contains millions of records. I have recently updated Mysql version from 5.1 to 5.6. I was using like in query which was taking around 15 sec to 30 sec. ...
0
votes
1answer
32 views

Multi Source replication on single slave having multiple instances MySQL

i had 1 instance of mysql 5.6 on one server A (Slave S) 1 Mysql installed on server B ( Master M1) 1 MySql installed on server C ( Master M2 ) At Slave end : I have created one more instance by ...
1
vote
1answer
146 views

MySQL 5.6 Datetime Range

According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', ...
4
votes
1answer
63 views

MySQL 1175 Error While Using Primary Keys

What would cause the error 1175 that starts out with: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.... The problem I have is that I did include ...
2
votes
2answers
1k views

What is default MySQL binlog retention in AWS RDS?

I tried to retrieve my MySQL RDS binlogs in order to run manual "rollback" for some SQLs. Unfortunately, I noticed that only 2 binlogs for the last 30 minutes are available. "call ...
3
votes
1answer
2k 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've also ...
1
vote
2answers
142 views

mysql multiple column index Cardinality order

How does Multiple-Column Indexes work in MySQL? How is it stored internally? consider a table with Multiple-Column Indexes on two columns (X,Y). If X has High-cardinality than Y i.e., X is less ...
2
votes
0answers
43 views

Mysql high cpu usage - cpanel

After an update from whm, I see a high CPU usage from MySQL (5.6). my.cfg: [mysqld] slow_query_log=1 slow_query_log_file="/var/lib/mysql/slow-query.log" long_query_time=1 skip_name_resolve ...
0
votes
2answers
5k views

How to force higher values of open_files_limit or table_open_cache?

I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'. This is MySQL 5.6.14 on MacOS X 10.8.3 I'm trying to address dropped connections ...
1
vote
1answer
32 views

How to write marker messages into the MySQL general_log?

I'm build a performance testing harness for our distributed web app. I'm using JMeter to drive it by simulating poorly performing parts of the app from the web side. I've set up MySQL to log to the ...
0
votes
1answer
28 views

How to Upgrade Master Slave MySQL on AWS RDS?

I already have Master Slave configuration on AWS RDS. We want to upgrade 5.5 to 5.6.23. ..What should be correct sequence to Upgrade ? 1. Upgrade Master ( Using Modify Instance ) --> Upgrade Slave 2. ...
3
votes
1answer
59 views

How can I copy the MySQL EXPLAIN result into a table?

I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis. I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how ...