MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
42 views

How to decipher the transaction message in innodb status log.

I have several transactions that seem have stalled. Looking at the innodb status log, I found several messages similar to the following: ---TRANSACTION 69DBBB, ACTIVE 947 sec starting index read, ...
2
votes
1answer
97 views

Relational modeled design to store versioned files

The scenario I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the ...
2
votes
2answers
381 views

Select different time

I looked on the internet and I found your post on the stackexchange. I will try to explain in more detail what we really need I do not know well to write English It is a system for airporttaxi id ...
2
votes
1answer
42 views

MySQL behaviour on connection failure

My MySQL database is hosted on a different machine from my application system, which issues queries to the MySQL database remotely. If for some reason the network connection between the two machines ...
2
votes
1answer
1k views

Replication from EC2 Master to RDS Slave - Connecting to master

I am migrating my company's database from an EC2 instance to an RDS instance. I have already migrated a snapshot of data and am now trying to set up replication to get the data that has been added ...
2
votes
3answers
239 views

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

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
3answers
414 views

Restore large MySQL dump thorugh EasyPHP

I am trying to upload large files on easyphp but as there is limitation on this software and large databases cannot be uploaded. Is there some other software that allows to import large databases? ...
2
votes
1answer
120 views

Mysql Master-Master with additional slaves

We have two datacenters with a copy of our web app (online training platform) at each. The primary datacenter receives all of the traffic at any given time and the second datacenter serves as a ...
2
votes
3answers
241 views

How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

