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

Group By on column from joined table

its about the following query. EXPLAIN SELECT phrase, infinitiv FROM `dict_verbs_all` `t` INNER JOIN `dict_verbs_all_language` `verbsGerman_c` ON (`t`.`id`=`verbsGerman_c`.`verb_id`) INNER JOIN `...
0
votes
1answer
1k views

Modify MySQL foreign key related column without dropping foreign key relation

I need to modify a foreign key related column. When I tried to alter it I got "Error on rename of 'x' to 'y' (errno: 150)" error. So I googled and found foreign key relation is the 'villain'. I ...
0
votes
1answer
264 views

MySQL installation and data location on Windows

I'm gonna install MySQL 5.5 in a virtual Windows Server. I'd like to have the data in an separate partition (it may be in a different virtual disk) than the system one. I'm considering two options: ...
0
votes
1answer
3k views

MySQL: Can't find record in tablename

After crash: mysql> repair table lot_not_sold; +------------------------+--------+----------+---------------------------------------------------------------+ | Table | Op | ...
0
votes
1answer
114 views

mysqlbackup mysql enterprise utility issue

I recent took a backup using mysqlbackup. While restoring it, I noticed that the files that were copied into datadir are with root:root (user:group) instead of mysql:mysql. Is anything wrong with ...
0
votes
1answer
203 views

Where is my old partition in mysql show create table?

I am keep adding daily base partition for a table as e.g. ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) ( PARTITION p20 VALUES LESS THAN (to_days('2012-12-25')), PARTITION ...
0
votes
2answers
339 views

Best Way to Partition By City / State in MySQL 5.5

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so: ALTER TABLE `...
0
votes
1answer
270 views

DBT2 benchmark for AWS RDS

I am new to MySQL and linux and also AWS. Previously i have worked with windows azure and .NET framework. Currently I am trying to get DBT2 benchmark to run for AWS RDS for MySQL. I was able to ...
0
votes
1answer
252 views

Converting dbo file from windows to linux

I have a .dbo file which is created from windows. This file is succesfully reloaded into the mysql database in windows. I need to reload the dbo file into the mysql/mariadb database in linux. How I ...
0
votes
1answer
2k views

Importing CSV file to MySQL via PHPMyAdmin

I am currently stuck and unable to upload a complete CSV file into a MySQL database. Unfortunately, the nice folks at Stackoverflow haven't been able to help me solve this issue, so I'm hoping someone ...
0
votes
2answers
601 views

microsoft webmatrix deleted my wamp databases?

I've been using WAMP server for a while and I have several databases on the mysql server. today I installed microsoft webmatrix and when I opened one of my sites on wamp I got a mysql error saying ...
0
votes
2answers
2k views

How can you monitor if MySQL binlog files get corrupted?

I was wondering if there was any nice way to continuously monitor mysql master binlog files and detect if get corrupted.
0
votes
1answer
792 views

Replication master binlog rotation when network is unavailable

I recently experienced an issue where the binlog file in master rotated because network connectivity between the slave and master was unavailable. After solving the network issue, the slave was not ...
0
votes
2answers
617 views

MySQL Benchmark on FreeBSD and Ubuntu [closed]

I'd like to benchmark two db servers, one is running Ubuntu the other FreeBSD. Unfortunately we are unable to install SysBench on the FreeBSD OS. Could anyone recommend any alternatives? Or should I ...
0
votes
2answers
1k views

How to run a cold backup with Linux/tar without shutting down MySQL slave?

I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
0
votes
2answers
209 views

Database setup/design for multiple services

I am working on a new MySQL database that will store orders/customer information for around 15 different services. 7 of the services are similar in regards to the information that is stored with a few ...
0
votes
1answer
1k views

Too many records on database and how to handle it? MySQL is it good to clone?

I am doing a social network and came up with the following concern regarding database storage. Let's put facebook as an example, imagine 2,000 photos per user, just 1,000 users, it would equal to 2,...
0
votes
1answer
42 views

Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

I am trying to create a new table new_table from t1 by comparing primary key values of another table t2 ie, 1) created table new_table 2) gave the query insert into new_table select a.* ...
0
votes
2answers
175 views

Understanding the use of Sql command parameters

