search

My-Little-Forum / mylittleforum

A simple PHP and MySQL based internet forum that displays the messages in classical threaded view (tree structure)
GNU General Public License v3.0
124 stars 48 forks source link

DB Structure Export After 2.4.24 --> 20220803.1 --> 20240308.1 Upgrade #709

Closed joeiacoponi1 closed 3 months ago

joeiacoponi1 commented 7 months ago

This is a phpMyAdmin structure export of MLF after upgrade from 2.4.24 --> 20220803.1 --> 20240308.1.

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Apr 01, 2024 at 08:50 AM
-- Server version: 10.5.15-MariaDB-cll-lve
-- PHP Version: 8.1.27

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_akismet_rating`
--

CREATE TABLE `mlf2_akismet_rating` (
  `eid` int(11) NOT NULL,
  `spam` tinyint(1) NOT NULL DEFAULT 0,
  `spam_check_status` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_b8_rating`
--

CREATE TABLE `mlf2_b8_rating` (
  `eid` int(11) NOT NULL,
  `spam` tinyint(1) NOT NULL DEFAULT 0,
  `training_type` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_b8_wordlist`
--

CREATE TABLE `mlf2_b8_wordlist` (
  `token` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `count_ham` int(10) UNSIGNED DEFAULT NULL,
  `count_spam` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_banlists`
--

CREATE TABLE `mlf2_banlists` (
  `name` varchar(255) NOT NULL DEFAULT '',
  `list` mediumtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_bookmarks`
--

CREATE TABLE `mlf2_bookmarks` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `posting_id` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT current_timestamp(),
  `subject` varchar(255) NOT NULL,
  `order_id` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_bookmark_tags`
--

CREATE TABLE `mlf2_bookmark_tags` (
  `bid` int(11) NOT NULL,
  `tid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_categories`
--

CREATE TABLE `mlf2_categories` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL DEFAULT 0,
  `category` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `accession` tinyint(4) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_entries`
--

CREATE TABLE `mlf2_entries` (
  `id` int(11) NOT NULL,
  `pid` int(11) NOT NULL DEFAULT 0,
  `tid` int(11) NOT NULL DEFAULT 0,
  `uniqid` varchar(255) NOT NULL DEFAULT '',
  `time` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_reply` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `edited` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `edited_by` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT 0,
  `name` varchar(255) NOT NULL DEFAULT '',
  `subject` varchar(255) NOT NULL DEFAULT '',
  `category` int(11) NOT NULL DEFAULT 0,
  `email` varchar(255) NOT NULL DEFAULT '',
  `hp` varchar(255) NOT NULL DEFAULT '',
  `location` varchar(255) NOT NULL DEFAULT '',
  `ip` varchar(255) NOT NULL DEFAULT '',
  `text` mediumtext NOT NULL,
  `show_signature` tinyint(4) DEFAULT 0,
  `marked` tinyint(4) DEFAULT 0,
  `locked` tinyint(4) DEFAULT 0,
  `sticky` tinyint(4) DEFAULT 0,
  `views` int(11) DEFAULT 0,
  `edit_key` varchar(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_entries_cache`
--

CREATE TABLE `mlf2_entries_cache` (
  `cache_id` int(11) NOT NULL DEFAULT 0,
  `cache_text` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_entry_tags`
--

CREATE TABLE `mlf2_entry_tags` (
  `bid` int(11) NOT NULL,
  `tid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_logincontrol`
--

CREATE TABLE `mlf2_logincontrol` (
  `time` timestamp NOT NULL DEFAULT current_timestamp(),
  `ip` varchar(128) NOT NULL DEFAULT '',
  `logins` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_pages`
--

CREATE TABLE `mlf2_pages` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL DEFAULT 0,
  `title` varchar(255) NOT NULL DEFAULT '',
  `content` mediumtext NOT NULL,
  `menu_linkname` varchar(255) NOT NULL DEFAULT '',
  `access` tinyint(4) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_read_entries`
--

CREATE TABLE `mlf2_read_entries` (
  `user_id` int(11) UNSIGNED NOT NULL,
  `posting_id` int(11) UNSIGNED NOT NULL,
  `time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_settings`
--

CREATE TABLE `mlf2_settings` (
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_smilies`
--

CREATE TABLE `mlf2_smilies` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL DEFAULT 0,
  `file` varchar(100) NOT NULL DEFAULT '',
  `code_1` varchar(50) NOT NULL DEFAULT '',
  `code_2` varchar(50) NOT NULL DEFAULT '',
  `code_3` varchar(50) NOT NULL DEFAULT '',
  `code_4` varchar(50) NOT NULL DEFAULT '',
  `code_5` varchar(50) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_subscriptions`
--

CREATE TABLE `mlf2_subscriptions` (
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `eid` int(12) UNSIGNED NOT NULL,
  `unsubscribe_code` varchar(36) NOT NULL,
  `tstamp` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_tags`
--

CREATE TABLE `mlf2_tags` (
  `id` int(11) NOT NULL,
  `tag` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_temp_infos`
--

CREATE TABLE `mlf2_temp_infos` (
  `name` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_uploads`
--

CREATE TABLE `mlf2_uploads` (
  `id` int(10) UNSIGNED NOT NULL,
  `uploader` int(10) UNSIGNED DEFAULT NULL,
  `filename` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `tstamp` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_userdata`
--

CREATE TABLE `mlf2_userdata` (
  `user_id` int(11) NOT NULL,
  `user_type` tinyint(4) NOT NULL DEFAULT 0,
  `user_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `user_real_name` varchar(255) NOT NULL DEFAULT '',
  `gender` tinyint(4) NOT NULL DEFAULT 0,
  `birthday` date NOT NULL DEFAULT '0000-00-00',
  `user_pw` varchar(255) NOT NULL DEFAULT '',
  `user_email` varchar(255) NOT NULL,
  `email_contact` tinyint(4) DEFAULT 0,
  `user_hp` varchar(255) NOT NULL DEFAULT '',
  `user_location` varchar(255) NOT NULL DEFAULT '',
  `signature` varchar(255) NOT NULL DEFAULT '',
  `profile` mediumtext NOT NULL,
  `logins` int(11) NOT NULL DEFAULT 0,
  `last_login` timestamp NULL DEFAULT current_timestamp(),
  `last_logout` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_ip` varchar(255) NOT NULL DEFAULT '',
  `registered` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `category_selection` varchar(255) DEFAULT NULL,
  `thread_order` tinyint(4) NOT NULL DEFAULT 0,
  `user_view` tinyint(4) NOT NULL DEFAULT 0,
  `sidebar` tinyint(4) NOT NULL DEFAULT 1,
  `fold_threads` tinyint(4) NOT NULL DEFAULT 0,
  `thread_display` tinyint(4) NOT NULL DEFAULT 0,
  `new_posting_notification` tinyint(4) DEFAULT 0,
  `new_user_notification` tinyint(4) DEFAULT 0,
  `user_lock` tinyint(4) DEFAULT 0,
  `browser_window_target` tinyint(4) NOT NULL DEFAULT 0,
  `auto_login_code` varchar(255) NOT NULL DEFAULT '',
  `pwf_code` varchar(255) NOT NULL DEFAULT '',
  `activate_code` varchar(255) NOT NULL DEFAULT '',
  `language` varchar(255) NOT NULL DEFAULT '',
  `time_zone` varchar(255) NOT NULL DEFAULT '',
  `time_difference` smallint(4) DEFAULT 0,
  `theme` varchar(255) NOT NULL DEFAULT '',
  `tou_accepted` datetime DEFAULT NULL,
  `dps_accepted` datetime DEFAULT NULL,
  `inactivity_notification` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_userdata_cache`
--

CREATE TABLE `mlf2_userdata_cache` (
  `cache_id` int(11) NOT NULL DEFAULT 0,
  `cache_signature` mediumtext NOT NULL,
  `cache_profile` mediumtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `mlf2_useronline`
--

CREATE TABLE `mlf2_useronline` (
  `ip` varchar(128) NOT NULL DEFAULT '',
  `time` int(14) NOT NULL DEFAULT 0,
  `user_id` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `mlf2_akismet_rating`
--
ALTER TABLE `mlf2_akismet_rating`
  ADD PRIMARY KEY (`eid`);

--
-- Indexes for table `mlf2_b8_rating`
--
ALTER TABLE `mlf2_b8_rating`
  ADD PRIMARY KEY (`eid`),
  ADD KEY `B8_spam` (`spam`),
  ADD KEY `B8_training_type` (`training_type`);

--
-- Indexes for table `mlf2_b8_wordlist`
--
ALTER TABLE `mlf2_b8_wordlist`
  ADD PRIMARY KEY (`token`);

--
-- Indexes for table `mlf2_bookmarks`
--
ALTER TABLE `mlf2_bookmarks`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `UNIQUE_uid_pid` (`user_id`,`posting_id`);

--
-- Indexes for table `mlf2_bookmark_tags`
--
ALTER TABLE `mlf2_bookmark_tags`
  ADD PRIMARY KEY (`bid`,`tid`);

--
-- Indexes for table `mlf2_categories`
--
ALTER TABLE `mlf2_categories`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `mlf2_entries`
--
ALTER TABLE `mlf2_entries`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `tid` (`tid`),
  ADD KEY `category` (`category`),
  ADD KEY `pid` (`pid`),
  ADD KEY `sticky` (`sticky`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `time` (`time`),
  ADD KEY `last_reply` (`last_reply`);

--
-- Indexes for table `mlf2_entries_cache`
--
ALTER TABLE `mlf2_entries_cache`
  ADD PRIMARY KEY (`cache_id`);

--
-- Indexes for table `mlf2_entry_tags`
--
ALTER TABLE `mlf2_entry_tags`
  ADD PRIMARY KEY (`bid`,`tid`);

--
-- Indexes for table `mlf2_pages`
--
ALTER TABLE `mlf2_pages`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `mlf2_read_entries`
--
ALTER TABLE `mlf2_read_entries`
  ADD PRIMARY KEY (`user_id`,`posting_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `posting_id` (`posting_id`);

--
-- Indexes for table `mlf2_settings`
--
ALTER TABLE `mlf2_settings`
  ADD PRIMARY KEY (`name`);

--
-- Indexes for table `mlf2_smilies`
--
ALTER TABLE `mlf2_smilies`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `mlf2_subscriptions`
--
ALTER TABLE `mlf2_subscriptions`
  ADD UNIQUE KEY `user_thread` (`user_id`,`eid`),
  ADD KEY `hash` (`unsubscribe_code`),
  ADD KEY `entry` (`eid`);

--
-- Indexes for table `mlf2_tags`
--
ALTER TABLE `mlf2_tags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `tag` (`tag`);

--
-- Indexes for table `mlf2_temp_infos`
--
ALTER TABLE `mlf2_temp_infos`
  ADD PRIMARY KEY (`name`);

--
-- Indexes for table `mlf2_uploads`
--
ALTER TABLE `mlf2_uploads`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `mlf2_userdata`
--
ALTER TABLE `mlf2_userdata`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `key_user_name` (`user_name`),
  ADD UNIQUE KEY `key_user_email` (`user_email`);

--
-- Indexes for table `mlf2_userdata_cache`
--
ALTER TABLE `mlf2_userdata_cache`
  ADD PRIMARY KEY (`cache_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `mlf2_bookmarks`
--
ALTER TABLE `mlf2_bookmarks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mlf2_categories`
--
ALTER TABLE `mlf2_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mlf2_entries`
--
ALTER TABLE `mlf2_entries`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mlf2_pages`
--
ALTER TABLE `mlf2_pages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mlf2_smilies`
--
ALTER TABLE `mlf2_smilies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mlf2_tags`
--
ALTER TABLE `mlf2_tags`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mlf2_uploads`
--
ALTER TABLE `mlf2_uploads`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mlf2_userdata`
--
ALTER TABLE `mlf2_userdata`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
joeiacoponi1 commented 7 months ago

One of my modified queries had a performance issue, so I dug in and found these 2 missing akismet indexes when going through the double upgrade code above:

--
-- Indexes for dumped tables
--

--
-- Indexes for table `mlf2_akismet_rating`
--
ALTER TABLE `mlf2_akismet_rating`
  ADD PRIMARY KEY (`eid`),
  **ADD KEY `akismet_spam` (`spam`),
  ADD KEY `spam_check_status` (`spam_check_status`);**
auge8472 commented 7 months ago

I found the issue with the missing indexes for the akismet-spam-table myself and solved it meanwhile in the pull request with the fixes for the upgrade script (#707). It's currently (2024-04-11) not merged into the main branch.

auge8472 commented 7 months ago

When upgrading from 2.4.24 with the interim version 20220803.1, the resultung structure misses the index for the column mlf2_userdata.user_type. This index is present, if one upgrades directly from 2.4.24. Have to check if the index is superfluous or really missing.

[edit]: The definition in the installation script is as follows.

CREATE TABLE mlf2_userdata (
    ...
    user_type tinyint(4) NOT NULL default '0',
    ...
    KEY key_user_type (user_type),
    ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
auge8472 commented 7 months ago

When upgrading from 2.4.24 with the interim version 20220803.1, the resultung structure is different regarding the column mlf2_userdata.user_email. After a direct upgrade from 2.4.24 its definition is user_email varchar(255) DEFAULT NULL, after an upgrade over the interim step of version 20220803.1 its definition is user_email varchar(255) NOT NULL. The latter is correct.

auge8472 commented 7 months ago

When upgrading from 2.4.24 with the interim version 20220803.1, the resultung structure misses the index for the column mlf2_userdata.user_type. This index is present, if one upgrades directly from 2.4.24.

Correct is: MLF 2.4.24 contains the index user_type for the column mlf2_userdata.user_type. With version 20220508.1 this was changed to the index key_user_type. The current upgrade script does not reflect this change. At the same time the script does delete indexes whose name starts with 'user_' from the table mlf2_userdata in cases of an upgrade, starting with version 2.4.99.2 or any later version. That causes the index for the column mlf2_userdata.user_type to be gone.

This is fixed now with the commits 3daf49457df and a355fb27b69 in #707.

auge8472 commented 7 months ago

After a direct upgrade from 2.4.24 the definition of mlf2-userdata.user_email definition is user_email varchar(255) DEFAULT NULL, after an upgrade over the interim step of version 20220803.1 its definition is user_email varchar(255) NOT NULL. The latter is correct.

This got corrected with 0f0692374a5 in #707.

auge8472 commented 3 months ago

I'll close this issue now. Thank you for your contribution. This is very appreciated and helpful.