Leitfaden Update der Wiki-Software

Leitfaden zur Aktualisierung der mediawiki-Software von SkipperGuide

1. Vorbereitung und Backup

1.1. Standard-Sicherung Datenbank und Webverzeichnis

 SSH> ./dumpdb
 SSH> ./dumpwebsites

1.2. Sicherung des aktuellen Version als Referenz

 SSH> cp -R www_skipperguide_de/* backup_skipperguide_de

1.3. Vorbereiten Testsystem

1.3.1. Kopieren der aktuellen Programmversion

Kopieren der Programmdateien

 SSH> cp -R www_skipperguide_de/* test_skipperguide_de

1.3.2. Anpassen DB-Connection

 Anpassen von 
 - LocalSettings.php
 - LocalDBSettings.php
 - AdminSettings.php

 DB-Präfix: "sg2_" --> "test_sg2_"

1.3.3. Anlegen Test-Datenbank

(Sofern die Testdatenbank nicht auf aktuellem Stand der mediawiki-Version ist)

Überblick Tabellen

sg2_archive
sg2_categorylinks
sg2_externallinks
sg2_filearchive
sg2_hitcounter
sg2_image
sg2_imagelinks
sg2_interwiki
sg2_ipblocks
sg2_ipblocks_old
sg2_job
sg2_langlinks
sg2_logging
sg2_math
sg2_objectcache
sg2_oldimage
sg2_page
sg2_pagelinks
sg2_page_restrictions
sg2_querycache
sg2_querycachetwo
sg2_querycache_info
sg2_recentchanges
sg2_redirect
sg2_revision
sg2_searchindex
sg2_site_stats
sg2_templatelinks
sg2_text
sg2_trackbacks
sg2_transcache
sg2_user
sg2_user_groups
sg2_user_newtalk
sg2_validate
sg2_watchlist

1.3.3.1. Kopieren der Tabellen

für jede Tabelle

 SQL> CREATE TABLE test_<TBL_NAME> AS SELECT * FROM <TBL_NAME>

1.3.3.2. Anlegen der Indizes

wichtig, weil das mediawiki-update-Skript sonst bei "DROP INDEX" Fehler meldet

Erzeugen des SQL-Statements zur Index-Erstellung auf Basis von "sg2_*":

 SELECT
     CONCAT(
         ‘ALTER TABLE `’,
         TABLE_NAME,
         ‘` ‘,
         ‘ADD ‘,
         IF(NON_UNIQUE = 1,
             CASE UPPER(INDEX_TYPE)
                 WHEN ‘FULLTEXT’ THEN ‘FULLTEXT INDEX’
                 WHEN ‘SPATIAL’ THEN ‘SPATIAL INDEX’
                 ELSE CONCAT(‘INDEX `’,
                             INDEX_NAME,
                             ‘` USING ‘,
                             INDEX_TYPE
                     )
             END,
             IF(UPPER(INDEX_NAME) = ‘PRIMARY’,
                 CONCAT(‘PRIMARY KEY USING ‘,
                         INDEX_TYPE
                 ),
                 CONCAT(‘UNIQUE INDEX `’,
                         INDEX_NAME,
                         ‘` USING ‘,
                         INDEX_TYPE
                 )
             )
         ),
         ‘(’,
         GROUP_CONCAT(
             DISTINCT
                 CONCAT(‘`’, COLUMN_NAME, ‘`’)
             ORDER BY SEQ_IN_INDEX ASC
             SEPARATOR ‘, ‘
         ),
         ‘);’
      ) AS ‘Show_Add_Indexes’
 FROM information_schema.STATISTICS
 WHERE TABLE_SCHEMA like ’sg2_%’
 GROUP BY TABLE_NAME, INDEX_NAME
 ORDER BY TABLE_NAME ASC, INDEX_NAME ASC

Generierte Liste SQL

(Auf Basis mediawiki-1.10.)

 ALTER TABLE test_sg2_archive ADD INDEX name_title_timestamp USING BTREE(ar_namespace, ar_title, ar_timestamp);
 ALTER TABLE test_sg2_categorylinks ADD UNIQUE INDEX cl_from USING BTREE(cl_from, cl_to);
 ALTER TABLE test_sg2_categorylinks ADD INDEX cl_sortkey USING BTREE(cl_to, cl_sortkey);
 ALTER TABLE test_sg2_categorylinks ADD INDEX cl_timestamp USING BTREE(cl_to, cl_timestamp);
 ALTER TABLE test_sg2_externallinks ADD INDEX el_from USING BTREE(el_from, el_to);
 ALTER TABLE test_sg2_externallinks ADD INDEX el_index USING BTREE(el_index);
 ALTER TABLE test_sg2_externallinks ADD INDEX el_to USING BTREE(el_to, el_from);
 ALTER TABLE test_sg2_filearchive ADD INDEX fa_deleted_timestamp USING BTREE(fa_deleted_timestamp);
 ALTER TABLE test_sg2_filearchive ADD INDEX fa_deleted_user USING BTREE(fa_deleted_user);
 ALTER TABLE test_sg2_filearchive ADD INDEX fa_name USING BTREE(fa_name, fa_timestamp);
 ALTER TABLE test_sg2_filearchive ADD INDEX fa_storage_group USING BTREE(fa_storage_group, fa_storage_key);   
 ALTER TABLE test_sg2_filearchive ADD PRIMARY KEY USING BTREE(fa_id);              
 ALTER TABLE test_sg2_image ADD INDEX img_size USING BTREE(img_size);             
 ALTER TABLE test_sg2_image ADD INDEX img_timestamp USING BTREE(img_timestamp);
 ALTER TABLE test_sg2_image ADD PRIMARY KEY USING BTREE(img_name);               
 ALTER TABLE test_sg2_imagelinks ADD UNIQUE INDEX il_from USING BTREE(il_from, il_to);
 ALTER TABLE test_sg2_imagelinks ADD INDEX il_to USING BTREE(il_to, il_from);
 ALTER TABLE test_sg2_interwiki ADD UNIQUE INDEX iw_prefix USING BTREE(iw_prefix);
 ALTER TABLE test_sg2_ipblocks ADD UNIQUE INDEX ipb_address_unique USING BTREE(ipb_address, ipb_user, ipb_auto);  
 ALTER TABLE test_sg2_ipblocks ADD INDEX ipb_expiry USING BTREE(ipb_expiry);            
 ALTER TABLE test_sg2_ipblocks ADD INDEX ipb_range USING BTREE(ipb_range_start, ipb_range_end);      
 ALTER TABLE test_sg2_ipblocks ADD INDEX ipb_timestamp USING BTREE(ipb_timestamp);          
 ALTER TABLE test_sg2_ipblocks ADD INDEX ipb_user USING BTREE(ipb_user);
 ALTER TABLE test_sg2_ipblocks ADD PRIMARY KEY USING BTREE(ipb_id);              
 ALTER TABLE test_sg2_ipblocks_old ADD INDEX ipb_address USING BTREE(ipb_address);          
 ALTER TABLE test_sg2_ipblocks_old ADD INDEX ipb_range USING BTREE(ipb_range_start, ipb_range_end);     
 ALTER TABLE test_sg2_ipblocks_old ADD INDEX ipb_user USING BTREE(ipb_user);            
 ALTER TABLE test_sg2_ipblocks_old ADD PRIMARY KEY USING BTREE(ipb_id);             
 ALTER TABLE test_sg2_job ADD INDEX job_cmd USING BTREE(job_cmd, job_namespace, job_title);       
 ALTER TABLE test_sg2_job ADD PRIMARY KEY USING BTREE(job_id);                
 ALTER TABLE test_sg2_langlinks ADD UNIQUE INDEX ll_from USING BTREE(ll_from, ll_lang);        
 ALTER TABLE test_sg2_langlinks ADD INDEX ll_lang USING BTREE(ll_lang, ll_title);          
 ALTER TABLE test_sg2_logging ADD INDEX page_time USING BTREE(log_namespace, log_title, log_timestamp);    
 ALTER TABLE test_sg2_logging ADD PRIMARY KEY USING BTREE(log_id);               
 ALTER TABLE test_sg2_logging ADD INDEX times USING BTREE(log_timestamp);            
 ALTER TABLE test_sg2_logging ADD INDEX type_time USING BTREE(log_type, log_timestamp);        
 ALTER TABLE test_sg2_logging ADD INDEX user_time USING BTREE(log_user, log_timestamp);        
 ALTER TABLE test_sg2_math ADD UNIQUE INDEX math_inputhash USING BTREE(math_inputhash);
 ALTER TABLE test_sg2_objectcache ADD INDEX exptime USING BTREE(exptime);
 ALTER TABLE test_sg2_objectcache ADD UNIQUE INDEX keyname USING BTREE(keyname);
 ALTER TABLE test_sg2_oldimage ADD INDEX oi_name USING BTREE(oi_name);
 ALTER TABLE test_sg2_page ADD UNIQUE INDEX name_title USING BTREE(page_namespace, page_title);      
 ALTER TABLE test_sg2_page ADD INDEX page_len USING BTREE(page_len);              
 ALTER TABLE test_sg2_page ADD INDEX page_random USING BTREE(page_random);            
 ALTER TABLE test_sg2_page ADD PRIMARY KEY USING BTREE(page_id);               
 ALTER TABLE test_sg2_pagelinks ADD UNIQUE INDEX pl_from USING BTREE(pl_from, pl_namespace, pl_title);    
 ALTER TABLE test_sg2_pagelinks ADD INDEX pl_namespace USING BTREE(pl_namespace, pl_title, pl_from);     
 ALTER TABLE test_sg2_page_restrictions ADD PRIMARY KEY USING BTREE(pr_page, pr_type);         
 ALTER TABLE test_sg2_page_restrictions ADD INDEX pr_cascade USING BTREE(pr_cascade);         
 ALTER TABLE test_sg2_page_restrictions ADD UNIQUE INDEX pr_id USING BTREE(pr_id);          
 ALTER TABLE test_sg2_page_restrictions ADD INDEX pr_level USING BTREE(pr_level);          
 ALTER TABLE test_sg2_page_restrictions ADD INDEX pr_page USING BTREE(pr_page);
 ALTER TABLE test_sg2_page_restrictions ADD INDEX pr_typelevel USING BTREE(pr_type, pr_level);
 ALTER TABLE test_sg2_querycache ADD INDEX qc_type USING BTREE(qc_type, qc_value);          
 ALTER TABLE test_sg2_querycachetwo ADD INDEX qcc_title USING BTREE(qcc_type, qcc_namespace, qcc_title);    
 ALTER TABLE test_sg2_querycachetwo ADD INDEX qcc_titletwo USING BTREE(qcc_type, qcc_namespacetwo, qcc_titletwo); 
 ALTER TABLE test_sg2_querycachetwo ADD INDEX qcc_type USING BTREE(qcc_type, qcc_value);
 ALTER TABLE test_sg2_querycache_info ADD UNIQUE INDEX qci_type USING BTREE(qci_type);         
 ALTER TABLE test_sg2_recentchanges ADD INDEX new_name_timestamp USING BTREE(rc_new, rc_namespace, rc_timestamp); 
 ALTER TABLE test_sg2_recentchanges ADD PRIMARY KEY USING BTREE(rc_id);             
 ALTER TABLE test_sg2_recentchanges ADD INDEX rc_cur_id USING BTREE(rc_cur_id);           
 ALTER TABLE test_sg2_recentchanges ADD INDEX rc_ip USING BTREE(rc_ip);             
 ALTER TABLE test_sg2_recentchanges ADD INDEX rc_namespace_title USING BTREE(rc_namespace, rc_title);     
 ALTER TABLE test_sg2_recentchanges ADD INDEX rc_ns_usertext USING BTREE(rc_namespace, rc_user_text);     
 ALTER TABLE test_sg2_recentchanges ADD INDEX rc_timestamp USING BTREE(rc_timestamp);         
 ALTER TABLE test_sg2_recentchanges ADD INDEX rc_user_text USING BTREE(rc_user_text, rc_timestamp);     
 ALTER TABLE test_sg2_redirect ADD PRIMARY KEY USING BTREE(rd_from);              
 ALTER TABLE test_sg2_redirect ADD INDEX rd_ns_title USING BTREE(rd_namespace, rd_title, rd_from);     
 ALTER TABLE test_sg2_revision ADD INDEX page_timestamp USING BTREE(rev_page, rev_timestamp);       
 ALTER TABLE test_sg2_revision ADD PRIMARY KEY USING BTREE(rev_page, rev_id);
 ALTER TABLE test_sg2_revision ADD UNIQUE INDEX rev_id USING BTREE(rev_id);            
 ALTER TABLE test_sg2_revision ADD INDEX rev_timestamp USING BTREE(rev_timestamp);          
 ALTER TABLE test_sg2_revision ADD INDEX usertext_timestamp USING BTREE(rev_user_text, rev_timestamp);     
 ALTER TABLE test_sg2_revision ADD INDEX user_timestamp USING BTREE(rev_user, rev_timestamp);       
 ALTER TABLE test_sg2_searchindex ADD UNIQUE INDEX si_page USING BTREE(si_page);           
 ALTER TABLE test_sg2_searchindex ADD FULLTEXT INDEX(si_text);                
 ALTER TABLE test_sg2_searchindex ADD FULLTEXT INDEX(si_title);               
 ALTER TABLE test_sg2_site_stats ADD UNIQUE INDEX ss_row_id USING BTREE(ss_row_id);          
 ALTER TABLE test_sg2_templatelinks ADD UNIQUE INDEX tl_from USING BTREE(tl_from, tl_namespace, tl_title);   
 ALTER TABLE test_sg2_templatelinks ADD INDEX tl_namespace USING BTREE(tl_namespace, tl_title, tl_from);    
 ALTER TABLE test_sg2_text ADD PRIMARY KEY USING BTREE(old_id);               
 ALTER TABLE test_sg2_trackbacks ADD PRIMARY KEY USING BTREE(tb_id);              
 ALTER TABLE test_sg2_trackbacks ADD INDEX tb_page USING BTREE(tb_page);             
 ALTER TABLE test_sg2_transcache ADD UNIQUE INDEX tc_url_idx USING BTREE(tc_url);          
 ALTER TABLE test_sg2_user ADD PRIMARY KEY USING BTREE(user_id);               
 ALTER TABLE test_sg2_user ADD INDEX user_email_token USING BTREE(user_email_token);          
 ALTER TABLE test_sg2_user ADD UNIQUE INDEX user_name USING BTREE(user_name);           
 ALTER TABLE test_sg2_user_groups ADD PRIMARY KEY USING BTREE(ug_user, ug_group);          
 ALTER TABLE test_sg2_user_groups ADD INDEX ug_group USING BTREE(ug_group);            
 ALTER TABLE test_sg2_user_newtalk ADD INDEX user_id USING BTREE(user_id);            
 ALTER TABLE test_sg2_user_newtalk ADD INDEX user_ip USING BTREE(user_ip);            
 ALTER TABLE test_sg2_validate ADD INDEX val_user USING BTREE(val_user, val_revision);         
 ALTER TABLE test_sg2_watchlist ADD INDEX namespace_title USING BTREE(wl_namespace, wl_title);
 ALTER TABLE test_sg2_watchlist ADD UNIQUE INDEX wl_user USING BTREE(wl_user, wl_namespace, wl_title);    
 ALTER TABLE sgtmp_transcache ADD UNIQUE INDEX tc_url_idx USING BTREE(tc_url);          
 ALTER TABLE test_test_sg2_archive ADD INDEX name_title_timestamp USING BTREE(ar_namespace, ar_title, ar_timestamp); 
 ALTER TABLE test_test_sg2_categorylinks ADD UNIQUE INDEX cl_from USING BTREE(cl_from, cl_to);       
 ALTER TABLE test_test_sg2_categorylinks ADD INDEX cl_sortkey USING BTREE(cl_to, cl_sortkey);       
 ALTER TABLE test_test_sg2_categorylinks ADD INDEX cl_timestamp USING BTREE(cl_to, cl_timestamp);      
 ALTER TABLE test_test_sg2_externallinks ADD INDEX el_from USING BTREE(el_from, el_to);        
 ALTER TABLE test_test_sg2_externallinks ADD INDEX el_index USING BTREE(el_index);
 ALTER TABLE test_test_sg2_externallinks ADD INDEX el_to USING BTREE(el_to, el_from);
 ALTER TABLE test_test_sg2_image ADD UNIQUE INDEX img_name USING BTREE(img_name);

1.3.3.3. Anlegen Auto-Inkrements

Ohne das kommt es zu Fehlern beim Berarbeiten von Artikeln (z.B.: old_id cannot be NULL)

 ALTER TABLE `test_sg2_categorylinks` CHANGE `cl_timestamp` `cl_timestamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;
 ALTER TABLE `test_sg2_ipblocks` CHANGE `ipb_id` `ipb_id` INT( 8 ) NOT NULL AUTO_INCREMENT;
 ALTER TABLE `test_sg2_job` CHANGE `job_id` `job_id` INT( 9 ) UNSIGNED NOT NULL AUTO_INCREMENT;
 ALTER TABLE `test_sg2_page` CHANGE `page_id` `page_id` INT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT;
 ALTER TABLE `test_sg2_recentchanges` CHANGE `rc_id` `rc_id` INT( 8 ) NOT NULL AUTO_INCREMENT;
 ALTER TABLE `test_sg2_revision` CHANGE `rev_id` `rev_id` INT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT;
 ALTER TABLE `test_sg2_text` CHANGE `old_id` `old_id` INT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT;
 ALTER TABLE `test_sg2_trackbacks` CHANGE `tb_id` `tb_id` INT( 11 ) NOT NULL AUTO_INCREMENT;
 ALTER TABLE `test_sg2_user` CHANGE `user_id` `user_id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;

1.3.3.4. Test der Testinstallation

Testen der Anwendung über test.skipperguide.de. Folgende Schnelltests durchführen:

 1. Anzeigen von Artikeln ohne Bilder
 2. Anzeigen von Artikeln mit Bildern
 3. Anzeigen der Änderungshistorie (entspricht sie der Standard-Installation?)
 4. Bearbeiten eines Artikels
 5. Anzeigen der Änderungdshistorie, prüfen, dass diese Änderung *nicht* in der Änderungshistorie der Standardinstallation auftaucht.