WordPress.org

1. stoi2m1
   Member
   Posted 1 year ago #
   

   Table creation for new sub blogs fails silently. I have been testing things from the create new site within the network section of the dashboard. When I create a new site I get the message saying "Site added.", but somewhere along the lines it fails to create the tables (ie wp_xx_posts, wp_xx_postmeta - where xx is the ID of the blog).

   I do not have the luxury to disable plugins, mu-plugins or change the theme. My site is being used and is active. I tried exporting the database to a test site. And the test site has the same plugins, mu-plugins and theme and creates new sites with no problem.

   I have checked the collate on both my live site and test site and they both match utf8_general_ci. I dont think that is the issue.

   So I have added some error_log() functions within the /wp-include/ms-functions.php and the wp-admin/network/upgrade.php files to find the functions being used and see where the table creation is failing.

   I have found the following functions are the main functions used to create the blog and then the table creation:
   

   wpmu_create_blog()
   install_blog()
   make_db_current_silent()
   dbDelta()

   Dumping $cqueries from dbDelta() had the following data:
   

   CREATE TABLE wp_1_terms (
    term_id bigint(20) unsigned NOT NULL auto_increment,
    name varchar(200) NOT NULL default '',
    slug varchar(200) NOT NULL default '',
    term_group bigint(10) NOT NULL default 0,
    PRIMARY KEY  (term_id),
    UNIQUE KEY slug (slug),
    KEY name (name)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_1_term_taxonomy (
    term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment,
    term_id bigint(20) unsigned NOT NULL default 0,
    taxonomy varchar(32) NOT NULL default '',
    description longtext NOT NULL,
    parent bigint(20) unsigned NOT NULL default 0,
    count bigint(20) NOT NULL default 0,
    PRIMARY KEY  (term_taxonomy_id),
    UNIQUE KEY term_id_taxonomy (term_id,taxonomy),
    KEY taxonomy (taxonomy)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_1_term_relationships (
    object_id bigint(20) unsigned NOT NULL default 0,
    term_taxonomy_id bigint(20) unsigned NOT NULL default 0,
    term_order int(11) NOT NULL default 0,
    PRIMARY KEY  (object_id,term_taxonomy_id),
    KEY term_taxonomy_id (term_taxonomy_id)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_1_commentmeta (
     meta_id bigint(20) unsigned NOT NULL auto_increment,
     comment_id bigint(20) unsigned NOT NULL default '0',
     meta_key varchar(255) default NULL,
     meta_value longtext,
     PRIMARY KEY  (meta_id),
     KEY comment_id (comment_id),
     KEY meta_key (meta_key)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_1_comments (
     comment_ID bigint(20) unsigned NOT NULL auto_increment,
     comment_post_ID bigint(20) unsigned NOT NULL default '0',
     comment_author tinytext NOT NULL,
     comment_author_email varchar(100) NOT NULL default '',
     comment_author_url varchar(200) NOT NULL default '',
     comment_author_IP varchar(100) NOT NULL default '',
     comment_date datetime NOT NULL default '0000-00-00 00:00:00',
     comment_date_gmt datetime NOT NULL default '0000-00-00 00:00:00',
     comment_content text NOT NULL,
     comment_karma int(11) NOT NULL default '0',
     comment_approved varchar(20) NOT NULL default '1',
     comment_agent varchar(255) NOT NULL default '',
     comment_type varchar(20) NOT NULL default '',
     comment_parent bigint(20) unsigned NOT NULL default '0',
     user_id bigint(20) unsigned NOT NULL default '0',
     PRIMARY KEY  (comment_ID),
     KEY comment_approved (comment_approved),
     KEY comment_post_ID (comment_post_ID),
     KEY comment_approved_date_gmt (comment_approved,comment_date_gmt),
     KEY comment_date_gmt (comment_date_gmt),
     KEY comment_parent (comment_parent)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_1_links (
     link_id bigint(20) unsigned NOT NULL auto_increment,
     link_url varchar(255) NOT NULL default '',
     link_name varchar(255) NOT NULL default '',
     link_image varchar(255) NOT NULL default '',
     link_target varchar(25) NOT NULL default '',
     link_description varchar(255) NOT NULL default '',
     link_visible varchar(20) NOT NULL default 'Y',
     link_owner bigint(20) unsigned NOT NULL default '1',
     link_rating int(11) NOT NULL default '0',
     link_updated datetime NOT NULL default '0000-00-00 00:00:00',
     link_rel varchar(255) NOT NULL default '',
     link_notes mediumtext NOT NULL,
     link_rss varchar(255) NOT NULL default '',
     PRIMARY KEY  (link_id),
     KEY link_visible (link_visible)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_1_options (
     option_id bigint(20) unsigned NOT NULL auto_increment,
     blog_id int(11) NOT NULL default '0',
     option_name varchar(64) NOT NULL default '',
     option_value longtext NOT NULL,
     autoload varchar(20) NOT NULL default 'yes',
     PRIMARY KEY  (option_id),
     UNIQUE KEY option_name (option_name)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_1_postmeta (
     meta_id bigint(20) unsigned NOT NULL auto_increment,
     post_id bigint(20) unsigned NOT NULL default '0',
     meta_key varchar(255) default NULL,
     meta_value longtext,
     PRIMARY KEY  (meta_id),
     KEY post_id (post_id),
     KEY meta_key (meta_key)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_1_posts (
     ID bigint(20) unsigned NOT NULL auto_increment,
     post_author bigint(20) unsigned NOT NULL default '0',
     post_date datetime NOT NULL default '0000-00-00 00:00:00',
     post_date_gmt datetime NOT NULL default '0000-00-00 00:00:00',
     post_content longtext NOT NULL,
     post_title text NOT NULL,
     post_excerpt text NOT NULL,
     post_status varchar(20) NOT NULL default 'publish',
     comment_status varchar(20) NOT NULL default 'open',
     ping_status varchar(20) NOT NULL default 'open',
     post_password varchar(20) NOT NULL default '',
     post_name varchar(200) NOT NULL default '',
     to_ping text NOT NULL,
     pinged text NOT NULL,
     post_modified datetime NOT NULL default '0000-00-00 00:00:00',
     post_modified_gmt datetime NOT NULL default '0000-00-00 00:00:00',
     post_content_filtered text NOT NULL,
     post_parent bigint(20) unsigned NOT NULL default '0',
     guid varchar(255) NOT NULL default '',
     menu_order int(11) NOT NULL default '0',
     post_type varchar(20) NOT NULL default 'post',
     post_mime_type varchar(100) NOT NULL default '',
     comment_count bigint(20) NOT NULL default '0',
     PRIMARY KEY  (ID),
     KEY post_name (post_name),
     KEY type_status_date (post_type,post_status,post_date,ID),
     KEY post_parent (post_parent),
     KEY post_author (post_author)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_users (
     ID bigint(20) unsigned NOT NULL auto_increment,
     user_login varchar(60) NOT NULL default '',
     user_pass varchar(64) NOT NULL default '',
     user_nicename varchar(50) NOT NULL default '',
     user_email varchar(100) NOT NULL default '',
     user_url varchar(100) NOT NULL default '',
     user_registered datetime NOT NULL default '0000-00-00 00:00:00',
     user_activation_key varchar(60) NOT NULL default '',
     user_status int(11) NOT NULL default '0',
     display_name varchar(250) NOT NULL default '',
     PRIMARY KEY  (ID),
     KEY user_login_key (user_login),
     KEY user_nicename (user_nicename)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci |
   CREATE TABLE wp_usermeta (
     umeta_id bigint(20) unsigned NOT NULL auto_increment,
     user_id bigint(20) unsigned NOT NULL default '0',
     meta_key varchar(255) default NULL,
     meta_value longtext,
     PRIMARY KEY  (umeta_id),
     KEY user_id (user_id),
     KEY meta_key (meta_key)
   ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

   Does this look correct, seems it should be the tables of the new site?

   Thanks, Jesse

2. Ipstenu (Mika Epstein)
   Half-Elf Support Rogue & Mod
   Posted 1 year ago #
   

   If your install isn't making the tables, check that the account has full access to do that with the DB.

3. stoi2m1
   Member
   Posted 1 year ago #
   

   I know the user has proper rights. I use the same user for my database client Navicat and I have been manually creating the tables as the new sub blogs are created.

4. Ipstenu (Mika Epstein)
   Half-Elf Support Rogue & Mod
   Posted 1 year ago #
   

   Manually... So you log in via control panel?

   You're not using a special ID for SQL?

5. stoi2m1
   Member
   Posted 1 year ago #
   

   I have verified the user assigned to the database, which there is only one, has all of the privileges assigned to it.

   Im using Navicat, a database client, and I remotely access the MySQL database. It is using the same user as my WordPress install.

   So I know its not an issue with my user privileges.

6. Ipstenu (Mika Epstein)
   Half-Elf Support Rogue & Mod
   Posted 1 year ago #
   

   Okay, the output you show is what should be happening, so really the only options are DB issues, be they access related or the DB itself... Can you export the DB to a new one, point to that, and try adding a site that way? Not this:

   I tried exporting the database to a test site. And the test site has the same plugins, mu-plugins and theme and creates new sites with no problem.

   But just make a new DB, copy it over, point your LIVE WP instance there, see if it works?

7. stoi2m1
   Member
   Posted 1 year ago #
   

   I used phpmyadmin to do this. Thinking it would happen faster within my hosts network rather then with a remote connection of my internet speeds.

   I have two databases one named live and one named livetest. I used the synchronize feature of phpmyadmin to copy the live database to the livetest database and I assigned the same user to the livetest database.

   I then changed the database name in wp-config so I was using the new database. I made a new site and then when I tried to visit it I got "Error establishing a database connection" and no tables were created.

8. Ipstenu (Mika Epstein)
   Half-Elf Support Rogue & Mod
   Posted 1 year ago #
   

   Okay... Something's really wrong with that DB server then. It's obviously not got the right permissions somewhere, and it's NOT just in that database. o.O

9. stoi2m1
   Member
   Posted 1 year ago #
   

   I have had a test site and live site on the same hosting for quite some time. This allows me to test things before making them live. To ensure they work with my host after developing things locally.

   My test site is on a subdomain of my live sites domain and use a different database on the same server. It uses the same user as my live site. Its essentially the same site in the same environment at a different location for testing purposes hidden from the rest of the site by only allowing access to logged in users of the test site.

   I have tested the creation of sub blogs on this test site and they are created with no problems. So it seems that the problem might not be with the database.

10. Ipstenu (Mika Epstein)
    Half-Elf Support Rogue & Mod
    Posted 1 year ago #
    

    But you said you're using a different database on the test site.

    Are you absolutely, 100%, positive that the SQL account specified in wp-config has the access? Because what I'm understanding you to say is this:

    Original install with original DB, can't make tables.

    Duplicate install with duplicate DB, can make tables.

    Original install with duplicate DB, can't make tables.

    Now, assuming that when you made a duplicate install you were, literally, copying everything and changing only the wp-config to point to the new DB, that makes little sense. But if you installed a new version of WP and then imported a copy of the original DB, that may make a little sense, but it would imply that your config in the original is messed up.

11. stoi2m1
    Member
    Posted 1 year ago #
    

    Are you suggesting there is an issue in my wp-config.php file or should I be looking else where?

12. Ipstenu (Mika Epstein)
    Half-Elf Support Rogue & Mod
    Posted 1 year ago #
    

    Well ... Depends.

    For your tests, did you:

    a) Install a new version of WP and then copy the DB over?

    or ...

    b) copy the whole install over, copy the DB to a new instance, and change the wp-config to point to it?

13. stoi2m1
    Member
    Posted 1 year ago #
    

    I did option b with a few more changes I had to change all of the instances in the DB to the new domain (test sub domain). Its what anyone would do to move their site to a new domain. I wanted things to be as close to identical as possible. So the DB server is the same, the DB user is the same, the hosting is the same, only differences are the database name, the domain (being its using a sub domain) and the file location on the server (which is a sub directory that the sub domain points to).

14. stoi2m1
    Member
    Posted 1 year ago #
    

    I had a much longer post of all the things I have tried this evening in narrowing down where my problem might be from error_log contents to various other oddities. However I feel this is the most pertinent data and I will wait to hear back from someone before writing another post and clouding the situation.

    I decided to dump the contents of $wpdb inside wp_install_blog() on my local dev and found it has contents like:
    

    last_error = Table 'thegzpsb.wp_2490_options' doesn't exist |
    num_queries = 204 |
    num_rows = 1 |
    rows_affected = 1 |
    insert_id = 2490 |
    last_query = SELECT option_value FROM wp_2490_options WHERE option_name = 'wp_2490_user_roles' LIMIT 1 |
    last_result = Array |
    col_info =  |
    queries =  |
    prefix = wp_2490_ |
    ready = 1 |
    blogid = 2490 |
    siteid = 1 |

    and my live site has data in $wpdb in the install_blog_fucntion() that looks like
    

    suppress_errors = 1 |
    last_error = Table 'gzpteam_live.wp_2501_options' doesn't exist |
    num_queries = 199 |
    num_rows = 1 |
    rows_affected = 1 |
    insert_id = 2501 |
    last_query = SELECT option_value FROM wp_2501_options WHERE option_name = 'wp_2501_user_roles' LIMIT 1 |
    last_result = Array |
    col_info =  |
    queries =  |
    prefix = wp_2501_ |
    ready = 1 |
    blogid = 2501 |
    siteid = 1 |

    All looks good and normal here so I need to check a little deeper!

    When I dump $wpdb in schema.php just before the variable $wp_quries (the variable with the tables to be created) on my local dev I get:
    

    show_errors =  |
    suppress_errors = 1 |
    last_error = Table 'thegzpsb.wp_2491_options' doesn't exist |
    num_queries = 204 |
    num_rows = 1 |
    rows_affected = 1 |
    insert_id = 2491 |
    last_query = SELECT option_value FROM wp_2491_options WHERE option_name = 'wp_2491_user_roles' LIMIT 1 |
    last_result = Array |
    col_info =  |
    queries =  |
    prefix = wp_2491_ |
    ready = 1 |
    blogid = 2491 |
    siteid = 1 |

    but on my live site I get something like for $wpdb in schema.php:
    

    show_errors =  |
    suppress_errors =  |
    last_error =  |
    num_queries = 150 |
    num_rows = 1 |
    rows_affected = 1 |
    insert_id = 4097590 |
    last_query = SELECT COUNT(id) FROM wp_bp_follow WHERE follower_id = 1 |
    last_result = Array |
    col_info = Array |
    queries =  |
    prefix = wp_1_ |
    ready = 1 |
    blogid = 1 |
    siteid = 1 |

    So it looks like between install_blog() and the require_once of schema.php $wpdb resets or changes to thinking it should be dealing with blog number 1, not the newly attempted to be created sub blog.

    This sort of goes back to my first post. Why are the tables trying to be created have the blog id of 1? Are you positive that data in the table creation dump looks correct for a newly created sub blog (this is in my first post)?

    What happens to $wpdb in between install_blog() and the require_once(ABSPATH . 'wp-admin/includes/schema.php'); within updrade.php?

15. Ipstenu (Mika Epstein)
    Half-Elf Support Rogue & Mod
    Posted 1 year ago #
    

    I don't think so...

    last_error = Table 'thegzpsb.wp_2491_options' doesn't exist |

    That's just saying it can't find the right table which ... we know it's not creating it. It's SUPPOSED to look for that table.

    Why are the tables trying to be created have the blog id of 1?

    They don't, that I see. They have a SITE ID of 1. Which is correct.

    WP creates the tables and kicks you back to blog ID 1, network admin.

16. stoi2m1
    Member
    Posted 1 year ago #
    

    I have tried to separate out the important data. I think too much info in a single posts is confusing the topic.

    Sorry for the caps, just trying to catch your attention with data that I think is important.

    On my live site in install_blog()
    

    $wpdb -> prefix = wp_2491_
    $wpdb -> blogid = 2491

    On my live site in /wp-admin/schema.php THIS IS MY SUSPECTED PROMBLEM
    

    $wpdb -> prefix = wp_1_
    $wpdb -> blogid = 1

    In my first post the $cqueries is trying to create the following tables (which is based on the table prefix of $wpdb?). I THINK THIS IS THE PATH TO MY PROBLEM.
    

    wp_1_terms
    wp_1_term_taxonomy
    ...
    wp_1_posts
    wp_users
    wp_usermeta

    $for_update on live site is empty nothing is created because those tables already exits

    If you need I have the same formatted data of of how my local dev is working and its not trying to create the tables for BLOGID 1.

17. Ipstenu (Mika Epstein)
    Half-Elf Support Rogue & Mod
    Posted 1 year ago #
    

    Use bold ;)

    /wp-admin/schema.php .... Okay, I don't have that on my server. Anywhere. It's not in a pull of trunk either, so I'm wondering where you got it from...

    (ETA: What confuses me most is that if WP was trying to recreate wp_1_whatever, SQL should error out and say 'Can't make that, it already exists!' But what you're seeing looks, to me, like a clearcut 'I can't create that DB table.')

18. stoi2m1
    Member
    Posted 1 year ago #
    

    ooops typo on my part

    /wp-admin/includes/schema.php

19. Ipstenu (Mika Epstein)
    Half-Elf Support Rogue & Mod
    Posted 1 year ago #
    

    OH! Sorry about the DP. Are you looking at /wp-admin/includes/schema.php

    That brings up two weird things.

    1) WordPress Multisite doesn't use wp_1_ - WPMU did. Was this, perchance, an old WPMU install that was upgraded?

    2) Where in that file are you seeing $wpdb -> prefix = wp_1_, or are you saying that's what the error trace is outputting?

20. stoi2m1
    Member
    Posted 1 year ago #
    

    1) Yes, this was originally a WPMU install and since upgraded to WordPress MultiSite.

    2) following the line that defines the globals. It looks like this.
    

    global $wpdb, $wp_queries;
    
    foreach ($wpdb as $key => $db)
    	error_log($key .' = '.$db.' |
    	', 3, '../error_log.log');

21. Ipstenu (Mika Epstein)
    Half-Elf Support Rogue & Mod
    Posted 1 year ago #
    

    I only now noticed you're on 3.2.1 .... Okay, I've been testing on 3.3.1 and 3.4-aortic. Any chance of you upgrading to 3.3.1?

22. stoi2m1
    Member
    Posted 1 year ago #
    

    I need to test that with all my plugins, themes and other custom code. I dont know if i want to do that right now. And i heard theres lots of changes in the admin bar, i heavily customized that. Id likely have alot of things to fix.

23. Ipstenu (Mika Epstein)
    Half-Elf Support Rogue & Mod
    Posted 1 year ago #
    

    If you customized via functions, most will still work. But ... yeah. I don't know if this is 3.2.1 or the fact that you upgraded from WPMU (which has always been odd...)

    That a copy of the existing setup worked, though, makes me think it may be a sign to do that and 'move' to the copy :/

24. stoi2m1
    Member
    Posted 1 year ago #
    

    I converted to WP3 when it was released. Upgraded to where i am months ago. All has been fine until 2 weeks ago.

25. stoi2m1
    Member
    Posted 1 year ago #
    

    Boooooom! (my hands up in the air like I just hit a home run. Im such a geek), there it is, the culprit, a plugin, Business Directory (heads hanging in shame). Non the less Resolved!

    And the default troubleshooting methods bites me in the butt again. Deactivate the plugins one at a time and see when it starts working again. This makes me wonder in an activated date or upgraded date in the plugins section would be useful.

    I was just hoping something would point me to the single source of the problem instead of starting at one end and working my way to the other. In a sense its all the same. I think I learned more doing it this way, the hard way. Plus I never took my site offline to fix it.

    Thank You, Ipstenu, for you patients and dedication. I was frustrated many times cause I knew it was something I did.

    Note to self: keep a log of changes I make and when I made them. Easier said then done.

26. Ipstenu (Mika Epstein)
    Half-Elf Support Rogue & Mod
    Posted 1 year ago #
    

    This makes me wonder in an activated date or upgraded date in the plugins section would be useful.

    You can go to the plugin repo page and see when it was last updated. But even then ... It's not like you make a site every day, right? So this could have been months before you noticed, with tons of change :(

27. stoi2m1
    Member
    Posted 1 year ago #
    

    I meant in the plugins section of the dashboard.

    I work on this site almost daily, making new changes and updates almost regularly. I have a small not book of all the features we want it to have. Its been almost a years worth of work.

    Check it out and see what my wordpress site is like TheGZP.com

    I think the best thing for me is to keep a record of my changes.

Topic Closed

This topic has been closed to new replies.