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
378 views

Postgres vs MySQL Database Maintenance

I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as ...
0
votes
0answers
49 views

MySQL beginner in troubles

My MySQL knowledge isn't really good. However, I tried to move my websites media folder to an other location and afterwards wanted to update the links in my database: UPDATE wp_posts SET ...
23
votes
6answers
37k views

SSD vs HDD for databases

I am trying to purchase a new Server to run MySQL Server on. This new server will be a slave of my main machine. However, this server will be dedicated for reporting only "Lots of reads and complex ...
-1
votes
1answer
909 views

Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows (...
1
vote
1answer
1k views

Error 1005: Can't create table can't find where the problem is

I have created a EER model using MySQL Workbench (latest release). Apparently all is fine but when I try to do a FORWARD ENGINEER I get the common Error 1005: Can't create table. As suggested in this ...
1
vote
2answers
162 views

Execute SQL query after execute TRUNCATE in a table

I have a table called fos_user_user where I have a default record "always". Since I'm testing all the time and sometimes I need to clean that table I'm asking if there is any way to run the following ...
2
votes
1answer
2k 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 ...
-5
votes
2answers
464 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly 2,5,...
0
votes
0answers
84 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE a....
-1
votes
1answer
27 views

Mysql InnoDB exsited but can't show it

I have a InnoDB table A , While I type "show tables" , I can't see it. Even I type "drop table A" , it responses "table doesn't exist" . While I create a table B which refers from A , table B can ...
0
votes
2answers
75 views

Query to test and return timed lockout in MySQL

I have some functionality in my webapp that locks out users from certain behavior for a set amount of time (e.g., they have to wait 10 minutes before they can update a level). My query looks like ...
0
votes
2answers
193 views

Table size reduce with help changing collation charset [MySQL]

I have table which is big enough, phpmyadmin shows that it takes more than 40Gb space. I can't remove data from it, but I've figure out one thing: there is one field which is described as varchar(...
0
votes
1answer
35 views

Managing deleted entries in large database table

Currently we are using Mysql 5.6 with Amazon RDS. We have table tracking uploads and we have a deleted flag in this table, we don't delete rows when a user subsequently deletes an upload. We do this ...
0
votes
1answer
327 views

Is there a more efficient way of using a match score in MySQL full text search?

SELECT i.*, MATCH(i.description, i.keywords) AGAINST ("searchterm") AS score FROM items AS i WHERE i.instock=1 AND MATCH(i.description, i.keywords) AGAINST("searchterm") ...
1
vote
2answers
104 views

How do I add an alias to the table

SELECT MAX(COUNT), A.id FROM ( SELECT supplier_id as id, COUNT(*) AS count FROM `library`.`lms_book_details` GROUP BY supplier_id ) GROUP BY A.id; While executing the query, MySQL gives ...
0
votes
1answer
228 views

Help With MySQL Computed Columns/Auto Updating Field Based On Average

I am currently building a social network type application on-top of MySQL and have run into a problem I can't wrap my head around. In my web application each user has a field called "weight" and this ...
0
votes
2answers
281 views

MAX from Subquery [closed]

How do I find the maximum value of a particular column from the result of a SubQuery? For example, if the result of the subquery is: 10 20 30 How do I get maximum from this, in this case 30?
0
votes
1answer
1k views

Split values from field and count them according to another field

I have following MySQL db structure: content ======= id | tags --------- 1 | ,abc,def,ghi, 2 | ,abc,foo, 3 | NULL 4 | ,foo,bar, categories ========== id -- 1 2 categories_to_content ==========...
1
vote
2answers
91 views

MySQL foreign key error

I need to update rows in a child table,but I am getting following error while I run the code, Company Table CREATE TABLE `company` ( `Company_id` int(11) NOT NULL AUTO_INCREMENT, `Company_name` ...
0
votes
2answers
781 views

Is a temporary table a good solution for importing temporary data into a database

i am facing the following scenario. I have a student database in which exam results are stored. Every now and then i get a list with the names of certain students for which i need to find out how well ...
0
votes
0answers
63 views

Two different unique constraints for 1 table?

I must enforce uniqueness within my table, But I cannot seem to find anywhere online(mysql manual,forums,stack overflow) talk about two unique constraints within one table. Why you might ask I need ...
1
vote
1answer
62 views

How can I optimize this query?

I'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query: ( SELECT `time` FROM `eventlog` WHERE userid = '...
-1
votes
1answer
355 views

Problem with intersection table

I seem to have came to a problem which I am having trouble solving, possibly because I have misunderstood intersections properly(or perhaps I shouldn't even use intersections for the goal). If we ...
0
votes
0answers
456 views

Mysql replication broke after mysql upgrade on master

I've seen this question, but I am not sure if the solution is right for my particular error (replication breaks after upgrading master) I upgrade my master server's mysql from 5.5.34 to 5.5.35. After ...
1
vote
1answer
545 views

XML dump of MySQL database don't include foreign key constraints. What can be the reason? sqldump(.sql) file list it

I am accessing schema information of databases in XML format. I am taking dump of the database in XML format using the command below. mysqldump --no-data --xml -u root -p bakerydb > bakerydb.xml ...
2
votes
4answers
2k views

Getting 0KB of size file while backup mysql database using mysql dump [closed]

I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions? $dbhost = 'localhost'; $dbuser = '...
4
votes
1answer
740 views

Online Schema migration in MySQL : When to start?

I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database. We're having a single MySQL db, and besides many ...
12
votes
1answer
8k views

changing mysql table comment

i know that: mysql table comment can be defined while creating it: create table (...)comment='table_comment'; and how to display it: show table status where name='table_name'; and i know how to ...
1
vote
0answers
93 views

crossplatform mysql_install_db script in mysql 5.5.34 on Windows

I downloaded mysql-5.5.34-win32.zip and found out there is mysql_install_db.pl script. However mysql 5.5 documentation says: mysql_install_db is a shell script and is available only on Unix ...
0
votes
2answers
802 views

How to change the path of data during database restore + SQL Server 2008

I am trying to restore a big database in SQL Server 2008 R2. The database is about 700GB in size. My SQL Server is installed in C drive which has only 100GB of space left in it. I have the .bak file ...
1
vote
2answers
312 views

Questions since MySQL Startup

Have a weird question / (bug?) here. Using PHP My Admin I installed via apt-get on Ubuntu 13.10 Versions are as follows: Server version: 5.5.35-0ubuntu0.13.10.2 - (Ubuntu) Apache/2.4.6 (Ubuntu) ...
0
votes
1answer
60 views

Maximum normalization

I have been studying the normal forms rules to guide me with database design and data storage, the problem I am having is that once I optimize for the first normal form (1NF), I figured I may well ...
1
vote
1answer
487 views

Mysql trying to recover from Hard disk failure [duplicate]

Am trying to get a database up and running after having a hard disk crash, got the data recovered. mysql can only start with the innodb_force_recovery set as 5 or 6. When started I can get a table ...
0
votes
1answer
290 views

Is there any way to trim data automatically

Lets I have a table named myTable in mysql with Three field 1. id int autoincrement 2. product_id varchar(20) not null 3. product_name varchar(200) not null Now I want to keep data in product_id ...
0
votes
1answer
97 views

Repair table field: Import/append records from specific backup field if they are not null

One of my 3rd party applications that writes to my production MySQL database mydb has nulled SOME of the data in one of my fields myfield in table mytable. I'm trying to import only the NON null ...
0
votes
1answer
46 views

Running several versions of a MySQL database in parallel?

We have a central read-only MySQL database that contains geo data, etc. It is used by various projects. As part of a refactoring, I am thinking about setting up a system for versioning the database. ...
0
votes
1answer
46 views

Couldn't connect to databasee

I have a dedicated webserver and a dedicated database server Often , i got errors connecting to database server couldn't connect to database Here is my current /etc/my.cnf configuration file [...
0
votes
0answers
550 views

Issues with mysqldump after upgrade 5.5 -> 5.6

A few months ago I updated a MySQL setup from 5.5 to 5.6. Since then I've been having problems with a script that I use to dump the various databases so that I can back them up. The script is a short ...
1
vote
1answer
1k views

InnoDB to MyISAM Conversion Problem

I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value ...
2
votes
1answer
7k views

select based on sum of rows compared to another field value

I have a table of invoice items. A single transaction may result in multiple debit items and multiple credit items sharing the same invoice_set_id, I need to compare the sum of the debit items against ...
1
vote
1answer
3k views

Compressing Base64 Encoded Image in Database Field. Table Size too Large

Database Info Database Type - Percona MySQL. Table engine - InnoDB. I have a database table that is currently using 17GB at 400,000 rows. This size is mainly because I'm storing a 40kb base64 ...
4
votes
1answer
11k views

MySQL: Migrating database with utf8 collation and charset but latin1 data to new full UTF-8 database

With my current situation I have mysql database with collation and character sets on database and tables set to utf8, but all data is still latin1. This is due to server being configured (my.cnf) with:...
1
vote
2answers
844 views

How can I repair a TokuDB table?

I have a TokuDB table that works fine for most queries, but segfaults on others. I did a CHECK TABLE which indicated that the table had errors. However, TokuDB doesn't support REPAIR TABLE. How can I ...
3
votes
1answer
5k views

Multiple “root” users in MySQL

Mysql is showing 5 root users: | root | % | | root | 127.0.0.1 | | root | ::1 | | root | localhost | | root | mysrverhostname | What are these 5 for? Are there some I can ...
0
votes
1answer
526 views

CHROOT install after I already installed Mysql 5.6

I installed some time ago Mysql 5.6 on Ubuntu server 12.04 and now I'd like to enhance security by putting mysql into a CHROOT environment on that same Ubuntu server. Is this possible without re-...
16
votes
3answers
37k views

How to make MySQL table name case insensitive in Ubuntu?

I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default. Now, I want to make MySQL work as case ...
0
votes
0answers
13 views

MySQL: Updating a column referenced by two foreign keys in the same table leads to foreign key constraint failure [duplicate]

MySQL 5.5 I have a simple InnoDB table: CREATE TABLE `T` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `deleted` bigint(20) unsigned NOT NULL DEFAULT '0', `other` varchar(32) DEFAULT ...
1
vote
2answers
469 views

MYSQL Master-Slave replication not working with PHP

My data gets replicated from the master database when I do it manually but when it gets inserted from PHP into the master database it doesn't get replicated to the slave database. My Master database -...
0
votes
0answers
3k views

MariaDB MySQL server does not start

My MariaDB mysql server doesn't start with error 1067 This is the error log: 140222 0:43:44 InnoDB: The InnoDB memory heap is disabled 140222 0:43:44 InnoDB: Mutexes and rw_locks use ...
0
votes
0answers
894 views

How to recover a crashed MyISAM table which myisamchk couldn't recover?

We take db backup by stopping the database and then issuing a tar -zcf on the mysql data directory. When we restore the data directory on another machine and try to start the slave, the mysql error ...