MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
2answers
51 views

Disk write in iotop when doing mysql “Copy to tmp table”

I have just moved servers from a two-server configuration with the application and database server running separately to a one-server LAMP stack. Unfortunately the issue that I have encountered on ...
0
votes
2answers
65 views

MySQL High Availability for InnoDB

I am looking for MySQL High Availability with huge transactions. I prefer to go with InnoDB Engine. I prefer to use MySQL Master - Slave Replication. However, this option provides us Manual fail over ...
2
votes
3answers
246 views

How to design a table that each rows have 5K boolean attributes?

I have about 2M rows and each row looks like the following. 244 true false ... true -> One integer column(V) and about 5K boolean columns(B1, B2, ..., B5K) associated to the integer. Due to the ...
1
vote
1answer
143 views

Can't SHOW GRANTS after MySQL re-install

I had to completely re-install MySQL on one of the slaves and then rebuild it via a sql dump (we have 1 master and 2 slaves). I tried to SHOW GRANTS for one of the users, but keep getting the ...
1
vote
1answer
80 views

Upgrading to MySQL 5.6, benefits to switching to GTID replication?

We have the most basic of MySQL replication setups, a single Master doing statement-based replication to a single Slave, a 90/10 mix of innodb/myisam tables. Both run Percona MySQL 5.5 at the moment ...
0
votes
1answer
132 views

MySQL: Filter results by joining a vertical table

Situation: I've got a table users and a table preferences along with some others. The table users is aligned horizontally (id, username,...) whereas the table preferences is aligned vertically ...
6
votes
4answers
10k views

Modify DEFINER on Many Views

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result. Got error: 1449: The user ...
11
votes
4answers
24k views

How do I change the DEFINER of a VIEW in Mysql?

When I run mysqldump, I get an error: mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES This makes sense because foobar is a legacy ...
1
vote
1answer
100 views

MySQL - Best long read query solution within a replication based system

I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 ...
0
votes
1answer
96 views

How can I push data from my local MAMP database to my remote database?

I'm developing a site locally using MAMP Pro, and I've created a mysql database locally that have the same structure as the remote server. (username and password are different though) I'm inserting ...
1
vote
2answers
69 views

How to Recover truncate table in MySQL?

I am getting the "wrong input table name" when use TRUNCATE as following: TRUNCATE `stats` The table should bans. How to recover stats table? I am using CentOS 6, and MySQL-Server.
2
votes
2answers
396 views

Federated tables and triggers

This is the scenario. Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2). I have a table (T2) on DB2 that needs to "fetch" rows from another table (T1) on DB1. ...
0
votes
1answer
175 views

Syncronize mysql databases between local and hosted servers automatically

We have many website with Development , Staging and Production Server. we have many developers for many projects, we need a solution to synchronize the database with developer database with staging ...
1
vote
0answers
15 views

MULTIPLE INSERT VALUES with INSERT into another table

here a scenario to have a more clear picture. The DB is MySQL. I've 2 tables events and *event_users* : EVENTS id int type int date date time time ... EVENT_USERS event_id ...
-2
votes
1answer
25 views

How does InnoDB store tables in ibdata file? [closed]

There are two tables and their default engine is InnoDB. I want know the following What's inside the ibdata file ? How does InnoDB store these two tables ?
5
votes
1answer
261 views

Can spatial index be a part of a multiple columns index?

This manual over here is extremely unclear and doesn't even provide some sample SQL statements: http://dev.mysql.com/doc/refman/5.6/en/column-indexes.html Another way to rephrase the question is the ...
-3
votes
1answer
33 views

How long will it take to import 28 millions rows in a MyISAM table? [closed]

Someone has idea how long time it will take to import? I hear something like 40k each minute? Thanks.
1
vote
0answers
48 views

Best database for fast read all in one table

I currently have a large database in SQLite that I was using for rapid-prototyping for a software I am working on. Now that I am planning to move it to another database that should give better ...
0
votes
2answers
594 views

InnoDB Tablespace critical error in great need of a fix

Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG (The value reported is outside the allowed range - Byte free, free, gauge, warn, critic) Question: ...
-1
votes
0answers
18 views

MySQL indexing types? [closed]

i am newbie for php and mysql developement still don't make any indexing because don't have idea about that. how to use the mysql indexing. mostly used indexing method is what?
0
votes
1answer
49 views

synchronizing local and server database

I am developing a billing system software. For this I have created a database it contains many tables database and this is in local system all transactions data will be stored in local systems ...
8
votes
2answers
7k views

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an ...
1
vote
2answers
23 views

Large MySQL table that need to index some fields?

I would like to know some hints about how to indexing some fields of a MySQL Table that has about 28 millions of rows. Maybe i could recreate the table indexing it properly then import these rows? ...
0
votes
0answers
14 views

#2002 Cannot log in to the MySQL server / Connection for controluser as defined in your configuration failed

I use PHP/MySQL in Ubuntu 13. To reduce the size of ibdata1, i followed this post What is the best way to reduce the size of ibdata in mysql? and make necessary changes as told in the answer. I did ...
0
votes
1answer
18 views

Should I create a new instance of amazon rds or just a new schema inside of it?

