MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
7 views

Does MySQL guarantee atomic upgrade from read lock to write lock?

If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the ...
0
votes
2answers
733 views

How to sync two databases (alternative to mysql replication)?

Hello i have 2 mysql databases where most of the structure is constant except few tables (additions) in 2 dbs. I need to synchronize these 2 databases mainly common tables from one db to another. This ...
0
votes
1answer
39 views

MySQL relay log rotates too soon and too often

I have a simple master/slave setup where the slave is just a backup. Sometimes I notice that the relay log on the slave rotates too often and too soon, when it is still very small: 4:00 PM ...
0
votes
1answer
19 views

“Display width out of range for column” error when importing mysql 5.1 dump into 5.5

I am migrating to a new server, and trying to move my database from a 5.1 to 5.5 server. I created a dump with mysqldump -u root -p --add-drop-database --triggers dbname > dbname.sql When I ...
0
votes
1answer
85 views

Dealing with data stored as arrays in a MySQL DB

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to ...
0
votes
2answers
20 views

Sorting of results is slow some times but fast at other times

I have a mysql query that is joining a few tables and then uses order by field for sorting. It sometimes runs quite fast at other times it is slow. While it was slow i profiled the query to find that ...
0
votes
1answer
24 views

Inserting empty string on Windows MySQL doesn't use default value

Why inserting '' on a column with default integer value on MYSQL/WINDOWS produces ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1 The query is: insert into test2 (id,test) ...
0
votes
0answers
3 views

MySQL Workbench EER Diagram and ENUM data type

I am creating EER model diagram using MySQL Workbench (version : 5.2.47) The version for MySQL, I am using is 5.5.35-0ubuntu0.13.10.2. I have created a schema with 'Default Collation' set to 'utf8 - ...
0
votes
1answer
12 views

Mysql tar ball installation

I need to install two different versions of Mysql in one server OS RHEL6 and it will be separated by user called mysqlprod and mysqlpreprod. I need to separate their binaries, softwares,data ...
0
votes
1answer
130 views

MySQL SSL encryption

Does setting MASTER_SSL to 1 in change master ensure encryption without specifying options MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY ? After setting MASTER_SSL to 1, show ...
0
votes
1answer
21 views

How to check if several tables exist in database, and how to show it in Mysql

I would like to check if several tables exist in a database, and return a easy to understand return, I know you can check if a single table exists by running show tables like "foo"; but is there ...
0
votes
2answers
28 views

How can I genereate a string from the returns of a SELECT query in Mysql?

I need to create a string from data that is extracted from an SQL query, and wish to accomplish it within the SQL Query. The return is something like +---------------+ | foo_ids | ...
0
votes
1answer
46 views

re-enabling a table for mysql replication

We were able to setup mysql master-slave replication with the following version on both master/slave: mysqld Ver 5.5.28-29.1-log for Linux on x86_64 (Percona Server (GPL), Release 29.1) One day, ...
1
vote
1answer
192 views

MySQL memory usage issues?

I have a MySQL instance running on my Ubuntu server that is eating up most of the memory on the machine. I currently have 2 GB of ram installed on the machine, and MySQL is using 1983 MB. Most of my ...
1
vote
2answers
122 views

Can counting rows be made any faster?

I need to calculate the total number of rows based on a set of criteria: SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a'; The above query takes 0.10 sec to run on a MyISAM table with ...
0
votes
2answers
21 views

How to Identify the user who issued MySQL Shutdown command

We have had several situations when our production MySQL server suddenly became unavailable. The error log just shows "Normal shutdown" followed by the typical shutdown messages. How can I determine ...
0
votes
0answers
12 views

MySql query keep running for a long time

Following query keep running for a long time and I'm using MySql 5.5.34 on XAMPP on Windows SELECT id_package, `pck`.`fk_sales_order`, `pck`.`fk_picklist`, count(pci.id_package_item) as items, ...
0
votes
1answer
14 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
0
votes
0answers
5 views

How is the key length determine is an index?

Aside from explicitly setting the key length when creating an index, how does MySQL determine the appropriate key len shown when running "explain"? Thanks
0
votes
0answers
9 views

Is there any value in adding a pk column to an composite index?

I recently inherited a database with indexes that contain the table's primary key as part of some composite indexes. Given MySQL adds a hidden column representing the PK regardless of whether you ...
1
vote
1answer
55 views

Correct way to write this query?

I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the ...
0
votes
2answers
37 views

What is the best way to upgrade a MySQL version in a Linux Server

What is the best way to upgrade a MySQL version in a Linux Server I have database around 14 GB running with 8 -15 GB ram(diffrent deployments) .At present MySQL version is 5.5 and I’m planning to ...
0
votes
0answers
18 views

