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

learn more… | top users | synonyms

1
vote
0answers
9 views

MySQL replication just hangs, then Database wont shutdown

I have recently set up some new MySQL Databases on a remote site. They are all 5.5.47, running on Debian Servers. They are set up as one Master with multiple slaves. The Master is itself a slave from ...
0
votes
0answers
7 views

Tungsten Replicator high CPU usage

We are observing CPU fluctuations (sudden increase) in the boxes due to Tungsten Replicator. May I know the cause for high CPU usage?
-3
votes
0answers
14 views

date wise convert row to column with sum of amount in mysql

i want dynamic table for dynamic date wise with sum of amount....plz help me guys.. Thanks in advance
0
votes
1answer
549 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
2answers
38 views

Why does my statement result in a syntax error?

I tried to run this SQL statement in MySQL 5.5 and 5.7.1 but always got error. DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE @batchsize BIGINT DEFAULT 1; ...
0
votes
2answers
850 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 ...
1
vote
1answer
38 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 ...
1
vote
1answer
30 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
731 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
148 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
15 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
28 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
482 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
340 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
400 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
679 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
58 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
120 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
210 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
263 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
178 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
296 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
17 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
285 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
54 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
172 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
373 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
147 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
255 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
462 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
214 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
177 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
0answers
16 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
18 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
251 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 ...