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

learn more… | top users | synonyms

3
votes
2answers
278 views

How to convert timezone in MySQL using the timezone name?

I want to convert date from one timezone to another timezone in MySQL, MySQL query which I am trying is SELECT DATE_FORMAT(CONVERT_TZ('2015-12-18 06:00:00', '+0:00', '-05:00' ),'%b %d %Y %h:%i %p') ...
3
votes
1answer
72 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 ...
2
votes
1answer
180 views

Query about MySQL Semaphores in Engine Status

During a recent problem / slow down with one of our slave databases I noticed that when I ran SHOW ENGINE INNODB STATUS. I am getting the following: ---------- SEMAPHORES ---------- OS WAIT ARRAY ...
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
155 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
53 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 ...
1
vote
1answer
87 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 ...
0
votes
0answers
22 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
22 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
21 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
924 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
50 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
82 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 ...
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
38 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 ...
1
vote
1answer
567 views

Mysql Tuning for a small server

I have a small Cloud Server running under Ubuntu 2 core, 2GB ram and 2GB swap, with Plesk Panel, Apache, Nginx as reverse proxy and Mysql 5.5. I have about 18 domains hosted here and each domain has ...
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 ...
0
votes
1answer
32 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
1answer
119 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
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) ...
1
vote
0answers
47 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
2answers
52 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
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
61 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 ...
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
48 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 ...
0
votes
0answers
84 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
2answers
89 views

Mysql slave use more memory than master server

I have used top command to check memory usage on both mysql master and slave server. Master is using only 12% of allotted memory but slave is using 82%. configuration wise both are same. can anyone ...
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
2answers
175 views

MySQL - installed version 5.5 but mysql --version says 5.0.95

I'm running CentOS 5.2 and upgraded MySQL to 5.5. When I run mysql I get this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.5.45 MySQL ...
0
votes
0answers
158 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 ...
1
vote
1answer
32 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?
2
votes
0answers
46 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....
0
votes
0answers
22 views

Migrating a DB of mysql 5.0 to 5.5

Sorry this is a newbie question. I have a DB of mysql 5.0 on a shared host and I would like to migrate this DB to another mysql server (5.5) on another shared host. What I did is like this. ...
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 = '...
1
vote
1answer
521 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 ...
4
votes
1answer
325 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
64 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
30 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
159 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
115 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 ...
4
votes
1answer
331 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
84 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 ...
0
votes
0answers
52 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 ...