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
1answer
2k views

MySQL crashes with Signal 8 after “Table has no partition for value” error

We have a mysql database running well and suddenly it stops and below are the snippet of the log file. What is the best remedy in this scenario? 130202 1:08:59 [ERROR] Event Scheduler: [root@...
4
votes
1answer
1k views

Make sure MySQL is not accepting remote connections

I am currently doing development for a LAMP-based website. I do not know what configuration changes have been made to MySQL since it was installed on our server. The way that we use MySQL, there is no ...
0
votes
1answer
180 views

compare the same table

I am facing an issue with the following query. When I execute the query, it takes very long. I broke the query into two parts, compared with a shell script, but is there any chance to go with one ...
1
vote
1answer
854 views

MySQL Tables crashing randomly

This is one of the many random Tables that get corrupted. Any ideas why and what would be causing this? How do I keep MySQL tables from crashing and MySQL from crashing? Repairing USR_wp537 ...
1
vote
3answers
10k views

How can I copy data from one MySQL server to another based on a SELECT statement (then delete the data from the original)?

I have a very large log table from which I want to copy rows, putting them into the same table structure on a new server. I don't want to copy everything, only old rows, so that the table on the main ...
1
vote
1answer
386 views

How to select only the number from “show global status like 'Com_insert';”

The title says it all. How can I select only the number result from show global status like 'Com_insert' ?
1
vote
1answer
1k views

Master to Slave to Slave Configuration in MySQL

We currently have 3 MySQL Servers running; the first which contains production data and is due to be retired mysqld Ver 5.5.28-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu)) mysql> ...
1
vote
1answer
492 views

Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

I have a MySQL development server that uses InnoDB tables in some of the databases. It was originally setup with the defaults so all data went into the system tablespace (e.g the main ibdata1 file). ...
1
vote
2answers
217 views

How to Perform Two Simple Queries using Select Value from First Query

I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select ...
5
votes
1answer
3k views

What is the proper way to backup MySQL database with rsnapshot?

I want to backup my website that contains both user uploaded images and data stored in MySQL database such that they are always consistent with each other in any particular point of time. While ...
1
vote
2answers
3k views

MySQL slow queries - Fast using PHPMyAdmin , low CPU and RAM usage

I have a site running on Amazon EC2 , which runs fast and contains some reasonably complex MySQL queries. Part of that site is an API which serves out data to another site. These queries are very ...
1
vote
1answer
575 views

Which Database to choose: MySQL or Oracle

I have an application generating time series data (32 channels, 22KHz, 6 secs) once a minute. These have to be stored in a database. At the moment I'm using MySQL with InnoDB tables (file_per_table,...
2
votes
1answer
482 views

Slow query performance

My database contains 20,000 rows and the following query is slow. Please help me to speed this up: SELECT r.report_id, r.user_id, u.user_name, u.user_mail, d....
1
vote
1answer
5k views

MySQL cannot start

I try to start MySQL /etc/init.d/mysqld start but a get the following messages in mysqld.log 130130 12:12:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130130 12:12:53 ...
1
vote
1answer
3k views

Show processlist / Max concurrent connections seem to max out at 131

When I put my database under a load test (basically just a basic write and basic read from our app), and run show processlist; in the middle of it, there is always 131 rows in the set and that's it. I'...
9
votes
1answer
6k views

Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes

I just started dabbling with the query cache for our CMS. Can anyone tell me (or at least give a good guess) why I get a lot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free? ...
6
votes
1answer
8k views

Dump of a mysql table on import replaced existing records

I took a dump using mysqldump.. mysqldump -u... -p... mydb t1 > mydb_table.sql Then I imported the dump in another database having same table, but different records.. mysql -u...-p... mydb < ...
1
vote
1answer
2k views

ERROR: Error 1005: Can't create table 'progetto.museo' (errno: 121)

I've found on the web that: You will get this message if you're trying to add a constraint with a name that's already used somewhere else and If the table you're trying to create includes a ...
2
votes
2answers
2k views

Slow MySQL query with Order and Group by

I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query: SELECT `user_articles`.* FROM `user_articles` ...
3
votes
1answer
2k views

InnoDB temporary table location

I have a busy InnoDB table that expands and contracts regularly in size on a daily basis based on it's load. Typically, we'd see the table grow to 80GB in size (50/50 data and indexes) and then shrink ...
0
votes
2answers
3k views

How to do something like UPDATE DELAYED in MySQL

I have an averages table that should keep track of an average value over time. I don't want to have a row for each value, just a single row that continuously updates the average. What I've come up ...
0
votes
1answer
526 views

mysqldump not generating USE statement

I've got a small script which allows me to select a database, and a number of tables, then output them via mysqldump. Currently this does not include the USE 'db_name' statement in the output file, ...
0
votes
1answer
2k views

Mysql not closing connections

I have a Java application that configured with a connection pool. I have ensured that the application terminates the connections properly. However when I run this command on mysql command line - ...
0
votes
2answers
287 views

MySQL Cluster - partitions on disk

I have question about MySQL Cluster: Is it possible to have database larger than amount of RAM? (to not have all cluster data in-memory, but some data will reside on clusters nodes hard disks) ...
3
votes
5answers
15k views

