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

learn more… | top users | synonyms

2
votes
1answer
1k views

How to set unique columns combination

see this table: CREATE TABLE IF NOT EXISTS `default_messages_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `message_id` int(11) NOT NULL, `owner_user_id` int(11) NOT NULL, `to_user_id` ...
13
votes
2answers
37k views

What is the difference between optimize table and analyze table in mysql

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
0
votes
0answers
321 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql [duplicate]

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
34
votes
1answer
46k views

intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
1
vote
1answer
62 views

Really strange behavior using variables in MySql query

I was answering a Question on Stack Overflow forum and the OP notice a really strange behavior that I could reproduce using the SQLFiddle site So lets go to it. We have this setup: CREATE TABLE ...
1
vote
0answers
29 views

MySQL can't connect as user with password locally

I am running into a strange authentification issue. Users can't connect to MySQL locally using a password, but they can without one. mysql -h localhost -u someuser -psomepassword won't work ...
0
votes
1answer
143 views

MySQL percona 5.6 to MySQL 5.5 replication

Scenario 1 I am having a MySQL DB on 5.5 which is compliance, So need to upgrade it to MySQL 5.6. while doing this we see that 5.6 is running slow, were as it runs perfectly on 5.5 backup. Scenario ...
0
votes
0answers
109 views

What happens if there is missing data in the slave MySQL server

At the data center there was electricity outage. And it appears that at that time my replication stopped syncing because it said something like this; "I cannot find the next position. Last proper ...
0
votes
1answer
35 views

Query to get the users who have a application approved

I have two tables Users and Submissions . Need to find the query to get the users who have submitted atleast one grant and have never won . I can write a subquery and achieve it .But I would like to ...
0
votes
1answer
258 views

Exception when trying to connect to remove MySQL from Intellij IDEA

I can't connect from Jetbrains IDA to my MySQL at AWS. I am getting exception: Connection to MySQL - aws failed java.sql.SQLException: Communications link failure The last packet sent successfully ...
4
votes
1answer
277 views

Subtract time difference between two columns in a table

Problem: How can I subtract the TIMEDIFF between two sensors (sensor 1 and sensor 2 in the example below) and return the the value in mili seconds? How do I get with the resolution of milliseconds? ...
0
votes
0answers
80 views

MySQL – Backup/Restore Selected Tables/Fields Only

I support a Python application (includes some C modules) that utilises a MySQL database. A number of clients are running this in a standalone environment but have agreed that we can use their data ...
1
vote
1answer
280 views

import text file getting command is not allowed with this MySQL version

here is my table CREATE TABLE cities ( country CHAR(2), city_ascii VARCHAR(100), city VARCHAR(255), region CHAR(2), population INT UNSIGNED, ...
0
votes
0answers
48 views

How to pivot a CSV file into a table

Input: a CSV file which cannot be changed prior to import Desired Output: A table where CSV_File.Name is a column and CSV_File.TagValue is the data in that column with a Key field Timestamp column ...
2
votes
0answers
66 views

Identifying table causing runaway rollback in MySQL 5.5 (InnoDB / Windows)

I have a large database (180 GB), running under Windows, with a runaway rollback that starts running as soon as the database is started up. I have started the database with innodb_force_recovery=3 ...
0
votes
0answers
15 views

How can i find next binary log to restore in mysql database

i am using mysql database,one database is writing data in binary log and those logs are sent to another machine through scp.In remote machine i want to restore binary logs. through mysqlbinlog bin-...
1
vote
0answers
74 views

changing mysql configuration . Getting errors like Table is marked as crashed and should be repaired

On Our server I have done following configuration for Database: [server has 2 core, 2 GB ram and windows 2008 operating system] query_cache_size= 32M [Old] query_cache_size= 50M ...
1
vote
1answer
394 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 ...
0
votes
1answer
322 views

mysql database under symlinked directory: ERROR 1018 (HY000): Can't read dir of './<dbname>/' (errno: 13)

I'm using mysql 5.5 on an ubuntu linux server. I need to move a database from its current location (under /var/lib/mysql) to a disk with more space, so I attempted to move the directory and create a ...
18
votes
2answers
29k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. http://forums.mysql.com/read.php?22,...
3
votes
2answers
105 views

Is it possible to block a specific query, or return a static result?

Before you yell at me for doing it wrong, I know I'm doing it wrong. I'm using WHMCS (source code is not available), and we have a quarter of a million invoices. One particular query, which runs on ...
1
vote
1answer
1k views

Many mysql queries stuck at sending data

I am using opencart and I can se many queries blocking the mysql server. All queries are the same except the p.manufacturer_id. Attached there is the query and the explain of the query. The query is ...
-1
votes
1answer
22 views

mysql performance

Currently,we are using mysql 5.5.4 database on server with 4GB RAM,500GB HDD on Linux.There are predominantly 3 databases and the maximum size of database is 100MB.Assuming data grows at rate of x mb ...
0
votes
1answer
52 views

Multiple select for single row insert

I need to insert a row into a table by selecting value from multiple tables. I want a row to be inserted from multiple tables. insert into tab(id,name,date) values ((select max(id)+1 from tab),(...
0
votes
0answers
155 views

Significance of common schema - MySQL

Why common schema is not available in MySQL 5.6? What is the significance of it? So If there is a need for common schema for an application, what is the workaround to have common schema in 5.6 or ...
5
votes
1answer
407 views

SELECT RELEASE_LOCK causing “Mysql has gone away”

During a long running process (90m+), our code calls SELECT RELEASE_LOCK('..');. However it always gives a "Mysql has gone away" error when we do this. We never got to see the return value of the ...
1
vote
3answers
3k views

Amazon RDS MySQL performance issues

I have published my website on Amazon EC2 (Singapore region) and I have used MySQL RDS instance for the data storage. Everything is working very fine except performance. It seems that all queries, ...
0
votes
2answers
178 views

Mysql datetime field partial match

Currently I am designing a mysql table structure that will hold a couple of million rows. I want to search in it on datetime field by date year and so on but the date will be entered by user so it ...
0
votes
1answer
806 views

MariaDB/InnoDB: Temp file write failure

When attempting to alter a table to enable compression by modifying the KEY_BLOCK_SIZE I received the error: ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB The command ...
0
votes
1answer
101 views

mysqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca

I am trying to recover binlog from a remote server using mysqlbinlog client program, however it appears that mysqlbinlog doesn't recognize the SSL options that's required to establish SSL connect to ...
1
vote
3answers
691 views

Amazon AWS RDS Aurora Row size too large (VB5)

We're trying to import our vbulletin 5 database into RDS/Aurora and getting this: ERROR 1118 (42000) at line 5733: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ...
1
vote
0answers
936 views

MySQL - Truncated incorrect datetime value on update statement

I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC. (there will be apx 60 logs produced at 0.25 GB per day). I need to ...
1
vote
1answer
151 views

MySQL - include a default value in LOAD XML INFILE statement

I have the follwing table: CREATE TABLE `tab1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `DEFAULT` VARCHAR(25) DEFAULT NULL, `ITEM` varchar(25) DEFAULT NULL, `TIMESTAMP` TIMESTAMP ...
12
votes
3answers
23k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
2
votes
3answers
2k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
1answer
48 views

