WordPress.org

Support

Support » Plugins and Hacks » SharDB » [Resolved] [Plugin: SharDB] connection problem…

[Resolved] [Plugin: SharDB] connection problem…

  • Hi,
    I’ve installed SharDB but for some reason my main site displays this error after I installed it.

    Warning: fsockopen() [function.fsockopen]: unable to connect to localhost:3306 (Operation timed out) in /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php on line 521

    I set the db-settings.php like this…

    // how many characters of hexidecimal hash
    $shardb_hash_length = 1;
    // what is the prefix of your blog database shards (everything before the hexidecimal hash)
    $shardb_prefix = ‘user1_wpmu_’;
    // set a string to be used as an internal identifier for the dataset
    $shardb_dataset = ‘abc’;
    // do you want to put your primary blog (blog_id 1) in its own ‘home’ database?
    $enable_home_db = false;
    // how many, if any, VIP databases do you have?
    $num_vipdbs = 0;
    // add this to set the write master read priority (default 1)
    // $shardb_master_read = 99;
    // add this if all of your databases are on a local server
    $shardb_local_db = true;
    // use this function to add a read slave host
    // add_slave($read_priority, $hostname, $local_hostname, $user, $password);

    I made the databases like this…
    user1_wpmu_0 to user1_wpmu_9 then user1_wpmu_a user1_wpmu_f

    I did everything else the instructions told me. I’m trying to use this on an existing wordpress site. I’d really appreciate any help with this.

    TIA

