MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

5
votes
3answers
8k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
4
votes
2answers
685 views

MySql master and slave design

I have set up mysql replication with single slave. Whenever i am adding data to master database, it is automatically copied to slave database. But if I add data to slave database it is not available ...
0
votes
0answers
357 views

RDS MySQL instance swapping lots of temp tables

I have an application that has few concurrent connections but does a lot of uncacheable fulltext searches sorting, limit with offset, etc. I have an xlarge instance with innodb buffer pool size @ 75% ...
3
votes
2answers
3k views

What data type should I use: ENUM, TINYINT, or CHAR?

I'm creating a new table and I would like to do it right. The table will be a list of members with their Professional Type and Decile associated. The Professional Type is a 3-character string. There ...
2
votes
1answer
5k views

How to recover data from corrupted innoDB table from a crashed mySQL server?

Some background: I had a MySQL server running on Ubuntu Server. I don't remember which versions they were running but it was likely a Ubuntu 12.04 with MySQL 5.5. During the course of a day, ...
0
votes
1answer
56 views

How should I set up these tables in my relational database?

I'm working on a project where a person can create a survey with custom questions and display the questions in whatever order they choose. They can break the questions into sections. The problem I'm ...
2
votes
2answers
66 views

Backing up MySQL database

I have quite large and rather important DB to backup. First off, whats best way to do it? Secondly, easiest way I know is to export it through phpmyadmin, but, i'm worried about options, although ...
2
votes
4answers
1k views

search for something in the first 1000 records in the table

If I do the following MySQL query select * from table where name Like '%john%' limit 5; that mean the query will search all the mysql database table and return only the top 5 results that match the ...
0
votes
1answer
426 views

Redo Logs Mysql Cluster

The mysql cluster has 4 directories namely D8 D9 D10 D11 for redo logfiles. But while inserting into table only one redo directory is used. I ran following command to get redo information "all dump ...
2
votes
1answer
315 views

pt-table-sync error: Called not_in_left in state 0

I have setup a Mysql replication between 2 servers, using Percona Xtrabackup: Master is a MySQL 5.0.91 Community Edition (CentOS 4.8) Slave is a MySQL 5.1.68 Community Edition (CentOS 6.4) When ...
2
votes
1answer
932 views

MySQL statement-based replication and stored procedure

When you have a MySQL statement-based replication and you create a stored procedure that modify some rows (INSERT, UPDATE, DELETE commands...), What is really replicated on the slave when you call ...
1
vote
2answers
43 views

Order by on an alphanumerical column

I have a column that has strings of the following type: Miller 10 Allen 20 King 10 .... Jones 100 I try to sort the column based on the numerical part of the data. I tried the following: SELECT ...
13
votes
2answers
11k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
0
votes
1answer
1k views

Optimizing ORDER BY for simple MySQL query

I'm trying to optimize this really simple query, and it gives me grief for a day now :( Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
1
vote
2answers
1k views

MySQL: CAST + SUBSTR vs. FLOOR + MOD

I have a field in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two ...
2
votes
1answer
941 views

Trying to allocate 3G for buffer pool in InnoDB fails

I'm trying to boost my InnoDB driver on my Windows 7 PC since each page request takes just about 1.7 seconds to load versus 0.002 seconds on my Ubuntu 12.10 workstation. The setting I'm playing ...
0
votes
1answer
226 views

Large queries on MyISAM tables crashing MySQL

I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that ...
0
votes
0answers
23 views

Myisam Mysql server crashed

I have a server with linux fedora 11 Operating System. Recently , I discovered that every morning mysql has been crashed, so I have to restart mysql service. Can any body help me?
1
vote
1answer
1k views

Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?

We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and ...
2
votes
2answers
1k views

mySQL data table seems to stuck at 16777215 rows

