WordPress.org

Forums

SharDB
[resolved] connection problem... (30 posts)

  1. bbizz
    Member
    Posted 4 years ago #

    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

  2. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 4 years ago #

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

    Did you migrate your data over to your shards?

  3. bbizz
    Member
    Posted 4 years ago #

    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

  4. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 4 years ago #

    /* 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.

  5. bbizz
    Member
    Posted 4 years ago #

    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...

  6. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 4 years ago #

    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.

  7. bbizz
    Member
    Posted 4 years ago #

    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!

  8. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 4 years ago #

    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.

  9. bbizz
    Member
    Posted 4 years ago #

    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

  10. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 4 years ago #

    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.

  11. bbizz
    Member
    Posted 4 years ago #

    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 :)

  12. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 4 years ago #

    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.

  13. bbizz
    Member
    Posted 4 years ago #

    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.

  14. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 4 years ago #

    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.

  15. webraket
    Member
    Posted 4 years ago #

    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.

  16. bbizz
    Member
    Posted 4 years ago #

    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?

  17. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 4 years ago #

    Tables for new blogs/sites are automatically created in the right database. You only need to migrate the data when you are setting SharDB up.

    @webraket - Someone had already sent me a note on that issue. I hadn't had a chance to test it yet. I would still like to be able to catch an error if there is one creating the table (which that fix doesn't check for).

  18. daneshjoo
    Member
    Posted 3 years ago #

    I set acording to:
    require_once('db-settings.php');
    /* That's all, stop editing! Happy blogging. */

    but in my admin show error for loading link
    domain.com/wp-admin/tools.php?page=shardb_migrate
    :

    you must configure your database settings by adding require('./db-settings.php'); to your /home/domain/public_html/wp-config.php for the migration process to work

  19. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 3 years ago #

    you must configure your database settings by adding require('./db-settings.php'); to your /home/domain/public_html/wp-config.php for the migration process to work

    If you are running suPHP or suExec, check the permissions on the db-settings.php file. If the permissions are wrong with either of those running, you don't have permission to access the file.

    Also, did you add your configuration to db-settings? The sample configuration in db-settings.php is commented out.

  20. daneshjoo
    Member
    Posted 3 years ago #

    I set permission on 755 but show same error.

    codes :

    /* Add your configuration here */

    $shardb_prefix = 'domain_wp';
    $enable_home_db = true;
    $shardb_local_db = true;

    /* Use this configuration for a hexidecimal based hash

  21. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 3 years ago #

    You are missing some required configuration values

    // 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;
  22. daneshjoo
    Member
    Posted 3 years ago #

    ok tnx,resolve.

    I whene click on (migrate global table) show:
    wp_blogs copied to domain_wpglobal
    wp_registration_log copied to domain_wpglobal

    but isnot wpglobal in my data base list.
    why?

    or with click on migrate sites show:
    wp_2_pollsa copied to domain_wpc8
    wp_2_pollsip copied to domain_wpc8
    wp_2_pollsq copied to domain_wpc8

    but wpc8 in empty!!!
    and show just tables from domain.com and blog1.com:
    wp_ and wp_2_

    after I upload db.php,show in admin:

    Connected to localhost:3306 but unable to select database 'domain_wpglobal' while querying table 'wp_sitemeta' (global_r)Connected to localhost:3306 but unable to select database 'domain_wpglobal' while querying table 'wp_blogs' (global_r)
    Warning: Cannot modify header information - headers already sent by (output started at /home/domain/public_html/wp-content/db.php:365) in /home/domain/public_html/wp-includes/ms-settings.php on line 94

    please help me.

  23. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 3 years ago #

    I recommend you consider buying the installation guide. We wrote that as an inexpensive alternative to hiring me to do the installation for you.

  24. daneshjoo
    Member
    Posted 3 years ago #

    how many is installation guide?
    too how many if you install for me?

    but I have 4 sites with wp multisite,and I want learn managing shardb myself.

  25. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 3 years ago #

  26. daneshjoo
    Member
    Posted 3 years ago #

    I buy guide.

    my database:
    1.1 gb size , 7,547,844 row,
    and wp_2_postmeta has 1,908,860 row , 230mb size
    and wp_2_term_relationships has 3,747,716 row and 207mb size

    after migrate wp_2_postmeta or wp_2_term_relationships Cleave in Several database??
    Otherwise for example even if I set domain_09 for wp_2_postmeta or wp_2_term_relationships it(domain_09) will be 230 mb size?

  27. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 3 years ago #

    and wp_2_postmeta has 1,908,860 row , 230mb size
    and wp_2_term_relationships has 3,747,716 row and 207mb size

    With large tables like that you'll probably need to manually copy those to the correct shard. Your webserver has a time limit on how long the request can run. Really large tables like that are probably running to the time limit and getting killed off by the webserver.

    Have you checked your post tags and categories to see if you can remove some of those.

    One thing you could do with the postmeta table is delete all the lock records (they keep track of who is editing the posts).

    If you have ssh access you can copy a table manually with

    mysqldump -u username -ppassword old_db wp_2_postmeta | mysql -u username -ppassword new_db

  28. daneshjoo
    Member
    Posted 3 years ago #

    yes, my site is on the server with:
    4 cpu,8gb ram,shared whit private ip
    and with themes deluxtheme my server downed.therfore I use now defual theme wordpress.

    my site have 320,000 post after 2 years,and all are useful.
    posts inport automaticaly by autoblog plugin from another sites.

    If you have ssh access you can copy a table manually with

    mysqldump -u username -ppassword old_db wp_2_postmeta | mysql -u username -ppassword new_db

    can I copy it by seting below code in config location? :
    add_db_table( 'wp_2_postmeta', 'domain_08' );
    ?

    too, my site is news site and import news from another site,it is in 10 language(10 blog in subdomain):en.domain.com,it.doamin.com,...
    after 1 or 2 year other table in another language also will be large size,now you can help me for future and resolve this problem?

    thanks a lot

  29. daneshjoo
    Member
    Posted 3 years ago #

    If you have ssh access you can copy a table manually with

    mysqldump -u username -ppassword old_db wp_2_postmeta | mysql -u username -ppassword new_db

    too new_db will be larg size,about 230mb
    and still it can down server?

  30. Ron Rennick
    MultiSite Guru
    Plugin Author

    Posted 3 years ago #

    can I copy it by seting below code in config location? :
    add_db_table( 'wp_2_postmeta', 'domain_08' );
    ?

    No, you have to copy it via the command line per my previous post or export with phpmyadmin and import into the other db.

    too new_db will be larg size,about 230mb
    and still it can down server?

    With 320,000 posts you'll have to make some decisions about what content you want to keep. That's the largest number of posts that I've seen in a single site.

Topic Closed

This topic has been closed to new replies.

About this Plugin

  • SharDB
  • Frequently Asked Questions
  • Support Threads
  • Reviews

About this Topic