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

learn more… | top users | synonyms

0
votes
1answer
44 views

DDL doesn't work in MySQL master to master replication while DML can work?

I have two master in replication left master -> 5.5.47-log MySQL Community Server (GPL) rigth master ->5.5.47-log MySQL Community Server (GPL) "left master" mysql> create table test.left ...
0
votes
2answers
2k views

How to resolve “Error log file size is increasing in mysql 5.5” issue w/o restarting mysql

I have an Ubuntu server with MySQL 5.5 installed. In /var/lib/mysql a .err file is kept on updating with these warnings only: [Warning] IP address '234.22.11.221' could not be resolved: Temporary ...
1
vote
0answers
36 views

MySQL 5.1 - Display the error-message returned inside a stored precedure

I'm trying to find an alternative to display the error-message returned from MySQL-5.1/5.5 inside a stored procedure. The example below is what I'm looking for, however it's seems to be only allowed ...
0
votes
1answer
37 views

Display the error message returned form Mysql

I need to get the whole error message returned from MySQL and assign it to @error_string : DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET @error_string = '...
0
votes
1answer
37 views

Centos 7 install MySQL5..5

Install MySQL5.5 in CentOS7 by following instructions on this page: A Quick Guide to Using the MySQL Yum Repository After that start the service: service mysqld start This returns the error: Job ...
2
votes
2answers
10k views

Some MySQL slave errors?

Master version: mysql-server-5.5.14-1.el5.remi Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340 Binlog format: ROW based I'm using PRM to setup HA for MySQL. Sometime, MySQL slave ...
0
votes
1answer
111 views

MySQL - using result of Procedure in Trigger to Insert in new table

I'm guessing I'm doing something that's prohibited by MySQL, but can't see what. Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to ...
1
vote
2answers
3k views

Trouble with triggers and insert .. on duplicate key update

I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
1
vote
2answers
60 views

Calculate Growth rate

I'm trying to calculate purchase/profit growth rate in different time periods, This is the query i've come up with (View in SQLFiddle): Schema: CREATE TABLE `profit_log` ( `market_id` smallint(6) ...
0
votes
1answer
46 views

Upgrading to MySQL 5.5 from 5.0 looks broken

I was using SLES SP3, and had earlier MySQL 5.0.67. I upgraded it to 5.5.48 (available with the CD) after upgrading my OS to SP4. I edited the /etc/my.cnf file, and replaced skip-locking to skip-...
0
votes
0answers
13 views

MySQL master- master replication

I am in the process of setting up Master - Master replication between 2 servers on a production environment. My settings are as follows, Existing System: Server 1: Status: Master databases: db1, ...
0
votes
0answers
69 views

After update triggers that deal with multiple column updates

I want to create a table for audit (audit in the sense of recording whatever a user has done in terms of data manipulation – e.g. update first name, delete contact etc.). For that I need to write a ...
39
votes
3answers
47k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
3
votes
1answer
25 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
10 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
50k 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
55 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
36 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
65 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
62 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
52 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
24 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
46 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 mysql-community-libs-5.5....
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
135 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
80 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
41 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
44 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
36 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
23 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
269 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
31 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
214 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
44 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 myadmin....
0
votes
0answers
35 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
59 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
26 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
446 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
49 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
167 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
46 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
332 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
47 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
51 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?