Getting SK2 to work in mu-plugins for sites with existing blogs

Farms2
Member
Posted 17 years ago #

So, I reckon that pumpkinslayer's new SK2 port is the dogs...

http://wpmudevorg.wordpress.com/project/Spam-Karma-2-for-WPMU

But there's a very definite problem for sites with existing users - SK2 doesn't get going until you visit the configuration page, and as that config page is only accessible by Site Admin - it can't get activated and comments become errorsville.

So, two possible solutions:

1. An adapted upgrade script that calls all the configuration pages for SK2 from Site Admin... i.e. http://username.uniblogs.org/wp-admin/options-general.php?page=spamkarma2

2. A hack of SK2 so that it autoinstalls without having to visit the configure page.

I reckon (2) is the solution... but I'm buggered if I know where to start... not that that's going to stop me ;)

Any help very gratefully received in the spirit of fighting spam!

Cheers, james
lunabyte
Member
Posted 17 years ago #

I think I read you needed to install the first version, then upgrade it to the second.
Farms2
Member
Posted 17 years ago #

Tried that, only works on individual blogs if the user does something with comments... and besides it's bust with 1.1.1 anyway :(

I reckon it needs a fix so that users who haven't started out with SK2 can get it to work.
Farms2
Member
Posted 17 years ago #
OK, so in line 746 of wpmu-sk2.php we see this:
```
if ($sk2_settings->get_core_settings('init_install') < 1)
```
So I reckon I need to figure out how simply logging into blog will init_install ... um...
jutecht12
Member
Posted 17 years ago #
I'm trying to use SK2 on our school's WordPressMU site. After installing it this is the error we get when someone tries to comment.

WordPress database error: [Table 'saschina_blogs.wp_28_sk2_spams' doesn't exist]
SELECT
```
comment_table
```
.*,
```
posts_table
```
.*,
```
users_table
```
.*,
```
spam_table
```
.*,
```
spam_table
```
.
```
id
```
AS
```
spam_table_id
```
, NOW() AS
```
now_sql
```
FROM
```
wp_28_comments
```
AS
```
comment_table
```
LEFT JOIN
```
wp_28_posts
```
AS
```
posts_table
```
ON
```
posts_table
```
.
```
ID
```
=
```
comment_table
```
.
```
comment_post_ID
```
LEFT JOIN
```
wp_users
```
AS
```
users_table
```
ON
```
users_table
```
.
```
ID
```
=
```
comment_table
```
.
```
user_id
```
LEFT JOIN
```
wp_28_sk2_spams
```
AS
```
spam_table
```
ON
```
spam_table
```
.
```
comment_ID
```
=
```
comment_table
```
.
```
comment_ID
```
WHERE
```
comment_table
```
.
```
comment_ID
```
= 16

sk2_comment: Cannot fetch comment record from table.
SQL error: Table 'saschina_blogs.wp_28_sk2_spams' doesn't exist
Error: Submitted Post_ID variable (14) not matching ours (0).
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND
```
comments
```
.
```
comment_approved
```
= '1' AND
```
comments
```
.
```
comment_date_gmt
```
< DATE' at line 1]
SELECT COUNT(*) AS
```
cmt_count
```
, AVG(
```
spams
```
.
```
karma
```
) AS
```
karma_avg
```
FROM
```
wp_28_comments
```
AS
```
comments
```
LEFT JOIN
```
wp_28_sk2_spams
```
AS
```
spams
```
ON
```
spams
```
.
```
comment_ID
```
=
```
comments
```
.
```
comment_ID
```
WHERE
```
comments
```
.
```
comment_ID
```
!= AND
```
comments
```
.
```
comment_approved
```
= '1' AND
```
comments
```
.
```
comment_date_gmt
```
< DATE_SUB('', INTERVAL 3 DAY) AND
```
comments
```
.
```
comment_author_IP
```
= ''

get_granularity: query failed.
Query: SELECT COUNT(*) AS
```
cmt_count
```
, AVG(
```
spams
```
.
```
karma
```
) AS
```
karma_avg
```
FROM
```
wp_28_comments
```
AS
```
comments
```
LEFT JOIN
```
wp_28_sk2_spams
```
AS
```
spams
```
ON
```
spams
```
.
```
comment_ID
```
=
```
comments
```
.
```
comment_ID
```
WHERE
```
comments
```
.
```
comment_ID
```
!= AND
```
comments
```
.
```
comment_approved
```
= '1' AND
```
comments
```
.
```
comment_date_gmt
```
< DATE_SUB('', INTERVAL 3 DAY) AND
```
comments
```
.
```
comment_author_IP
```
= ''
SQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND
```
comments
```
.
```
comment_approved
```
= '1' AND
```
comments
```
.
```
comment_date_gmt
```
< DATE' at line 1
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND
```
comment_post_ID
```
= AND
```
comment_approved
```
= '1' AND
```
comment_date_gmt
```
> D' at line 1]
SELECT COUNT(*) AS
```
cmt_count
```
FROM
```
wp_28_comments
```
AS
```
comments
```
WHERE
```
comments
```
.
```
comment_ID
```
!= AND
```
comment_post_ID
```
= AND
```
comment_approved
```
= '1' AND
```
comment_date_gmt
```
> DATE_SUB(NOW() , INTERVAL 15 DAY)

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]
UPDATE
```
wp_28_comments
```
SET
```
comment_approved
```
= '0' WHERE
```
comment_ID
```
=

