mysql

How can I add a user through SQL?

This will create a new admin user called username with password: password in a database called DATABASE with table prefix wp_ Change the two accordingly to your own database name and table prefix. Try this: First create a row in wp_users. Replace DATABASE with your database name, username with your choosen username, password with your … Read more

“#1067 – Invalid default value for ‘post_date'” when trying to reset AI after backup

The post_date default value is 0000-00-00 00:00:00. If you check the sql_mode variable like this: show variables like ‘sql_mode’; … it will show you the sql_mode variable, that will be sth like this: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION You have to set up again sql_mode variable without NO_ZERO_IN_DATE,NO_ZERO_DATE So in the previous example you should set the sql_mode like … Read more

Slow SQL_CALC_FOUND_ROWS Query

The use of SQL_CALC_FOUND_ROWS is not really a problem, although it incurs an overhead. What happens is, WordPress uses SQL_CALC_FOUND_ROWS in order to determine the total posts that would have been returned, if no LIMIT clause was provided. This is necessary in order to calculate and provide you with correct pagination links. Disabling it unconditionally … Read more

Built to scale, or worry later?

Have a look into how WordPress.com do it – they released all the software they use under open licensing, and it’ll let you scale up in future as you need it – far as I know, DB replication is pretty easy to build in. If in doubt, email Matt (ma.tt) – the WP team are … Read more

Multipart/formatted MySQL query problem

After much trial and error I finally figured out a solution using mysqli. I consider it a acceptable solution since the query will only be run on plugin updates. $mysqli = new mysqli(); $mysqli->connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); $sql = ” DROP TRIGGER IF EXISTS {$wpdb->get_blog_prefix($site->id)}post_queue_insert; CREATE TRIGGER {$wpdb->get_blog_prefix($site->id)}post_queue_insert BEFORE INSERT ON {$wpdb->get_blog_prefix($site->id)}posts FOR EACH ROW … Read more

Custom query to get post names beginning with a digit

The solution given by @RolandoMySQLDBA will give you all posts because you have a * and not a +. The * means to return zero or more matches, which is not what you want. In this case, you actually don’t need either, but just to match the first character. Try this: $sql .= $wpdb->prepare( ” … Read more

+ More