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

1
vote
2answers
2k views

mysql no longer defaulting to /tmp/mysql.sock

I was testing some software to see if it could connect to mysql correctly when mysql is not using /tmp/mysql.sock. I stopped mysql, editted /etc/my.cnf to add socket=/var/lib/mysql/mysql.sock ...
1
vote
0answers
211 views

Dealing with Multiple User Roles

I'm working on a project based on a CMS, where there is a built-in roles table. However, the CMS allows a user to have one role only using role_id And these are the requirements that struggling me: ...
1
vote
2answers
2k views

mysqld constant CPU usage when absolutly nothing is connected and no queries

I'm using mysql on a windows development machine, and all was good for a long time. But today my PC was running slowly, and I noticed mysqld.exe is using between 25% and 40% CPU. I've tried ...
1
vote
0answers
868 views

Why does this MySQL date_sub statement return wrong values?

I had a complex statement that was returning strange values and I traced it to the following line: SELECT id, updated FROM status WHERE updated > date_sub(NOW(), INTERVAL 6 MINUTE) ORDER BY ...
6
votes
4answers
18k views

How to change a MySQL previous slave to be a master and remove slave status information?

I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine. What I can't seem to do is to remove ...
2
votes
1answer
4k views

Unique insert performance: Primary Key vs Unique Index

I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records. What's the fastest way to insert into the table, by keeping the domain unique ...
0
votes
2answers
118 views

Should I create a multi-column UNIQUE index?

I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits): CREATE TABLE `Visits` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `...
1
vote
0answers
2k views

“Temporary file write failure” when trying to change primary key in MySQL

In MySQL, When I trying to change primary key by this query: ALTER TABLE `resources` DROP PRIMARY KEY, ADD PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`); I execute this query ...
0
votes
1answer
43 views

Trying to get this MySQL Trigger working

create trigger check_td before for each row begin declare v_code_ID varchar(25); declare c_Worker1 cursor for select code_ID from Worker1; declare c_Worker2 cursor for select code_ID from Worker2; ...
2
votes
1answer
508 views

How does the alter command work internally when adding a column?

Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files? As per my test, I ...
-2
votes
1answer
66 views

How to freeze a view in standard SQL? [closed]

I have the following: create view ONE as select ID workers_A; create view TWO as select ID workers_B; create view FINAL as (select * from ONE) UNION (select * from TWO); That works correctly, but ...
0
votes
0answers
130 views

tables don't exist after unmounting and remounting external drive where database was installed

I want to create a database that's too big to keep on my own machine, so I'm trying to create one on an external hard drive. It works up until I unmount and remount the drive, at which point show ...
1
vote
3answers
367 views

Mysql to Oracle communication

I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server). The orders ...
0
votes
2answers
161 views

“GROUP BY” in ranges?

Suppose I have a table with a numeric column (lets call it "score"). I'd like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | ...
0
votes
0answers
145 views

Would an INNER JOIN query in a sharded relational database hit the same shard when using a compound primary key?

In this question I have asked about modulus calculation in sharded tables, this design is used: CREATE TABLE Orders (order_id INT PRIMARY KEY, ...); CREATE TABLE LineItems (order_id INT, lineitem_id ...
2
votes
1answer
85 views

Backing up large MySQL DB for DR purposes

Fundamentally I am just trying to come up with the best way to back up my MySQL DB nightly. Complicated by the fact that: its a 400 gb DB and growing its running on windows I am not permitted any ...
0
votes
0answers
14 views

Specific id to appear first in result of query [duplicate]

I have a sample query where: Select gID, entID, cID, gString From tblGF Where ((entID=$entID And cID=0) Or (entID=$entID And cID=$cID)) And (gFP='BB' Or gFP='CP' Or gFP='PS' Or gID=$gID) Order ...
3
votes
6answers
1k views

Database Normalization - Can one field point to another?

This may sound like a dumb question, but I am a software developer new to database design... So the concept makes sense to me, but maybe this doesn't carry-- Is it possible for a field in a record ...
1
vote
1answer
483 views

wordpress table is crashed and should be repaired

I run a wordprees website and use a managed VPS server with cPanel/WHM. Got bellow error log and check the table. I repaired the table with repair table command from phpmyadmin. But this problem occur ...
2
votes
1answer
175 views

How can I solve this error InnoDB: is in the future?

InnoDB: is in the future! Current system log sequence number 412018685584. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See ...
2
votes
0answers
80 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders (...
1
vote
1answer
3k views

GROUP BY two different columns

I store hits. My table looks like this: ID | time | Country -------------------------------- 1 | 01:00:00 | France 2 | 01:00:00 | France 3 | 01:00:00 | Brazil ...
0
votes
1answer
451 views

COUNT and GROUP BY performance

I have a table where I store visits (id, datetime, country, browser). I want to count how many visits are from which country. I use this query: SELECT country, COUNT(*) as hits FROM hits_table ...
0
votes
0answers
29 views

Indexed column INSERT vs. UPDATE

I have an indexed column. I could give it a value on INSERT. But it would be easier if I would add this value via UPDATE later (I have to update this row anyway). But I was thinking if it would be ...
1
vote
1answer
41 views

Should I denormalize lat & lon on area searches?

I run a hobby web site for tabletop gamers. They can search based on postal code in a radius round their location. As I consider adding new countries, my best option seem to be silo'ing the geo ...
2
votes
1answer
129 views

Making MySQL use primary index without hinting or forcing

I have the following db structure: CREATE TABLE `question` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level_id` int(11) NOT NULL, `video_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, ...
0
votes
1answer
130 views

Transforming Huge Database table to Another Table

We are migrating lot of data from our old database to new database. Presently, both of them are actively[writing/reading] used based on flows/activity as migration is in progress. Here's the real ...
0
votes
1answer
852 views

Join 3 tables returns too many results [closed]

I have 3 tables: users, posts, videos. The last 2 tables have user_id that associate with users.id (first table). The problem is that when I try to run inner join query and return all posts and ...
0
votes
1answer
270 views

make MariaDB Auto-Increment-Increment dynamic

In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 ...
1
vote
2answers
741 views

Converting MSSQL Stored Procedure to MySQL - Syntax Error

I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out: CREATE PROCEDURE ...
2
votes
4answers
413 views

Aggregate query optimization

Could you please help me to optimize this query - currently it takes ~4 minutes to run this query. It looks like keys: ip and timestamp are non-unique so shall I add some indexes? mysql> EXPLAIN ...
1
vote
0answers
200 views

MySQL replication of single DB to same server

I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source). I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which ...
0
votes
0answers
8 views

MySQL replication of single DB to same server [duplicate]

I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source). I have dozens of databases but one of them is being hit quite hard with large updates which degrade the performance of ...
4
votes
2answers
3k views