Error: cannot update comment entry ID: 0 to status: moderated.
SQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Cannot update sk2_kSpamTable info (no comment ID provided).

Warning: Cannot modify header information - headers already sent by (output started at /home/saschina/public_html/blogs/wp-includes/wp-db.php:111) in /home/saschina/public_html/blogs/wp-comments-post.php on line 55

Warning: Cannot modify header information - headers already sent by (output started at /home/saschina/public_html/blogs/wp-includes/wp-db.php:111) in /home/saschina/public_html/blogs/wp-comments-post.php on line 56

Warning: Cannot modify header information - headers already sent by (output started at /home/saschina/public_html/blogs/wp-includes/wp-db.php:111) in /home/saschina/public_html/blogs/wp-comments-post.php on line 57

Warning: Cannot modify header information - headers already sent by (output started at /home/saschina/public_html/blogs/wp-includes/wp-db.php:111) in /home/saschina/public_html/blogs/wp-includes/pluggable.php on line 281

From what I understand SK2 needs to make a table in the database for each blog? Is there a way to make it do this for blogs that already exist?
Farms2
Member
Posted 17 years ago #

That's exactly my problem - it comes from the issues above.
Farms2
Member
Posted 17 years ago #

Installing the old version into 1.1.1 mu-plugins and then trying the new one doesn't work either :(
jutecht12
Member
Posted 17 years ago #

I don't know enough about .php and MYSQL but is there a way to make SK2 go through and create a table for each blog? It works if you create a new blog after installing it, but does nothing for the 300+ student blogs we have running already.

No of any other Spam Filters that work with MU? I love SK2 on my personal install so thought it would be good for the MU site as well....

ug
Farms2
Member
Posted 17 years ago #

It's critical and I'm in the same boat as you code-wise... we really need it to install it as soon as every blog is accessed.

Mr Ball once wrote soemthing like this... I wonder if I could hack it:

http://mu.wordpress.org/forums/topic.php?id=2070&replies=22#post-12521
zeug
Member
Posted 17 years ago #
I tried using a hack of wpmu-upgrade-site.php, called it sk2_install.php and dropped it in wp-admin, then loaded it in the browser. It calls the sk2_wpmu_new_blog_create_tables function via a line I added in wpmu_sk2.php:

if (isset($_GET['update_site'])) { sk2_wpmu_new_blog_create_tables ($blog_id, $user_id); return; }

This is totally just a proof of concept hack, seems to have worked on my 100+ blog Mu and not killed anything ... probably ... at least all the wp_x_sk2_spams tables are in there.

sk2_install.php:

