1

I want to use mysqldump to retrieve X first rows from a database (where "first" are ordered by primary key).

I'm trying to use mysqldump the following way:

mysqldump [connection-info] -where"1=1 LIMIT 40" --order-by-primary database table >> file.sql

(this is inside a script who runs different configurations for different tables)

Now, the problem, that command creates the following query:

SELECT /*!40001 SQL_NO_CACHE */ * FROMtableWHERE 1=1 LIMIT 40 ORDER BY primary_key

Is an invalid query, I need the LIMIT 40 to be placed after the ORDER BY statement.

  • If you know the right value of 40th ID or if it goes 1-40 without gaps, you could do --where "<idcol> <= 40" or similar. – jkavalik May 14 '16 at 10:22
2

My guess is that you cant use limit in combination with --order-by-primary

One way around this is to define the order by within the -where argument. If you know the primary key for the table:

mysqldump -u lelle -p -w"1 = 1 order by <primary key> LIMIT 40" <database> <table>

Note that <primary key> is a place holder for the columns in the primary key.

if you don't know the primary key in advance you can use the INFORMATION_SCHEMA to determine that:

select GROUP_CONCAT(column_name SEPARATOR ',') 
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
where CONSTRAINT_NAME = 'PRIMARY' 
  and CONSTRAINT_SCHEMA = ... and table_name = ...;

and then use the information in mysqldump.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.