MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

4
votes
1answer
731 views

Designing a database for a site that stores content from multiple services?

I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
3
votes
1answer
62 views

MySQL Available Cars With multiple owners

I have a complex SQL question. We are building a car rental system. We have a car model table, a owner table and a link_model_owner table. In addition our bookings are kept in another table. Scheme ...
3
votes
1answer
205 views

Removing “duplicates” within a time range

I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple ...
3
votes
1answer
498 views

MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3

I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew brew install mysql Everything seemed to go smoothly, but...not really, as it turns out. I cannot add a new user (neither ...
3
votes
1answer
138 views

MySQL - Creating a user and specifying a password

How do I create a user and specify a password in one line: I thought CREATE USER 'foo'@'2.2.2.2' IDENTIFIED BY 'bar'; would do it but apparently it doesn't. Any thoughts? I'm on version 5.1.49 of ...
3
votes
1answer
275 views

Deadlock free MERGE-alike in MySQL?

I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions. So, ...
3
votes
1answer
1k views

Take individual MySQL database offline

MySQL does not have any SQL commands or internal mechanisms for making an individual database unavailable / offline moving an individual database Thtis being the case, how can you take an ...
3
votes
1answer
2k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
3
votes
1answer
491 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
1answer
57 views

Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1?

AFAIK running transactions as READ UNCOMMITTED means that there is no lock, so every change caused by a transaction is immediately visible to other transactions. Is it correct to assume that this is ...
2
votes
1answer
126 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
1answer
57 views

What are possible causes for consistently volatile INSERT performance in MySQL tables?

I have a Java program running on a VM that is sequentially feeding a queue of single row INSERTs through a single connection to a MySQL server on the same VM. The queue execution is not artificially ...
2
votes
1answer
170 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
2
votes
1answer
71 views

Will MySQL be effective when selecting a row from a table with billions of records?

Note: this is a hypothetical scenario Lets say that: I'm running MySQL, InnoDB engine, UTF-8 encoding I have a table with 50+ billions of rows The tables primary key is a BIGINT The table also has ...
2
votes
1answer
138 views

Best Practices for Creating and Deleting Records in MYSQL

I'm fairly new to working with databases, but I've currently have a webCrawler that I've written in C# using MYSQL database. The crawler is frequently writing and deleting records from the database ...
2
votes
1answer
23 views

How to use a table as a reference for another table in MySQL

I have code from generic SQL that I need to find out how to run in MySQL: CREATE TABLE firstTable ( freq int NULL, w1 int NULL, w2 int NULL, w3 int NULL ) CREATE TABLE secondTable ( [wID] [int] ...
2
votes
1answer
213 views

Designing database with Category and Tags for Posts - Seeking for guidance/advice

I'm not really a database novice but I'm not an expert either. Problem: I'm having this difficulty on designing my Post, Category and Tags relationship. I've read Normalization many times but could ...
2
votes
1answer
161 views

Is this amount of disk IO expected with MySQL?

I am running MySQL 5.5.34 on an Amazon EC2 micro instance with EBS (elastic block storage). The instance has been running for around 10 days and according to Amazon I've accumulated around 8.5 ...
2
votes
1answer
2k views

MySQL requires FORCE INDEX on huge table and simple SELECTs

We have an application which stores articles from different sources in a MySQL table and allows users to retrieve those articles ordered by date. Articles are always filtered by source, so for client ...
2
votes
1answer
184 views

SQL table architecture

I'm looking for a SQL architecture solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his ...
2
votes
1answer
131 views

Queryplan changes depending on filter values

I created a clustered index on a table expecting it to make the queries with ranges perform better, but, different values in the where clause can produce differente query plans, one uses the clustered ...
2
votes
1answer
649 views

My MySQL server time is not the same as my Server

So a month ago I had purchased a server from a hosting company. The info are; Linux Centos (latest version). I installed ZPanel which installs phpMyAdmin, MySQL and all the other good stuff. ...
2
votes
1answer
255 views

Alter mysql database engine doesn't work from cluster to other

I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked. It did not throw any ...
2
votes
1answer
922 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 ...
2
votes
1answer
137 views

Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

So, I am sure I have done something really stupid while designing this, and I'm open to schema changes if they'll really help me out. On to the problem: I have a custom shopping cart system (backed ...
2
votes
1answer
171 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 ...
2
votes
1answer
690 views

MySQL - run SELECT statement on another server without defining the table structure

In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally. In MS SQL Server, however, I can run any SQL statement against a ...
2
votes
1answer
160 views

MySQL server connections capped at 1013

For the last few days I've been trying to figure out why my MySQL read slave servers are capped at 1012+1 (+1 for the superuser) connections when I have the max_connections set to 5000. It seems that ...
2
votes
1answer
184 views

innobackupex is failing while exporting backup

I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex. I have following ...
2
votes
1answer
317 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 ...
1
vote
1answer
20 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
21 views

MySQL server performance plummets

Part of our software solution is responsible for processing files that get uploaded to us by our clients. We want to process them as quickly as we can. Our system is architected so that there's a ...
1
vote
1answer
21 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into innodb internals regarding buffer pool and checkpoint, dirtypage. However there isn't enogh information to answer my question. When I run show global status, I can get ...
1
vote
1answer
20 views

add column without metadata lock in mysql 5.6

I am using MySQL Server version: 5.6.15 Homebrew. I need to add new null column. alter table requires no metadata lock. In order to avoid this MySQL 5.6 introduced algorithm=inplace. Here is the ...
1
vote
1answer
72 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 ...
1
vote
1answer
23 views

How to make MySQL join one-to-many with lowest value?

I have following database structure Domain has many URL which has many positions which belongs to phrase which belongs to user which belongs to subscription. In subscriptions table I have period ...
1
vote
1answer
50 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 ...
1
vote
1answer
43 views

How to export a MySQLtable with a longBLOB column to an Oracle database?

In MySQL I have a table: Select * from Dummy; Columns like below: id name salary binary_col 1 a 100 A.pdf 2 b 200 b.pdf 3 c 300 c.pdf 4 d 400 d.pdf 5 e 500 e.pdf ...
1
vote
1answer
38 views

mysql restore from data directory

I recently asked a question regarding the restoration of a pretty huge (around 300 to 400 GB) mysql database and got some nice suggestions as seen here. Now, I have backed up the data directory of ...
1
vote
1answer
46 views

Reviewing relational database tables

I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong. I have ...
1
vote
1answer
22 views

Should I denormalize lat & lon on area searches?

I run a hobby web site for tabletop gamers. They can search based on postal code in a radius round their location. As I consider adding new countries, my best option seem to be silo'ing the geo ...
1
vote
1answer
20 views

How to force MySQL Workbench to use constraints in my file?

I'm currently working on a project that requires I write my own constraints for my database (to show I know how they work). I'm attempting to test them in MySQL Workbench however the programs settings ...
1
vote
1answer
68 views

Mysql database in a symlink-ed directory gives ERROR 1005 (errorno: 13)

I've the need to put my database outside the default datadir of mysql. To achieve that I followed the symlink guidance in the mysql documentation. However when I use the db if I try to create a ...
1
vote
1answer
43 views

Concurrency Issues when multiple users uses Portal (MySQL and PHP)

We have set up a portal using Yii Framework in Apache, PHP and MySQL. In the portal, there is a feature to export the data in dbf format, the extract takes around 7 minutes, but in those 7 minutes no ...
1
vote
1answer
59 views

Improve select query performance

The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ? select count(id) from ...
1
vote
1answer
326 views

MySQL Workbench Cannot start SSH tunnel manager

I don't know why this error is happening when I try to connect by MySQL Workbench. Cannot start SSH tunnel manager. I'm using slackware 14.1 and installed all dependencies. I already installed the ...
1
vote
1answer
90 views

Mysql find 10 nearest point from given lat/long per type?

I have a database given with 3 different types of locations A, B and C. These points have latitude and longitude information with them. Now I have a random lat-long given and I want to find the ...
1
vote
1answer
52 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
0
votes
0answers
334 views

Oracle Flashback Archive aka Total Recall in MySQL

What would you suggest as a replacement system to achieve the same functionality of Oracle Flashback Archive aka Total Recall in other DBMS, in general or specifically in MySQL or MariaDB? ...
-1
votes
0answers
26 views

Group by not working in MySQL

I have Dimension table (Dim_Store)with 224 records and fact table with 256 M rows. If i fire the below query, it gives me only one record, which is absolutely fine. SELECT s.store_code, ...