MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
4 views

Correct Database structure , variable names, types

Here is my database ERR diagram http://imgur.com/M0E6Syn I'm new to this and I'm creating a database for the following: Question and answer website The user will interact with only one question at ...
0
votes
1answer
9 views

Accuracy of mysqldbcompare vs other tools like pt-table-checksum

I was curious to hear what everyone's experiences are with using mysqldbcompare vs pt-table-checksum( from Percona Toolkit ) to find the differences between two MySQL databases. I have heard that ...
0
votes
0answers
6 views

mysqldbcompare shows checksum fail but row differences passes

I have started to try using mysqldbcompare to help me compare two databases that are each about 5 gigs with lots of tables. When the tool runs, I am noticing many results like this : # TABLE ...
0
votes
1answer
18 views

Large MySQL table, inserts with primary key check

I have a table with 12 million of rows and a following task I should frequently perform: Get search results from somewhere (50 rows). Each result has a key looking as a md5 hash and a table's PM is ...
0
votes
2answers
11 views

Returning true or false in a custom function

I have this mysql table schema CREATE TABLE `nxt_records` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `tel_number` VARCHAR(50) NULL DEFAULT NULL, `nxt_status` VARCHAR(50) NULL DEFAULT NULL, ...
0
votes
2answers
28 views

One field with multiple values?