Viewing 15 replies - 1 through 15 (of 29 total)
  • Plugin Author Ron Rennick

    @wpmuguru

    Are your configuration settings inside the /* and */ ?

    Did you migrate your data over to your shards?

    Hi Ron, thanks for the reply.

    Yes the configuration for db-settings.php is in the /* */

    It’s between these lines…
    /* Use this configuration for a hexidecimal based hash

    And this one…

    // instructions for adding vip blogs at the bottom of this confg filei
    */

    I didn’t migrate my data over. It’s still in the main database that I first made when installing wordpress. I saw you posted a developer version(I think it was) and I planned on using that for migrating. But that error appears on the pages and I can’t even login. Although it does seem to be connecting to my original database because the pages still come up.

    Thanks

    Plugin Author Ron Rennick

    @wpmuguru

    /* and */ comment those lines out. You have to put your configuration outside of that. Those are there so you have a sample to work with.

    Also, don’t install db.php until you have migrated the data.

    Thanks Ron that did fix the error above. The migration did seem to work too. Although I did run into another problem. I’m getting this error when I click on a post etc.

    Warning: Invalid argument supplied for foreach() in /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php on line 271
    Unable to connect to : while querying table ‘wp_sitemeta’ (global_r)
    Warning: Invalid argument supplied for foreach() in /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php on line 271
    Unable to connect to : while querying table ‘wp_blogs’ (global_r)
    Warning: Cannot modify header information – headers already sent by (output started at /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php:271) in /usr/home/user1/domains/mysite.com/public_html/wp-includes/ms-settings.php on line 91

    I have this line in db-settings.php commented out because it won’t connect at all if it’s enabled…

    // add_db_server('global', 0, '', 1, 1, DB_HOST, DB_HOST, DB_NAME, DB_USER, DB_PASSWORD);

    I did try to put my info there on that line uncommented but that didn’t seem to work either. I’m not sure if it would fix the error anyway…

    I figured it would use my old database as global for now. So I’m kind of lost here…

    Plugin Author Ron Rennick

    @wpmuguru

    The migration tool does not migrate the global tables. I plan on adding that at some point in the future.

    Commenting out that line will definitely take your site down. Try creating the global database following the suggested naming convention. Once you have the data in the global database edit your wp-config.php and change the DB_NAME to your new global database.

    SharDB is quite customizable, but I only recommend you get into that area if you’re familiar enough with PHP, MySQL & the WP DB class to do your own fixing if/when you have issues.

    Hi Ron,
    For right now I was just going to use the old main database for global until I can figure out what tables needs to be copied so I can stop using that the old one. Using the old database as global for now should work right?

    When I enable this line here the page just keeps trying to load…

    add_db_server('global', 0,    '', 1, 1,    DB_HOST, DB_HOST, DB_NAME, DB_USER, DB_PASSWORD);

    I also tried removing the database info from the wp-config.php and then adding all that info to the line above but the same thing happens. Do you have any idea why the page would just keep trying to load? Actually the page stopped trying to load and it says this several times…

    Warning: fsockopen() [function.fsockopen]: unable to connect to DB_HOST:3306 (Operation timed out) in /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php on line 521
    
    Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'DB_HOST' (4) in /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php on line 327

    thanks!

    Plugin Author Ron Rennick

    @wpmuguru

    Is your require('./db-settings.php'); at the top of the file?

    You need to put it after the database configuration lines and before the that's all, stop editing line.

    Hi Ron,

    I had require(‘./db-settings.php’); below the database settings in wp-config. But I put it even lower just above the “that’s all” line but I’m still seeing errors on the page…

    Warning: fsockopen() [function.fsockopen]: unable to connect to localhost:3306 (Operation timed out) in /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php on line 521

    Warning: fsockopen() [function.fsockopen]: unable to connect to localhost:3306 (Operation timed out) in /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php on line 521

    Warning: Cannot modify header information – headers already sent by (output started at /usr/home/user1/domains/mysite.com/public_html/wp-content/db.php:521) in /usr/home/user1/domains/mysite.com/public_html/wp-includes/functions.php on line 3125
    Error establishing a database connection

    Plugin Author Ron Rennick

    @wpmuguru

    Note that you now have different error messages. So, the previous issue was that you did have that line in the wrong place.

    If your site is large enough to need SharDB, you should be looking for a programmer to work part time on your install. Relying on me being around to do all your troubleshooting through this forum probably won’t lead to a successful site in the long term.

    Honestly I’m not sure if my site is large enough to need sharDB. My host said 5000 tables in one database is probably too many. So I figured I would work on splitting them up into more databases.

    But anyway I’ll look for a programmer or maybe give multi-db a shot.

    Thanks for trying 🙂

    Plugin Author Ron Rennick

    @wpmuguru

    If you’re on a shared host then you are getting in the range where they are going to want you to move to a bigger account. If your site is growing, sharding the DB will buy you a few months, but you should start making plans to move to a VPS.

    There are a handful of different multi-DB options/plugins available. Due to the nature of what they do, with all of them you have to be able to implement an alternate configuration. Whether or not you choose to implement another plugin long term, you should try to sort out what you’ve missed in the configuration so you know what you have to do with the other plugin.

    Hi Ron,
    It turns out this error was just due to mysql not being configured right.
    “Warning: fsockopen() [function.fsockopen]: unable to connect to localhost:3306 (Operation timed out)”

    Although the page now just displayed this…
    Error establishing a database connection

    I checked the databases and noticed the data wasn’t all copied over to the shard databases. I tried it again after the fsockopen error was fixed but it’s doing the same thing. I can see it did create the tables but there’s no data in them. Oh well at least I can see where the tables go, I may just have to do it manually.

    Thanks again for the help. Oh btw it’s on a VPS with 2GB of ram. I was having issues with the VPS running out of memory and using swap. My host said it was probably due to wordpress and mysql because there was so many tables.

    Plugin Author Ron Rennick

    @wpmuguru

    You’re memory issue probably wasn’t due to the number of tables. After you do the sharding you will still have as many tables. I was working on an install in the last couple weeks with a similat VPS spec as yours and it had well over twice as many tables in one DB as you do.

    You’re performance will improve some through the sharding but you should look around for another issue. The best place to start is your error log.

    I’ve encountered the same problems.

    I fixed the “unable to connect to localhost:3306” error by commenting
    # skip-networking in my.cnf
    Mysql was only listening to local connections, it still does (firewall rules), but somehow “skip-networking” doesn’t allow the fsockopen() connection in db.php.

    The migration tool repeatedly returned an empty error after creating tables in the shards, this prevented the data from being inserted.
    I fixed this by changing:

    if( !$target_object->query( $create[1] ) ) {
    $errors[] = $target_object->last_error;
    continue;
    }

    to:

    $target_object->query( $create[1] );
    in shardb-admin.php

    I also changed:

    $sites = $wpdb->get_col( "SELECT blog_id FROM {$wpdb->blogs} ORDER BY blog_id ASC LIMIT {$next}, 5" );

    to:

    $sites = $wpdb->get_col( "SELECT blog_id FROM {$wpdb->blogs} ORDER BY blog_id ASC LIMIT {$next}, 1" );

    To prevent memory problems, with large tables.

    I wish I would’ve waited for your reply webraket, that would’ve saved me a lot of time. I spent about 12 hours copying the data over manually.

    Would changing this help or even work after I’ve already copied the data?
    $sites = $wpdb->get_col( “SELECT blog_id FROM {$wpdb->blogs} ORDER BY blog_id ASC LIMIT {$next}, 5” );

    I’m guessing it wouldn’t do any good for tables already created but maybe for new ones?

Viewing 15 replies - 1 through 15 (of 29 total)
  • The topic ‘[Resolved] [Plugin: SharDB] connection problem…’ is closed to new replies.
Skip to toolbar