MySQL binary logs - skip a table

Is there a way to skip a specific table in the binary logs? I've got binary logs configured as my incremental backup strategy between nightly backups. But we have one huge reference table that we're ...
2
votes
1answer
7k views

Get only overall summary WITH ROLLUP and GROUP BY for multiple fields

Performing a WITH ROLLUP when grouping by multiple fields, MySQL returns a rollup row for each group, as well as the overall summary: CREATE TABLE test (name VARCHAR(50), number TINYINT); INSERT INTO ...
2
votes
1answer
32 views

How to use a table as a reference for another table in MySQL

I have code from generic SQL that I need to find out how to run in MySQL: CREATE TABLE firstTable ( freq int NULL, w1 int NULL, w2 int NULL, w3 int NULL ) CREATE TABLE secondTable ( [wID] [int] ...
0
votes
1answer
166 views

Select historical prices for an exact datetime - MySQL

I have a price_list table where I store historical prices for each item. columns are - id, name, price, start_date, item_id When a price is changed, a one new row is inserted with a current ...
0
votes
1answer
56 views

ON DELETE CASCADE pseudo delete

For data integrity ON DELETE CASCADE is necessary but it's really dangerous when a delete affects like a Chain Reaction! I know one way to prevent this is to make a pseudo delete by putting a "Delete"...
1
vote
1answer
963 views

Difference between DATE(time_field) AND time_field in a WHERE clause

I have 3 tables: users movements unique_ids I want to get the records gotten from march until now of the users registered in march, counting the unique id of their movements with a distinct and the ...
2
votes
1answer
807 views

Subselect dependent on first select value

I wish to do a select of all data matching a particular id. I would also like to return a selection of all data matching the alt_id field in the first select. This example works fine for the main ...
0
votes
1answer
107 views

MySQL 5.1: How to rollback partitioned table

I have partitioned a table on the basis of one flag column have value either 0 or 1. Now, I want to roll back these tables. How can I do this?
1
vote
1answer
57 views

What is the generally accepted method and tools for profiling MySQL queries?

Profiling database queries is difficult, and dependent on database technology. In MySQL, what is the usual (or, in deference to that, a great) process for profiling a query? For example: Run the ...
1
vote
1answer
139 views

MySQL: records, inserted via phpMyAdmin, do not replicate

As it says on the topic, for some reason records inserted/ modified via PMA do not get replicated to the slave, If I do exactly the same insert/modification on master via mysql command line, or ...
0
votes
1answer
371 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | +---------...
2
votes
1answer
4k views

MySQL Foreign Key Prevents Deleting Table — But Table is Empty

Why would dropping an empty table produce a foreign key error? In MySQL, I have a table with the following definition CREATE TABLE `enterprise_rma` ( `entity_id` int(10) unsigned NOT NULL ...
1
vote
2answers
314 views

Disk write in iotop when doing mysql “Copy to tmp table”

I have just moved servers from a two-server configuration with the application and database server running separately to a one-server LAMP stack. Unfortunately the issue that I have encountered on ...
1
vote
1answer
645 views

Restore mysql from database snapshot

I have a folder containing the snapshot taken from a database. They are all in the format organisation_product_tablename.sql. How can I restore them back into the database? EDIT 1: I managed to ...
1
vote
3answers
303 views

Will redirecting queries to dev/null warm up indexes?

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice? e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt Thanks
1
vote
1answer
151 views

More Details for MySQL's errorno:150

When MySQL can't create a table due to a foreign key constraint problem, it returns the following error Can't create table 'dbname.table_name' (errno: 150) Is there anyway to have MySQL tell you ...
0
votes
1answer
69 views

two auto incrementing columns

OK I have hit an issue, now I was going to look at having two auto incrementing columns, but I cant using InnoDB. So I am after a Solution. I have a table called queue. Everything to do with my task ...