I have two tables 1. Customer_details and 2. Order_details. 1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name, 2.Order_details table --> fields-- Ord_Id(Primary ...
2
votes
1answer
423 views

Is there any way MySQL can list relationship cardinality between two entities?

I am working on the project where I have to analyse the MySQL schema. As a part of schema information, I want to list down relationship cardinality between different relations. Does MySQL stores ...
2
votes
2answers
520 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 ...
2
votes
1answer
815 views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
2
votes
1answer
663 views

MySQL Queries take up to 35 seconds

My site is very slow sometimes, so I activated the php-slowlog to find out whats wrong. It is full of mysql-functions like mysql_query. So I activated the mysql-slowlog and found out that some querys ...
2
votes
1answer
177 views

Risk of changing clocktime on MySQL server host

I have a MySQL server host that have a clock that is about 75 minutes wrong. Is there any risk in changing the hosts clock while MySQL is running? Update: Obviously NOW() and other functions ...
2
votes
1answer
77 views

Formatting a float value

I am fetching data from MySQL using PHP. I know that most values stored in the database are float. Sometimes, the database outputs values like 3.9999E-5 and sometimes like 0.00104961904762 (14 ...
2
votes
1answer
380 views

row locking within ACID transaction innodb

I have a financial transaction that looks something like this (pseudo): begin select record for update update record(same record as selected) insert another record into another table ...
2
votes
2answers
956 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
400 views

MariaDB: Created_tmp_disk_tables high after start

today i looked in the status of my MySQL server which is based on MariaDB and i noticed that Created_tmp_disk_tables is very high (over 168k). I think this is the reason for my high I/O usage. I try ...
2
votes
1answer
681 views

Strange things with Avg_row_length

The data from one tables is dumped to file and inserted into calls_old table with load data infile it shows Avg_row_length 7366. Later I dumped table with mysqldump, droped table, then created again ...
2
votes
1answer
2k views

Can I execute an external process via MySQL on table change?

When using triggers, if an update is done to a table then a trigger is executed. This is very convenient. But what I would need is to execute an external script. Is it possible to configure MySQL ...
2
votes
1answer
233 views

Does MySQL do UPDATE in place on NOT NULL column?

Assume I have a table like this in MySQL: CREATE TABLE Foo ( Key INT NOT NULL , MyCol INT NOT NULL PRIMARY KEY (Key)) And I execute a statement like this on: UPDATE Foo SET MyCol = 13 WHERE Key = ...
2
votes
3answers
1k views

How to remove master role from MySQL server

I have 2 MySQL servers, one is the master and other one is slave, that's all good. The problem I have is that the slave is also set up as a master. I would like to disable or remove the master role ...
2
votes
1answer
4k views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
2
votes
2answers
614 views

MySQL - INSERT … SELECT .. FROM .. - Single transaction or transaction per row?

Question: I am currently working on a data migration. I need to migrate 2.5 million rows from one InnoDB table to another InnoDB table (with a different setup). I am using the following syntax to do ...
2
votes
1answer
302 views

MySQL cluster DataMemory and DiskPageBufferMemory parameters

I want to setup a MySQL cluster. The amount of data is more than double the amount of RAM. I have 3 Data Nodes each with 8GB RAM. QUESTIONS What is a good rule for the DataMemory and ...
2
votes
2answers
158 views

MySQL login after secure installation

I have installed mysql5.5 via secure installation procedure by using: sudo mysql_secure_installation Now when I am trying to login in MySQL from the terminal by using: sudo -u root -p The ...
2
votes
3answers
7k views

Purge old records from mysql ibdata1 file

Is it possible to purge old records from an ibdata1 file without taking down the database attached to it? Records that have been deleted from the database are still present when I scan ibdata1 for ...
2
votes
1answer
350 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. ...
2
votes
1answer
268 views

Design advice regarding a users/groups/tasks database scheme

Here are my requirements: one user can have many tasks one group can have many task one group can have many users only invited user by a user can be added into a group Does this db design appear ...
2
votes
1answer
162 views

Can MySQL use an index to calculate SUM?

The following query is run in a MySQL database (all InnoDB tables, Percona 5.5), and is slow for some parameter values (i.e. for some large customers). It returns the total size of all files uploaded ...
2
votes
1answer
283 views

Implications of a second index on a timestamp field in a sequential table

I have a MySQL table of a few gigabytes with roughly ~100M rows. I store data sequentially, such that the timestamps increase as the ID also increases. Because ordering and filtering by the timestamp ...
2
votes
1answer
447 views

How to handle connection pooling for massive multi-tenancy/multi-schema environment

Here's a quick run down of the situation: We have 1 Schema per customer. We have 2000+ customers. We have 50+ Database servers (with the above schema's distributed unevenly amongst them). We are ...
2
votes
1answer
256 views

“Set Password” with a limited user

I'm using MySQL 5.6 and I want to have a limited service-user which is able to create/modify the users of my databases. Now my problem is that I can quite figure out what privileges my service-user ...
2
votes
1answer
966 views

Does MySQL reindex on update

I have a users table with various fields which are getting updated frequently. i.e table have: userid - PRIMARY int profile_views - int ratings - int last_active - int ( to store UNIX timestamps for ...
2
votes
1answer
362 views

Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)

Innodb works in Mysql 5.1 whether or not the innodb plugin, ha_innodb_plugin.so is loaded at startup or not. Configuration dependent of course. What are the advantages and disadvantages of either ...
2
votes
1answer
115 views

Should I be concerned about excessive Com_change_db commands?

I installed MONyog recently to check for problems on a MySQL server that I admin. Most of the information was useful and helpful, but there's one warning I am not sure is worth fixing. It reports ...
2
votes
5answers
7k views

MySQL gives me:“Can't open and lock privilege tables: Table 'host' is read only”

I am facing problem restoring a MySQL database. My primary database was MySQL 5.1 and now I am trying to copy it to MySQL 5.5. The database was backed up by using Xtrabackup. I am using Ubuntu ...
2
votes
1answer
787 views

Mysql Getting Access denied for user 'root'@'localhost' (using password: YES) constantly in log

I have MonYog running and it is monitoring my DB Server. It gives me constant alerts that the number of refused connections has exceeded the threshold etc. Looking in the LySQL.Log I can see constant ...
2
votes
1answer
116 views

When to stop splitting tables?

I am creating a league management system. I came up with the diagram below. I am no database expert but I feel it would be better to merge the three following tables: season_league, league_division ...
2
votes
1answer
70 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...
2
votes
1answer
207 views

Sphinx: Indexer causes MySQL to hang

Not sure what's going on. I run indexer --all --rotate When it finishes mysql hangs and not accepting new connections. from my observation as soon as indexer finishes, all update,insert,delete ...
2
votes
1answer
638 views

Subselect dependent on first select value

I wish to do a select of all data matching a particular id. I would also like to return a selection of all data matching the alt_id field in the first select. This example works fine for the main ...
2
votes
2answers
608 views

How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables

I've got a table that has hundreds of rows, each row is a recipe with nutritional information, for example: recipe_table: id | calories | protein| carbs | fat recipe1, 100, 20g, 10g, 2g ...
2
votes
2answers
1k views

changing TEMPORARY TABLE default collation

when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
2
votes
1answer
427 views

Does partitioning a MySQL table also increase index creation performance?

This is something I was wondering since I'm planning to partition a aggregation table with 10 mil records where rebuilding indexes cause table locks for some time. Because partitions are bluntly put ...
2
votes
2answers
1k views

Multiple Similar Tables vs One Master Table

I'm a web app developer and less experienced in database design, so would appreciate anyone's input. When I first built this web app, the app managed one type of object (table) with a handful of ...
2
votes
2answers
279 views

Specific disadvantages to storing infrequently accessed large files in a large database

I have heard many times across the web that it is, for various reasons, a bad idea to store files directly in a database, the preferred method being to store filenames in the database, and files ...
2
votes
1answer
230 views

mysql binlog file size stop growing

I made a master(A)-slave(B)-slave(C) mode MySQL instances for backup. But today when I see whether the mode works fine, I find A and B are OK. That is B can get binlog from A, but B does not write ...
2
votes
2answers
1k views

Two primary keys to one foreign key

racing_couple ID (PRIMARY) breeding_couple ID (PRIMARY) egg IDpar(FOREIGN KEY) I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) ...
2
votes
2answers
2k views

MySQL Workbench Alter Procedure

i have a problem with my workbench program. I use Mac Lion OS X and mysql workbench program. When i want to do any procedure to alter that doesn't work. Which is show to the procedure code i right ...