Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [mysql]

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.

1
vote
2answers
754 views

Sizing MySQL Database Servers for Drupal

What are some guidelines to follow when it comes to sizing MySQL Servers? CPUs, RAM, Disk Space, Clock Speeds, etc? We're going to go with either the community edition or the enterprise edition, and ...
6
votes
3answers
14k views

MySQL gives “Out of Memory” error when inserting a large file. From where is this filesize limitation arising?

I'm using a MySQL database to store files. The table I'm using is structured as follows: +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default |...
3
votes
1answer
74 views

Database appearing in one tool but not another

Can anyone think of a reason why a mySQL database would be accessible from one tool (phpMyAdmin, logged in as db_user) but not from another (Sequel Pro, logged in as db_user). The user is the same in ...
2
votes
1answer
2k views

No results for match…against in boolean mode search with fulltext index (MySQL)

We have a column with a fulltext index, and are matching 'asp.net' against the column in boolean mode. We know there are records where this column contains the value 'asp.net', but the query is ...
6
votes
3answers
21k views

How do I remove backslashes from records in MySQL

Long story short, I have backslashes in my data due to PHP magic quotes. I have straightened out what was wrong there, but have some records now which have unnecessary backslashes. 1 | This is an ...
4
votes
3answers
3k views

Connecting to MySQL database from Analysis Services

I need to know whether it is possible or not to get data from a MySQL database and create a Cube in Analysis Services 2008. If so, tell me how to connect to MySQL Database from Analysis Services.
22
votes
3answers
7k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
6
votes
3answers
2k views

How can I import/export a MySQL database without redirection?

I've been told that redirection via shell (e.g. < and > ) can (not always) affect the collation of a database (.e.g utf8). How can I import an export without redirecting so that I don't have to ...
6
votes
3answers
11k views

Avoiding temporary tables while sorting by a column in a different table

So I have this MySQL InnoDB query here: SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt FROM stories s LEFT JOIN votes v ON s.vid = v.vid WHERE s.lv = 1 AND s.status = 1 GROUP BY s.vid ...
2
votes
1answer
2k views

Use order from another select clause

So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one. I have this statement to get the vid column order ...
3
votes
1answer
474 views

MySQL export/import yields error [duplicate]

When trying to export and import an 58G database yields the following error: ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes Dump created using mysqldump -q --opt ...
2
votes
2answers
2k views

How to get a user's friends' names?

I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow ...
6
votes
2answers
1k views

How to retrieve granted privileges without access to the database “mysql”?

As a MySQL user without access to the mysql database, is it possible to retrieve my privileges?
5
votes
2answers
1k views

More table or more database for better performance?

Lets say 1 website need 10 database table to work and i want a database pool that 40.000+ website will be inside. Which option is better for server performance? 1) 400 databases each have 1000 table?...
2
votes
2answers
14k 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 ...
5
votes
3answers
861 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
1k 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), ...
8
votes
1answer
10k 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 ...
4
votes
2answers
7k 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 server....
16
votes
3answers
38k 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 ...
8
votes
4answers
7k 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 ...
5
votes
1answer
5k 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 ...
9
votes
4answers
38k 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) ...
3
votes
5answers
2k views

Best way to automate running several MySQL commands [closed]

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 ...
7
votes
3answers
601 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 tag_post.post_id=...
3
votes
1answer
258 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
3k 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', NOW(...
25
votes
7answers
36k 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 ...
5
votes
1answer
3k 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.
35
votes
6answers
25k 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? ...
14
votes
5answers
120k 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 ...
8
votes
1answer
231 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 "...
6
votes
2answers
6k 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?
7
votes
4answers
2k 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 ...
23
votes
7answers
15k 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 ...
6
votes
2answers
1k 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` ...
11
votes
4answers
10k 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 ...
20
votes
1answer
12k 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 ...
6
votes
3answers
1k 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 ...
35
votes
1answer
92k 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 ...
9
votes
4answers
12k 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` ...
3
votes
1answer
154 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?
8
votes
2answers
5k 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 ...
4
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 ...
1
vote
2answers
832 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 now,...
6
votes
1answer
16k 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 ...
3
votes
1answer
1k 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 ...
7
votes
2answers
12k 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 ...
3
votes
3answers
260 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 ...
6
votes
1answer
5k 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 ...