Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
2answers
6k views

Restore MySQL DB from its folder

Some time ago, I've built an webapp on top of WAMP server. At that time, I didn't do any export/dump of my MySQL DB but I've did a copy of the WAMP folder. Does anyone have any ideas about how I can ...
4
votes
3answers
628 views

Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?

Is it possible to create an InnoDB table without a Primary Key? Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that ...
4
votes
4answers
966 views

Is tuning the innodb_buffer_pool_size important on Solaris ZFS?

We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), ...
5
votes
1answer
5k views

MySQL high availability, failover and replication with Latency

We are in the process of implementing a new CMS (Drupal 6.x) that runs on MySQL. We have two data centers -- primary and secondary -- with known latency between them. We're unsure which version of ...
1
vote
1answer
2k views

One MySQL user from 2 hosts not on same subnet

I've googled around for this with no luck. I have a master database with one slave. The perms for the slave are set and it runs fine. I'd like to add another slave using a clone of the original ...
7
votes
2answers
12k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
7
votes
4answers
4k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
3
votes
1answer
3k views

How do I make MySQL auto increment in random order?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
7
votes
4answers
15k views

Why does MySQL say I'm out of memory?

I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception: Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes) ...
1
vote
4answers
452 views

Best way to automate running several MySQL commands

We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see. We created these views in a new db specific to ...
5
votes
3answers
362 views

MyISAM Performance: Join Decomposition

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones: Converting SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON ...
2
votes
1answer
238 views

How do you convert IBM DB2's recursive increment to MySQL version?

DB2 Queries: WITH TABLE1 (YEAR_END) as( VALUES (2011)) , TABLE2 (YEAR_END) AS ( VALUES(2011)) , TABLE3 (YEARS) AS ( SELECT 0+2008 FROM TABLE1 T1, TABLE2 T2 union ...
4
votes
1answer
2k views

nest SELECT in INSERT

I'm trying to do something like: INSERT INTO stories (vid, ip, body, timestamp) VALUES ( (SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1), INET_ATON('127.0.0.1'), 'test', ...
13
votes
3answers
7k views

What are reasons *not* to use the MEMORY storage engine in MySQL?

I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go). It seems like this option should give me ...
2
votes
1answer
2k views

Migration from DB2 to MySQL

Does any one have experience on migration from DB2 to MySQL ? Please share with me. I tried googling, but I have not received much information on this.
14
votes
5answers
6k views

In MySQL, does the order of the columns in a WHERE clause affect query performance?

I am having performance issues on certain database queries that have large possible result sets. The query in question, I have three ANDs in the WHERE clause Does the order of the clauses matter? ...
6
votes
5answers
23k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
7
votes
1answer
203 views

Are relations slower than a big, inefficient table?

I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a ...
4
votes
2answers
3k views

Determine Reason for Deadlock in MySQL

We've been seeing some deadlocks occur but have not been able to reproduce. Is there any way to determine, after the fact, what queries were running, etc to figure out why deadlock occurred?
6
votes
4answers
832 views

When to use MySQL query_cache?

Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a ...
15
votes
7answers
6k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
2
votes
2answers
561 views

Is there a way to have MySQL GROUP BY utilize trailing whitespace?

I recently had an issue come up where I noticed that MySQL would ignore trailing whitespace when issuing a GROUP BY. Here's the table: CREATE TABLE `myTable` ( `id` int(10) unsigned NOT NULL, `foo` ...
9
votes
3answers
5k views

Installing default MySQL Tables in Windows (mysql_install_db)

