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

3
votes
1answer
2k views

How to determine MySQL queries per day?

I'm investigating the big switch from MySQL to a NoSQL DBaaS and I've run into an issue trying to forecast expenses. Essentially, I can't figure out how many queries my current MySQL server handles ...
0
votes
1answer
860 views

MySQL: What is SQL mode and why we use it

If in an application, if we execute sql_mode=mysql4.0 as a first query in my application but using MySQL 5.1 in my application then what will happen? Will my system use MySQL 4.0 or MySQL 5.1 ...
1
vote
2answers
397 views

Why does MySQL + Windows Server = crashed tables periodically?

Windows Server 2008 R2 x64 MySQL Server 5.6.14 x64 Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by ...
0
votes
1answer
54 views

Order result by number of occurence of a word in a text

I am working with sqlite3/python . I would like to order results by the number of occurence of %@% in text. For exemple Jimmy 10 // Jimmy wrote 10 strings like %@% James 2 Sophia 0 ..etc The ...
1
vote
1answer
268 views

How to calculate average of list of qualifications of each pupil MYSQL

I have this database and query: http://sqlfiddle.com/#!2/b4cf72/116 And I would like to add a column with the average of the exam qualifications. However, I try doing this but it tells me there are ...
4
votes
2answers
5k views

Error Querying MySQL server linked to SQL Server 2008

I've created a linked server in MS SQL Server 2008 to a remote MySQL server. When I try to query any tables, I get an error: .tablename. contains no columns that can be selected or the current user ...
15
votes
4answers
3k views

How to create a conditional index on MySQL?

How to create an index to filter a specific range? AFAIK it's impossible to create directly but I think it's possible simulate this feature. Example: I wanna create an index for NAME column just to ...
1
vote
2answers
118 views

Do I need a locking read?

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
1
vote
1answer
3k views

Auto Increment Primary ID inserting random values

Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird ...
2
votes
2answers
1k views

Should I replace my varchar primary key with an integer primary key?

I have a table that has a varchar primary key that's associated with "Base Product Codes". They rarely change, and if they do, it's usually just being deleted or added. I figured it would be better ...
1
vote
0answers
182 views

#1046: No database selected in phpMyAdmin

I have a problem with phpMyAdmin. When I click on any database on the left I get an error like: SELECT tracking_active FROM.`pma_tracking` WHERE db_name = 'information_schema' AND table_name = ...
0
votes
0answers
141 views

non-optimized regex and substr with injected list in mysql query

edit: I can replace phone, -10 (that is last 10 digits of phone ) with full phone digits. on tip i got is SELECT….FROM… WHERE phone IN (…) UNION SELECT … FROM … WHERE email IN (…) edit end I am ...
1
vote
1answer
273 views

select items with one or more specified tags

