MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
328 views

MySQL table relations, inheritance or not?

Im building a micro CMS. Using Mysql as RDMS, and Doctrine ORM for mapping. I would like to have two types of pages. Static Page, and Blog Page. Static page would have page_url, and page_content ...
2
votes
1answer
239 views

How to normalize my db schema for a quiz application?

I am putting together a quiz application. (I'm calling each quiz a "challenge") I have a "challenge" table and a "challenge_question" table which is pretty straightforward. challenge table: id ...
2
votes
3answers
544 views

Mysql datatype for username of maximum 15 characters

I currently have a database with the "user" table having username as one of the columns. I allow a maximum of 15 characters for the username in my system... Is it fine to have the username column as ...
0
votes
1answer
218 views

MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Can someone tell me how to prevent the following commands DROP TABLE <tbl-name>; TRUNCATE TABLE <tbl-name>; from being replicated to slaves?
2
votes
1answer
134 views

What could cause waiting for table metadata lock?

I am using a MySQL 5.6 DB with MyISAM tables under FreeBSD 8.3. The main part of code is in PHP, but the problem arises, when I launch the self-written on C++ chat-server. I use the mysql.h for DB ...
0
votes
1answer
39 views

Mysql Select vs Select SUM performance

I need to check whether the surfer has alrady voted this can be done using Method 1 Select record_num FROM table where etc etc. i.e. SELECT record_num FROM content_votes_tmp WHERE up = 1 AND ip ...
2
votes
0answers
28 views

longitude lattitude performance

I have to store frequently (at least every min) the position (longitude, latitude) of animals in a DB, and perform for each update a search in order to determinate if ananother animal is near to it. ...
1
vote
1answer
310 views

creating a report on the mysql jasperReport server

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf ...
0
votes
1answer
83 views

MySQL select with left join query

I have following code. select class.name as classname, class.hours as hours, teachers.first_name as teacherFirstName, teachers.last_name as teacherLastName from teachers,class left join ...
0
votes
0answers
21 views

MySQL database autoscaling process

Is there a tool or a method for MySQL autoscaling? If the load increases then more slaves are added and if the load decreases then the number of slaves is reduced.
0
votes
1answer
237 views

MySQL relay log rotates too soon and too often

I have a simple master/slave setup where the slave is just a backup. Sometimes I notice that the relay log on the slave rotates too often and too soon, when it is still very small: 4:00 PM ...
0
votes
1answer
115 views

Consolidated realtime mysql backup with master/slave setup or binlog

I have 20+ mysql servers and their have low write requirements. So, I want to see if any way I can replicate the data/binlog from these servers to a single slave so realtime backup purpose. If I ...
0
votes
1answer
61 views

MySQL - When Command column in Processlist shows “Prepare” or “execute”

Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist" Any example which shows this would be helpful.
0
votes
1answer
377 views

The total number of locks exceeds the lock table size, even after increasing buffer pool size

I am trying to partition a table with 200 million records in it. This table already exists: CREATE TABLE linkmap_ntoken ( target_nToken varchar(200) NOT NULL, ntoken_hashcode int(15) NOT NULL, ...
1
vote
0answers
13 views

Conversion to Barracuda/Compressed Rows Doesn't Free Disk Space

I'm currently converting large tables to the Barracuda file format. We are using file_per_table. I had one table that decreased from 4.3gb in size down to 1.8gb and we're very happy about this. ...
0
votes
1answer
65 views

mysql Replication and Point in time Recovery

I have a master and a slave mysql database (version 5.1). Is it possible to dump the database only on the slave side? If I do a dump with mysqldump I have a fulldump. Which files can I use for a point ...
0
votes
1answer
233 views

How to configure my.cnf for Mysql 5.1, which is using too much CPU?

I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system ...
0
votes
2answers
3k views

How to sync two databases (alternative to mysql replication)?

Hello i have 2 mysql databases where most of the structure is constant except few tables (additions) in 2 dbs. I need to synchronize these 2 databases mainly common tables from one db to another. This ...
0
votes
1answer
29 views

Database structure for bonus system in MySQL

I have a problem with creating the structure for my database in MySQL as I'm new in creating databases. The problem is in this: there are three tables sellers, clients and bonus. Table sellers stores ...
-1
votes
1answer
46 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
0
votes
1answer
91 views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
0
votes
1answer
180 views

How to organize the tables of a MySQL database with several statistics data of several years?

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the ...
0
votes
0answers
12 views

Control Query Time

I have a table for comments and I want users to wait 5 minutes before sending other comments. I have five columns: id, id_user_to, id_user_from, message and ,date (this is a table for comments). I ...
0
votes
1answer
107 views

Get child record from specific root

I have two tables articles and topics and I am using MySQL 5.6 Articles id topic(fk on Topics.id) featured 1 1 1 2 2 0 3 3 ...
0
votes
0answers
23 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
1
vote
1answer
53 views

MySQL - varchar length and performance

Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?
0
votes
1answer
2k views

MySQL count multiple columns and sum the total occurrence

I have the following table, say, MyDRAW ... DRAWNO PRIZENO FIRSTD SECONDD THIRDD FOURTHD M123/14 8458 8 4 5 8 M123/14 7214 7 2 1 4 M123/14 1640 1 6 4 0 ...
1
vote
2answers
118 views

How to make a better Data model

I am creating a database system using MySQL for a group in my school (a University). I am new to MySQL and its usage.I have started with the data modelling process, since this process will be the main ...
-2
votes
0answers
17 views

Performance suggestion tool for MySQL in windows? [closed]

Is there any free tools for Windows which make suggestions on how to improve the MySQL database?
0
votes
1answer
418 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...
1
vote
1answer
54 views

Cannot create foreign key on 2 VARCHAR(45) columns in MySQL Workbench 6.1

I'm trying to create a foreign key. The columns in both tables are VARCHAR(45) and neither is a PK, however I keep getting this error message: It is saying the PK of reference table is INT, which ...
1
vote
1answer
18 views

MySQL Junction Table as Foreign Key?

I have several tables: company, customer, company_has_customer, and transaction. company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK. In almost all ...
0
votes
4answers
155 views

Amazon: How to change database name in Amazon RDS MySQL?

I want to change MYSQL database name of the DB instance of Amazon RDS. How it can be possible?
0
votes
1answer
49 views

Quickly etablishing if a remote MySQL connection is valid using PHP

I have a hosted website (Namecheap Pro shared hosting) that connects to a remote MySQL database using a read-only user privilege. This remote DB is updated by a separate system. The website works ...
-1
votes
0answers
16 views

Slow Performance with Multi-threaded app - MySQL InnoDB

My application is a java 1.7, multi-threaded application with MySQL 5.6 as the backend. Application reads data from files and writes it to corresponding tables in multithreaded fashion. The ...
0
votes
1answer
1k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
2
votes
1answer
48 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
0
votes
1answer
31 views

MySQL Entity Relationship Problem - 3-way join table?

This is a db relationship question. I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows: ...
0
votes
1answer
64 views

Using non-replicated read/write databases on a MySQL slave server

I have a MySQL slave server that has two databases being replicated from a master. What I'd like to do is add a third database that I will exclude from replication and use as a read/write database. ...
0
votes
2answers
308 views

Is there a way to enable MariaDB MEMORY storage engine for every table?

I am new to MariaDB and interested in using it because I want to use MEMORY storage engine for every table. I read MEMORY storage engine on mariadb.com and it seems like I had to specify storage ...
0
votes
1answer
133 views

AES 256 data encryption on Mysql

Is there a way to encrypt the whole data of a mysql DB using the AES 256 symmetric encryption method without encrypting the data manually using AES_ENCRYPT ? This article explains about the same ...
1
vote
1answer
19 views

pt-query-digest results are empty

I have a slow log generated by MySQL 5.1.71-log, that I've copied onto another machine. It appears I'm not getting any results from pt-query-digest. $> pt-query-digest slow.log # 2.1s user time, ...
0
votes
1answer
102 views

Duplicate Relationships in Rails/MySQL Healthcare Application

Overview of the Application I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
0
votes
1answer
32 views

Efficient way to DELETE whole partition's data in table partitioned by HASH(DAY(datetime))?

I have a MySQL InnoDB table PARTITIONed by HASH(DAY(dateTime)). I'd like to remove partitions holding data older than 10 days. CREATE TABLE `partitioned` ( `id` bigint(20) unsigned NOT NULL ...
0
votes
1answer
54 views

Percona cluster + Wordpress blogs, low performance

We currently run ~ 900 WordPress blogs on several machines so was decided to move to load balanced environment with clustered db. I followed instructions from ...
0
votes
2answers
24 views

How do I fix #1062 error received while importing EE database to localhost from remote server

I am trying to import a MySQL database into the localhost database that was exported from the remote database and I am receiving a #1062 error (which is weirdly in French!): #1062 - Duplicata du ...
7
votes
8answers
7k views

What tools are there for data masking? (MySQL, Linux)

I'm looking for (ideally free, open-source) data masking tools. Do any such exist? Note: this related question deals with tools for generating test data, but in this question I'm more interested in ...
2
votes
2answers
223 views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
0
votes
2answers
54 views

Is it possible to copy a dev. database schema over a prod. one, but keep the prod. database' data?

I'm using MySQL 5.6. I have a production database and a development database. As I'm developing the application, I sometimes need to add new columns/tables to the database. Is it possible, when I'm ...
0
votes
1answer
27 views

column value is not increment after 10

I tried below 2 codes.... showing same error 1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1 SELECT MAX(columnname ) AS max FROM tablename I am trying to increment value of ...