Finding the nth query in a very long sql file

Question

I am getting this error on import of a ~1g SQL dump:

[ERROR in query 620] Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

I would like to look at this query, but there are 4537 of them (at least by count of the semicolons) so how do I find the 620th of them quickly? I opened the sql file in xcode, but I am open to grepping if that is the solution.

Per Steven’s request I am generalizing this more. Say we have an sql file of form:

# Dump of table table1
# ------------------------------------------------------------

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
   `a` int(11) NOT NULL AUTO_INCREMENT,
   `b` varchar(255) NOT NULL DEFAULT '',
   `c` varchar(255) NOT NULL DEFAULT '',
   `d` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`),

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


 # Dump of table table2
# ------------------------------------------------------------

DROP TABLE IF EXISTS `table2`;

CREATE TABLE `table2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(255) NOT NULL DEFAULT '',
  `c` varchar(255) NOT NULL DEFAULT '',
  `d` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


...

# Dump of table table310
# ------------------------------------------------------------

DROP TABLE IF EXISTS `table310`;

CREATE TABLE `table310` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(255) NOT NULL DEFAULT '',
  `c` varchar(255) NOT NULL DEFAULT '',
  `d` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Dump of table table311
# ------------------------------------------------------------

DROP TABLE IF EXISTS `table311`;

CREATE TABLE `table311` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(255) NOT NULL DEFAULT '',
  `c` varchar(255) NOT NULL DEFAULT '',
  `d` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So in this example (... represents intervening tables) the query that starts with CREATE TABLE table310 would be the 620th Query if everything followed this pattern. Say the pattern and naming convention was not so regular, how would I be able to go to the 620th query referenced in the Error?

possible duplicate of [Change limit for "Mysql Row size too large"](http://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large) — Pரதீப், Nov 06 '14 at 01:19
Pradeep the referenced answer does indeed get rid of the error message, thank you! But the underlying question of how to grep to the query referenced in the error is still unanswered. — vaene, Nov 06 '14 at 07:01

Zombo · Accepted Answer · 2014-12-12T16:39:16.380

1

#!/usr/bin/awk -f
BEGIN {
  RS = ORS = ";"
}
NR == 6

Result

CREATE TABLE `table310` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(255) NOT NULL DEFAULT '',
  `c` varchar(255) NOT NULL DEFAULT '',
  `d` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

edited Dec 12 '14 at 16:39

answered Nov 06 '14 at 01:28

Zombo

1
62
391
407

Looks good! as long as there are no ';' not associated with a query in the SQL I think this will work. – vaene Nov 06 '14 at 19:00
Just a warning: If you have any INSERT or UPDATE commands in the .sql file, and their VALUES include escaped HTML (which is not uncommon), you will likely encounter things like ` ` or `<`. The semi-colons in those entites will throw off this method of detection and render it useless. – beporter Dec 12 '14 at 16:36

Finding the nth query in a very long sql file

1 Answers1