Re: [Tiki-users] Dropping abandoned Tables in Tiki-Schema

Hello Marc,

very good, I downloaded it and it certified:

Columns in database and missing in db/tiki.sql
sessions

  * SESSKEY
  * EXPIRY
  * EXPIREREF

Tables in db/tiki.sql and missing in database
good

Columns in db/tiki.sql and missing in database
sessions

  * expiry
  * expireref

Dynamic tables in database
index_5f631cb192f68
index_pref_en

Tables in database and missing in db/tiki.sql
good


This helps a lot.
So I have a column too many and mismatched cases (I'd never would have 
thought this could be an issue, good catch...)
I guess it's time to put

lower_case_table_names=1

into my configuration (I'm running on windows but might switch to Linux 
-> bam, problem). One does learn new stuff every day :-)
BTW, the check.php is very useful, it gave me a ton of info how to 
configure my server better and better still.

Thanks, Manuel

On 18.09.2020 18:56, [email protected] wrote:
> Using http://doc.tiki.org/Server-Check
>
> There is a tool to check your DB, introduced here
> https://sourceforge.net/p/tikiwiki/code/76193/
> And improved after.
>
> Best regards,
>
> Marc
>
> On 2020-09-17 15:34, Manuel Martin wrote:
>> Hi Jonny,
>>
>> no worries, I'm still testing the upgrade process and your input is
>> very valuable, thanks!
>>
>> To rename unused tables in a certain way is a great idea, next time I
>> upgrade I'll know :-)
>>
>> I did now compare the schemas of a "stock newly installed 21.2" and my
>> upgraded database and found very minor differences (column char
>> instead of varchar, I guess not important), differing default values,
>> some orphaned keys and one table which has some bigger differences
>> (tiki_comments).
>>
>> Would that be worth a ticket? Here's my correction:
>>
>> ALTER TABLE `tiki_comments`
>>     CHANGE COLUMN `title` `title` VARCHAR(255) DEFAULT NULL,
>>     CHANGE COLUMN `userName` `userName` varchar(200) DEFAULT '';
>> ALTER TABLE `tiki_comments`
>>   drop KEY `no_repeats`;
>> ALTER TABLE `tiki_comments`
>>     ADD UNIQUE KEY `no_repeats`
>> (`parentId`,`userName`(40),`title`(43),`commentDate`,`message_id`(40),`in_reply_to`(40)); 
>>
>>
>>
>> (The update tries to add IIRC a key on title(191) which failed.)
>>
>> Generally, I found defaults set for columns in my database which
>> "stock" doesn't have, and I found default values in stock which my
>> database doesn't have, and once I found differing defaults (I guess
>> I'll fix that one anyway).
>>
>> Do TikiWiki developers generally rely on correct default values when
>> inserting rows, or is there some sort of rule to not rely on them? I'm
>> not sure if it is sensible to fiddle in such low level stuff when it
>> doesn't matter anyway :-)
>>
>> TIA, Manuel
>>
>> On 17.09.2020 11:41, Jonny Bradley via TikiWiki-users wrote:
>>
>>> Hi Manuel
>>>
>>> Sorry i didn't get back to you in time, this is a good point - we
>>> don't generally drop tables in upgrades just in case people have
>>> unbacked up info in them, and lately scripts have renamed obsolete
>>> tables with zzz_ in front, but how would you know? ;)
>>>
>>> Over time we have added various scripts to tidy up unused tables but
>>> only in the "devtools" directory which isn';t part of the release
>>> tarballs, but they can all be found here
>>> https://gitlab.com/tikiwiki/tiki/-/tree/master/doc/devtools
>>>
>>> The most up to date one is here
>>>
>>>
>>>
>> https://gitlab.com/tikiwiki/tiki/-/blob/master/doc/devtools/upto_18_drop_unused_tables.sql 
>>
>>>
>>>
>>> and is basically what you worked out yourself! (although you seem to
>>> have found a few extra!?)
>>>
>>> Obviously all there is no undo so make sure you have a working
>>> backup of your database before you start!
>>>
>>> So apologies again for not being quicker, happy Tiki'ing!
>>>
>>> jonny
>>>
>>> On 17 Sep 2020, at 09:38, Manuel Martin <[email protected]> wrote:
>>>
>>> Hello all,
>>>
>>> after some fiddling I made the following SQL-Script which hopefully
>>> deletes superfluous tables.
>>>
>>> I give no guarantees! (And I have no idea why my user database has
>>> system tables. Anyone?)
>>>
>>> -- vor Upgrade Schema bereinigen/korrigieren
>>>
>>> use mytiki;
>>>
>>> -- falsche Feldlänge:
>>> ALTER TABLE `tiki_comments`
>>> CHANGE COLUMN `title` `title` VARCHAR(255) DEFAULT NULL;
>>>
>>> -- bereinigen: Galaxia (wurde nie benutzt)
>>> drop table if EXISTS galaxia_activities;
>>> drop table if EXISTS galaxia_activity_roles;
>>> drop table if EXISTS galaxia_instance_activities;
>>> drop table if EXISTS galaxia_instance_comments;
>>> drop table if EXISTS galaxia_instances;
>>> drop table if EXISTS galaxia_processes;
>>> drop table if EXISTS galaxia_roles;
>>> drop table if EXISTS galaxia_transitions;
>>> drop table if EXISTS galaxia_user_roles;
>>> drop table if EXISTS galaxia_workitems;
>>>
>>> -- weitere(s) nicht im aktuellen Installationsskript
>>> -- und nicht umgestellt auf InnoDB
>>> -- und bekannt gedropptes Feature
>>> drop table if EXISTS tiki_eph;
>>>
>>> drop table if EXISTS tiki_charts;
>>> drop table if EXISTS tiki_charts_rankings;
>>> drop table if EXISTS tiki_charts_votes;
>>> drop table if EXISTS tiki_chart_items;
>>> drop table if EXISTS tiki_drawings;
>>> drop table if EXISTS tiki_newsreader_marks;
>>> drop table if EXISTS tiki_newsreader_servers;
>>> drop table if EXISTS tiki_page_drafts;
>>> drop table if EXISTS tiki_quicktags;
>>> drop table if EXISTS tiki_users;
>>> drop table if EXISTS tiki_users_score;
>>> -- Überbleibsel:
>>> drop table if EXISTS users_score;
>>>
>>> -- seltsame Tabellen:
>>> drop table if EXISTS zzz_unused_metrics_assigned;
>>> drop table if EXISTS zzz_unused_metrics_metric;
>>> drop table if EXISTS zzz_unused_metrics_tab;
>>>
>>> -- Systemtabellen?
>>> drop table if EXISTS columns_priv;
>>> drop table if EXISTS db;
>>> drop table if EXISTS func;
>>> drop table if EXISTS help_category;
>>> drop table if EXISTS help_keyword;
>>> drop table if EXISTS help_relation;
>>> drop table if EXISTS help_topic;
>>> drop table if EXISTS host;
>>> drop table if EXISTS proc;
>>> drop table if EXISTS procs_priv;
>>> drop table if EXISTS tables_priv;
>>> drop table if EXISTS time_zone;
>>> drop table if EXISTS time_zone_leap_second;
>>> drop table if EXISTS time_zone_name;
>>> drop table if EXISTS time_zone_transition;
>>> drop table if EXISTS time_zone_transition_type;
>>> drop table if EXISTS user;
>>>
>>> On 15.09.2020 16:58, Manuel Martin wrote:
>>> Hello all,
>>>
>>> I want to update my slightly outdated Tiki 8.2 installation to 21.2
>>> and find some error message pertaining not yet properly converted
>>> tables (to uft8mb4). I'm using MariaDB 10.5 BTW. I *believe* it's
>>> not that important mostly, but want to be sure.
>>>
>>> Well, my best guess is those are leftovers from features which have
>>> been dropped (like galaxia.* or tiki_eph), or they somehow came out
>>> of use? They have in common that they are empty and that they have
>>> the outdated charset/collation or perhaps special names
>>> (zzz_unused*)
>>>
>>> Long story short: is there a positive list which tables can be
>>> dropped? Or is it 101% safe to drop tables which are not included in
>>> tiki.sql? (Theoretically tiki-install.php could just rename things
>>> or create tables by other means?)
>>>
>>> TIA, Manuel
>>>
>>> _______________________________________________
>>> TikiWiki-users mailing list
>>> [email protected]
>>> https://lists.sourceforge.net/lists/listinfo/tikiwiki-users
>>>
>>> _______________________________________________
>>> TikiWiki-users mailing list
>>> [email protected]
>>> https://lists.sourceforge.net/lists/listinfo/tikiwiki-users
>>
>> _______________________________________________
>> TikiWiki-users mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/tikiwiki-users
>> _______________________________________________
>> TikiWiki-users mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/tikiwiki-users
>

_______________________________________________
TikiWiki-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-users