MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
27 views

select where index + other field

Learning indexing I have made an index, consisting of usergroupid and birthdate I understand that queries will then benefit from indexing when searching usergroupid and/or birthdate But what ...
0
votes
0answers
53 views

Combine two mySQL sum queries into one which use different where clauses

I have two sum quires referencing different tables in mySQL. How can I combine the two in order to calculate the difference between the two values for each user in the database? Query 1: SELECT ...
1
vote
1answer
18 views

Multi Replications Plan on 2 servers

I need some advice about our new database deployment plan. We have 6GB of total mysql data(all innoDB) and innoDB_buffer_pool is set to be 16GB Ans we have 2 database servers.(plus 1 server for the ...
0
votes
1answer
81 views

Splitting Columns

I want to ask a complicated question and I think its advanced a little. I have a table has this design, its an attendance system. I need to split the time if the attendance count per day was not 2: ...
0
votes
0answers
36 views

Nesting multiple INNER JOIN within LEFT OUTER JOIN in sql on deifferent side of keyword

I am having some problems with one of the question's answered in the book "SQL FOR MERE MORTALS". Here is the problem statement Problem Statement Here is the Database Structure The Database ...
0
votes
1answer
57 views

Does having a big table that I do not query cause the whole DB to be slower?

I have a log table that I use for logging files sent to clients. I'm not querying that table, just use it for logging (INSERT) and when I need to check when something was sent to clients, usually once ...
2
votes
3answers
321 views

Amazon RDS - and binary log rotation

New to RDS (used to having my own server in my own data center). We are on MySQL 5.6 on RDS. when a slave gets behind in replication lag, the master ceases to rotate out its binary logs. It is, ...
2
votes
2answers
1k views

How do I add a MySQL linked server to SQL Server?

I am trying to add a linked server on SQL Server to MySQL Server. Note the SQL Server is installed on a separate machine with a different domain name. The SQL Server is 10.0.1.1 on old_domain.com. ...
0
votes
0answers
20 views

Correct Schema Design for form builder? [closed]

I'm attempting to build a form builder app that allows users to: signup build a form store the data from that form into this db. I have designed the following schema: ...
1
vote
1answer
97 views

pt-table-checksum and row-based replication

The set up I work with is MySQL 5.5 in a master-slave replication configuration, but we have to use "MIXED" replication (statement and row-based) because we use an application that requires it, and it ...
0
votes
1answer
203 views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
-1
votes
1answer
36 views

mysql database performance issue [closed]

I have a 2x Xeon Quad Core E5410 2.33 GHz with 24 GB of RAM. running 5.5.31 with the following my.cnf file. It supports lots of reads and writes and transactions(all INNODB). Are there any obvious ...
0
votes
1answer
50 views

Is this statement syntactically correct? [closed]

$q = "SELECT var1, var2, var3 FROM tablename WHERE var_a = '$var_a' AND var_b = '$var_b' AND ( ( var1 = '$var1' AND var2 = '$var2' ) OR ( var1 = '$var2' AND var2 = '$var1' ) )"; This ...
1
vote
1answer
46 views

Finding reason behind high write IOPS on MySQL (RDS) server

I am having performance issues with an AWS RDS instance (running MySQL 5.6). This is the backend db to an XMPP server called Tigase. According to Tigase Inc. this type of db instance should be able ...
1
vote
1answer
31 views

for what My mysql server get mr than 2 gig RAM on my vps?

imnot know english very well so sory for my bad dictation on my vps that has 4 gigabyte RAM and 4 core CPU and 220 gig HDD . i haved installed centos basic server 6.5 x64 and virtualmin last version ...
0
votes
1answer
61 views

Mysql can't write to filesystem

I need to output some queries as csv files. The directory is in the webroot and I want it to be writable by apache too, so I'm trying to add mysql to the www-data group (on Ubuntu). I thought this ...
1
vote
1answer
263 views

MySQL users corrupt

I have a strange situation here: From time to time I cannot log in with any of my mysql users. I even cannot make a mysql dump. So I started searching in mysql files and I found that users.MYD and ...
0
votes
1answer
429 views

MySQL failover - Master to Master Replication

My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered ...
0
votes
1answer
37 views

Can a slave with a dynamic ip update the master

How can I setup mySQL replication in such a way that a slave on localhost with dynamic IP can update the master as well as get updates from the master which has a static IP. The slave will not be ...
0
votes
1answer
22 views

Synchronize part of the tables to use offline (no internet use) then sync back to server [closed]

I read some synchronization post but seems not suit my case. As I will sometimes go travelling locally and other country which I may have no access to internet then I could not even get the data in my ...
0
votes
2answers
22 views

MySQL get values from the other table by using inner joins

In mysql I have 3 tables called as ps_product_lang ps_order_detail and ps_image here are the tables sample data === ps_product_lang === CREATE TABLE IF NOT EXISTS `ps_product_lang` ( ...
0
votes
0answers
18 views

How to change the domain name or IP address in mysql database in every tables

I was using Openfire chat server in Ubuntu, whose IP address was 192.168.1.15. Then, I wanted to move it to other ip (192.168.1.55), so i took a backup of my database. The database I was using is ...
0
votes
2answers
67 views

MySQL - prevent OS cache for query

On high load production server I have many sql selects which are unique or re-use will be in hours. I use SQL_NO_CACHE but this queries are still cached with OS cache. Server have 64G ram and used ram ...
1
vote
1answer
114 views

mysql replication missed quite a lot SQL statement

I have set up a master/slave with mysql-5.1.73 The master's binlog format is "statement". The master and slave seemed running very well with slave status: Slave_IO_Running: Yes ...
0
votes
1answer
1k views

What is the actual difference between innodb_autoinc_lock_mode 0,1,2

I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given. I see in "0" option, it locks the entire table and does the ...
0
votes
2answers
903 views

InnoDB Tablespace critical error in great need of a fix

Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG (The value reported is outside the allowed range - Byte free, free, gauge, warn, critic) Question: ...
0
votes
1answer
30 views

How to retrieve stored procedures from raw data

I have all the raw data from a database that I can't access through the mysql program anymore. I was able to get the tables by copying the .frm files over to the new installation of mysql, but I don't ...
0
votes
1answer
39 views

MyISAM recover updated row

The row (with type MEDIUMTEXT) has been accidentally updated with empty value. I can see previous data in MYD file of a table, but I can't get them neither from mysqldump or any mysql client. If I ...
3
votes
4answers
195 views

Best practice for a Table with private columns

I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ...
0
votes
0answers
32 views

Moving datadir OK start, but socket error for mysql client

I'm trying to move my MySQL datadir from /var/lib/mysql over to /home/mysql (an ssd drive as I'm having iowait issues on /var/lib/mysql when MySQL is running on that drive). The OS is CentOS 5.6 ...
0
votes
1answer
33 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
0
votes
1answer
184 views

Change MySQL database name in SQL file

I have a 2GB mysql backup file that has several databases in it and would like to change the name for one of the databases. What approaches can I use to tackle this issue ? I have used sed but it ...
0
votes
1answer
41 views

Can't find source code for building mysql

I'm trying to follow the instructions for building MySQL from source. They guide me to a link where I can download the latest GA. I end up at the downloads page, where I can download ...
2
votes
3answers
133 views

MySQL Explain has different row count than slow query log

I have this entry in slow query log: # User@Host: user[host] @ [ip] # Thread_id: 1514428 Schema: db Last_errno: 0 Killed: 0 # Query_time: 2.795454 Lock_time: 0.000116 Rows_sent: 15 ...
3
votes
1answer
495 views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: ...
2
votes
3answers
193 views

MySQL buffer pool usage + high load

We've got a quite powerful server: HP ProLiant DL360p Gen8, 2xIntel Xeon E5-2630 2.30GHz, 128GB of RAM. The whole data set should fit in memory: mysql> SELECT ...
1
vote
2answers
835 views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
2
votes
1answer
1k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
0
votes
0answers
11 views

Logging all user actions

I'm creating a community-driven project and, of course, I want to be protected against spam or evil users. In case of undesirable changes in the DB I would log all users' actions ...
0
votes
1answer
71 views

How to use on delete cascade and on update cascade

I want to ask how to use the various reference options for on delete cascade along with on update cascade. My delete cascade is working. That is child table records are getting deleted on deleting ...
0
votes
1answer
142 views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
0
votes
0answers
28 views

Result of query not showing in phpmyadmin

I have a 500,000 row database that I am running a reasonably complex query on: SELECT yfog, (SELECT AVG(yds) FROM pbp_yards_gained p2 WHERE p2.yfog=p1.yfog AND type = ...
0
votes
2answers
135 views

MYSQL Master-Slave replication not working with PHP

My data gets replicated from the master database when I do it manually but when it gets inserted from PHP into the master database it doesn't get replicated to the slave database. My Master database ...
0
votes
1answer
458 views

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Here is my scenario Master1 hosting DB1 Master2 hosting DB2 ... MasterN hosting DBN replicate to: Slave1 hosting DB1,DB2... DBN I've read similar questions and they recommend to start different ...
0
votes
1answer
33 views

How to compare all rows in a table and extract data using triggers

I have two table one table stores the gps data of various users another table stores the idle time of the users based upon the gps data of latitude and longitude values and timestamp difference. ...
1
vote
2answers
132 views

Optimizing large MySQL SELECT WHERE IN clauses

How can the following SELECT WHERE IN case be optimized? I have a table with over 100 million rows with only 3 columns. The primary key (col1) is a 127 varchar. I am performing a SELECT col1 WHERE ...
0
votes
1answer
91 views

Mysql - Select To XML

I'm Building Audit Trail with Trigger, and want to convert the inserted / updated / deleted value into XML and store it into another table. my trigger look like this: DELIMITER $$ CREATE ...
0
votes
0answers
14 views

Pasting into PHPMyAdmin

This is probably really simple but I have had a look on the web but can't seem to find an answer. I am experimenting with MySQL + PHPMyAdmin on a WAMPserver stack and was saving queries in Excel When ...
0
votes
3answers
40 views

Select rows where 2 cells not the same

I have the following table: +----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | ...
3
votes
2answers
225 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...