I'm fairly new to Amazon Web Services and one of my customers asked me to install several things for them on EC2. I would normally use the same box as webserver and database server, mainly for ...
1
vote
2answers
62 views

Why does MySQL slow down in loading data?

We have a really large file (26GB) that we need to upload to MySQL. In order to do so we split it into nine 3GB files. What's confusing is that the first file took only 2 minutes to load, whereas the ...
3
votes
1answer
21 views

Is it possible to remove useless GRANTS in MySQL

I'm using pt-show-grants to create all my GRANTS on a new server but there's lots of old GRANTS that refer to tables that doesn't exist anymore so I get errors when I try to apply the SQL file on the ...
1
vote
1answer
176 views

Extremely slow mySql restores

Please bear with me as I ask this question, which looks similar to questions already on DBA@SE. In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database ...
2
votes
4answers
334 views

MySQL replication - slave update

I have a master slave setup of MySQL and my question is that if I make any changes in the slave database: 1. Will it mess up the sync in any way 2. Will the changes get overwritten from the master ...
1
vote
1answer
39 views

MySQL master master replication, safe to delete rows on both masters?

We have a MySQL (v5.1.61) cluster with two masters (and two slaves). We have a table logs with a primary key column logID. This log table grows rather huge, and we'd like to delete data from it. So ...
1
vote
1answer
92 views

Mysql HY000 the table table_name is full when adding new column

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full. I changed my tmp_table_size and heap_size but mysql still says same error. ...
0
votes
1answer
35 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have 150GB database and my ibdata1 file size is 240GB so i want shrink the ibdata1 file so for that i have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
0
votes
1answer
17 views

SQL File Upload to MySQL Server

I'm fairly new to databases. I want to upload a .sql file to a mysql server. However the database I want to update contains many .frm .myi .myd files. Should I delete those files and replace with the ...
5
votes
2answers
3k views

Cannot open table mysql/innodb_index_stats

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well. The errors I ...
0
votes
1answer
74 views

Relation between multiple hierarchical taxonomies linked to a single entity

I am working on an online test application which supports multiple choice questions. I have created schema for taxonomy (classification) system to be used with questions. taxonomy(id, name, slug, ...
0
votes
0answers
30 views

MySQL silly query plan [duplicate]

I'm using 5.6.15-56-log Percona Server. Here's my table(which has over 5M records) CREATE TABLE `promotion_codes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` varchar(255) DEFAULT NULL, ...
0
votes
3answers
70 views

MySQL vs. Microsoft Access [on hold]

I'm trying to aggregate about 500-1000 MS Excel files that deal with various small business issues (inventory, purchase orders, etc) and I'm currently trying to decide what kind of RDMS I should use. ...
0
votes
0answers
17 views

Using triggers to sync databases

Is it more reliable to use triggers to sync 2 mysql database that has different structures than doing it on an application level? See related question at stackoverflow. How would you sync 2 mysql ...
1
vote
1answer
53 views

MySQL 5.5 to 5.6 Upgrade. Will data remain the same?

We are considering to upgrade to MySQL 5.6 this summer. I would like to know if our data will be changed in any way once we perform the upgrade? I've been reading and I found that mysql_upgrade ...
0
votes
1answer
14 views

MySQL Server 5.6 only runs when making swapfile

I am new to MySQL server and I have setup a dedicated MySQL server. I am aiming to install MySQL server version 5.6 and since version 5.6 is not available on the Ubuntu repository I have to upgrade it ...
0
votes
1answer
39 views

Why is setting open_files_limit making MySQL slow?

We noticed that adding open_files_limit in /etc/my.cnf is making server slow. Reference : What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?
0
votes
1answer
28 views

How to prevent MySQL to consume much memory soon after Windows7 startup?

When I turn on my desktop (Windows 7 SP1), I often find 2 or 3 GB of memory is already occupied by MySQL processes. I don't care about how much memory a database requires, if it actually runs some ...
2
votes
2answers
15 views

where is the index physically located in MySQL database

Just wondering where is the index info physically located in MySQL database if I have indexed a table with ALTER TABLE contacts ADD KEY (columnName); ? it will stay with that table and add a new ...
1
vote
1answer
212 views

optimizing MySQL for traffic analytics system

background : I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as ...
0
votes
0answers
18 views

MySQL 5.5 InnoDB migration using stored procedure is very slow in Linux/Windows 2008 Server

I am Migrating MYSQL data from one server to another server using my stored procedure. The target server is Linux//Windows Server 2008. The migration speed is extremely poor. It takes about 5-6 ...
1
vote
0answers
104 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
1
vote
1answer
254 views

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
0
votes
0answers
11 views

started using cleardb and curious to see if way to verify that heroku connections are via ssl

I'm hooking up ClearDB's MySQL for Heroku and have set up SSL connections via provided certs / keys but am curious if there is any way to verify that the heroku-cleardb connection is running over ...
0
votes
1answer
115 views

Getting the top count of records from a table for each month and user

I have a table where translations are stored along with the user_id and when the translation was added. I want to create a query that shows the 'Translator of the month' (the person who did the most ...
0
votes
1answer
31 views

MySQL - prevent OS cache for query

On high load production server I have many sql selects which are unique or re-use will be in hours. I use SQL_NO_CACHE but this queries are still cached with OS cache. Server have 64G ram and used ram ...