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

learn more… | top users | synonyms

2
votes
2answers
99 views

Optimizing a simple query on a large table

DB: MySQL 5.5.20 (WampServer, default configuration) OS: Win 7 HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX) Memory: 8GB MySQL my.ini: http://pastie.org/private/...
1
vote
2answers
2k views

Slow INSERT/UPDATE on InnoDB

I have a problem with the performance of my MySQL server, let me explain: I instaled a MySQL server, version 5.5.20 Server version: 5.5.20-log Source distribution SELECT queries runs fast on ...
2
votes
0answers
40 views

Composite index works for larger set of data, but not smaller

This is a follow on question to optimise my.ini for key query Data model: DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT NULL AUTO_INCREMENT, `...
0
votes
0answers
175 views

Change MySQL stored procedure 'Database Collation' name

I have imported a DB from Local machine to server machine. While importing the Database, the character set values of the DB are set by system default to "Latin". I have changed the character set to "...
2
votes
2answers
32 views

MySQL Database Archive

We have 300 databases in our environment mixed of MyISAM and Innodb(innodb_file_per_table has been enabled) tables.Using MySQL 5.5.19 version. I we are planning archive inactive database.Is there any ...
3
votes
1answer
63 views

How we can recover MySQL database (Innodb,myisam)

I have found some recovery tool innodb-recovery but it works only on linux machine, I would like to know how to recover innodb tables on windows. Transaction ID based recovery Cancel based, Change ...
1
vote
1answer
2k views

what could cause a mysql process to close (clean)

We are using mysql master-master (version 5.5.32) for an API manager called Layer7. The 2 hosts holding the instances are in the same ESX From time to time we have mysql doing a clean shutdown for ...
1
vote
2answers
147 views

Mysql 5.5.25 must be restarted to connect

We have MySQL v5.5.25 running on Windows Server 2008 R2. Occasionally people are unable to connect, and the only way we have found to fix this is to restart the mysql service. This happens every 2-3 ...
1
vote
0answers
3k views

mysqld upgrade 5.5 -> 5.6 mysql Error: Failed while fetching Server version! Could be due to unauthorized access

After updating mysql with yum to 5.6 I am getting such an error message when trying to run mysql [root@host rpm]# mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck ...
1
vote
3answers
140 views

why in explain in mysql row count is very high

i am using mysql, here is query- mysql> select count(*) from payments as Payment WHERE `Payment`.`fordate` BETWEEN '2015-11-28 00:00:00' AND '2015-12-31 23:59:59'; +----------+ | count(*) | +------...
0
votes
1answer
37 views

Why is MySQL 5.5 ignoring this index?

I have a pretty simple query that is not using an index I expect it to because of the WHERE. Table: CREATE TABLE `cba_device_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `device_id` varchar(30) ...
0
votes
0answers
45 views

MySql server failing to start

I was reinstalling backup image on Raspberry Pi and I noticed that MySql server isn't running. I've looked in /var/log/syslog and I bumped into this: Dec 4 20:06:00 node mysqld_safe: Starting mysqld ...
0
votes
2answers
49 views

How to reconfigure primary key columns

I have a series of tables in my database that are using DATE as the primary key. I would like to reconfigure the tables so that an AUTO_INCREMENT ID field is the primary key. What is the best way to ...
0
votes
2answers
3k views

MySQL 5.5 Fail start Fedora 16

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error. [root@server]# service mysqld start Redirecting to /bin/systemctl start mysqld....
0
votes
1answer
77 views

MariaDB is Forgetting Previous Auto Increment IDs

I'm using MariaDB 5.5.39, and it seems like it is forgetting the max id of the auto-incremented primary key if I delete all rows from the table and then too much time passes before another row gets ...
3
votes
1answer
260 views

innobackupex is failing while exporting backup

I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex. I have following ...
1
vote
1answer
86 views

MySQL binlog does not show SQL statements

While checking the binlogs, it struck me that I cannot see any SQL statement. Here's an example: $> sudo mysqlbinlog --database=securities --start-datetime='2015-11-26 13:00:00' --...
1
vote
0answers
13 views