I have two tables. One table holds tags: CREATE TABLE `tags` ( `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(30) NOT NULL ) COMMENT=''; INSERT INTO `tags` (`id`, `name`) VALUES (1, ...
0
votes
1answer
157 views

Is it possible to format SQL on output into associative array?

We have a table. customer +----+------+------+-------+ | id | type | name | group | +----+------+------+-------+ | 1 | boy | dave | adult | | 2 | girl | lisa | child | | 3 | girl | amy ...
0
votes
1answer
445 views

Why are InnoDB's index keys limited to 767 bytes?

MySQL's official documentation states about InnoDB: An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. The meaning of this ...
0
votes
1answer
60 views

How to connect to mysql on a server

My uncle gave me access to his server so I could play around with mysql and php. He installed php and mysql on it created a user as well folder for me to play around in. I was wondering how to access ...
0
votes
1answer
164 views

Poor SQL performance with raid10

Maybe I'll try my luck here. Server Fault site was quite hostile for non-professional server user. I recently bought HP ProLiant ML310e Gen8 v2 server with 8G's RAM and 4 HP 1TB 6G 7.2k rpm SATA ...
0
votes
0answers
28 views

How can i update two tables

On my server, after insert on one table, I want to update two other tables. How can I do this using trigger?
0
votes
1answer
417 views

MYSQL REGEXP for text not between parentheses

I'm looking to search a large MYSQL table for a string and only return results that aren't between parentheses. If my table looks like this: | ID |Data | | 1 |Some search ...
5
votes
2answers
5k views

When should I think about upgrading our RDS MySQL instance based on memory usage?

It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not ...
0
votes
1answer
66 views

Select,From,Join,Where within 1 query MySQL [closed]

Wondering if its possible to use WHERE within such a big query string? I cannot seem to achieve what I want once I add Where into the query. customer +----+------+------+ | id | type | name ...
0
votes
1answer
137 views

Slow MYSQL performance due to sub queries

Here is my query: SELECT BAL,NAME, (SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR , (SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < ...
2
votes
1answer
570 views

How to handle connection pooling for massive multi-tenancy/multi-schema environment

Here's a quick run down of the situation: We have 1 Schema per customer. We have 2000+ customers. We have 50+ Database servers (with the above schema's distributed unevenly amongst them). We are ...
3
votes
1answer
1k views

mysql: find column data size on disk

I have fairly large MyISAM table with longtext field in it Table size is somewhat 16GB. I need to know if clearing this column in old records helps to keep table small I can retrieve table wise ...
0
votes
1answer
276 views

Annonymous user has no privileges, but still able to show databases?

Output of SHOW GRANTS FOR ''@'localhost'; GRANT USAGE ON *.* TO ''@'localhost' However, when I log in using my default user account, which is later defaulted to an annonymous account by mySQL as ...
2
votes
1answer
125 views

How safe is it to widen a column in MySQL?

Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot. A MySQL database I volunteered to help maintain has a table that looks like ...
1
vote
3answers
3k views

MYSQL - Unable to change 'old_passwords' variable

I have full access to a 'MYSQL 5.0.27-community-nt-log' database one of our new updates for a core app requires PHP 5.4, after installing and configuring I'm stuck at the error regarding old vs. new ...
1
vote
0answers
401 views

load_file() doesn't work but load data local infile works

I'm trying to load a non-regular file with MySQL: select load_file ('/proc/cmdline') It returns NULL So I checked the documents here, the file is readable, because as mysql user, I can execute ...
1
vote
0answers
15 views

MySQL: index statistics dropped when table dropped?

When a table is dropped, are its index statistics also removed? The reason I ask is that I was hoping to 'prep' the stats by running log playback via the percona-playback tool prior to cutting over ...
1
vote
0answers
66 views

“Binlog has bad magic number” - recoverying InnoDB tables

A system crash raised the error in the title. I've tried the suggestion here, but then I can't access any of my InnoDB tables. They are still there, but data is missing. The provided solution ...
0
votes
1answer
491 views

MySQL MyISAM index causes query to match no rows; indexes disabled, rows match

I created a table and index as described in this SE post, but when I query the table for a particular ID, no matches are found. When I disable the index, matches are found. Commands ran: CREATE ...
0
votes
1answer
41 views

In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped?

In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped? Also, what happens when a new primary key is added?
0
votes
3answers
76 views

Should frequently accessed tables containing large blobs with one-to-one relationships be normalized and columns split into two tables?

I have a frequently accessed table containing 3 columns of blobs, and 4 columns of extra data that is not used in the query, but just sent as result to PHP. There are 6 small columns (big int, small ...
4
votes
1answer
2k views

Speeding up a NOT IN subquery

Good evening, I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery ...
1
vote
1answer
311 views

Can I create a MySQL CSV table directly from an existing CSV file?

MySQL has a CSV storage engine. I'm wondering if I could create such a table directly from an existing CSV file. Made up command as example: CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ...
0
votes
2answers
111 views

Mysql Windows Issues

I've been having allot of issues with my mysql databases on Windows dying on me. About once a week or so it will refuse to work and I'll spend all night poking at it & a few computer restarts ...
7
votes
6answers
8k views

Maximum value from multiple tables

Consider the following situation: we have T1, T2, & T3 tables in which saved id and price of products. Now we need to find the id of products which have maximum price of all 3 tables. I have this ...
0
votes
2answers
45 views

mysql replication

I have a master mysql server (M1) with two replicating slaves behind it. I also have another master mysql server (M2) with it's two slave servers. The slave servers currently replicate everything from ...
5
votes
1answer
4k views

Slow InnoDB INSERT/UPDATE/DELETE

Background A couple of weeks ago all of my INSERT's/UPDATE's/DELETE's became overall slow (not SELECT's). Before that, everything was normal (mysql statements executed in <0,01 seconds). Now a ...
0
votes
2answers
247 views

Multiple relationships issue

Here is my SQLFiddle: http://sqlfiddle.com/#!2/affc4/9 A brief description of the issue: I have the following data: topics brands celebrities All 3 of those data types can be associated to an ...
0
votes
2answers
302 views

I can't import my database into mysql

I have a website based on drupal and I tried to export its database via both Backup wizard and phpmyadmin. the database was exported but I can't import it on my localhost database.this the error ...
1
vote
1answer
484 views

MySQL/MariaDB stored procedure inserts even with syntax error

createdAt has not been defined, but the query still runs when I call this proc and inserts a "0" date. Why, shouldn't it fail? DELIMITER $$ USE `MobiFit_Dev` $$ DROP PROCEDURE IF EXISTS ...
9
votes
1answer
10k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select ...
1
vote
1answer
422 views

MySQL: Why am I getting this warning?

I am calling the stored procedure below, and get this warning, is this warning a false positive or is my query unsafe? 1 queries executed, 1 success, 0 errors, 1 warnings Query: call ...
1
vote
1answer
149 views

Sorting a list of records alphabetically: is there any reason NOT to let the database do it

I have a list of records that contain a column of arbitrary strings. I would like the application to display them sorted in some order (preferably alphabetically, since humans have to read the data). ...
0
votes
1answer
113 views

Would it be better to do a sepparate query instead of a left JOIN?

Only a couple of rows is returned when not using LEFT since there isn't always data in the associated table - recipe_preparation. This is one of a couple of tables with the same "issue". SELECT r.id, ...
1
vote
1answer
63 views

SELECT showing two COUNTs

I had this table: tblA id | name | value 1 nameA 1 2 nameA 0 3 nameB 0 4 nameC 0 5 nameC 1 6 nameC 1 As you see, Value can be 1 or 0 I need to perform a query ...
0
votes
1answer
426 views

How to remove duplicates in col1 and get group MAX in col2? / mysql

Given the following table: col1 Col2 A 1 B 1 C 1 B 2 B 3 A 0 C 5 how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the ...
3
votes
1answer
938 views

Mysql database slow responding to connects when having only few concurrent connections

We have a database setup with in master/slave. It runs innodb and its on a 8 core with 32gb. When i run "ab" to do a load test on a website (4 frontend webservers) which does the query's with 10 ...
0
votes
0answers
69 views

mysql slow optimization of small tables

I'm having a problems with mysql perfomance. Optimization of small tables takes too long time. I increased tmp_table_size variable: mysql> SHOW VARIABLES LIKE '%_table_size%'; ...