The sql query for updating the users table is not correctly generated when using the option --sanitize-email

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where uid > 0' at line 1

The query it tries to execute is this:

update users set pass = '$S$DEeoGPMoyOpm7CY.JaXe.ht/T.nUThYN4UwDUN7hs0g8m9fJReQ.', mail =  where uid > 0;

Here the detailed output with --debug:

ncoovemac:default peterlozano$ drush --debug sql-sanitize
Bootstrap to phase 0. [0.01 sec, 4.4 MB]                             [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drush() [0.01 sec, 4.66 MB] [bootstrap]
Loading drushrc "/Users/peterlozano/.drushrc.php" into "user" scope. [0.01 sec, 4.67 MB]                                                                    [bootstrap]
Loading drushrc "/Users/peterlozano/.drush/drushrc.php" into "home.drush" scope. [0.01 sec, 4.67 MB]                                                        [bootstrap]
Cache MISS cid: 5.0-dev-commandfiles-0-ceba138b9644fc78384658aa8884cf29 [0.02 sec, 4.68 MB]                                                                     [debug]
Cache SET cid: 5.0-dev-commandfiles-0-ceba138b9644fc78384658aa8884cf29 [0.37 sec, 4.7 MB]                                                                       [debug]
Bootstrap to phase 0. [0.45 sec, 12.54 MB]                                                                                                                  [bootstrap]
Bootstrap to phase 0. [0.48 sec, 12.54 MB]                                                                                                                  [bootstrap]
Found command: sql-sanitize (commandfile=sql) [0.48 sec, 12.54 MB]                                                                                          [bootstrap]
Load alias @server_master [0.61 sec, 12.56 MB]                                                                                                                 [notice]
Do you really want to sanitize the current database? (y/n): y
Drush bootstrap phase : _drush_bootstrap_drupal_root() [5.84 sec, 12.62 MB]                                                                                 [bootstrap]
Initialized Drupal 7.12 root directory at /Users/peterlozano/Sites/systemseed/project/project/build [5.87 sec, 16.28 MB]                                       [notice]
Drush bootstrap phase : _drush_bootstrap_drupal_site() [5.87 sec, 16.29 MB]                                                                                 [bootstrap]
Initialized Drupal site default at sites/stage.yourproject.systemseed.com [5.87 sec, 16.29 MB]                                                                 [notice]
Cache MISS cid: 5.0-dev-commandfiles-2-62a9e2847aa4e71a44f7e7f2a34b3343 [5.88 sec, 16.3 MB]                                                                     [debug]
Cache SET cid: <em class="placeholder">5.0-dev-commandfiles-2-62a9e2847aa4e71a44f7e7f2a34b3343</em> [5.89 sec, 16.3 MB]                                         [debug]
Drush bootstrap phase : _drush_bootstrap_drupal_configuration() [5.89 sec, 16.3 MB]                                                                         [bootstrap]
Cache MISS cid: 5.0-dev-commandfiles-3-d970728376beb137057865c63baa822f [5.89 sec, 16.31 MB]                                                                    [debug]
Cache SET cid: <em class="placeholder">5.0-dev-commandfiles-3-d970728376beb137057865c63baa822f</em> [6.15 sec, 16.35 MB]                                        [debug]
Backend invoke: /Applications/MAMP/bin/php/php5.3.6/bin/php -d memory_limit="1024M" -d error_reporting="E_ALL|E_NOTICE|E_STRICT" -d display_errors="stderr"   [command]
/usr/local/drush/drush.php --php=/Applications/MAMP/bin/php/php5.3.6/bin/php --php-options='-d memory_limit="1024M" -d
error_reporting="E_ALL|E_NOTICE|E_STRICT" -d display_errors="stderr" '  --verbose --debug --config=./drushrc.php:.
--root=/Users/peterlozano/Sites/systemseed/project/project/build --uri=default sql-conf   --all --backend=2 2>&1 [6.38 sec, 18.67 MB]
/Applications/MAMP/bin/php/php5.3.6/bin/php -d memory_limit="1024M" -d error_reporting="E_ALL|E_NOTICE|E_STRICT" -d display_errors="stderr"                    [notice]
/usr/local/drush/drush.php --php=/Applications/MAMP/bin/php/php5.3.6/bin/php --php-options='-d memory_limit="1024M" -d
error_reporting="E_ALL|E_NOTICE|E_STRICT" -d display_errors="stderr" '  --verbose --debug --config=./drushrc.php:.
--root=/Users/peterlozano/Sites/systemseed/project/project/build --uri=default sql-conf   --all --backend=2 2>&1 [6.38 sec, 18.67 MB]
Bootstrap to phase 0. [6.55 sec, 12.54 MB]                                                                                                                  [bootstrap]
Bootstrap to phase 3. [6.58 sec, 12.54 MB]                                                                                                                  [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drupal_root() [6.58 sec, 12.55 MB]                                                                                 [bootstrap]
Initialized Drupal 7.12 root directory at /Users/peterlozano/Sites/systemseed/project/project/build [6.61 sec, 16.2 MB]                                        [notice]
Drush bootstrap phase : _drush_bootstrap_drupal_site() [6.61 sec, 16.2 MB]                                                                                  [bootstrap]
Initialized Drupal site default at sites/stage.yourproject.systemseed.com [6.61 sec, 16.2 MB]                                                                  [notice]
Cache HIT cid: 5.0-dev-commandfiles-2-62a9e2847aa4e71a44f7e7f2a34b3343 [6.62 sec, 16.21 MB]                                                                     [debug]
Drush bootstrap phase : _drush_bootstrap_drupal_configuration() [6.62 sec, 16.21 MB]                                                                        [bootstrap]
Cache HIT cid: 5.0-dev-commandfiles-3-d970728376beb137057865c63baa822f [6.62 sec, 16.22 MB]                                                                     [debug]
Found command: sql-conf (commandfile=sql) [6.64 sec, 17.79 MB]                                                                                              [bootstrap]
Load alias @server_master [6.8 sec, 18.39 MB]                                                                                                                  [notice]
Command dispatch complete [6.81 sec, 18.41 MB]                                                                                                                 [notice]
Peak memory usage was 20.34 MB [6.81 sec, 18.41 MB]                                                                                                            [memory]
Undefined variable: newmail sync.sql.inc:120 [7.08 sec, 18.75 MB]                                                                                              [notice]

The following post-sync operations will be done on the destination:
  * Reset passwords and email addresses in user table

Calling system(mysql --database=project --host=localhost --user=project --password=XXXX  < /private/tmp/drush_HS9vWh);
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where uid > 0' at line 1
Command dispatch complete [7.12 sec, 18.7 MB]                                                                                                                  [notice]
 Timer  Cum (sec)  Count  Avg (msec) 
 page   1.23       1      1230.29    

Peak memory usage was 20.62 MB [7.12 sec, 18.7 MB]

And the drush status output:

ncoovemac:default peterlozano$ drush status
 Drupal version                :  7.12                                                                                                          
 Site URI                      :  http://default                                                                                                
 Database driver               :  mysql                                                                                                         
 Database hostname             :  localhost                                                                                                     
 Database username             :  project                                                                                                       
 Database name                 :  project                                                                                                       
 Database                      :  Connected                                                                                                     
 Drupal bootstrap              :  Successful                                                                                                    
 Drupal user                   :  Anonymous                                                                                                     
 Default theme                 :  custom                                                                                                   
 Administration theme          :  seven                                                                                                         
 PHP configuration             :  /Applications/MAMP/bin/php/php5.3.6/conf/php.ini /Users/peterlozano/.drush/drush.ini                          
 Drush version                 :  5.0-dev                                                                                                       
 Drush configuration           :  /Users/peterlozano/.drushrc.php /Users/peterlozano/.drush/drushrc.php                                         
 Drush alias files             :  ….. removed for privacy                                          
 Site path                     :  sites/….                                                                        
 File directory path           :  sites/…./files                                                                  
 Private file directory path   :  sites/…./private/files 
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Pedro Lozano’s picture

Status: Active » Needs review
FileSize
1.21 KB
moshe weitzman’s picture

Assigned: Unassigned » greg.1.anderson

Could we get a unit test for this?

greg.1.anderson’s picture

I'm willing in theory, but I'm really overwhelmed with work this week. Maybe the OP can help?

moshe weitzman’s picture

Status: Needs review » Postponed (maintainer needs more info)

sqlSyncTest class already performs a --sanitize. perhaps OP can figure out why that passes and his usage fails.

Pedro Lozano’s picture

I'm starting to suspect that OP = me lol (I don't know what it stands for)

ok, I'll have a look at the test when I have some time, maybe next week at some drupalcon sprint.

greg.1.anderson’s picture

OP == original poster. Sorry for being obtuse; thanks for your help.

Pedro Lozano’s picture

Status: Postponed (maintainer needs more info) » Needs review
FileSize
1.91 KB

The problem with the test is that it is executed with the default pattern ("user+%uid@localhost").

The sql_drush_sql_sync_sanitize() function only fails when the email doesn't include a pattern (ex: --sanitize-email="user@localhost").

This patch extends the test perform the sanitization also with a fixed email address. It also fixed the sql_drush_sql_sync_sanitize() function but in a simpler way than the previous patches.

Since different users on drupal cannot have the same email, another approach to solve this would be to make patterns required in --sanitize-email.

moshe weitzman’s picture

Status: Needs review » Fixed

Committed. Thanks.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.