0

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?

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
4
  • 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. Commented Dec 3, 2017 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. Commented Dec 3, 2017 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. Commented Dec 3, 2017 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. Commented Dec 3, 2017 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.