I know that what I'm asking can't be done or shouldn't be done, but I really don't know how to do it. This is the problem: 2 tables: - Drivers (id_driver, name, etc...) - Trucks (id_truck, model, ...
0
votes
1answer
14 views

Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command

We need to optimize a large table (on a weekly basis) in order to reclaim disk space, since records are being deleted regularly from the table. The table in question is an InnoDB table and the ...
0
votes
0answers
6 views

MySQLfabric — Manage setup problem

MySQL fabric does not get configured , getting these errors : [mysql@dev-dm-gitest101z /root]# mysqlfabric manage setup Error : [INFO] 1413953890.788408 - MainThread - Initializing persister: user ...
0
votes
2answers
23 views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
0
votes
1answer
17 views

How to get all the months from the last paid date in MySql?

I need to get the all the months from the last_payment_Date for the user from that table for processing purposing. As of now i am using this query.. SELECT `Name`, `Acc_No` FROM `user_table_details` ...
1
vote
1answer
16 views

mariadb - EXPLAIN works but ANALYZE throws ERROR 1064 (42000) error in SQL syntax

I want to see the execution plan of a query to find out why it is slow, but ANALYZE does not seem to work, it throws an error on the same query that EXPLAIN accepts. MariaDB [dbx]> explain select ...
1
vote
1answer
25 views

Query where there are only two rows with specific data

I have a table with room which has a data of who are in it. Each room has have 0 to any number of people and each person can be in multiple rooms simultaneously. (simplified below) id name 1 ...
1
vote
1answer
18 views

mysql - sum interval dates

I would like to sum the hours for each name, giving a total interval between START and END activities. It would be simple if I could subtract from each record the end of the beginning. For example, ...
0
votes
0answers
10 views

How to model efficient permission checks for a social network?

The goal is to build a facebook like permission system. So that users can grant other users access on objects based on a direct assignment (allow user X to see posts) or allow users to access them ...
0
votes
0answers
26 views

“DISTINCT” JOIN in mySQL

+----+-------------+-----------+---------+ | id | login_ip | hostname | user_id | +----+-------------+-----------+---------+ | 1 | 192.168.0.4 | 'host 1' | 1 | | 2 | 192.168.0.4 | 'host ...
0
votes
0answers
25 views

MySQL Upgrade from 5.5 to 5.6 causes system / table locks

We recently upgraded from MySQL 5.5 to 5.6 on AWS RDS. Before, we had a relatively performant database - definitely had its quirks, but it seemed to be tuned relatively well. After the upgrade, we're ...
1
vote
2answers
42 views

Upgrading hardware for MySQL. If we get SSDs, are CPUs likely to become a bottleneck

We are upgrading hardware for a MySQL database server (DB is around 200GB). We're getting 4x Samsung 850 Pro SSDs and are planning to run them in a RAID10 configuration. Also, 128GB of RAM. The ...
0
votes
1answer
49 views

Retrieving month from date field in my database

i have dates stored in database with the function : CURDATE() , when a user makes an order . Now with a form where the admin chooses the month and the year , i want a query to display all the orders ...
1
vote
1answer
32 views

Trying to combine one row from one table with mulitple rows from another table but not sure if I'm doing it right?

The Situation I have two tables unit and unit_translations defined as: CREATE TABLE `unit` ( `id` int(11) NOT NULL auto_increment, `title` varchar(255) NOT NULL COMMENT 'Title of unit', ...
1
vote
1answer
37 views

Can't start MySQL server because of wrong file permissions?

I'm trying to start the server and then fix the root user by $ mysql -u root mysql $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root'; $mysql> FLUSH PRIVILEGES; But ...
1
vote
1answer
20 views

Converting 'False'(string) to False(boolean) During LOAD DATA LOCAL INFILE

In MySQL is there anyway of loading "False" into a boolean column. While loading data in table having boolean column and where data is in "True" or "False", it gives warning of: "Incorrect integer ...
0
votes
0answers
13 views

Best way to approach building search functionality using PHP on existing mySQL Database

I am looking for advice on how to approach an upcoming project I am due to undertake. The reason I am asking is that I'm just out of college and the company I work for is currently searching for a new ...
3
votes
1answer
68 views

When is mariadb creating tmp tables?

I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 ...
0
votes
2answers
37 views

How to allow remote connections to mysql from specific IPs only?

I have a fresh install of mysql server 5.2.6 on a 64 bit Ubuntu running on a remote server with static IP. Is there anyway to allow Mysql remote connections to be made from pre-approved IPs only? I ...
0
votes
0answers
30 views

MYSQL Out of memory, table crashing

an specific table on my DB keeps crashing. I suspect to be a bad buffer setting on my.conf, it show this error before crashing (most of the time it needs to be repaired): 18:26:59 [ERROR] ...
1
vote
3answers
53 views

Deleting data from a large table with replication

I need to delete all the rows from a given table. The table contains millions or records. The master database is being replicated to several slaves and I wish to do that without creating a replication ...
0
votes
1answer
17 views

Using pt-table-checksum to compare a slave's DB to another slave

I understand that pt-table-checksum can do checksum compares between a master's DB and a slave's DB easily. However, let's say your master has two slaves replicating from it for the same DB, i.e. ...
0
votes
0answers
16 views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
0
votes
0answers
15 views

How to store highly configurable relationships based on multiple conditions

I'm trying to devise a schema that needs to be extremely configurable when necessary, but also minimal wherever possible. There is a large number of condition types that would lead to a number of ...
0
votes
1answer
15 views

Importing a SQL Dump File through XAMPP's Command Line

A few days ago, I exported a .SQL dump file with all my databases, which is about 150MB. The thing is I cannot import it through phpMyAdmin since the file is way to big. I remembered there was ...
0
votes
1answer
22 views

increasing disk space for mysql data volume

I have a master mysql server which has approached 80% of the available disk space. Total disk space size is 200GB. Though, it's not an immediate concern but I have to deal with at some point soon. the ...
0
votes
1answer
18 views

How to deny access to any system user who attempts to log in without password usage?

I have mysql-server-5.5 in Ubuntu Server 12.04 I realized that when typing mysql command logged as any system user I got access to mysql, despite I can only display test and information_schema ...
0
votes
0answers
15 views

MySQL on Server side PRIMARY KEY taking automatically random Id

I have problem in my database which i have uploaded in Database Version libmysql 5.1.73 In that i got problem like one PRIMARY_KEY field incrementing random values. inserted id's like 1, 2, 35, 67, ...
0
votes
2answers
38 views

HA - MySQL Master Master replication - Duplicate entry errors

I am running Percona MySQL 5.5.39 on two masters with keepalived monitoring a VIP which will failover to the standby master if the primary master goes dark. While testing the fault tolerance of my ...
0
votes
0answers
19 views

Mysterious LOAD DATA LOCAL INFILE

This is a piece of log file for loading ~19m rows via CSV into a mysql myisam partitioned table already containing ~90m rows: 01:46:17 Keys for two_legs disabled. 03:45:46 Data copied from ...
0
votes
1answer
25 views

In which situations is TokuDB higher performance? [on hold]

I noticed that WordPress runs quite a bit faster when I use XtraDB (MariaDB's InnoDB replacement) instead of TokuDB. This is strange, because TokuDB is advertised as being 20x faster. However, I ...
0
votes
0answers
12 views

MySQL: #126 - Incorrect key file for table '/tmp/#sql_9dc_1.MYI'; try to repair it

I've been reading possible solutions to this problem but until this time, those solutions don't work with my case. Activity: In phpMyAdmin, the following error pops up while opening a View. Error: ...
0
votes
1answer
22 views

InnoDB error: exceeded log capacity…but only during import

I see this error in mysql.log: InnoDb error: the age of last checkpoint is...which exceeds the log group capacity etc.. I think I understand it, but the fact is that it's happening only during a ...
1
vote
1answer
41 views

How group by affects aggregate functions

I tried to understand the following scenarios: Table name: family +------+-------+ | id | name | +------+-------+ | 1 | alice | | 2 | bob | +------+-------+ That table will be used for ...
-2
votes
2answers
30 views

Explicit transactions for each DML query are necessary or not? [on hold]

My question is simple: Which mechanism should I use in databases to ensure that if device shuts down in middle, the process is unified. transactions?
0
votes
1answer
19 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
1
vote
1answer
33 views

Large Data Long Query A Lot of Joins horibble performance

I have 3 Tables Person, Family, City Person Table (6 Million Rows) id (primary key auto_increment unsigned) family_id (index) city_id (index) full_name first_name (index) last_name (index) ...
0
votes
1answer
13 views

How to limit schema size by a trigger

The problem: I am running on a Webhotel a MySql database with different schemas with a maximum size limit on each schema. I can't change any settings on the DBMS. To be able to keep the schema below ...
1
vote
1answer
30 views

MySQL repair deleted data - Any chance of recovery?

Well, this has been a long Saturday morning. MySQL was throwing an error when dumping backups. That error was... mysqldump: Error 1030: Got error 5 from storage engine when dumping table mytable at ...
1
vote
0answers
38 views

innodb slower much slower than expected

Trying to figure this one out and I apologize for not providing a lot of information from the start (if you need further let me know and I can try to provide it). Query is performing very poorly - it ...
0
votes
1answer
12 views

Insert GEOMFROMTEXT (lat lng)

I am stuck, Please help. "INSERT INTO `routeplans` ( `polygon` ) VALUES ( GEOMFROMTEXT( 'POLYGON( (39.593254951279626 -119.2415714263916, 39.596231192849004 -119.2441463470459, ...
0
votes
1answer
22 views

Stacked Left Joins or Left Join inside a Subquery?

OK, this is a fun one. Designing a database for a gaming system. I have 3 tables that are relevant for the query I'm trying to work on right now. character_skill_log with the relevant fields of id, ...
-2
votes
0answers
21 views

What goes well to link with java? MySQL or Oracle XE? [closed]

I will be using Eclipse to build a web application. I am trying to decide on my backend database. I am familiar with Oracle DB but I have questions Will there be any issue in plugging in with ...
1
vote
1answer
61 views

Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21. I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
1
vote
1answer
17 views

Importance of uniformity of hardware for Galera cluster

How important is it for the nodes in a Galera cluster to be identical? I've found this answer which says they should be the same: http://dba.stackexchange.com/a/55224/770 I understand that you ...