Trigger quest updating a column as data is entered in multiple columns at different times

What I am looking at is I have a project I'm doing and within it there is to be a column that would be turned into a GPA based off their individual assignment scores. My question is would it be ...
1
vote
5answers
28k views

1286 - Unknown storage engine 'InnoDB'

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
0
votes
0answers
21 views

MySQL Trigger at Insertion with nested CAST

So I'm using MySQL 5.5 and trying to insert a VARCHAR as ID (since my teacher requested so), I am trying that each time there is an insertion of a new USER, a trigger automatically assigns an ID to it....
0
votes
1answer
28 views

Optimising a query with two aggregate fields

I have a table in a MySQL 5.5 database with around 7 million records, with fields typeId, regionId, scanTime, scanId and a query that returns around 239,000 results. For every combination of typeId ...
0
votes
0answers
21 views

adding rows vs creating new tables MySQL

I am having a table where I have not more than 15 columns (maximum data type of any of those column is SMALL INT and only a single column of type VARCHAR with maximum 20 chars only). Out of those 15 ...
0
votes
0answers
20 views

server response is getting slow day by day

MySQL server 5.5 > running in Linux Box is getting slow. MySQL server is being used by Java Web application, please help me to figure out the problem.
1
vote
1answer
807 views

#1041 - Out of memory issue in mysql [closed]

I am using Amazon RDS db.m1.medium instance. It has at least 40-50 database and atleast 10k table. For last one year it was working fine, but today I was altering one table It started giving error. I ...
0
votes
1answer
44 views

MySQL order by is equal, in what order are the results returned?

I use mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3 I have rows that are ordered by an order column. Turns out, many times, the order number is the same, say 1. ...
0
votes
1answer
27 views

selecting duplicate values with a condition from a mysql table

I have the following table in mysql: Key DI CI FD FA NM Valid_from Valid_to 0 1224468 123 2012-06-30 3 6 2013-01-23 9999-12-31 1 1234567 123 2013-12-31 3 ...
0
votes
0answers
35 views

Special Operators in MySQL

I am going through the MySQL doc here and reading about the operators. I couldn't find anything related to special operators over there. Hence I am wondering, is there anything that exists and falls ...
0
votes
1answer
31 views

Unable to connect to mysql remote

I am struggling to connect MySQL database with my application, I am having the following set up 3 Applications running in cluster mode and trying to connect with same DB. MySQL database running on ...
0
votes
0answers
22 views

How can I get month wise leave for a financial time period for an employee in MySQL?

