MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
180 views

Database design that handles data growth with time

I have a big table that stores video rental data with the following columns: id, title, language, duration, owner, remarks, closing_date_for_loan Assuming every day there are thousands of data ...
8
votes
3answers
6k views

One Big Database vs. Several Smaller Ones

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g., ...
2
votes
3answers
2k views

mysql cannot start because of init.d folder?

After I setup mysql 5.5, I changed the data dir to another folder and modify the related config in my.cnf. This is quite simple, but mysql cannot start anymore even if reinstall or not change data ...
0
votes
0answers
95 views

MySQL max_connections Changed Without Notice

I'm running a Linux (512MB of RAM) server with MySQL 5.1. I host an app that is used by a small # of users (less than 50). Everything has been working fine until yesterday, connecting to my DB would ...
9
votes
4answers
11k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
1
vote
2answers
499 views

Mysql synchronization databases on one machine

I have MySQL databases. Database A and Database B. Running on Mysql 5.5 and Ubuntu 12.04. Into Database A i am doing INSERTs, UPDATEs every minute (cron) (The whole database has over 15 millions ...
0
votes
0answers
762 views

How long would a delete take for 50 million records in MySQL/InnoDB?

How long would a delete take for 50 million records in MySQL with the InnoDB engine? Server Config: MySQL Version 5.0.77 CentOS 5.4 Final 64 bit 16GB RAM RAID 5 Processor Model : Intel(R) Xeon(R) ...
0
votes
1answer
74 views

Index use and column type

I was told today that having a table structure similar to this is bad for performance of index's because of the mismatch of column types Table 1 id - int (7) PK Others .... Table 2 id int (11) ...
0
votes
2answers
771 views

Bypass MyISAM table lock

I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action ...
3
votes
1answer
636 views

Can memcached improve performance for InnoDB in mem with a large innodb_buffer_pool_size?

I learned that innodb_buffer_pool_size makes innodb behave like an in memory db. If I give innodb_buffer_pool_size enough ram for the entire db could memcached improve further the performance, or ...
2
votes
1answer
357 views

Can't query one view from another?

Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, ...
2
votes
1answer
835 views

How to restrict MySQL user to not import and export database

I have created a read only MySQL user but this user have an option to export the database through phpmyadmin. How can I disable the options Export and Import?
4
votes
2answers
348 views

How to re-simulate a long query stuck in mysql queue?

BACKGROUND Occasionally, in my live web app, I'll get a query that locks up the database for 1-2 minutes. Sometimes it is hard to predict which queries will do this until the load gets to a certain ...
3
votes
2answers
1k views

What's wrong with this MySQL query?

I am getting a syntax error for my MySQL (5.5.24) query. The error is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right ...
4
votes
2answers
8k views

Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?

My Hardware Spec Technology: Ivy Bridge CPU: Intel Xeon E3 1245v2 Intel Smart Cache: 8MB Cores / Threads: 4 / 8 Frequency: 3.4GHz+ / 3.8GHz Turbo Boost RAM: 32 GB DDR3 Hard disk: 2x 2TB SATA3 Raid 1 ...
-4
votes
0answers
76 views

Can I move up rows in the memory itself? [closed]

Lets say i have the following table : id col2 col3 1 1 1 2 2 2 3 3 3 4 4 4 And I'm trying to move rows to different ...
1
vote
3answers
186 views

Where can I learn about MySQL master/slave, master/master, or clustering setups?

What are the best resources you've read or watched about setting up master/slave or clustering for database replication? I don't know much about this. I have a database that is getting rather large ...
3
votes
2answers
1k views

Implementing soft delete

Could someone please show or describe to me how to implement soft deletes? I have table of vouchers with these fields: id, username, password, serial. I want to display a given number of rows ...
2
votes
4answers
3k views

Tons and tons of relay logs on a master

I have a master that has 298 relay bin files as recent as today, going back well 298 days. There is no relay-log definitions in the .cnf and mysql> show variables like '%relay%'; ...
1
vote
1answer
6k views

How to return resultset from MySQL Stored Procedure using prepared statement?

DELIMITER $$ CREATE PROCEDURE List_IL() BEGIN DECLARE Project_Number_val VARCHAR( 255 ); DECLARE Temp_List_val VARCHAR(255); DECLARE Project_List_val VARCHAR(255); DECLARE FoundCount INT; ...
21
votes
5answers
13k views

Performance implications of MySQL VARCHAR sizes

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
5
votes
1answer
8k views

InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

Server Specification: VPS with following info model name : Intel(R) Xeon(R) CPU E5649 @ 2.53GHz MemTotal: 2058776 kB MemFree: 244436 kB We are running IP.Board from Invision ...
2
votes
2answers
453 views

Relational database for address model

I want to design an "Address" model for all types of entities like users, businesses, etc. I have two types of main models: one is User and the other is Business. Each one has different address types ...
2
votes
1answer
4k views

16 Cores 12 GB RAM server MySql Configuration - my.cnf

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
30
votes
5answers
5k views

When is the right time to use MariaDB instead of MySQL, and Why?

What we earn and what we lost with this migration? What should I expect as drawbacks after the migration? Is it really unnecessary to change the applications in any situation?
3
votes
1answer
114 views

Screwed up replication by sharing server ids

So I had my screwup for the week today. I was adding another pair of slaves and set the same server ID on the new slave as an old slave. The layout kind of looks like Master | | \/ | oldS | ...
1
vote
1answer
457 views

Convert the IP Address range to two BIGINT for faster lookup

I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets which will look like below and now the next steps is to populate this datasets in to the MySql ...
11
votes
5answers
32k views

How to properly kill MySQL?

I have CentOS 64bit with CPanel installed and I use service mysql stop However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
2
votes
2answers
174 views

Representing SQL constraints on a table

I have this table: CREATE TABLE `sold` ( `part_ID` INT(11) NOT NULL , `date` DATE NOT NULL , PRIMARY KEY (`part_ID`, `date`) , FOREIGN KEY (`part_ID` ) REFERENCES `part` (`part_ID` ); This ...
2
votes
1answer
49 views

How to read this output from MySQL?

Query that is being ran: INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES (?, ?, ?) Error that is being thrown: SQLSTATE[23000]: Integrity constraint violation: 1062 ...
0
votes
1answer
336 views

Mysql Join with filters

I have two tables: MAINCOLOR and SUBCOLOR with the data as: MAINCOLOR : MAINID COLOR 1 RED 2 BLUE 3 GREEN SUBCOLOR: SUBID MAINID SUBCOLOR STATUS 1 1 PINK 0 2 1 ...
2
votes
1answer
287 views

Physically migrate MySQL without a dump

A client ask me to migrate his MySQL DB: The server doesn't have free space and it also has a very big table that is broken, so I can't dump it. I can't REPAIR it because of lacking of free space. ...
0
votes
1answer
88 views

Do I need tweak something for this MySQL setup?

I'm running a fairly popular website with the following stats and setup. stats: ~250,000 pageviews per day, rendering ~47 queries / second to MySQL setup: the MySQL instance is running alone on a ...
1
vote
0answers
2k views

'Incorrect datetime value: for column 'last_login' at row 1'

i am getting below error: Last_SQL_Errno: 1292 Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. ...
0
votes
2answers
875 views

How are DB locks tied to connections and sessions?

I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
2
votes
1answer
930 views

ON DELETE cascade alternate

I am trying to create a database for 2 Tables like: Table1 : tb1-id(pk) | tb2-id | other Table2 : tb2-id(pk) | other FOREIGN KEY Table2(tb2-id) REFERENCES Table1(tb1-id) ON DELETE ...
4
votes
1answer
3k views

mysql_connect() system error :111

I have 2 VPS. I would like to connect from the frist to the second's mysql db like this : $db = mysql_connect("94.xxx.xxx.xxx:222", "user", "pass"); I got this error: Warning: ...
1
vote
1answer
173 views

Access denied Error in MySQL and Drupal

We are trying to deploy a Drupal application on our production server. We see this strange error Lost connection to MySQL server at ''Access denied root@ipaddres password(yes) after deploying. We ...
0
votes
1answer
113 views

Implementation of stack in MySQL

I had an abstract idea of implementing a stack in MySQL. Tried a lot of Google work but couldn't reach to any logical conclusion. The Stack must be a collection of tables with the stack top pointing ...
1
vote
1answer
2k views

Permissions for a MySQL user which will monitor replication status?

I'd like to use a script to monitor the replication status of a MySQL database like in this bash script: https://gist.github.com/wesdeboer/1791632 I'd like to create a user which will only be used to ...
1
vote
1answer
5k views

What are some best monitoring tools available for mysql? [duplicate]

I have a setup a linux mysql server and would like to monitor it. I found percona plugins for nagios and cacti. Are there any other tools available?
1
vote
3answers
604 views

Complex query with multiple normalized fields

We have a fairly simple table structure, but with a LOT of fields per table (talking 40+). This data is initially produced in plain-text, user-readable tables, but then it is translated into ...
0
votes
1answer
35 views

Why isn't my includeThreadNamesAsStatementComment JDBC parameter working?

The JDBC API lists a includeThreadNamesAsStatementComment parameter that seems to indicate it will prefix all queries with the Java thread ID. I thought this would be useful in tying back DB activity ...
3
votes
1answer
973 views

How do you extend the tablespace when using innodb_file_per_table?

With innodb_file_per_table off, you can create multiple tablespaces on multiple devices if necessary to manage growth, balance I/O, etc. With the option on, how do you control growth of the files? Do ...
3
votes
1answer
344 views

MySQL licensing [closed]

I have looked at a few questions posted in stackoverflow http://stackoverflow.com/questions/225987/can-someone-explain-mysqls-license-and-what-it-means-to-closed-source-developme ...
2
votes
1answer
613 views

SQL Uniqueness Across Multiple Tables

I have two SQL tables: Users and clients. Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field. The users ...
0
votes
1answer
530 views

Integer comparison in MySQL. does it cause lots of overhead?

I have an 8 digit number in my db. I want to do a select on the table in MySql (Innodb) and get the list of all numbers that are equal or smaller than my number. Then I sort them from larger to ...
2
votes
2answers
138 views

Logging only mySQL logins on heavily used system

I have an audit requirement where they want all database access (logins) logged. We have a very large distributed system of hundreds of mySQL servers (shards, slaves, slaves of slaves) that are ...
2
votes
2answers
271 views

Can I configure MySQL's typecasting to consider 0 != 'foo'?

This article points out the following security problem with MySQL: USERS id | email | password_reset_token 1 | one@example.com | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx 2 | two@example.com | ...
2
votes
1answer
291 views

How to scale MySQL transaction wrapped queries?

So I'm working on an app that wraps many of it's SQL queries in a transaction for it's SQL usage and I'm trying to think of some ways to scale the DB layer up.... Up until now we've been throwing ...