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

learn more… | top users | synonyms

0
votes
1answer
199 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
1answer
531 views

Strange Locking issues while playing back logs via percona playback

Problem while replaying logs I am in the process of benchmarking a new DB node (specs at the end) and have run across some strange behavior: As described here i: Created a dump (innobackupex ftw) ...
3
votes
1answer
23 views

MySQL binary log reporting execution time as UINT_MAX

Looking through the MySQL Binlogs, I see some data that just does not make sense. There are queries that say they have been running for 4294967295 seconds (which also happens to be UINT_MAX on this ...
0
votes
0answers
8 views

Server memory consumption not changed after innodb buffer size change

I observed a strange thing while applying the innodb_buffer_pool_size on mysql. I applied the innodb_buffer_pool_size=20G (75% of my physical RAM size) and as per logs and innodb status the mysql is ...
17
votes
5answers
49k views

How to properly kill MySQL?

I have CentOS 64bit with CPanel installed and I use service mysql stop However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
1
vote
0answers
31 views

What is the maximum value for the index_length of a MySQL 5.5 table?

I'm trying to find out why a series of inserts is painfully slow (1 minute 15 seconds, on average) on a dedicated MySQL 5.5.47 server, while the same inserts are normally fast (less than 5 seconds) on ...
1
vote
1answer
48 views

Host not Allowed Connectivity to MySQL Server

I currently have a website and it references mySQL database. The website uses HTML, CSS and PHP. PHP code embedded inside HTML website, fetches a value and stores it on the SQL database. Then the ...
0
votes
0answers
35 views

MySQL Upgrade query

I have 5.5.44-log MySQL Community Server (GPL) running in one of my RHEL 6.4 server. I need to upgrade the server to Percona Server-5.6.29-76.2. How to go about the upgrade? Should I completely remove ...
0
votes
1answer
60 views

MySQL dies at same time by night [closed]

my MySQL Database dies every night at around 11p.m. and I have absolutly no idea whats the problem. Maybe you can help me: The error.log says: 160412 11:37:13 [Warning] IP address '123.158.49.205' ...
2
votes
1answer
39 views

How do you properly remove “REQUIRE SSL” from a single user?

I successfully granted REQUIRE SSL to a single user by doing... mysql -u"${targetMySqlUser}" -p"${targetMySqlPass}" -e "GRANT USAGE ON dbname.* TO 'dbusername'@'%' REQUIRE SSL;" but im failing on ...
0
votes
0answers
51 views

Very Very Veeery slow insert queries

I am writing here because I`ve already spent days reading tutorials, other topics in here, and trying different configurations. The project : Fashion website with ~200k products which should be ...
3
votes
1answer
66 views

MySQL Killed by OOM after Corrupted Database

One of our web-app servers recently had an issue where MySQL was taking more memory than we have physical RAM. This server has 64GB of memory and 8GB in swap, and MySQL was OOM-ed because it was ...
0
votes
0answers
20 views

Boosting select queries during INSERT ON DUPLICATE KEY UPDATE into 150Gb innoDb table

I have a single server with 2 innoDb tables: 35Gb (partitioned, 24M rows), 150Gb (without partitioning, 220M rows). 25% data, 75% index. 99% of select queries are SUM + WHERE + GROUP BY. 100% of ...
0
votes
2answers
41 views

How to properly change “innodb_buffer_pool_size” value for mysql?

I have following environment: # cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core) # rpm -qa | grep ^mysql-community- mysql-community-common-5.5.48-2.el7.x86_64 ...
3
votes
1answer
1k views

Keeping MySQL staging database on same server updated from production

I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
2
votes
1answer
108 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 ...
3
votes
2answers
74 views

Inserting rows with WHERE condition

Structure 1 table named: scan 2 columns named: id, and date Problem I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the ...
0
votes
1answer
39 views

Why does this SQL work on one version of MySql-Server but not another?

I'm trying to run the following very simple SQL command on two different MySQL databases: CREATE TABLE mytable ( created DATETIME(6) NOT NULL, updated DATETIME(6) NOT NULL, my_id ...
0
votes
1answer
39 views

What is the difference between CREATE INDEX and ALTER TABLE ADD INDEX? [duplicate]

I have searched the web but I have failed to find a conclusive answer for this question. Is there any difference between CREATE INDEX and ALTER TABLE ADD INDEX? Are there any problems that may ...
0
votes
0answers
29 views

how to force mysql 5.5 server to allow tls/ssl encrypted connections only?

given a mysql Server 5.5 community edition (which came with SLES 11sp4). i configured it to support TLS and it works fine. but i did not found any options or parameters to FORCE IT to do TLS/SSL ...
0
votes
1answer
19 views

commit statement missing for a dml statement in binary log

I have noticed 'commit' statement missing for a DML statement in binary log. Does it mean that this statement is rolled back in server? I am using mysql 5.5.40. sample binlog at 159527792 160229 ...
3
votes
3answers
268 views

Listing rows with a WHERE condition

I am struggling with a SQL query that lists hosts that are offline. Problem The problem occurs when I need to list hosts that don't have any ports open (status = 0), so looking at the table at the ...
3
votes
1answer
30 views

how to “revoke” a “GRANT OPTION” from an mysql user/database?

i have a mysql installation with lots of users and grants. But one grant is unique, since its supposed to be a simple DB user and usually does not need GRANT privileges. However someone added it when ...
3
votes
2answers
184 views

Different behavior between MySQL 5.5 and 5.6

I have this schema with data: CREATE TABLE `ticket` ( `id` INT NOT NULL, `name` VARCHAR(45) NULL, `type` INT(11) NULL, PRIMARY KEY (`id`)); CREATE TABLE `task` ( `id` int(11) NOT NULL, ...
1
vote
1answer
36 views

Cannot open some tables in phpmyadmin

Recently i get backup of my database from host in that backup i get table in .ibd,.frm,.myd format . I placed this files in xampp->mysql->data->test . Now i can open the tables in php ...
0
votes
0answers
27 views

Can't connect to MySQL server via SSL

I'm trying to connect to a MySQL server via SSL. I know the server is set up correctly to handle SSL connections because I can connect fine using MySQL workbench, PHP, and command line MySQL client ...
0
votes
2answers
44 views

MySQL SELECT n-th row between dates (time interval)

I have a server which records various sensor data each second and inserts it into a MySQL table. To view the sensor data on a dashboard I have a chart which also gets updated with live data each ...
0
votes
0answers
25 views

Trigger query fails to execute because of local variable

I have the following table: |-------------------------------------| |Column |Type |Null|Default| |-------------------------------------| |//**id**// | bigint(11) | No | | |soldier_id ...
0
votes
1answer
428 views

How to restore data after MySQL Upgrade?

I upgraded from MySQL 5.5 to 5.6 by uninstalling 5.5 and then installing MySQL 5.6. Now that MySQL 5.6 is running, none of my DBs or data is present/visible in MySQL Workbench. I also see that I ...
1
vote
2answers
45 views

Counting rows from a subquery

Simple: I would like to count the number of rows from the sub-query. Note that status is whether the host is online or not. Bad code SELECT COUNT(ip_address) FROM `ports` ( SELECT DISTINCT ...
4
votes
0answers
1k views

Why is loading data into MySQL cluster taking so long?

I have a mysql-5.5.22 ndb-7.2.6 cluster 64bit running on Centos 6 with each node installed on the same Citrix Xen Server communicating via a virtual private network. Each node has 4GB of RAM, 4 vCPUs ...
0
votes
1answer
2k views

Following mysqltuner suggestions: query_cache_size and join_buffer_size

So I am following the suggestions of mysqltuner, and so far I've had great results, the server is performing well (dedicated VPS running a large Magento eCommerce store) There is only two issues left ...
2
votes
2answers
9k views

Unable to drop the table since its showing waiting for meta data lock

We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on ...
1
vote
2answers
80 views

can't I use Current_timestamp as default value in mysql 5.5.47-cll

I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47. Query is ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ; While it is working ...
1
vote
1answer
44 views

Incorrect backup size

We are storing our everyday full backup in remote storage. Everything seems to be working fine, but every seven day interval the archived folder size is much more than the everyday archived backup ...
1
vote
0answers
305 views

Records not getting replicate from Mysql 5.5.32 to MySQL 5.6

I have a MySQL 5.5.32 server (community edition) on one Linux server which is master. I have a MySQL 5.6 server (community edition) on another Linux server which is slave. I have installed various ...
0
votes
1answer
46 views

Select rows with most recent date for the given date

I have a table to manage the stock which looks like below product batch qty date Grape BAT0 42 10/3/2016 Apple BAT1 12 11/3/2016 Banana BAT2 15 11/3/2016 Orange ...
1
vote
1answer
34 views

MySQL allows records longer than varchar(N)

We have a database with a field of type VARCHAR(255) but in it I can see some records having a length of larger than 255, such as 264. How can it be possible? Can it be that the database is corrupt? ...
2
votes
2answers
47 views

How to get month name from week number and year [closed]

I have a week number and a year (e.g. 34 and 2015). I want to get the name of the month to which the specified week belongs. How can I do that?
0
votes
1answer
924 views

Change MAMP to use a lower Mysql 5.1 version

I need to have the same development environment for my PHP program. My clients test server is running on Mysql 5.1 while my local is using MAMP running Mysql 5.5. How do downgrade my mysql database ...
0
votes
2answers
79 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 ...
1
vote
1answer
74 views

MySQL. Capture Cis program makes incorrect query: SELECT DISTINCT “PartType” FROM “Capacitor” ORDER BY “PartType”

I am having problems to get one database running. I have a MySQL in my server, the server is running "5.5.48-MariaDB - MariaDB Server" with "UTF-8 Unicode (utf8)" I have a MySQL ODBC 64 bits 3.51 ...
0
votes
1answer
12 views

What configs influence whether user credentials/grants are written to the binlog in Mysql

I have a fairly trivial master-master setup. When testing this setup I see that mysql users are binlogged and replicated on slaves. However on each of the masters the binlog-do-db* is for a specific ...
0
votes
0answers
24 views

MySQL 5.5 client SSL connection only works when adding cipher list argument, why?

I set up the packaged MySQL server 5.5 and client on two Ubuntu VM. I configured the server to support SSL. I can connect to the server with the MySQL CLI tool between the VMs, but the connection is ...
0
votes
0answers
54 views

EXPLAIN SELECT query is writing to tmp table

I am getting a "copying to tmp table" when running an EXPLAIN SELECT on a query. This is a pseudo-query showing the structure of the query (I can't post the actual query): SELECT colA , colB , ...
3
votes
2answers
352 views

View active MySQL connections per user

I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so ...
1
vote
1answer
32 views

Altering a large table to INNODB

I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master. I tried to use the ...
2
votes
3answers
752 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
0
votes
1answer
23 views

Slave (SQL thread) replicates very slowly

See this video: https://mckay.media/Yxjp2 I have two MySQL replication slaves which are replicating from the same master. Both slaves are running on identical hardware. Each has its data directory ...
1
vote
3answers
202 views

MySQL database uses too much CPU

I have a MySQL database which uses too much CPU. I am actually performing benchmarking to the web servers which queries the database. Right now the database is being the bottleneck, it uses too much ...