<?php require_once('admin.php');
$http_fopen = ini_get("allow_url_fopen"); if(!$http_fopen) require_once('../wp-includes/class-snoopy.php'); $title = __('WPMU Admin'); $parent_file = 'options-general.php?page=spamkarma2'; require_once('admin-header.php'); if( is_site_admin() == false ) { die( __('<p>You do not have permission to access this page.</p>') ); } if (isset($_GET['updated'])) { ?><div id="message" class="updated fade"><p><?php _e('Options saved.') ?></p></div><?php } print '<div class="wrap">'; switch( $_GET[ 'action' ] ) { case "upgrade": if( isset( $_GET[ 'n' ] ) == false ) { $n = 0; } else { $n = intval( $_GET[ 'n' ] ); } $blogs = $wpdb->get_results( "SELECT * FROM $wpdb->blogs WHERE site_id = '$wpdb->siteid' AND spam = '0' AND deleted = '0' AND archived = '0' ORDER BY registered DESC LIMIT $n, 5", ARRAY_A ); if( is_array( $blogs ) ) { print " "; foreach( $blogs as $details ) { if( $details[ 'spam' ] == 0 && $details[ 'deleted' ] == 0 && $details[ 'archived' ] == 0 ) { $siteurl = $wpdb->get_var( "SELECT option_value from {$wpmuBaseTablePrefix}{$details[ 'blog_id' ]}_options WHERE option_name = 'siteurl'" ); print " $siteurl "; if($http_fopen) { $fp = fopen( $siteurl . "wp-admin/options-general.php?page=spamkarma2&sk2_section=general&update_site", "r" ); if( $fp ) { while( feof( $fp ) == false ) { fgets($fp, 4096); } fclose( $fp ); } } else { $client = new Snoopy(); @$client->fetch($siteurl . "wp-admin/options-general.php?page=spamkarma2&sk2_section=general&update_site"); } } } print " "; ?> <p>If your browser doesn't start loading the next page automatically click this link: ">Next Blogs </p> <script language='javascript'>  </script> <?php } else { print "All Done!"; } break; default: ?> <p>Install SK2 on all the old blogs on your site. (New blogs install SK2 by default)</p> <p>Upgrade Site</p> <?php break;
} ?> </div> <?php include('admin-footer.php'); ?>
Farms2
Member
Posted 17 years ago #

Well I for one am all up for proof of concept :) I'll give it a shot now on eslblogs (700 odd blogs).
jutecht12
Member
Posted 17 years ago #

Let me know if it works for you and how you did it and I'll try it this weekend on our site. 560 blogs.
Ovidiu
Member
Posted 17 years ago #

could someone incorporate/mod the mu version of sk2 from wpmudev.org so there is no more need for patching?

Farms2
Member
Posted 17 years ago #

Update: Actually it is just that - insert the wpmu-sk2.php code in line 33 and you'll be laughing - all good as far as I can tell using the code below :)

Almost there, I can feel it - I made a couple of changes, deleting an errant asterix and fixing up the hrefs but we're still getting the

