All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

0
votes
0answers
16 views

Users_With_Messages Table normal form

I have the following tables in my database: Above these tables (not pictured) is a Users table where the SendingUserId and ReceivingUserId are keyed onto (they are foreign keys). Are these tables ...
1
vote
0answers
1k views

Error when syncing MySQL database (XAMPP) on Dropbox

I'm working on a MySQL database for local development using XAMPP, and I would like to work on two different computers. Looking online, I saw this link that gives a tutorial on syncing your database ...
3
votes
1answer
1k views

fetching externally hosted db's table from within the local mysql server

Let's say we have a mysql database on localhost with table A and table B. Is it possible to fetch details from a 3rd party mysql server to which we have login info and which allows external access? ...
4
votes
1answer
623 views

When interrupting a query on a Master, does it replicate to the Slave?

In MySQL, when does the master send a query to the slaves? Here's a specific scenario that I'm trying to understand. Admin entered an update on master that took a long time to execute. Admin hits ...
0
votes
1answer
33 views

MySQL still seems to be establishing a connection even when access is denied, causing aborted connects and instability

Lately the server's been going a bit haywire and we've spent several weeks chasing down leads to no avail. All we can tell is that when the websites go down (and come back up, and go down, and come ...
0
votes
1answer
56 views

How to optimise very simple query with subquery that only needs to execute once

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING ...
0
votes
1answer
853 views

Split MySql table into two tables with One to many Relationship

I have an old database table in MySql. It is used to store the orders from online ecommerce. Simplified diagram looks like this: +----+------+-----------------------+-------+--------------+ | id | ...
1
vote
2answers
497 views

MySQL5.6 on Mac OS X 10.6.8 problems when granting permissions to root

I'm having serious problems with a MySQL 5.6 instance on a Mac Server. We had to upgrade MySQL to a newer version, but it turned to be a bad idea, as we lost control to it. We had a backup of the ...
1
vote
0answers
37 views

How to update all primary keys in the existing database

I'm moving mysql multi-master replication to production using tungsten. I had setup the auto increment and offset values in my.cnf file. To achieve this i'll have to update my all primary keys of all ...
3
votes
2answers
1k views

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb Moving the undo log files outside seemed logical but this procedure seems ...
1
vote
2answers
4k views

Only “grant usage”, but can still select, drop, create?

I have a mysql user (I'll call) "user5" who only has "grant usage" (i.e. no privileges) in the output of "show grants", but can still do a select on database "app_db" (which is what I want, but I do ...
1
vote
1answer
251 views

percona xtrabackup to backup mysql database

I'm trying to use percona xtrabackup to backup a mysql database. in the restoring the database according to the documentation: rsync -avrP /data/backup/ /var/lib/mysql/ this will copy the ibdata1 ...
0
votes
0answers
45 views

Normalization of irregular relations?

I have two tables in MySQL: The orders table CREATE TABLE `orders` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `transaction_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `task_id` ...
1
vote
2answers
668 views

Mysql Connection Pooling Similar To PostgreSQL's PgBouncer

I'm looking for a pooling solution for mysql that is independent of the language. I am using PGBouncer as an example because it Resides on the server the database is on Just change the connection ...
1
vote
1answer
3k views

What is the Best Way to detect if server is a Master inside a Stored Procedure?

I'm want to create a stored procedure/event to do some maintenance tasks. My setups are master/slave configured for failover scenarios (meaning the slave is not used for reporting, just might be ...
6
votes
2answers
3k views

MySQL Locks while CREATE TABLE AS SELECT

I am running the following (dummy) query CREATE TABLE large_temp_table AS SELECT a.*, b.*, c.* FROM a LEFT JOIN b ON a.foo = b.foo LEFT JOIN c ON a.bar = c.bar Suppose the query ...
3
votes
1answer
4k views

mysql table very large size although it doesn't have a lot of records

I have drupal website, one of the tables is cache_form. I have a cron to DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW()); which works fine. The size of cache_form.myd is 4.9G ...
0
votes
1answer
420 views

Unable to migrate local database to RDS instance

I recently created an MySQL instance on Amazon RDS. Unfortunately, I'm not able to connect to the instance from the MySQL command line. I will brief you the details. 1) I want to move/migrate my ...
1
vote
2answers
535 views

MySQL Database replication for high availability

configuration: 1 balancer 2 Azure machines(4 gb ram, 2 cores), with MySQL master-master replication enabled. X users connecting to the balancer to sync data. test: i've tested the MySQL ...
2
votes
1answer
1k views

Is there a product to auto-build indexes based on slow queries?

I'm looking to either find, purchase, or write a script to identify slow queries, identify potential missing indexes, and when needed create the appropriate index within a reasonable amount of time of ...
0
votes
1answer
100 views

Column to Row and Vice Versa

I need to create a new table by combining the contents of two different tables. Is there any way to transform columns into rows and rows into columns in MySQL? I tried using JOIN but I can't seem to ...
0
votes
1answer
5k views

Find the size of each index in a MySQL table

I am trying to figure out size of each index of a table. SHOW TABLE STATUS gives "Index_length" is the summation of all the indices of the table. However if a table has multiple indices (e.g. in an ...
0
votes
0answers
4k views

getting SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (Unknown error code)

I am getting error with MySQL NDB Cluster 7.3.5 as described below: I have two tables: -User Profiles -User Services User services is a child table which contains foreign key constraint ...
2
votes
1answer
297 views

Why am I getting different results from a query vs. a view

The base problem I'm facing is that I need the latest record for each item. A little setup... MySQL 5.6.14. I need to create two views (because MySQL won't let me have a subquery in a view). My ...
2
votes
1answer
6k views

Aborted connection (Got an error reading communication packets) MySQL on Amazon RDS

I'm getting the following warning in MySQL: Aborted connection (Got an error reading communication packets) I'm using Amazon RDS for MySQL hosting. Both MyISAM and InnoDB storage engines are ...
0
votes
1answer
97 views

How To Prevent Replication Failure

If I become a MySQL DBA, will I had to deal all day with those kind of issues or do you have tips to prevent from breaking the whole replication? I received this message because I removed manually ...
1
vote
1answer
89 views

MySQL from table to table data transfer

I've got some problem with my MySQL. I have the database with many many data. Well, there is only one table actually with huge ammount of data, over 1 billion rows. For some reasons I need to create ...
1
vote
1answer
25 views

CREATE TABLE not issued on other masters

Database is created on all multi masters. Why CREATE TABLE doesn't being replicated to other masters. some explanations?
0
votes
0answers
150 views

CREATE VIEW joining two tables, only displaying rows from the first table with the most recent date in the second table

I have multiple tables. My main table has a composite key consisting of a phone number and an order number. I want to create a VIEW where only the subscriptions with the most recent order date are ...
1
vote
3answers
5k views

Is there a free software that can import excel into mysql on mac? [closed]

not csv format files, but .xls or .xlsx format excel files, is there any free software for mac, that can import data into mysql?
0
votes
1answer
87 views

MySQL - Bin folder [closed]

I'm trying to learn all the binary files inside the mysql bin folder,but unfortunately I couldn't able to find the detailed explanation of these.So please tell me the procedure to have good knowledge ...
0
votes
0answers
71 views

Upgrading MySql 5.1 to MySql 5.5

I followed a tutorial to update My MySql 5.1 to 5.5 (actually i wanted to upgrade to 5.6, but the tutorial mentioned that i should upgrade to 5.5 first and then to 5.6). However, after removing ...
1
vote
0answers
219 views

Match unique id's on import- csv to sql

I have a csv file that I would like to import into a phpMyAdmin database (Wordpress). The CSV file is formatted as shown below: list-id | post_title | slider | ...
0
votes
0answers
241 views

Upgrading MySql 5.1 to MySql 5.5

I followed a tutorial to update My MySql 5.1 to 5.5 (actually i wanted to upgrade to 5.6, but the tutorial mentioned that i should upgrade to 5.5 first and then to 5.6). However, after removing ...
1
vote
0answers
41 views

Need help in optimizing a Slow Query

I am using userpoints module to provide points for different actions that they do on my site. mysql> desc userpoints; +-------------+---------+------+-----+---------+----------------+ | Field ...
0
votes
0answers
2k views

mysql takes too long on “checking permissions”

We have about 20 databases with about 8000 tables each. Modifying the software is not an option (it is bought), and it seems most of the cpu is consumed by "checking permissions". While the s/w ...
0
votes
0answers
30 views

MySql cannot get started after increasing buffer_pool_size and log_file_size in my.ini

I try to change buffer_pool_size to 256 MB and log_file_size to 64 MB in my configuration file. After the changes made, I was unable to start MySQL service. System RAM size is (1.99 GB usable)4 GB. ...
0
votes
0answers
292 views

Huge load on CPU because of mysql process [closed]

I am getting a huge load on my linux servers because of this query. CPU is on 500% load & this query is causing load. /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql ...
2
votes
2answers
1k views

MySQL-NT is crashing frequently

MySQL is crashing frequently and there is no error in the err log file. The following error message was found in the event viewer: faulting application mysqld-nt.exe, version 0.0.0.0, faulting ...
4
votes
2answers
16k views

How are auto_increment keys handled in INSERT (SELECT * FROM…)

I have table1 and table2 in MySQL. Both have a primary auto_increment key id. If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows ...
-6
votes
2answers
117 views

MySQL stores 0.00 as 0? [closed]

How can we store 0.00. The column is of float datatype. It automatically converts 0.00 to 0
0
votes
2answers
3k views

How to maintain MySQL performance at high concurrency

The following has been observed using "mysqlslap". mysqlslap -u root -p --create-schema=matrix --query=monthly_kpi.sql --iterations=1 --concurrency=1 --delimiter=";" Benchmark Average number ...
3
votes
1answer
3k views

MySQL crashes frequently

I am facing frequent MySQL crash problem. Not sure how to fix the problem. Attaching the mysql error log. Suggest me a solution. Server Config Digitalocean SSD server 1GB RAM CentOS 6.5 64-bit ...
3
votes
1answer
5k views

Getting unique names when the IDs are different (DISTINCT ?)

I've got this table: id | name 1 | John 2 | John 3 | Peter 4 | Mike 5 | Mike And I want to get the unique names, getting the latest one in the list. I could go for: SELECT DISTINCT name ...
0
votes
0answers
22 views

MySQL crashes frequently [duplicate]

I am facing frequent MySQL crash problem. Not sure how to fix the problem. Attaching the mysql error log. How can I improve the performance? Suggest me a solution. Server Config Digitalocean SSD ...
0
votes
0answers
198 views

Mysql DB recovery

Mistakenly I have deleted "ibdata1" file in mysql server. Now my database is not working. All the tables are corrupted. But still I have "ib_logfile0" and "ib_logfile1" files.Also I have a recent ...
0
votes
0answers
242 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 ...
2
votes
2answers
18k views

Declaring and using variables in triggers

I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem. DELIMITER $$ USE `pucko`$$ CREATE TRIGGER `after_delete_Alien` AFTER ...
0
votes
2answers
82 views

Which database for survey app [closed]

I need to build a very simple mobile survey app. The app will ask for different questions where people can give one of 3 possible answers, which will be always "yes", "no" or "maybe". To save ...
0
votes
0answers
221 views

MySQL: after query optimization the CPU is still over 100%

my first time here! First of all I want to say that I'm not a DBA and I have a very little experience with MySQL but I like to study and understand what I'm doing. I have a virtual machine (2gb RAM, ...