Coding an accounting database from scratch? [closed]

My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc. Most ...
1
vote
1answer
409 views

What are the performance implications of creating an index in MySQL?

The MySQL reference guide doesn't (from what I see) detail the implications of creating an index; by this I mean the performance implications and any locks it may take on the table or columns against ...
2
votes
1answer
1k views

Disabling “SHOW TABLES;” on mysql

I have been looking all over the web now, and can not seem to find the option to disable this command. I think this is quite a risky security hole. There is an option to disable SHOW DATABASES; , but ...
5
votes
1answer
14k views

mysql logging activity from specific user or ip

I have mysql server. The server is accessed by my application, and by external auditor (person using mysql workbench). The auditor has specific user and password and dedicated IP and it is granted ...
0
votes
0answers
645 views

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)! in Fedora16

I installed MariaDB server in linux using tar file(mariadb-5.5.28a-linux-i686.tar.gz) followed by the instructions from this link : https://kb.askmonty.org/en/installing-mariadb-binary-tarballs/. ...
1
vote
1answer
48 views

How to change the replicate_*_table variable without modifying my.cnf?

what is the syntax of replicate_*_table without having to modify my.cnf file? and do i need to restart mysqld for changes to be applied
2
votes
1answer
446 views

Is the Memory engine adequate for a read intensive/lookup table?

I have an InnoDB table with just two columns, a VARCHAR(20) and a DATETIME, and around 4M rows. This table serves as a blacklist for other tables, and is truncated and recreated from a csv file from ...
4
votes
1answer
231 views

Performance for fast filling table

I am currently creating a web application using the twitter streaming API and would need some advice concerning my database scheme. I basically have a list of hashtags that I track in real-time. All ...
4
votes
1answer
7k views

How to scale when database gets huge?

I'm creating a project that could get a lot of users very quickly and I wanted to know what is the suggested approach I should consider from the start. I'm developing it with a PHP framework and ...
3
votes
1answer
2k views

Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted). Furthermore I'd like to show ...
3
votes
1answer
6k views

sql error 1241 operand should contain 1 column

This is my query select * from players where sport='football' and position='DEF' and pname!='Binoy Dalal' and pname not in (select player1,player2,player3,player4,player5,player6,player7,...
3
votes
1answer
234 views

Query optimization with extreme statistic

I have a table: mysql> describe table_1; +---------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default ...
3
votes
1answer
483 views

Which MySQL table to use for read-only, bulk-loaded tables?

So I have a workload on 2 key tables in my application with the following characteristics: READ-ONLY DATA The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
1
vote
2answers
589 views

What exactly do database passwords protect?

I would like to know what exactly I am password protecting on my server when I set a database password. I am unclear as to what people are referring to when saying to password protect a database. Are ...
3
votes
2answers
6k views

Query: All employee with sum of salary > 500

In my MYSQL Database COMPANY, I have a Table: Worker : mysql> DESC `Worker`; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----...
2
votes
1answer
767 views

Proper procedure for migrating a MySQL database to another Debian machine?

I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL. I've created a backup of all databases on the first server like so: mysqldump -uuser -...
1
vote
1answer
3k views

Changing InnoDB data directory prevents MySQL service from starting

I have a MySQL instance installed on Windows 7 and I wanted to change the directory of InnoDB tables data. I modified the "my.ini" adding the following line after "[mysqld]": innodb_data_home_dir="/...
3
votes
2answers
2k views

How to add 'root' MySQL user back on MAMP?

On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?
2
votes
1answer
69 views

Query difference with where condition

I need to find the ids of all rows in a table A without a matching row on table B. Following the answer on this question I'm using a left join like this: select A.id from A left join B on B.id_A = A....
1
vote
2answers
15k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
2
votes
1answer
2k views

Select multiple columns in subquery

I am having trouble selecting all columns in a sub query and can't seem to get anywhere with joining for replacement. My Table Data is: CREATE TABLE `w7nwd_com_mtapp_product_engine` ( `id` bigint(...
1
vote
1answer
283 views

Query for localization app

Here are my tables: mysql> SELECT * FROM locale; +-----------+---------+ | locale_id | value | +-----------+---------+ | 1 | English | | 2 | Deutch | | 3 | French | +---...
6
votes
2answers
14k views

What's the difference between POINT(X,Y) and GeomFromText(“POINT(X Y)”)?

I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table. ...
2
votes
3answers
2k views

Problem with nested UPDATE queries

I cannot make an update query work. As a first step, the following one works correctly: UPDATE Tab1 SET Tab1.a = '3' WHERE Tab1.id IN ( 123, 456 ); where id is the primary key of Tab1. ...
1
vote
2answers
2k views

How to partition the table, week-wise?

I have a table with hundreds of thousands of records. How to partition the table, week-wise? A few million rows insertion will be taking place in my table in a day.So i need to partition it weekly. ...
0
votes
0answers
89 views

Configure phpmyadmin to run on different hosts

Just created locally a new host pointing to a new Virtual Host. And added through command line a new user at the new host. So like 'user1'@'localhost' I now have 'user2'@'local2' . The problem is I ...