I am using MySql database and I have a stored procedure to insert data, like this- CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`( IN id varchar(10), IN pwd varchar(10), IN fname ...
0
votes
1answer
336 views

SQL Table Indexing - Database Overloaded With Queries

I'm a bit of a newbie to SQL who has been thrown in on the deep end with a server that gets about 2 million queries an hour. I'm trying to optimise our database and I noticed this in our database ...
0
votes
1answer
946 views

MySQL daemon keeps terminating unexpectedly

The MySQL daemon on my CentOS server keeps crashing, i got the logs from /var/logs/mysqld but still i am not sure how to fix this: 121114 16:22:56 mysqld_safe mysqld from pid file /var/run/mysqld/...
0
votes
1answer
70 views

What is an accepted method of keeping old data in MySQL?

I think this may be more of a programming question, but since it directly relates to MySQL, I wanted to ask it here. Some background: I am running an application whereby users can take tests using ...
0
votes
2answers
1k views

Keep total transaction in invoice application

I want to make an application called as Invoice Application with MySQL database. Now I have designed this database. Now I want to know is this database good for Invoice Application. I will use MySQL ...
0
votes
1answer
635 views

INNER JOIN between 2 tables vs WHERE on one table performance

Suppose I have two tables: CREATE TABLE `doctors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `clinic_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; ...
0
votes
1answer
169 views

What MySQL storage engine and table schema should I use?

I am making a web project and I created necessary and the most important table, but I am not sure that I did it right. In this site users will post data like text, picture, text's category, etc. I ...
0
votes
1answer
150 views

MySQL query optimization

I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be ...
0
votes
1answer
38 views

Mysql database connector table or keys

This may be a simple a question but I just wanted to clarify, at work I have a table containing a list of titles and I also have a list of authors in two different tables. What would be best way to ...
0
votes
1answer
1k views

MySQL query with an OUTFILE to csv not working when called from a batch file

When I execute the following query, it works: SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; But when I ...
0
votes
1answer
410 views

how to pass url value to index [closed]

How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next ...
0
votes
1answer
404 views

Most efficient way to structure segmented data

I am faced with a set of data which I am currently unsure on how to store efficiently. The data in question relates to a calendar which stores information on two different categories of event which ...
0
votes
1answer
1k views

“Connection to host lost” using telnet to MySQL

I am trying to setup MySQL Workbench to manage a remote database and I keep getting "waiting for initial communication packet" error. So I try to telnet to the box on port 3306, I get in, but then I ...
0
votes
1answer
177 views

Handling error of foreign key

How to set a FK value NULL upon receiving error for foreign key constraint? Consider a simple table a CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT ex_key int(11), FOREIGN KEY(...
0
votes
1answer
103 views

SQL Query for the following structure

Given the following type of data structure. ID NAME TREELEFT TREERIGHT PARENT 1 XYZ 1 2 0 2 ABC 3 6 0 3 DEF 4 5 ...
0
votes
2answers
2k views

how to estimate rebuild index on 140 million records?

My existing project has data of about 140million records in Contentq table. And I have planned to rotate the table just incase not to face any load on the table. So I have planned to rotate data older ...
0
votes
1answer
2k views

How can I enable my-huge file on windows server?

I installed Windows Server 2008 and MySQL 5.5. Now I need configure MySQL for huge InnoDB tables and a lot of connections. I see that on my Windows Server, there is a my-huge file(configs files that ...
0
votes
1answer
140 views

What is stored in mysqld-slow.log?

My MySQL log directory contains the following files: 7.9K mysqld.err-old 4.0G mysqld-slow.log 94 mysql.log 93M mysqld.err mysqld-slow.log contains a long list of queries with some ...
0
votes
1answer
2k views

How to protect mysql database from sql-injection?

I am new to databases and security. I am planning to put up a website. I've read that sql-injection vulnerabilities can be very dangerous since the server might leak user passwords and credit-...
0
votes
1answer
470 views

Combining a tree structured Group model with its HABTM User model in one VIEW

I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a Has-and-...
0
votes
1answer
60 views

Searching through lots of applications

Google search has instant search feature. While you are typing, it finds the pages. Apple.com search box. StackExchange ask question part finds similar questions while you are typing question title. ...
0
votes
1answer
377 views

What would be possible reasons for a DeadLock to occur in Mysql Database?

If some one has a Table structure some thing like this, CREATE TABLE IF NOT EXISTS `domain_source` (`nid` int(11) NOT NULL DEFAULT '0', `domain_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`nid`) ) ...
0
votes
1answer
3k views

Db design for a quiz with variable number of answer choices, and each worth different score in a different category?

I'm working on a question/answer project and having a hard time coming up with a suitable database structure. Each question can have from 1 - "n" answers, and each answer is associated with one of ...
0
votes
2answers
3k views

MySQL database backup every day using java code

We are developing a Java web application using Spring, Hibernate and MySQL. I want our application to automatically backup the MySQL database daily; is there any way to accomplish that with Java code?...
0
votes
2answers
3k views

MySQL how to release memory used up by SELECT

I executed an INSERT IGNORE combined with SELECT from large table. The SELECT took up 16GB of memory and went into SWAP before I terminated it with CTRL+C. INSERT IGNORE INTO sid(sid) SELECT sid ...
0
votes
1answer
264 views

How to simplify schema for multi database / hundreds of millions of rows data set

We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month. The people that developed our system started ...
0
votes
1answer
11k views

Exporting MySQL Database into single SQL File in TOAD

Using TOAD, is it possible to export an entire MySQL database to a single file?
0
votes
1answer
2k views

MySQL Innodb: What is the difference between running 'mysqlcheck --optimize --all-database' compared to running 'optimize table' in the mysql client?

Without having to script an 'optimize table' query to do all the databases/tables, we would like to run mysqlcheck --optimize --all-database instead. What are the drawbacks of doing this through ...
0
votes
1answer
50 views

Does a numeric field always use the same disk space?

When you have a numeric field in MySQL(MYISAM) (E.g. Integer) will it always use the same disk space, no matter what number is actually stored?
0
votes
2answers
113 views

Tiered permissions to generate conditional data access

I am working to improve the confidentiality and access level of the information presented within a program interface over top of a MySQL database. My goal is to create a two tier system: one based on ...
0
votes
1answer
893 views

InnoDB - Use combined index with primary key on GROUP BY

As far as I know, an InnoDB index always contains the primary index. I cannot get the following to work: SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid WHERE t1.b = 99 GROUP ...
0
votes
1answer
3k views

Converting Python's date format to a MySQL

First of all I hope this is the right place to ask. I have a Python script which parses data from a few links. In the parsers I parse also the date of the last modified article. This is the output ...