Changing Default Values of Percona Audit Log

Have I missed Something here? I am installing the Percona Audit Log Plugin (MySQL 5.5). I can install it fine using: INSTALL PLUGIN audit_log SONAME 'audit_log.so'; I can then see all the default ...
1
vote
0answers
32 views

MySQL/Percona Audit Log Plugin Not Working

I've been looking to implemement the Percona Audit Log plugin. So far I have installed it and configured it, and it seems to be working ok. Except, I have installed it on a slave Database as I don't ...
0
votes
1answer
193 views

Fulltext Search Doesn't Match a Hashtag

We maintain a table that looks like this to perform search queries on our forums database. I'll draw your attention to the FULLTEXT columns: CREATE TABLE `forums_posts_search` ( `post_id` bigint(20)...
1
vote
1answer
162 views

Diffrences between master and slave replication in mysql?

In a database named asterisk, there is a table named cdr which was partitioned on the time field. mysql> show create table cdr; Create Table: CREATE TABLE `cdr` ( `gid` bigint(20) NOT NULL ...
1
vote
0answers
93 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...
4
votes
1answer
769 views

How to removing or reduce the size of redo logs in MySQL cluster

Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we ...
6
votes
2answers
582 views

MySQL: Show list of tables with paging in Terminal

I am using MySQL Ver 14.14 Distrib 5.5.44 for debian-linux-gnu. How can I show my tables page wise in a terminal?
2
votes
1answer
1k views

Mysql error “#1030 - Got error -1 from storage engine” when doing insert/update queries

I am starting to learn manipulating some long list of data from datalogs in mysql, around 300k entries, and it is being added by about 1k off entries everyday, it was working fine in a week then ...
2
votes
1answer
72 views

Why does coalescing make this query quicker?

I have the following query SELECT SQL_NO_CACHE `table1`.* FROM `table1` LEFT JOIN `table2` ON table2.id = table1.table2_id WHERE (`table2`.`date_assigned` >= '2015-06-21') AND ( ...
0
votes
1answer
229 views

MySQL Trigger On UPDATE “Unknown Column” Error

I want to log a table when its updating a row which is column "insertDate" or "updateDate" value is before than 3 month. I wrote this trigger: DELIMITER /// CREATE TRIGGER `trig_before_up_dad_records`...
0
votes
1answer
40 views

Percona / MySQL audit log plugin - limiting the content?

I've been looking to experiment with Perconas Audit Plugin, as it sounds like it could be potentially very useful. However it appears that it is a very blunt (ALL or NOTHING) tool. From my ...
19
votes
4answers
11k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
3
votes
1answer
163 views

Any risks in updating the system time on a mysql 5.5 server?

We just discovered one of our mysql systems has not been running ntp, with the result that the time has drifted a bit. If I enable ntp, and synchronize the time while the system is running are ...
1
vote
1answer
60 views

enable log_bin_use_v1_row_events in mysql 5.6

I have a MYSQL master with two slaves also running MYSQL 5.6. I want to create another slave and that needs to be MYSQL 5.5. I was wondering if replication will work smoothly. I have ...
5
votes
2answers
212 views

How can I display a mysql bit(64) column as a string of ones and zeros?

We have a MySQL 5.5 database with a table that holds a number of 64-bit values which are used for calculating the hamming distance. The columns for these are defined as BIT(64) and are populated ...