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

learn more… | top users | synonyms

0
votes
1answer
16 views

Referring alias name in subselect returns “Unknown column in 'where clause'”

I have following table: + ---------- + --------- + --------- + -------- + ------------ + ---------- + | Field | Type | Null | Key | Default | Extra | + ---------- + -----...
4
votes
1answer
711 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 ...
0
votes
2answers
142 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 ...
1
vote
0answers
10 views

Mysql InnoDB relations vanish when exporting/importing to online database

My issue is the following : I have a local database running on EasyPhp server : WEB serv : Apache/2.4.7 (Win32) PHP/5.5.8 DB serv : 5.6.15-log - MySQL Community Server (GPL) PHPMYADMIN : version 4.1....
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
1answer
26 views

Sync two mysql database one in intranet and the other on internet

I'm making the training and placement module for my college. But I'm facing problems to sync the databases, one that will be used by the students and the other that will be used by the HR of a company....
1
vote
1answer
470 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 ...
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
3answers
5k views

Automatic MySQL Replication Fail-over Strategy

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the ...
0
votes
1answer
330 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 ...
0
votes
1answer
35 views

mysql performance test

We are planing to test the performance of the new DB server. For that we have the below the plan: get production sql queries replay those sql queries against the different DB I was planing to use ...
0
votes
1answer
385 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 ...
2
votes
2answers
677 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
53 views

MySQL: Log only 'select' queries executed on a specific set of tables of a database

I have a Magento site in which I want to log only SELECT queries(after SELECT it could be any column or all columns, in short all SELECT statements) executed on a specific set of tables, also as it's ...
1
vote
1answer
148 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 ...
0
votes
1answer
119 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 ...
2
votes
1answer
3k views

How to turn Event Scheduler On permanently in MySQL?

How do I turn on the event scheduler in mysqld permanently? I'm using phpMyAdmin and I have scheduled an event to copy records from one table to another everyday at a specific time, but the records ...
0
votes
1answer
209 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
261 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 ...
1
vote
0answers
177 views

Importing large .sql file stops before even midway and starts deleting

Okay so i'm trying to import my minecraft servers .sql file into database and after first 1GB of importing data it stops without error and starts deleting what already has been imported any ideas? ...
0
votes
1answer
335 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 ...
2
votes
1answer
293 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 ...
1
vote
0answers
16 views

my ssh is ok but masterha_check_ssh is fail

my environment is vmware12 + centos6.6 + mysql5.5 + mha4mysql-manager-0.53-0.noarch.rpm + mha4mysql-node-0.53-0.noarch.rpm my structure is one master three slaves master 192.168.233.135 node ...
0
votes
1answer
283 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 ...
0
votes
0answers
19 views

Innodb Change Buffer merge

I have been testing the performance of my innodb mysql. I ran a script which inserts and selects one after the other for 100000 times. So according to the change buffer concept, the inserted ...
0
votes
1answer
51 views

MySQL 5.5 to 5.7 doesn't work, apparently this is a feature?

Has anyone found a way yet of circumventing MySQL 5.7s insistence that it's Master must have a server_UUID. I currently have multiple instances of MySQL 5.5 over various sites, and want to look at ...
0
votes
1answer
151 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
1answer
535 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
2answers
171 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
2answers
372 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 ...
1
vote
1answer
146 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' ...
0
votes
1answer
252 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 ...
3
votes
1answer
461 views

Implementing a one to one (1:1) relationship between Person and User

Scenario description I have this person table as super parent (or supertype), id firstname lastname email telephone ... ... and user table as a child (or subtype) id person_id (FK) password ...
0
votes
1answer
113 views

MySQL Database Memory Consumption

What is the right formula to calculate memory allocation for MySQL, and how can I check/query MySQL memory consumption or we tell where does memory consumed by MySQL is going. MySQL 5.5 in REHL 6.3 ...
0
votes
1answer
212 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 ...
4
votes
1answer
19 views

Combine individual sensor values into an overall value with correction factors

For a school project, we are trying to calculate (corrected) Ozone values based on a combination of 5 sensors (3x O3, 1x temp, 1x humidity). We are using MySQL and PHP for the rest of the project. ...
0
votes
1answer
176 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 ...
3
votes
1answer
20 views

mysql replication and GRANT that doesn't quite grant

I am trying to set up mysql replication. All's well, but I see an error 1045 for the slave: mysql> show slave status\G *************************** 1. row *************************** ...
4
votes
3answers
739 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 ...
0
votes
0answers
22 views

MySQL: excessive memory usage

In our production system, MySQL service uses above 95 % of the available memory. System Info: OS: CentOS 6 RAM: 32 GB CPU: 4 core MySQL version: MySQL 5.5 Database Size: 158 GB my.cnf info: # ...
0
votes
2answers
837 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
0answers
14 views

MySQL: physical table vs temporary table

This question is part of this question MySQL: excessive memory usage I am in process of debugging MySQL high memory usage, I need to know the difference and impact of the physical tables and ...
1
vote
1answer
17 views

Is there a way to set bind_address from command line in mysql 5.5 without restarting?

I made a modification to bind_address in my.cnf that knocked down a monitor. It has been repaired since, firewall allowing the monitor IP in. However, mysql is still running in its previous state ...
4
votes
1answer
250 views

SQL query to compute longest travel time for each id number

I am a novice in SQL and I have a problem as follows. There are two tables TRNSCTN_TAB: CREATE TABLE TRNSCTN_TAB( `transactionId` int , `UniqueId` int , `transactionTimeStamp` varchar(...
0
votes
0answers
26 views

Query Across Multiple MySQL Databases

The majority of our clients use the same MySQL schema and a number (subject to the usual conditions) are willing to give us a copy of their data for research. I have written a utility which merges the ...
0
votes
1answer
543 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 ...
1
vote
1answer
477 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
46 views

MySQL Replication Duplicate Entry for Primary Key

160523 13:07:29 [ERROR] Slave SQL: Error 'Duplicate entry '914166' for key 'PRIMARY'' on query. Default database: 'zo_dev_20121216'. Query: 'UPDATE activity SET activity_type_id = 9, ...
0
votes
0answers
18 views

Check MySQL database growth

I need to check the growth rate for my database (MySQL 5.5). So far I found only a query that answers me the current size - despite the title of the article - (How to check growth of database in ...