In Linux, the default MySQL grant tables can be created with the mysql_install_db script, but that does not work on Windows. How can the default grant tables be installed on Windows? (No, I’m not ...
9
votes
1answer
6k views

Clarification on MySQL innodb_flush_method variable

Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in ...
5
votes
3answers
896 views

MySQL client believes they're in a transaction, gets KILLed, wreaks havoc

Open up two mysql command-line clients and connect to your database. In client #1, enter START TRANSACTION; In client #2, use SHOW PROCESSLIST, then KILL [n]; where n is the id for client #1's ...
13
votes
1answer
21k views

How long is “too long” for MySQL Connections to sleep?

I logged onto my server to check the status of the database and noticed on the SHOW PROCESSLIST; that there are a large number of connections sleeping that are very old. What duration should the ...
6
votes
4answers
7k views

How to improve InnoDB DELETE performance?

So I have this audit table (tracks actions on any table in my database): CREATE TABLE `track_table` ( `id` int(16) unsigned NOT NULL, `userID` smallint(16) unsigned NOT NULL, `tableName` ...
1
vote
1answer
124 views

Is there any tool to convert Oracle commands to MySQL?

Is there any tool which I can use to convert Oracle commands to mysql commands?
6
votes
1answer
2k views

COUNT(*) on InnoDB whenever phpMyAdmin loads

It's a known issue that innodb is slow in SELECT count(*) FROM Table Hence I avoided that. But I notice that whenever I login to phpmyadmin, it will automatically execute a count(*) for my innodb ...
3
votes
2answers
2k views

MySQL 5.5.9 Wont Start

I installed mysql version 5.5.9 on my mac and I tried to start it using this command : sudo /usr/local/mysql/support-files/mysql.server start mysql didn't start with this command. I checked the ...
0
votes
2answers
494 views

How wrong is my web app modeling schema?

So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years ...
5
votes
1answer
8k views

64-Bit OLEDB Provider for ODBC (MSDASQL) issues

I need to create a Linked Server from SQL 2008 SP1 64 bit instance located on a Windows 64 bit 2003 SP2 server towards a Mysql Server. As many of you know MSDASQL provider for 64 bit systems was put ...
2
votes
1answer
694 views

Query between two related tables

I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are ...
4
votes
2answers
3k views

MySQL Database Schema for Train Timetables

I am interested in what a MySQL database schema would look like for a train timetable. Generally this is presented in table format as the final result. Train No. 11111 22222 11111 Train Day ...
2
votes
3answers
239 views

multi-table mysql query

I am trying to make a multi-table query that I am not quite sure how to do properly. I have User, Message, Thread, and Project. A User is associated with Message/Thread/Project as either the Creator ...
5
votes
1answer
3k views

MySQL INNODB SELECT query hanging on copying to tmp table

I have a query that is hanging (seemingly) randomly on a specific query. Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website ...
2
votes
1answer
267 views

databases/tables with nonconventional names

Disclaimer: not an administrator How does one deal with non-kosher names such as 'data?base' ? I'm always one to ensure the cleanliness of my databases, but after finally installing phpmyadmin to ...
6
votes
3answers
2k views

Problem with MySQL subquery

Why does this query DELETE FROM test WHERE id=(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); sometimes delete 1 row, sometimes 2 rows and sometimes nothing? If I write it in this ...
0
votes
1answer
203 views

How to design databases? [closed]

Describe the process in steps, please. What tools do you use for it?
7
votes
3answers
2k views

What Mechanism does MySQL use to schedule Backups on Windows Server 2008?

When scheduling database backups on MySQL Server (v5.1.45-community) running on Windows Server 2008, what mechanism is used to trigger the backups to run? I think it's using the Windows Task ...
11
votes
3answers
4k views

Why does importing a 12 GB .sql file take more than 36 hours?

I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB. Considering the ...
2
votes
3answers
3k views

Script to Monitor MySQL Replication

G'Day, I am looking for a bash script (using Perl - As that is already installed on the machine and I don't want to have to install Ruby, Python etc. just for this requirement) that will perform some ...
2
votes
3answers
1k views

How do I get % progress for “type db.sql | mysql”

I am doing: type db.sql | mysql and the console output is nothing. I see the ibdata1 file growing, but I don't know how many GB it will grow to. I would like to see a % progress indicator of some ...
3
votes
1answer
350 views

MySQL Config on Mac

I have a MacBook pro with 2.53 GHz Intel Core 2 Duo and 4GB DDR3 ram. I am running a huge database on my laptop with over 100 million records. I want to know : Which of the .cnf files is suitable ...
43
votes
4answers
49k views

How to safely change MySQL innodb variable 'innodb_log_file_size'?

So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB: ...
3
votes
1answer
413 views

MySQL Database Schema for Volleyball

I am developing a database for the recording of Volleyball results. I have a fixture database that tells me who plays who in each round. Teams Table, say IDs 1,2,3,4 Fixtures Table, Round No.1, Team ...
18
votes
3answers
14k views

How do you calculate mysql max_connections variable?

How do you calculate mysql max_connections ? What do you take into consideration ?
4
votes
2answers
1k views

After I kill my clients, MySQL continues to process queries for hours

Hi I have a MySQL db with 10 million rows with lots of triggers. A php client connects to mysql and INSERT's hundreds of rows per second. When the client has been running for hours, and I terminate ...
2
votes
1answer
579 views

Question on Design for User + User Groups + User Types + Subscriptions Model

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, ...
5
votes
2answers
973 views

MySQL: What is the maximum number of cores used by a single MySQL server?

What is the maximum number of cores used by a single MySQL server in a production setting?