I have the following columns in a table: start_date end_date no_of_days start_day_half -- (if the starting day to be a half day then its flag is 1) end_day_half -- (if the end day to be a half ...
1
vote
0answers
23 views

MATCH(title) AGAINST('test 11' IN BOOLEAN MODE) can't search number

SELECT *, MATCH(title) AGAINST('test 11' IN BOOLEAN MODE) AS Score FROM search_table WHERE `date` > DATE_SUB(NOW(), INTERVAL 1 MONTH) AND MATCH(title) AGAINST('test 11' IN BOOLEAN MODE) ...
0
votes
1answer
101 views

Insert and Update Queries Log in MySQL

I have sensitive information in multiple databases on the MySQL Server. I need to record which user have inserted or updated the record and incase of update we should be able to see the previous and ...
1
vote
0answers
46 views

What should be the best practice to alter the table using the script to shift columns

I have a MySQL table which has columns 93 in it. All first 92 columns are INT(11) and the last column is of type DATE. |number|0|1|2|3........|88|89|90|ddate| | | | | | ........| | | | | ...
0
votes
0answers
29 views

MySQL Event Failed to Run

We have a selection of MySQL 5.5 Databases running on Debian Linux, We use MySQL Scheduled Events for certain processes, and until now they have always run as expected. But yesterday we had to move ...
0
votes
0answers
60 views

mysqldump slows down to a crawl

I have a table with ~10mln records that I want to dump. Running: mysqldump --opt --skip-extended-insert database table > table.sql While monitoring progress I have noticed that first 9.8mln ...
12
votes
3answers
23k views

MySQL Error Reading Communication Packets

In MySQL error logs, I see these quite few a warnings like these: 120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading ...
2
votes
2answers
261 views

MySQL Cluster Steps To Recover Data If Catstrophic Failure Occurs

I want to know if a catstrophic failure occurs (like a power outage), and all nodes in MySQL Cluster are shutdown in cluster then data will be lost even though a backup was taken? Or it possible that ...
0
votes
0answers
10 views

RDS MySQL Storage and N/W Usage

have Single Schema and single MySQL Instance on RDS Where my website users are creating app and app data stored in MySQL database in my schema each app has Unique id. One user may have one or more app....
1
vote
1answer
46 views

Not able to connect to local MySql database “only” when connected to VPN on VM [closed]

Setup: MySql installed on my Mac using Xammp VM installed using VMware Fusion running win XP. I am able to connect to the mysql database through my VM normally. As soon as I connect to a VPN it ...
1
vote
1answer
471 views

MySQL performance issue - intermittently slow queries

I manage an intranet application used by around 50 staff in a few locations. The application is hosted on a Windows Server 2008 VM with 8GB RAM and MySQL (5.5) is running on the same server as IIS. I ...
0
votes
0answers
79 views

Memory usage still growing

I have problem with my production mysql server. The memory usage is still growing and I don't know why. Trouble started when we changed the server. My mysql version: 5.5.44-0+deb8u1-log - (Debian). [...
0
votes
0answers
30 views

Enormous MySQL 5.5 Handler_read_rnd_next

The value of Handler_read_rnd_next is actually 91,3 G on my Database server but I am pretty sure that all tables have proper indexes. I am 100% sure that there is no query with a WHERE clause that ...
0
votes
0answers
20 views

Database design: history - transactions, states, durations?

I have not delved into this type of db info before, and it seems foreign to me what I need to do. So I would ask if this seems familiar to you, and is there a way this is generally done? (And would ...
1
vote
0answers
75 views

Proper database configuration

I'm running MySQL 5.5 on Debian 7.8. My entire DB is 2200 MB large (all tables are InnoDB), the mysql process is taking up to 6G RAM but still I think that not evrything is loaded in RAM I have large ...
0
votes
0answers
150 views

MySQL Load XML Infile with dynamic file name

I have a shell script which loops through a folder and picks up a set of files in turn. I then pass the filename to a .sql script so that the file can be imported into my database. BUT, I can't for ...
0
votes
1answer
282 views

MySQL starts using > 90% of CPU, “Copying to tmp table on disk” and “converting HEAP to MyISAM”

I have a server where MySQL is periodically taking up 11 of the 12 CPU cores. Once this starts it doesn't end until I restart MySQL. At least, I haven't let it run like this for more than a half hour ...
0
votes
1answer
8k views

MySQL slave stuck in “Reading event from the relay log”?

5.5.28-log MySQL Community Server (GPL) by Remi binlog-format=MIXED My problem is similar to this question. *************************** 2. row *************************** Id: 973415 User: ...
4
votes
1answer
293 views

MySQL Write performance bottleneck

We are trying to diagnose a MySQL Bottleneck, specifically for data writes. Basically, we have a single process that picks files from a folder and load into a MySQL table X using LOAD DATA CONCURRENT ...
1
vote
1answer
31 views

MySql - How to monitor the amount of reads/writes to the DB

Is there a way to fetch the amount of reads / writes made to the entire DB (MySql) in a given period, e.g. the amount of read / writes in the last hour?
0
votes
1answer
33 views

Create SQL Query in function or PROCEDURE

I am trying to create function inside MySQL to create several queries for example : DELIMITER | CREATE PROCEDURE queryBuilder(_tableName varchar(100)) BEGIN SET @str_query = '...
2
votes
0answers
45 views

Database Architecture & Design

I am building a web application for project management which will be hosted on my servers. My application design is like this 1 - user register 2 - domain name will be created i.e abc....