I think that the Internal Server Error you're getting is fired due to the server ending the script because of its execution taking too long to load. This might be the same for all Forums with an high number of users.
The script Wildcard provided, although it is correct, runs one query per setting per user. Simplified example: if you have 9447 users and 5 alert types (why you're not using MentionMe, Plugins Alerts Pack or Moderation Alerts Pack? bad boy
), the script will run 1*5*9447 = 47235 queries. This is a really high value isn't it?
The solution is quite easy: rather than querying the database in the foreach loop, just build an array of data and then build a single query with insert_query_multiple() function. Result: 2 SELECT queries, 1 INSERT query, fast edits.
I've edited Wildcard's script, just upload and run it once by opening the file from your browser.
I haven't tested the script, but it should work.
Update 12/05/2013
The script has been updated with noticeable optimizations. Thanks to Omar G. for pointing them out (
http://community.mybb.com/thread-127444-...#pid987880 )
Update 04/07/2013
The script has been included in the core of MyAlerts v1.05. Download it from
GitHub.
(2013-03-29, 12:11 AM)Shade Wrote: [ -> ]I think that the Internal Server Error you're getting is fired due to the server ending the script because of its execution taking too long to load. This might be the same for all Forums with an high number of users.
The script Wildcard provided, although it is correct, runs one query per setting per user. Simplified example: if you have 9447 users and 5 alert types (why you're not using MentionMe, Plugins Alerts Pack or Moderation Alerts Pack? bad boy ), the script will run 1*5*9447 = 47235 queries. This is a really high value isn't it?
The solution is quite easy: rather than querying the database in the foreach loop, just build an array of data and then build a single query with insert_query_multiple() function. Result: 2 SELECT queries, 1 INSERT query, fast edits.
I've edited Wildcard's script, just upload and run it once by opening the file from your browser.
I haven't tested the script, but it should work.
Thanks
But i got this:
SQL Error:
1064 - 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 '* FROM mybb_alert_setting_values' at line 1
Query:
DELETE * FROM mybb_alert_setting_values
Sorry, there was a SQL error. I've updated the script you can download below. I've just tested it and it works fine (tested with a 1100 users' board with 21 alert types).
Euan, I'm enjoying your plugin! I have a suggestion so tell me what you think...
Add permissions to restrict user groups and user id's
(2013-03-29, 12:25 AM)Shade Wrote: [ -> ]Sorry, there was a SQL error. I've updated the script you can download below. I've just tested it and it works fine (tested with a 1100 users' board with 21 alert types).
Thanks Shade! That is so much better and I don't know why I didn't think of that.
You are awesome!
(2013-03-29, 12:25 AM)Shade Wrote: [ -> ]Sorry, there was a SQL error. I've updated the script you can download below. I've just tested it and it works fine (tested with a 1100 users' board with 21 alert types).
You're a Genious!
Done.
Alert Types: 5
Total Users: 9450
Infinite thanks to you
You can save up more queries if you don't require the global.php file. You can pretty much replicate the ajax file or even add a functionality on MyAlerts core to allow administrators to do this update via the ajax file. Or whatever xD
Also, the die() part may not even work because most probably an SQL error page will be shown instead.
You can as just select what you actually require from the DB (i.e: only 'uid' from the users table and 'id' from the alert_settings one).
(2013-03-29, 12:11 AM)Shade Wrote: [ -> ]I think that the Internal Server Error you're getting is fired due to the server ending the script because of its execution taking too long to load. This might be the same for all Forums with an high number of users.
The script Wildcard provided, although it is correct, runs one query per setting per user. Simplified example: if you have 9447 users and 5 alert types (why you're not using MentionMe, Plugins Alerts Pack or Moderation Alerts Pack? bad boy ), the script will run 1*5*9447 = 47235 queries. This is a really high value isn't it?
The solution is quite easy: rather than querying the database in the foreach loop, just build an array of data and then build a single query with insert_query_multiple() function. Result: 2 SELECT queries, 1 INSERT query, fast edits.
I've edited Wildcard's script, just upload and run it once by opening the file from your browser.
I haven't tested the script, but it should work.
EDIT: sorry there was a SQL error. Now it should work.
That was what I had intended doing my self, so you've saved me a job. Mind if I include this in the core of MyAlerts?
(2013-03-29, 12:32 AM)Prosper Wrote: [ -> ]Euan, I'm enjoying your plugin! I have a suggestion so tell me what you think...
Add permissions to restrict user groups and user id's
Thanks, glad you're enjoying it. I might consider doing that for usergroups in the next release. Might be a good idea
(2013-03-29, 03:41 AM)Omar G. Wrote: [ -> ]You can save up more queries if you don't require the global.php file. You can pretty much replicate the ajax file or even add a functionality on MyAlerts core to allow administrators to do this update via the ajax file. Or whatever xD
Also, the die() part may not even work because most probably an SQL error page will be shown instead.
You can as just select what you actually require from the DB (i.e: only 'uid' from the users table and 'id' from the alert_settings one).
True. You could just include init.php as I believe it sets the $db object up. Would make it slightly (barely noticeable) faster. I'll make that change before including it in the core if Shade allows it.
Re: running it via AJAX, I don't see the point. What I'd do is the same as frostschutz does in Google SEO to make core edits. Add a link to the plugin description that runs that file.
(2013-03-29, 09:56 AM)Euan T. Wrote: [ -> ]True. You could just include init.php as I believe it sets the $db object up. Would make it slightly (barely noticeable) faster. I'll make that change before including it in the core if Shade allows it.
Sure. Feel free to edit it
You don't even have to ask
Is just me, or the new version is not working with the Post Reputation plugin of Mynetwork?