My host needs to know, to troubleshoot certain issues, what type of query or syntax is used by MyBB to make backups/dumps of the database when performing a backup using the schedule system.
I don't know where I can find this. Help appreciated
No query is used iirc. You can use things like mysqldumper, the built in backup system of MyBB or the host's cPanel backup system.
Just selects everything and writes to a file... what issues are you having exactly??
(2010-05-21, 10:09 PM)MattRogowski Wrote: [ -> ]Just selects everything and writes to a file... what issues are you having exactly??
Well, I had two mysql table crashes last week. My host looked at the log files and it seems a specific incident only affecting me (and not other clients on the server). They say that *sometimes* automated database dumping can cause issues like this depending on how the query is executed and things happening on the server at the same time. To investigate the recent table crashes they would like to know what query is used in order to see if those two things are related to each other.
Have a look in ./inc/tasks/backupdb.php. First it selects the tables, goes through each one, gets the fields, gets the structure to create the table again, then selects all the data from the table and writes it. It's effectively having to select the entire database, but there's no other way of doing it, wouldn't have thought that would have made the tables crash, and seems to work fine on all other hosts.
If it helps any, these are the queries run for me in a test forum by the weekly backup task. Sorry for the bad formatting.
SELECT * FROM mybb_tasks WHERE tid='4'
SELECT * FROM mybb_tasks WHERE tid='4'
UPDATE mybb_tasks
SET `locked`='1274531418' WHERE tid='4'
UPDATE mybb_tasks
SET `nextrun`='1274565600' WHERE tid='4'
SHOW TABLES FROM `forum` LIKE 'mybb_%'
SHOW FIELDS FROM mybb_adminlog
SHOW CREATE TABLE mybb_adminlog
SELECT * FROM mybb_adminlog
SHOW FIELDS FROM mybb_adminoptions
SHOW CREATE TABLE mybb_adminoptions
SELECT * FROM mybb_adminoptions
SHOW FIELDS FROM mybb_adminsessions
SHOW CREATE TABLE mybb_adminsessions
SELECT * FROM mybb_adminsessions
SHOW FIELDS FROM mybb_adminviews
SHOW CREATE TABLE mybb_adminviews
SELECT * FROM mybb_adminviews
SHOW FIELDS FROM mybb_announcements
SHOW CREATE TABLE mybb_announcements
SELECT * FROM mybb_announcements
SHOW FIELDS FROM mybb_attachments
SHOW CREATE TABLE mybb_attachments
SELECT * FROM mybb_attachments
SHOW FIELDS FROM mybb_attachtypes
SHOW CREATE TABLE mybb_attachtypes
SELECT * FROM mybb_attachtypes
SHOW FIELDS FROM mybb_awaitingactivation
SHOW CREATE TABLE mybb_awaitingactivation
SELECT * FROM mybb_awaitingactivation
SHOW FIELDS FROM mybb_badwords
SHOW CREATE TABLE mybb_badwords
SELECT * FROM mybb_badwords
SHOW FIELDS FROM mybb_banfilters
SHOW CREATE TABLE mybb_banfilters
SELECT * FROM mybb_banfilters
SHOW FIELDS FROM mybb_banned
SHOW CREATE TABLE mybb_banned
SELECT * FROM mybb_banned
SHOW FIELDS FROM mybb_calendarpermissions
SHOW CREATE TABLE mybb_calendarpermissions
SELECT * FROM mybb_calendarpermissions
SHOW FIELDS FROM mybb_calendars
SHOW CREATE TABLE mybb_calendars
SELECT * FROM mybb_calendars
SHOW FIELDS FROM mybb_captcha
SHOW CREATE TABLE mybb_captcha
SELECT * FROM mybb_captcha
SHOW FIELDS FROM mybb_datacache
SHOW CREATE TABLE mybb_datacache
SELECT * FROM mybb_datacache
SHOW FIELDS FROM mybb_events
SHOW CREATE TABLE mybb_events
SELECT * FROM mybb_events
SHOW FIELDS FROM mybb_forumpermissions
SHOW CREATE TABLE mybb_forumpermissions
SELECT * FROM mybb_forumpermissions
SHOW FIELDS FROM mybb_forums
SHOW CREATE TABLE mybb_forums
SELECT * FROM mybb_forums
SHOW FIELDS FROM mybb_forumsread
SHOW CREATE TABLE mybb_forumsread
SELECT * FROM mybb_forumsread
SHOW FIELDS FROM mybb_forumsubscriptions
SHOW CREATE TABLE mybb_forumsubscriptions
SELECT * FROM mybb_forumsubscriptions
SHOW FIELDS FROM mybb_google_seo
SHOW CREATE TABLE mybb_google_seo
SELECT * FROM mybb_google_seo
SHOW FIELDS FROM mybb_groupleaders
SHOW CREATE TABLE mybb_groupleaders
SELECT * FROM mybb_groupleaders
SHOW FIELDS FROM mybb_helpdocs
SHOW CREATE TABLE mybb_helpdocs
SELECT * FROM mybb_helpdocs
SHOW FIELDS FROM mybb_helpsections
SHOW CREATE TABLE mybb_helpsections
SELECT * FROM mybb_helpsections
SHOW FIELDS FROM mybb_icons
SHOW CREATE TABLE mybb_icons
SELECT * FROM mybb_icons
SHOW FIELDS FROM mybb_joinrequests
SHOW CREATE TABLE mybb_joinrequests
SELECT * FROM mybb_joinrequests
SHOW FIELDS FROM mybb_mailerrors
SHOW CREATE TABLE mybb_mailerrors
SELECT * FROM mybb_mailerrors
SHOW FIELDS FROM mybb_maillogs
SHOW CREATE TABLE mybb_maillogs
SELECT * FROM mybb_maillogs
SHOW FIELDS FROM mybb_mailqueue
SHOW CREATE TABLE mybb_mailqueue
SELECT * FROM mybb_mailqueue
SHOW FIELDS FROM mybb_massemails
SHOW CREATE TABLE mybb_massemails
SELECT * FROM mybb_massemails
SHOW FIELDS FROM mybb_moderatorlog
SHOW CREATE TABLE mybb_moderatorlog
SELECT * FROM mybb_moderatorlog
SHOW FIELDS FROM mybb_moderators
SHOW CREATE TABLE mybb_moderators
SELECT * FROM mybb_moderators
SHOW FIELDS FROM mybb_modtools
SHOW CREATE TABLE mybb_modtools
SELECT * FROM mybb_modtools
SHOW FIELDS FROM mybb_mycode
SHOW CREATE TABLE mybb_mycode
SELECT * FROM mybb_mycode
SHOW FIELDS FROM mybb_polls
SHOW CREATE TABLE mybb_polls
SELECT * FROM mybb_polls
SHOW FIELDS FROM mybb_pollvotes
SHOW CREATE TABLE mybb_pollvotes
SELECT * FROM mybb_pollvotes
SHOW FIELDS FROM mybb_posts
SHOW CREATE TABLE mybb_posts
SELECT * FROM mybb_posts
SHOW FIELDS FROM mybb_privatemessages
SHOW CREATE TABLE mybb_privatemessages
SELECT * FROM mybb_privatemessages
SHOW FIELDS FROM mybb_profilefields
SHOW CREATE TABLE mybb_profilefields
SELECT * FROM mybb_profilefields
SHOW FIELDS FROM mybb_promotionlogs
SHOW CREATE TABLE mybb_promotionlogs
SELECT * FROM mybb_promotionlogs
SHOW FIELDS FROM mybb_promotions
SHOW CREATE TABLE mybb_promotions
SELECT * FROM mybb_promotions
SHOW FIELDS FROM mybb_reportedposts
SHOW CREATE TABLE mybb_reportedposts
SELECT * FROM mybb_reportedposts
SHOW FIELDS FROM mybb_reputation
SHOW CREATE TABLE mybb_reputation
SELECT * FROM mybb_reputation
SHOW FIELDS FROM mybb_searchlog
SHOW CREATE TABLE mybb_searchlog
SELECT * FROM mybb_searchlog
SHOW FIELDS FROM mybb_sessions
SHOW CREATE TABLE mybb_sessions
SELECT * FROM mybb_sessions
SHOW FIELDS FROM mybb_settinggroups
SHOW CREATE TABLE mybb_settinggroups
SELECT * FROM mybb_settinggroups
SHOW FIELDS FROM mybb_settings
SHOW CREATE TABLE mybb_settings
SELECT * FROM mybb_settings
SHOW FIELDS FROM mybb_smilies
SHOW CREATE TABLE mybb_smilies
SELECT * FROM mybb_smilies
SHOW FIELDS FROM mybb_spiders
SHOW CREATE TABLE mybb_spiders
SELECT * FROM mybb_spiders
SHOW FIELDS FROM mybb_stats
SHOW CREATE TABLE mybb_stats
SELECT * FROM mybb_stats
SHOW FIELDS FROM mybb_tasklog
SHOW CREATE TABLE mybb_tasklog
SELECT * FROM mybb_tasklog
SHOW FIELDS FROM mybb_tasks
SHOW CREATE TABLE mybb_tasks
SELECT * FROM mybb_tasks
SHOW FIELDS FROM mybb_templategroups
SHOW CREATE TABLE mybb_templategroups
SELECT * FROM mybb_templategroups
SHOW FIELDS FROM mybb_templates
SHOW CREATE TABLE mybb_templates
SELECT * FROM mybb_templates
SHOW FIELDS FROM mybb_templatesets
SHOW CREATE TABLE mybb_templatesets
SELECT * FROM mybb_templatesets
SHOW FIELDS FROM mybb_themes
SHOW CREATE TABLE mybb_themes
SELECT * FROM mybb_themes
SHOW FIELDS FROM mybb_themestylesheets
SHOW CREATE TABLE mybb_themestylesheets
SELECT * FROM mybb_themestylesheets
SHOW FIELDS FROM mybb_threadratings
SHOW CREATE TABLE mybb_threadratings
SELECT * FROM mybb_threadratings
SHOW FIELDS FROM mybb_threads
SHOW CREATE TABLE mybb_threads
SELECT * FROM mybb_threads
SHOW FIELDS FROM mybb_threadsread
SHOW CREATE TABLE mybb_threadsread
SELECT * FROM mybb_threadsread
SHOW FIELDS FROM mybb_threadsubscriptions
SHOW CREATE TABLE mybb_threadsubscriptions
SELECT * FROM mybb_threadsubscriptions
SHOW FIELDS FROM mybb_threadviews
SHOW CREATE TABLE mybb_threadviews
SELECT * FROM mybb_threadviews
SHOW FIELDS FROM mybb_userfields
SHOW CREATE TABLE mybb_userfields
SELECT * FROM mybb_userfields
SHOW FIELDS FROM mybb_usergroups
SHOW CREATE TABLE mybb_usergroups
SELECT * FROM mybb_usergroups
SHOW FIELDS FROM mybb_users
SHOW CREATE TABLE mybb_users
SELECT * FROM mybb_users
SHOW FIELDS FROM mybb_usertitles
SHOW CREATE TABLE mybb_usertitles
SELECT * FROM mybb_usertitles
SHOW FIELDS FROM mybb_warninglevels
SHOW CREATE TABLE mybb_warninglevels
SELECT * FROM mybb_warninglevels
SHOW FIELDS FROM mybb_warnings
SHOW CREATE TABLE mybb_warnings
SELECT * FROM mybb_warnings
SHOW FIELDS FROM mybb_warningtypes
SHOW CREATE TABLE mybb_warningtypes
SELECT * FROM mybb_warningtypes
INSERT
INTO mybb_tasklog (`tid`,`dateline`,`data`)
VALUES ('4','1274531418','The database backup task successfully ran.')
UPDATE mybb_tasks
SET `lastrun`='1274531418', `locked`='0' WHERE tid='4'
SELECT nextrun FROM mybb_tasks WHERE enabled=1 ORDER BY nextrun ASC LIMIT 1
REPLACE INTO mybb_datacache SET `title`='tasks',`cache`='a:1:{s:7:\"nextrun\";s:10:\"1274477100\";}'
INSERT
INTO mybb_adminlog (`uid`,`ipaddress`,`dateline`,`module`,`action`,`data`)
VALUES ('1','127.0.0.1','1274531418','tools/tasks','run','a:2:{i:0;s:1:\"4\";i:1;s:13:\"Weekly Backup\";}')
How many threads / posts do you have in your forum? It'd have to be huge for table locking to be an issue.
Not much:
1.075 threads
16.285 posts