MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
18 views

Does a whole-server backup guarantee a reliable backup of running databases?

Many hosts offer a full-server backup once per day. However, I've been told that a backup of this kind doesn't guarantee a reliable backup of running databases, because even if the database uses ...
0
votes
2answers
26 views

Relational Model vs. Database Products

The formal Relational Model (logical level) for databases is not exactly the same thing as you get with MySQL, Oracle or other major products / implementations of ‘relational’ database management ...
1
vote
0answers
45 views
+50

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
0
votes
3answers
17 views

What's the proper design for the table that will hold the processor load stats?

Let's say that I need to implement a widget on the site that allows the user to view the processor load stats via a chart. The user will be allowed to choose how detailed the statistics are: with ...
0
votes
0answers
7 views

MySQL hook and alter incoming INSERT queries?

We have an application running on a server which parses an xml data feed into SQL queries and executes them continually 24/7. There is a serious issue in the design of the queries and we need to ...
0
votes
0answers
34 views

Why isn't this index helping my InnoDB MySQL query?

I'm having trouble understanding why an index I have on one of my tables is doing nothing to help the performance of a JOIN, even when forcing the index. Here is the query: EXPLAIN SELECT * FROM ...
1
vote
2answers
21 views

Unable to SQL => Getting content of last 7 days

I thought I was good with SQL till I was told to get the content of all 30 days. I do have a column called "connect_time" which has date and time in the following format :2014-08-28 11:11:42 All my ...
0
votes
0answers
8 views

Testing data integrity porting from old database to new

I've created a Web/ASP.NET/MySQL database front end & back end that gets created from the XML logs from a previous Java based XML client-server that is not serving the company very well (the XML ...
0
votes
1answer
450 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
149 views

innodb completely crashed after interruption

I am having a major crash on mysql affecting a lot of websites. We had a crash on our SAN storage. Since that time, mysql refuse to start. I tried to reinstall mysql, but still no luck. I think ...
0
votes
1answer
191 views

Error Code: 1133. Can't find any matching row in the user table

Im reading O'Relly "Learning MySQL" and it says that you can create a user in MySQL by doing just this: mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY 'the_password'; Query OK, 0 ...
0
votes
2answers
57 views

Which files in the “MySQL-5.0.27-win32” folder do i need to backup?

Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7). Here is the current structure of of mysql-5.0.27-win32: In ...
0
votes
1answer
15 views

Appropriate use of mysql Optimize Table

I want to come up with some best practice for maintaining our mysql database. versions 5.5/6 and using InnoDB. i came across this artice - ...
2
votes
3answers
470 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
2
votes
1answer
193 views

Bi-directional replication for the same MySQL table

AppA stores/retrieves data from dbA.tableA AppB stores/retrieves data from dbB.tableA tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA ...
0
votes
0answers
10 views

store code/script value as text in database

I have the following situation: I have to work with software, developed in Java, that allows the user to input some custom scripts in order to do some calculations. so i have to store/save the ...
0
votes
1answer
27 views

What drawbacks are there in scheduling an event to run every too often,say after every 60 seconds?

I have this event i want to run every 60 seconds but i wonder if this will take up many resources in my database. CREATE EVENT myevent ON SCHEDULE EVERY 60 SECOND DO UPDATE ...
0
votes
0answers
25 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
2
votes
0answers
16 views

Translate SQL Server update statement to MySQL

I have a development environment with MySQL and a production environment with SQL Server. This update statement works with SQL Server: UPDATE THIS SET <list_of_column_assignments> FROM ...
1
vote
1answer
71 views

MySQL (or MariaDB) multi-master without MMM

I've been reading blogs on MySQL multi-master setup. The way I've used MySQL replication in the past was master-slave where if the master died (or was being taken down for maintenance) we would shut ...
0
votes
1answer
19 views

database search index for dating site [on hold]

I have a MySQL innodb table used for searching but I am not sure what columns do I put a index on, I was going to add a index to every column but recently learned that would do a full table scan and I ...
1
vote
2answers
13 views

mysql information schema database last used datetime

I have a lot of tables in my database, almost 1,000. Most of them I am not using now. Is there any way to know from the information schema tables when I have last used my tables?
1
vote
1answer
12 views

Mysql Server version: 5.0.77-log Source distribution

I have a master(M)-slave(S) replication on MySQL. During a failover test, the master was brought down and the slave was made the master by doing STOP SLAVE; RESET MASTER;. Since there are only 2 ...
0
votes
1answer
159 views

Report Builder cannot publish to report server

This is currently in a cloud lab environment. We're not allowed to join any AD domains. We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2. Our laptop is ...
1
vote
2answers
986 views

Master - Master replication Duplicate entry

