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

learn more… | top users | synonyms

0
votes
1answer
325 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
-1
votes
0answers
6 views

Mysql 5.1 vs Mysql 5.5+

What is the difference between partitioning 5.1 vs 5.5 version. can someone eleborate the differences because Mysql5.1 also does Partitioning.
0
votes
1answer
130 views

How to Upgrade Master Slave MySQL on AWS RDS?

I already have Master Slave configuration on AWS RDS. We want to upgrade 5.5 to 5.6.23. ..What should be correct sequence to Upgrade ? 1. Upgrade Master ( Using Modify Instance ) --> Upgrade Slave 2. ...
0
votes
2answers
156 views

Large query with subquery runs indefinitely after mysql update

We recently did a big server update, in the process migrating from mysql-5.0 to 5.5.40 Several of the queries, which had been running well on the old server, now grind everything to a screeching halt. ...
-4
votes
0answers
30 views

Mysql 5.5 improvements for MyISAM? [on hold]

What new improvements MySQL 5.5 announce for MyISAM as compare to MySQL 5.1. or is there any change in 5.5 for MyISAM
0
votes
1answer
26 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 ...
0
votes
1answer
109 views

Slow query while selecting a higher offset from millions of rows

I have sort of security log files containing millions of rows. On my current testing I imported the data for one month which is the sum of ~14,000,000 records. The table contains several set of fields ...
0
votes
1answer
234 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
0
votes
1answer
201 views

How to only display a variable in PHP inside a MySQL query when it has a content or when a condition is true?

How do I only display $mon or any variable when it has content? whenever i use this syntax and for example, monday must contain the number 1 and tuesday would contain number 2 and the other ones have ...
0
votes
0answers
7 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
59 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 ...
1
vote
1answer
122 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
4
votes
2answers
343 views

Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
4
votes
3answers
718 views

Finding differences between a master and slave when pt-table-sync fails

I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC). I've found some verifying differences beyond that. Certain tables in the mix have composite ...
38
votes
3answers
45k 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 ...
0
votes
1answer
158 views

change character set in mysql permanently

I want to change following parameters to utf8mb4 so that mysql can store emoji characters and special language characters. but when i try running following statements one by one SET ...
0
votes
1answer
104 views

MySQL Database Memory Consumption

i am newbie in mysql, just want to ask if what is the right formula in computing memory allocation for mysql, and how to check/query mysql memory consumption or we say where does memory consume by ...
0
votes
2answers
706 views

MYSQL Row Count Limits?

What is the maximum row count for a table in MYSQL? I have a system with around 600,000,000 rows in three tables. Average Data Length (from data_length column) for these tables is 58,000,000,000 ...
0
votes
1answer
198 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
523 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) ...
0
votes
1answer
528 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
0
votes
1answer
103 views

Cannot give privileges after upgrade from mysql 5.1 to 5.5.46

When I try create a user on phpmyadmin it gives me these errors: #1133 - Can't find any matching row in the user table The selected user was not found in the privilege table This is the command it ...
3
votes
1answer
22 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
7 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 ...
4
votes
1answer
569 views

Stuck in “query end” status every 5 minutes

I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about. An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is ...
16
votes
5answers
48k 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
2answers
2k views

Mysql closing immediately after new installation

I am using mysql as an backend for my webapp ,the software ran without any glitch but today it gave unexpectedly ERROR:2003 i tried many solution on the internet but none worked. so reinstalled the ...
1
vote
0answers
27 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 ...
3
votes
1answer
1k views

Convert Oracle Query for Date to Mysql queries

Need to convert the following oracle queries to MYSQL queries. 1) select to_char(to_date(?,'DD-MM-YY')) from dual; 2) select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY')) from dual; 3) ...
1
vote
1answer
454 views

Mysql query is very slow

I am developing a website like any other social networking site using mysql. I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is ...
0
votes
1answer
222 views

Slave not getting replicated SERVER_UUID error

Unable to start replication Master-Slave, I have searched a lot for this but unable to get the Server-UUID of both the server. Do we have any solution for this. Scenario: I have created new slave ...
1
vote
1answer
42 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
53 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' ...
1
vote
1answer
308 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 ...
2
votes
1answer
26 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
49 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 ...
1
vote
1answer
131 views

Finding out the exact size of the database after inserting records?

I have this query: SELECT table_schema "db.name" , Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; Does this ...
1
vote
1answer
253 views

“Remote” connection to a local MYSQL database on LAN

I have a mySQL (5.5.38) server running on my desktop (windows 7), I can connect just fine into any of the servers Users, and skip-networking is off when checked via show variables. I am trying to give ...
0
votes
1answer
234 views

Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

CRUD replicates just fine. But we noticed that DB structure altering commands such as ALTER TABLE and/or adding/updating columns to a table and even creating new ones do not replicate to the slave at ...
3
votes
1answer
64 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 ...
1
vote
2answers
627 views

Mysql improve Self join performance large table?

I have a task to help identify conflicting IP ranges , I want to highlight when one companies IP range falls within another's ... below is the sample table, I have converted the IP ranges from .dot ...
0
votes
0answers
16 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
37 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
86 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 ...
0
votes
1answer
244 views

MySQL - Enabling Scheduled Event on Master and Slave Simultaneously

This may seem a bit strange, but I am trying to get a Scheduled Event to execute on both Master and Slave. I have two databases set up in a Master (A) to Master (B) replication environment. Master A ...
3
votes
2answers
70 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
32 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
267 views

How to access another Connect server table in my Local server in SQLyog 7.01?

In same server name using same database select * from area;(Working Good) Same Servername but Different Database select * from mysql.event(Working Good) Now i am connection TWO Different server ...