I am creating custom tables from a plugin. Foreign keys are required. To be safe, I'm want turn off foreign_keys_check before issuing maybe_create_table().

This codes is what I have to check variable's initial status, make the change and, and show the result:

  echo $wpdb->query( "SHOW VARIABLES LIKE '%foreign%'" );
  $wpdb->query( 'SET foreign_key_checks=0' );
  echo $wpdb->query( "SHOW VARIABLES LIKE '%foreign%'" );

I expected the output to be 10 reflecting that the SET succeeded. Instead, I get 11 indicating the foreign_key_checks variable remains true.

What is the proper way to programmatically set a MySQL variable from inside WordPress?


$wpdb->query() doesn't return the data but the rows affected, use $wpdb->get_var().

|improve this answer|||||
  • Excellent. $wpdb->get_var( "SHOW VARIABLES LIKE '%foreign%'", 1 ) does in fact show ON, and then OFF after executing the SET. However, what resets the value to ON? I'm not doing it programmatically, so WordPress (or something else) resets the value to ON after the script completes? I was expecting to have to explicitly turn it back on after I finished creating the tables. – tim.rohrer Dec 3 '17 at 20:49
  • The changes will only affect the current session. In WP context that is most likely the current request. Persistent database connections might be another thing, but they are rare in standard WP setups. – janh Dec 3 '17 at 20:51
  • In this case would completion of the current php script constitute the session? Or, should I be issuing the SET inside the foreach loop that prepares and creates each table? I know if I reload the page, the value is back to ON at th start. – tim.rohrer Dec 3 '17 at 20:56
  • 1
    Yes, the session is over when the database connection is closed, which happens when the script exists. I'd re-enable it as soon as you don't need it to be disabled any longer, as some other plugin might rely on foreign keys working as they should. – janh Dec 3 '17 at 21:07

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.