Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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
Comment | File | Size | Author |
---|---|---|---|
#7 | 1470220-sanitize-email-2.patch | 1.91 KB | Pedro Lozano |
#1 | 1470220-sanitize-email.patch | 1.21 KB | Pedro Lozano |
Comments
Comment #1
Pedro Lozano CreditAttribution: Pedro Lozano commentedComment #2
moshe weitzman CreditAttribution: moshe weitzman commentedCould we get a unit test for this?
Comment #3
greg.1.anderson CreditAttribution: greg.1.anderson commentedI'm willing in theory, but I'm really overwhelmed with work this week. Maybe the OP can help?
Comment #4
moshe weitzman CreditAttribution: moshe weitzman commentedsqlSyncTest class already performs a --sanitize. perhaps OP can figure out why that passes and his usage fails.
Comment #5
Pedro Lozano CreditAttribution: Pedro Lozano commentedI'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.
Comment #6
greg.1.anderson CreditAttribution: greg.1.anderson commentedOP == original poster. Sorry for being obtuse; thanks for your help.
Comment #7
Pedro Lozano CreditAttribution: Pedro Lozano commentedThe 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.
Comment #8
moshe weitzman CreditAttribution: moshe weitzman commentedCommitted. Thanks.