I am creating a MySQL table and loading data from a local file. CREATE TABLE `patent` ( `seq_number` mediumint(9) unsigned NOT NULL auto_increment, `seq_id` varchar(24) default NULL, `date` ...
0
votes
1answer
260 views

Memcache implementation

I have a Wordpress site that uses WP Super Cache plugin in order to make my blog posts and pages stay as HTML so that less PHP/MySQL code is executed. However, there's a featured box that can't get ...
0
votes
1answer
540 views

MySQL: replicating to a different table engine type

According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say ...
0
votes
2answers
82 views

Error log - What does this mean and what can I do about it?

I have the following repeated errors in my error.log and I don't know what it means or what I should/can do about it: Access denied for user 'root'@'[some IP]' (using password: YES) There are ...
4
votes
1answer
297 views

High amount of Read Misses and Pages To Be Flushed

I am running a mysql database backend for a Moodle installation, and after a few months performance really starts to suffer (Up to 30 seconds for some pages to load). Under investigation in the InnoDB ...
1
vote
1answer
2k views

Getting the data from a foreign key table

I have a table that relates a user to client using both their table id's UsersClients Table --------------------- | userid | clientid | --------------------- | 2 | 32 | | 3 ...
0
votes
2answers
994 views

VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed?

For a log table, I have a custom message column. It will never be updated. It will be searched with LIKE conditions. It will be capped at 1KB. TEXT fields are slower than VARCHAR fields for ...
1
vote
1answer
132 views

Proper indexes or anything else to optimize

I need help to optimize the query to avoid using "Using temporary", "Using filesort". CREATE TABLE `target_bahrain_pepsi` ( `id` int(11) DEFAULT NULL, `col_value` varchar(50) DEFAULT ...
-2
votes
2answers
4k views

How can I search for a name in two columns in MySQL? [closed]

I want to search a name 'Hamed Haddadi'. 'Hamed' is first name and 'Haddadi' is the last name of the user. When I search individually in the first name and last name columns it shows a record. My ...
2
votes
1answer
37 views

Which MySQL directories to back up using FTP

My server has been hacked and I only have FTP access to recover my data before reinstalling the OS. Which/where are the directories that needs to be backed up? Will MySQL be recovered back to its ...
4
votes
1answer
2k views

What are the disadvantages if indexes are added on all columns?

I know, that it is not a good design to set too many indexes, but just for understanding the theory: What exactly are the disadvantages if you add an index on all columns in a table in MySQL? Are ...
3
votes
1answer
1k views

Db design for historical option data (~200GB)

I need some help determining how to best design my tables. I have about 200GB worth of data I wish to place in a table (historical option price data). The table looks something like: String symbol ...
2
votes
1answer
72 views

Do I need to reenter old data after adding an index to a table?

I want to add index to my tables. Some of my tables already have couple thousand rows. Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing ...
3
votes
2answers
3k views

How can I improve this nested MySQL query to avoid redundant WHERE statement?

I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question): tbl_users: ID | email (all subscribers to our email list) ...
0
votes
1answer
446 views

Mysql suddenly stopped after is said to be crashed

We have a mysql instance running well and suddenly it stop. I have gone through the logs file but found no indication why it stopped? But there have some instance start and stop before this? Below is ...
2
votes
3answers
1k views

do MYSQL views occupy physical space?

do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?
0
votes
1answer
381 views

Which privileges for MySQL extended status?

If I want to execute "SHOW STATUS" within MySQL or use "mysqladmin extended-status" on command line with a non-root user, which privileges would this user then need?
2
votes
1answer
60 views

Show all results for past N “insert batches”

Every once in a while I have several users check in with some value: +-------+-------------+------+---------------------+ | id | user | RT | time | ...
1
vote
3answers
132 views

Correct way to perform an UPDATE on multiple tables?

This query is working, but I am only just beginning to understand relational tables. I have spent the last few yeas doing all of the processing with PHP after retrieving the data I needed (oftentimes ...
1
vote
1answer
489 views

MySQL: OPTIMIZE after TRUNCATE?

Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a ...
0
votes
1answer
32 views

mysql error creating table

I was creating a DB with mySQL, and this error appeared: Can't create table 'projectefinal.apuntats' (errno: 150) I've got this: CREATE DATABASE IF NOT EXISTS projectefinal CREATE TABLE IF ...
0
votes
2answers
776 views

How does mysql transaction works?

I already know how to use transactions, what I want to know is how MySQL transaction handles data being processed... For example, consider the following: 1. I have 2 databases (db1 and db2) ...
1
vote
2answers
3k views

Recursive query in mysql

I am using GetAncestry function found at this post, The problem is when I try to use it with select query Mysql hang, I am not sure why it is happens my Mysql version is "5.5.16" any help appriciable ...
1
vote
1answer
92 views

extra steps after changing storage engine and adding index

Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases. I have a php/MySQL site uses the MyISAM storage engine. It ...
2
votes
1answer
971 views

How to properly secure MySQL database?

We have a web application based on the famous triad apache + php + mysql which we sell to our customers and gets installed on their servers. Currently, we are using MySQL 5.1.41 which has only a ...
1
vote
1answer
2k views

Where is the MySQL variable - innodb_flush_method?

I would like to tweak the value of innodb_flush_method to find out its performance impact on a database server. That variable is listed when I run the command SHOW VARIABLES. But I could not find it ...
0
votes
1answer
516 views

ERROR 1034 (HY000) : Wrong aligned block, while importing in mysql

I got a SQLdump from my database and now I want to import it in to an other mysql server with same version(5.5.22-0ubuntu1) in ubuntu 12.04 .but at the middle of importing, it said: ERROR 1034 ...
0
votes
1answer
3k views

how to resolve ELFCLASS32 error in mysql for UDF lib_mysqludf_sys.so

I'm trying to install lib_mysqludf_sys.so in mysql to create sys_exec function which will run an external java programm .. first of all i issued this command : CREATE FUNCTION sys_exec RETURNS INT ...
1
vote
1answer
618 views

mysql column naming conventions for foreign keys

Lately I've been thinking about what's a better name for foreign keys, either user_id, or id_user style. Personally I think id_user style is better for handling within database (db queries) where ...
7
votes
2answers
2k views

Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?

I have a table that stores available appointments for teachers, allowing two kinds of insertions: Hourly based: with total freedom to add unlimited slots per day per teacher (as long as slots don't ...
1
vote
0answers
92 views

pt-table-checksum generates no output

I've been using pt-table-checksum on our staging server successfully, but when I run ./pt-table-checksum h=localhost,u=user --ask-pass --lock-wait-time=50 --databases prod It just sits there. No ...