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

2
votes
3answers
2k views

Block user access to certain tables

DBMS allows the admin to grant table access to a user using something like: GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost'; However, is it possible to grant all tables to a user and ...
0
votes
0answers
174 views

Slow query (Wordpress)

Can I speed up somehow the following query? Somehow to stop from creating a tmp table/filesort. Tried a couple of indexes but no luck. mysql> explain SELECT wp_posts.ID FROM wp_posts LEFT JOIN ...
1
vote
1answer
133 views

Correct way to write this query?

I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the ...
0
votes
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
206 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
831 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
117 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
1k 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
471 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
129 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
354 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
158 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
144 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
477 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
165 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
78 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
443 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
128 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
128 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
821 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
263 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
730 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
396 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
184 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
7 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
6k 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
160 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
54 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 ...
1
vote
1answer
910 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
798 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
101 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
50 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
138 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
360 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
311 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
637 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 ...