Wenn man MySQL von 4.* auf 5.1.* aktualisieren möchte, erlebt man als Betreiber einer Joomla Seite unter Umständen eine böse Überraschung nach dem MySQL Upgrade. In meinen Überlegungen gehe ich von einer Joomla 1.5.23 Installation aus und wir benötigen entweder MySQL Zugriff per Shell oder über ein frei verfügbares Werkzeug wie zum Beispiel PHPMyAdmin.
Bei Joomla werden in der Regel die folgenden drei Tabellen unbrauchbar und können auch nicht mehr unter MySQL 5.1.* repariert werden:
jos_session
jos_core_acl_aro
jos_components
Sollte man ein anderes Tabellenpräfix als jos_ gewählt haben, so heißen die Tabellen entsprechend anders und man muss den Code abändern.
Joomla Tabelle jos_session wiederherstellen
Die Session-Tabelle kann man gefahrenlos neu erstellen. Dabei werden alle Benutzer, die zuvor eingeloggt waren, aus dem Joomla System ausgeloggt:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DROP TABLE IF EXISTS `<strong>jos_</strong>session`; CREATE TABLE IF NOT EXISTS `<strong>jos_</strong>session` ( `username` varchar(150) DEFAULT '', `time` varchar(14) DEFAULT '', `session_id` varchar(200) NOT NULL DEFAULT '0', `guest` tinyint(4) DEFAULT '1', `userid` int(11) DEFAULT '0', `usertype` varchar(150) DEFAULT '', `gid` tinyint(3) unsigned NOT NULL DEFAULT '0', `client_id` tinyint(3) unsigned NOT NULL DEFAULT '0', `data` text, PRIMARY KEY (`session_id`), KEY `whosonline` (`guest`,`usertype`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
jos_core_acl_aro neu anlegen und sinnvoll befüllen
Etwas kniffliger wird die Angelegenheit bei der Tabelle, die zur Regelung der Zugriffsrechte mitverantwortlich ist: jos_core_acl_aro
Wir legen die Tabelle zunächst neu an:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DROP TABLE IF EXISTS `<strong>jos_</strong>core_acl_aro`; CREATE TABLE IF NOT EXISTS `<strong>jos_</strong>core_acl_aro` ( `id` int(11) NOT NULL AUTO_INCREMENT, `section_value` varchar(240) NOT NULL DEFAULT '0', `value` varchar(240) NOT NULL DEFAULT '', `order_value` int(11) NOT NULL DEFAULT '0', `name` varchar(255) NOT NULL DEFAULT '', `hidden` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `__section_value_value_aro` (`section_value`(100),`value`(100)), KEY `jos_gacl_hidden_aro` (`hidden`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ; -- -- Daten für Tabelle `<strong>jos_</strong>core_acl_aro` -- INSERT INTO `<strong>jos_</strong>core_acl_aro` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`) VALUES (10, 'users', '62', 0, 'Administrator', 0); |
Anschließend befüllen wir die neu angelegte Tabelle mit den Nutzern:
1 | INSERT INTO <strong>jos_</strong>core_acl_aro (section_value, value, name) SELECT 'users', id, name FROM <strong>jos_</strong>users; |
jos_components neu anlegen und Daten wiederherstellen
Abschließend müssen wir noch die Komponenten-Tabelle wiederherstellen:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | DROP TABLE IF EXISTS `<strong>jos_</strong>components`; CREATE TABLE IF NOT EXISTS `<strong>jos_</strong>components` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(150) NOT NULL DEFAULT '', `link` varchar(255) NOT NULL DEFAULT '', `menuid` int(11) unsigned NOT NULL DEFAULT '0', `parent` int(11) unsigned NOT NULL DEFAULT '0', `admin_menu_link` varchar(255) NOT NULL DEFAULT '', `admin_menu_alt` text NOT NULL, `option` varchar(50) NOT NULL DEFAULT '', `ordering` int(11) NOT NULL DEFAULT '0', `admin_menu_img` varchar(255) NOT NULL DEFAULT '', `iscore` tinyint(4) NOT NULL DEFAULT '0', `params` text NOT NULL, `enabled` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=197 ; -- -- Daten für Tabelle `<strong>jos_</strong>components` -- INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (1, 'Banners', '', 0, 0, '', 'Banner Management', 'com_banners', 0, 'js/ThemeOffice/component.png', 0, 'track_impressions=0\ntrack_clicks=0\ntag_prefix=\n\n', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (2, 'Banners', '', 0, 1, 'option=com_banners', 'Active Banners', 'com_banners', 1, 'js/ThemeOffice/edit.png', 0, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (3, 'Clients', '', 0, 1, 'option=com_banners&c=client', 'Manage Clients', 'com_banners', 2, 'js/ThemeOffice/categories.png', 0, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (4, 'Web Links', 'option=com_weblinks', 0, 0, '', 'Manage Weblinks', 'com_weblinks', 0, 'js/ThemeOffice/component.png', 0, 'show_comp_description=1\ncomp_description=Our weblinks show a fine selection of websites\nshow_link_hits=1\nshow_link_description=1\nshow_other_cats=1\n show_headings=1\nshow_page_title=1\nlink_target=0\nlink_icons=google.png\n\n', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (5, 'Links', '', 0, 4, 'option=com_weblinks', 'View existing weblinks', 'com_weblinks', 1, 'js/ThemeOffice/edit.png', 0, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (6, 'Categories', '', 0, 4, 'option=com_categories&section=com_weblinks', 'Manage weblink categories', '', 2, 'js/ThemeOffice/categories.png', 0, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (7, 'Contacts', 'option=com_contact', 0, 0, '', 'Edit contact details', 'com_contact', 0, 'js/ThemeOffice/component.png', 1, 'contact_icons=0\nicon_address=\nicon_email=\nicon_telephone=\nicon_fax=\n icon_misc=\nshow_headings=1\nshow_position=0\nshow_email=1\n show_telephone=1\nshow_mobile=0\nshow_fax=0\nallow_vcard=1\n banned_email=\nbanned_subject=\nbanned_text=\nvalidate_session=1\n custom_reply=0\n\n', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (8, 'Contacts', '', 0, 7, 'option=com_contact', 'Edit contact details', 'com_contact', 0, 'js/ThemeOffice/edit.png', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (9, 'Categories', '', 0, 7, 'option=com_categories&section=com_contact_details', 'Manage contact categories', '', 2, 'js/ThemeOffice/categories.png', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (10, 'Polls', 'option=com_poll', 0, 0, 'option=com_poll', 'Manage Polls', 'com_poll', 0, 'js/ThemeOffice/component.png', 0, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (11, 'News Feeds', 'option=com_newsfeeds', 0, 0, '', 'News Feeds Management', 'com_newsfeeds', 0, 'js/ThemeOffice/component.png', 0, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (12, 'Feeds', '', 0, 11, 'option=com_newsfeeds', 'Manage News Feeds', 'com_newsfeeds', 1, 'js/ThemeOffice/edit.png', 0, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (13, 'Categories', '', 0, 11, 'option=com_categories&section=com_newsfeeds', 'Manage Categories', '', 2, 'js/ThemeOffice/categories.png', 0, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (14, 'User', 'option=com_user', 0, 0, '', '', 'com_user', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (15, 'Search', 'option=com_search', 0, 0, 'option=com_search', 'Search Statistics', 'com_search', 0, 'js/ThemeOffice/component.png', 1, 'enabled=1\nshow_date=0\n\n', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (16, 'Categories', '', 0, 1, 'option=com_categories&section=com_banner', 'Categories', '', 3, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (17, 'Wrapper', 'option=com_wrapper', 0, 0, '', 'Wrapper', 'com_wrapper', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (18, 'Mail To', '', 0, 0, '', '', 'com_mailto', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (19, 'Media Manager', '', 0, 0, 'option=com_media', 'Media Manager', 'com_media', 0, '', 1, 'upload_extensions=bmp,csv,doc,epg,gif,ico,jpg,odg,odp,ods,odt,pdf,png,ppt,swf,txt, xcf,xls,BMP,CSV,DOC,EPG,GIF,ICO,JPG,ODG,ODP,ODS,ODT,PDF,PNG,PPT,SWF,TXT,XCF,XLS\n upload_maxsize=10000000\nfile_path=images\nimage_path=images/stories\n restrict_uploads=1\nallowed_media_usergroup=3\ncheck_mime=1\n image_extensions=bmp,gif,jpg,png\nignore_extensions=\n upload_mime=image/jpeg,image/gif,image/png,image/bmp, application/x-shockwave-flash,application/msword,application/excel, application/pdf,application/powerpoint,text/plain, application/x-zip\nupload_mime_illegal=text/html\nenable_flash=0\n\n', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (20, 'Articles', 'option=com_content', 0, 0, '', '', 'com_content', 0, '', 1, 'show_noauth=0\nshow_title=0\nlink_titles=0\nshow_intro=1\n show_section=0\nlink_section=0\nshow_category=0\nlink_category=0\nshow_author=0\n show_create_date=0\nshow_modify_date=0\nshow_item_navigation=0\nshow_readmore=0\n show_vote=0\nshow_icons=0\nshow_pdf_icon=0\nshow_print_icon=0\nshow_email_icon=0\n show_hits=0\nfeed_summary=0\nfilter_groups=29|18|19|20|21|30|23\nfilter_type=BL\n filter_tags=\nfilter_attritbutes=\n\n', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (21, 'Configuration Manager', '', 0, 0, '', 'Configuration', 'com_config', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (22, 'Installation Manager', '', 0, 0, '', 'Installer', 'com_installer', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (23, 'Language Manager', '', 0, 0, '', 'Languages', 'com_languages', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (24, 'Mass mail', '', 0, 0, '', 'Mass Mail', 'com_massmail', 0, '', 1, 'mailSubjectPrefix=\nmailBodySuffix=\n\n', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (25, 'Menu Editor', '', 0, 0, '', 'Menu Editor', 'com_menus', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (26, 'Menu Manager', '', 0, 0, '', 'Menu Manager', 'com_menumanager', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (27, 'Messaging', '', 0, 0, '', 'Messages', 'com_messages', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (28, 'Modules Manager', '', 0, 0, '', 'Modules', 'com_modules', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (29, 'Plugin Manager', '', 0, 0, '', 'Plugins', 'com_plugins', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (30, 'Template Manager', '', 0, 0, '', 'Templates', 'com_templates', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (31, 'User Manager', '', 0, 0, '', 'Users', 'com_users', 0, '', 1, 'allowUserRegistration=1\nnew_usertype=Registered\nuseractivation=1\n frontend_userparams=1\n\n', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (32, 'Cache Manager', '', 0, 0, '', 'Cache', 'com_cache', 0, '', 1, '', 1); INSERT INTO `<strong>jos_</strong>components` (`id`, `name`, `link`, `menuid`, `parent`, `admin_menu_link`, `admin_menu_alt`, `option`, `ordering`, `admin_menu_img`, `iscore`, `params`, `enabled`) VALUES (33, 'Control Panel', '', 0, 0, '', 'Control Panel', 'com_cpanel', 0, '', 1, '', 1); |
Damit wurden alle Kernkomponenten wiederhergestellt. Nun muss man lediglich noch die Komponenten neu installieren, die man nach der Erstinstallation von Joomla hinzugefügt hat.
Hat man Joomla nicht in einem Unterverzeichnis, sondern dem Hauptverzeichnis installiert, so bekommt man eine Liste der Komponenten, indem man in der Shell folgenden Befehl eingibt:
1 | ls -1 /home/username/public_html/components/ |
Die folgenden Komponenten müssen nicht neuinstalliert werden, da wir sie oben bereits der Datenbanktabelle wieder hinzugefügt haben:
com_banners
com_contact
com_content
com_mailto
com_media
com_newsfeeds
com_poll
com_search
com_user
com_weblinks
com_wrapper
Zur besseren Darstellung des Codes musste ich zu lange Zeilen leider manuell umbrechen. Dies erhöht zwar die Lesbarkeit, sorgt aber in der jos_components Tabelle dafür, dass man den Text nicht hier kopieren und in PHPMyAdmin einfach einfügen kann. Daher habe ich die entsprechenden SQL-Befehle hier als Datei angehängt.
SQL Dump