WordPress database error: [Table 'eslblogs_wpmu4.wp_5_sk2_spams' doesn't exist]

Error on every 'Comments' tab and comments can't be posted... it could well be the line at which I've inserted:

if (isset($_GET['update_site'])) {
sk2_wpmu_new_blog_create_tables ($blog_id, $user_id);
return;
}

in wpmu_sk2.php - can someone help me out?

<?php
require_once('admin.php');

$http_fopen = ini_get("allow_url_fopen");
if(!$http_fopen) require_once('../wp-includes/class-snoopy.php');

$title = __('WPMU Admin');
$parent_file = 'options-general.php?page=spamkarma2';
require_once('admin-header.php');
if( is_site_admin() == false ) {
die( __('You do not have permission to access this page.
') );
}
if (isset($_GET['updated'])) {
?><div id="message" class="updated fade"><?php _e('Options saved.') ?>
</div><?php
}
print '<div class="wrap">';
switch( $_GET[ 'action' ] ) {
case "upgrade":
if( isset( $_GET[ 'n' ] ) == false ) {
$n = 0;
} else {
$n = intval( $_GET[ 'n' ] );
}
$blogs = $wpdb->get_results( "SELECT * FROM $wpdb->blogs WHERE site_id = '$wpdb->siteid' AND spam = '0' AND deleted = '0' AND archived = '0' ORDER BY registered DESC LIMIT $n, 5", ARRAY_A );
if( is_array( $blogs ) ) {
print "

      ";
      foreach( $blogs as $details ) {
      if( $details[ 'spam' ] == 0 && $details[ 'deleted' ] == 0 && $details[ 'archived' ] == 0 ) {
      $siteurl = $wpdb->get_var( "SELECT option_value from {$wpmuBaseTablePrefix}{$details[ 'blog_id' ]}_options WHERE option_name = 'siteurl'" );
      print " $siteurl
      ";
      if($http_fopen) {
      $fp = fopen( $siteurl . "wp-admin/options-general.php?page=spamkarma2&sk2_section=general&update_site", "r" );
      if( $fp ) {
      while( feof( $fp ) == false ) {
      fgets($fp, 4096);
      }
      fclose( $fp );
      }
      } else {
      $client = new Snoopy();
      @$client->fetch($siteurl . "wp-admin/options-general.php?page=spamkarma2&sk2_section=general&update_site");
      }
      }
      }
      print "

";
?>
If your browser doesn't start loading the next page automatically click this link: <a href="sk2_install.php?action=upgrade">Next Blogs</a> 

<script language='javascript'>
<!--

function nextpage() {
location.href="sk2_install.php?action=upgrade&n=<?php echo ($n + 5) ?>";
}
setTimeout( "nextpage()", 250 );

//-->
</script>
<?php
} else {
print "All Done!";
}
break;
default:
?>
Install SK2 on all the old blogs on your site. (New blogs install SK2 by default)

<a href="sk2_install.php?action=upgrade">Upgrade Site</a>

<?php
break;

}
?>
</div>
<?php include('admin-footer.php'); ?>

Cheers, James

Zeug - yet again I drink a glass of redgate ullinger reserve 2001 cab sav in your honour... although I'm running out of ciggies :(

zeug
Member
Posted 17 years ago #

Damn, I've got the ciggies but no plonk! Well, there's an old cask lying around but it's probably gone to vinegar. Life can be so cruel sometimes, especially when messing with php, but at least this script looks like a goer.

I put the 'get' just above the actual function in wpmu-sk2.php, near the end. No idea why it should matter.
jutecht12
Member
Posted 17 years ago #

OK...this is looking good, but I'm lost on what I need to do. What files need to go where and what I need to call. Can someone put this in a step by step for me.?

Thanks,

Jeff
Farms2
Member
Posted 17 years ago #
Glad to be of help rather than usual lengthy requests for it :)

1. Be on WPMU 1.1.1 (The current main download: http://mu.wordpress.org/download/ - don't try this with one that's months old)

2. Grab the latest SK2 mod from WPMUdev and upload it into mu-plugins: http://wpmudevorg.wordpress.com/project/Spam-Karma-2-for-WPMU

3. Modify the file wpmu-sk2.php by adding the following lines at line 33:
```
if (isset($_GET['update_site'])) {
sk2_wpmu_new_blog_create_tables ($blog_id, $user_id);
return;
}
```
4. Copy the code from the above post into a php file, call it sk2_install.php and upload it to wp-admin

5. Go to http://yoursite.com/wp-admin/sk2_install.php and click on 'Upgrade'

6. If your site decides to crash half way through make a note of the URL it crashes on - load exactly the same URL again when things are back up and running and it'll continue from where it left off.

Cheers, James
Farms2
Member
Posted 17 years ago #

Working well 7K into the 21K learnerblogs install... occasionally flunks out but generally seems alright :)
jutecht12
Member
Posted 17 years ago #

Just finished my 560 install and it worked perfectly. Now it's time for the vino! Thanks James for the flawless instructions, and zeug for all the work. Now after all this I hope it helps stop spam. :)

Thanks again everyone!

You can check out the site a http://blogs.saschinaonline.org our students and teachers thank you!

Jeff
zeug
Member
Posted 17 years ago #

Hi jutecht12, great mu you got there, I've started a uni mu as well at:

http://soca.ecu.edu.au/

My fellows are somewhat less enthusiastic as far as the uptake goes.

Have you used SK2 on your subdirectory install before? It seems to be working fine on mine, so long as those blog tables are in there although there were some addressing problems in the options tabs on the last version.
Farms2
Member
Posted 17 years ago #

And 10K in... it stopped :(

Basically it's this problem: http://mu.wordpress.org/forums/topic.php?id=3588&replies=1

On a P4 4GigRAM box in the middle of the night (US) on a weekend simply running this script at around the 10K number took the server load from 2.0 to over 12.

It's not a problem with this script, it's a problem for anyone who's running a large-ish site off a single database and I *wish* I could figure it out.

Perhaps adapting the script to run only by cronjob and for that cronjob to be called every 5 minutes?
lunabyte
Member
Posted 17 years ago #

Hmmm. I'm wondering if it might be possible to break it up into groups of say, 1000 or 500 or something. If it's been OK up to that point, as it seems it has in most cases, then maybe a script that runs X number of times, then pauses, runs the next set and pauses, etc would help decrease the load and make it complete.

Either that, or set it so that the script only runs for a blog when it's visited. Then the function is only run once, and not much "worse" than a page load where the cache is updated.
jutecht12
Member
Posted 17 years ago #

hhhhmmm...OK not sure if the two are related. SK2 is working well for me, but the sidebar widget plugin is not working. The AJAX drag and drop doesn't work...anyone else experiencing this? Just trying to find out if the two things are related.
lunabyte
Member
Posted 17 years ago #

I had that problem initially after I upgraded to 1.1.1, but it was a combination browser cache issue and one of the js files were changed.
Farms2
Member
Posted 17 years ago #

widgets fine with 1.1.1. as far as I can tell and no relation to SK2 or upgrade issues.

Cheers, James
jutecht12
Member
Posted 17 years ago #

Widgets still not working. I think it's a 1.1.1 issue come to think of it. I upgraded and added SK2 all at the same time so the two are probably different issues. Lunabyte mentioned a js file anyone know where and which file?

Also now with SK2 users get this message every time they try to comment:

"Sorry, but your comment has been flagged by the spam filter running on this blog: this might be an error, in which case all apologies. Your comment will be presented to the blog admin who will be able to restore it immediately.
You may want to contact the blog admin via e-mail to notify him."

It doesn't matter if you are logged in or not. Is this a global setting. The length of the comment and whether or not it has links doesn't seem to affect the out come.
zeug
Member
Posted 17 years ago #

Comments are working fine with sk2 on my Mu subdirectories, and it's catching spam, so no idea what's wrong sorry.
Farms2
Member
Posted 17 years ago #

Oh Arse.

Am still having the same problem that we had before (which was kindof a dealbreaker) in that SK2 overrides moderation settings in Options > Discussion.

Everyone else getting this? This looks like a possible fix although obviously the changing admin settings in each blog is more pain than I can imagine:

http://blog.ftwr.co.uk/wordpress/sk2-moderate-plugin/

Cheers, James
Farms2
Member
Posted 17 years ago #

OK, that works a treat - however, it basically drops all comments into 'Spam' when moderation is on - so you need to edit wp-admin/options-discussion.php to make this clear to users.

Have tested both on and off and all good.
jutecht12
Member
Posted 17 years ago #

I had to disinstall it today as it was marking every post a spam and teachers and students were complaining. Did you get this to work? What did you do?

1. Install the http://blog.ftwr.co.uk/wordpress/sk2-moderate-plugin/ plugin in the mu-plugin folder
2. Turn something on or off in the Options-discussion setting..is that for each blog, or just in the admin setting.

Would really like to reinstall it.

Jeff

1 2 Next »

WordPress MU

The MU forums have moved to WordPress.org

Getting SK2 to work in mu-plugins for sites with existing blogs (37 posts)

About this Topic

Code is Poetry