I've got a Master - Master replication setup and it worked fine for a couple of days but I keep getting: Duplicate entry errors after a few days... My setup is as follow: One server in The ...
-1
votes
1answer
44 views

mysql xtrabackup missing binary logs

It seems xtrabackup is not backing up binary logs, although it creates xtrabackup_binlog* with apparently correct information inside. Should the binary logs be copied manually beforehand or is this a ...
3
votes
2answers
266 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 ...
1
vote
2answers
121 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
1
vote
0answers
157 views

Missing rows in MyISAM tables with 10-20 billion records

I've got 2 tables with 10-20 billion records each. Currently these are in MyISAM (MySQL). They only require to be built and indexed once, after this only SELECT is used. They are currently ...
1
vote
1answer
20 views

how to create database with charset utf-8

I'm new in Mysql, how to create a db with charset utf-8 like i did in navicat? create mydatabase; seems using some kind of default charset.
0
votes
1answer
72 views

mysql dump a table to remote host

i want to dump a table from localhost host (with public ip), here is the command so far, the command will be run on localhost mysqldump --opt --host localhost --user=username --password=password ...
0
votes
0answers
24 views

Cant Normalize my dimensions and fact table, need help

I have a task at school that I can't get my head around. The task is to create some dimetables and create a facttable from those. The data I have chosen is from Export and Import cost for each year ...
1
vote
1answer
181 views

How to repair all indexes in a MySQL database?

My database got corrupted, and now the php app is throwing this error: Invalid query: Can't find FULLTEXT index matching the column list The error is not very useful, since it doesn't say what ...
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 ...
-3
votes
0answers
12 views

mysql join side by side problem [on hold]

my problem is that, i have 2 tables and i want one query and fast but, i did not this query. 1st. table detail name matches column names is matches_id, match_code, match_date, 2nd.table detail name ...
0
votes
1answer
51 views

Querying MySQL, duration increases 10x fold after adding one more Join

I have a somewhat complicated query consisting of many joins on an issue tracker/project management database in MySQL. My issue is that my query was doing OK while I was in the process of building it ...
1
vote
2answers
27 views

what is the difference between mysql community server and mysql cluster

I am starting to look into different database's for different use cases. I see there is a mysql community server and mysql cluster. What is the difference between the two? Are they both database ...
0
votes
1answer
21 views

Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries?

I am running MySQL 5.5.37. I have a cronjob set to run every 6 hours to flush the query cache. I realized that if I don't do that, then the cache becomes fragmented and average query response time ...
0
votes
0answers
24 views

mysql connection requires socket flag

I'm running mysql 5.6.19 on centos 6.4. I have my /etc/my.cnf file set as the following: [client] port = 3306 socket = /data/mysql_data/mysql.sock [mysqld] bind-address = 0.0.0.0 report-host = ...
1
vote
0answers
12 views

Copying to tmp table slow for query … but only sometimes

I have a query, and I admit it may not be the prettiest query in the world. Sometimes this query runs fairly fast (e.g. 200ms) and other times it runs super slow (e.g. 30s). I don't know why, and ...
1
vote
1answer
58 views

How to improve this product rental schema?

I have a rental system where each product's stock item can be rented from a location and returned to a different location (think Alamo, Hertz etc.). When saving an order, first an inventory event is ...
1
vote
1answer
285 views

optimizing MySQL for traffic analytics system

background : I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as ...
1
vote
1answer
101 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
7 views

General error: 1615 Prepared statement needs to be re-prepared

I have been running into this issue every time I try and sync a medium size JSON object to my database so we can perform some reporting on it. From looking into what can cause it I have come across ...
0
votes
1answer
29 views

Adding two rows in a database together based on columns [on hold]

Let's say I have rows in my database like so: A|B|Num1|Num 2 21|21857|51|1 21|21857|15|1 16173|21312|48|1 How would I write a query that would aggregate the rows that have the same A and B, and ...
1
vote
1answer
145 views

Innodb buffer pool indexes versus data usage

The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB. mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+--------------+ | Variable_name ...
1
vote
1answer
877 views

Amazon RDS - Can't access MySQL DB from local machine

I read many questions/answers here but they are from a different interface from AWS Console. I created a DB Instance but can't create a rule in the security groups that's allows me to connect from my ...
0
votes
1answer
108 views

phpmyadmin not working after mysql update

i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal. But now there is another ...
0
votes
1answer
18 views

MySQL InnoDB log files on different location

I am using MySQL version: 5.5.38 There are following parameters that can control InnoDB log files: innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
0
votes
1answer
15 views

Slow server with many access [on hold]

I have a problem of slowness in a server with few tables and many changes and many data I changed the MySQL by MariaDB My server is in the digital ocean with 8 processors and 16GB of RAM, but are the ...