MySQL best practice DB recovery

I am trying to figure out what is the best practice is for Mysql recovery after a power failure. I need to find a way that if a server (which is running on a virtualbox VM) goes down when it goes ...
2
votes
2answers
84 views

Why is this MySQL SELECT performing so slowly despite indexing?

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
0
votes
1answer
31 views

Replication in mysql with many servers

There are 3 shops. In each of these is a mysql server. I want all servers to synchronize their date if at least one server changes the date. I read a bit of literature and realized that I need ...
1
vote
1answer
12 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
0
votes
2answers
18 views

Does MySQL have a version of Change Data Capture?

We're in the process of phasing out an old system and migrating onto a new one. The last time that we phased out an old system, we ran both systems in parallel and integrated data between both until ...
0
votes
2answers
38 views

JOIN on variable number of columns

I have the two tables courses AND something_about_courses: courses course_code course_session course_type something_about_courses course_code course_session some_description To ensure that I ...
0
votes
2answers
54 views

MYSQL Optimize tables on Innodb in production environment advice

According to mysqltunner.pl I have 177 Fragmented tables. I have over 5000 tables and 200 databases. If I run optimize table on each of them, do you think I should take down the application to ...
0
votes
1answer
12 views

How to structure renting and sale as type of purchase?

I'm currently planning a database which involves two different types of Products that can be bought outright, or rented for a weekly or monthly amount. I'm just trying to figure out how to structure ...
65
votes
8answers
39k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
2
votes
1answer
661 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 ...
13
votes
2answers
19k views

Multi cores and MySQL Performance

Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
1
vote
2answers
42 views

Do Inactive MySQL Databases Consume Memory?

I have inactive databases that are on the server just for archive purposes. I know I can do mysqldump for all of them, but I am not 100% sure they are inactive. When I started my system I didn't ...
0
votes
1answer
463 views

Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL

I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows. The query ...
1
vote
0answers
19 views

Why is this MySQL query performing slowly despite correct indexing? [duplicate]

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
3
votes
1answer
126 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 ...
0
votes
1answer
48 views

Query multiple rooms with different conditionals (Adults, Children)

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per ...
0
votes
1answer
21 views

InnoDB primary key efficiency

I've read a number of articles regarding efficiency of primary keys depending on the storage engine, and I am left confused. Given a simple many-to-many table with two fields, storeId and zoneId, ...
0
votes
1answer
27 views

How do I access a result set from a StoredProcedure… in another StoredProcedure

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like ...
0
votes
0answers
39 views

update and select is taking over 1.2 seconds to return [on hold]

I am trying to debug my MySQL server which takes up to 1.5 seconds to execute and return a simple update or select command. I have around 600k rows in this particular table and the db engine is ...
-2
votes
0answers
39 views

Unclear deadlock condition messages in MySQL 5.6.17

I have recently upgraded from MySQL 5.6.10 to MySQL 5.6.17 in production server. In previous and in recent version i am getting deadlocks very frequently perhaps in every 5-10 minutes. I have online ...
-4
votes
0answers
12 views

I can't connect with the DB through the php file. [on hold]

i can't transmin from php file,to the localhost db created with phpMyAdmin. I doesn't giv mi any errors,but still,doesn't transmit the informations. The code is simple. from e form with post method, ...
1
vote
1answer
88 views

MySQL with slow queries

I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page. I ...
1
vote
2answers
191 views

MySql InnoDB tables are missing after restarting

I had a MySql database with a few InnoDB tables. After restarting the server, and starting MySql Server again, I connected to the MySql Server using MySql Workbench, and couldn't see the database at ...
0
votes
2answers
53 views

Reset of root password not working?

Our previous DBA left and didn't note the root password down, and I've been pressganged into doing part of his job until we can hire someone. So I create a file : /root/mysql.password Inside that ...
0
votes
1answer
74 views

wordpress table is crashed and should be repaired

I run a wordprees website and use a managed VPS server with cPanel/WHM. Got bellow error log and check the table. I repaired the table with repair table command from phpmyadmin. But this problem occur ...
0
votes
2answers
54 views

How to solve multidimensional data relationship

I'm having a problem, that I could solve programmatically, but for my personal (test) project I'm trying to see if I can fix it in the relational model. Imagine a complex fashion product Pants with ...
0
votes
1answer
26 views

MySQL: Converting Character set fails?

I'm in the progress of migrating my database from latin1 to utf8 character sets/collations. I wrote a simple script that loops through all of my tables and executes this statement: ALTER TABLE ...
0
votes
1answer
12 views

MySQL group concat not showing distinct values when joined to another table

I have one view that gives me comma separated values and want to use that data in another view. In the first view, I am using group_concat( distinct ...) and when I run that view by itself, I can see ...