dartcafe
commented
3 days ago have you tried occ polls:db:rebuild?
Open m8ichael opened 6 days ago
dartcafe
commented
3 days ago have you tried occ polls:db:rebuild?
m8ichael
commented
2 days ago @dartcafe Yes, I tried that, but with no results. The error still exists.
dartcafe
commented
2 days ago That makes no sense.
occ polls:db:rebuild removes all indices and foreign key constraints.
After that the tables are checked and updated (or created, if not existent). So there can't exist any foreign key constraint affecting the polls tables.
Either the error message is misleading or something else exists inside the database, preventing the action. Is there any stack trace of the error?
What confuses me, is that the creation of the comments table is reported to be failing. This leads to the conclusion, that it does not exist.
An export of the table definitions could help.
m8ichael
commented
2 days ago OK, here we go... ;-) Here's a dump of the exported structure:
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Erstellungszeit: 20. Nov 2024 um 02:40
-- Server-Version: 10.5.8-MariaDB
-- PHP-Version: 8.2.21
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
--
-- Datenbank: `nextcloud`
--
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_comments`
--
CREATE TABLE `oc_polls_comments` (
`id` bigint(20) NOT NULL,
`poll_id` bigint(20) NOT NULL DEFAULT 0,
`user_id` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`comment` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
`timestamp` bigint(20) NOT NULL DEFAULT 0,
`deleted` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_log`
--
CREATE TABLE `oc_polls_log` (
`id` bigint(20) NOT NULL,
`created` bigint(20) NOT NULL DEFAULT 0,
`processed` bigint(20) NOT NULL DEFAULT 0,
`poll_id` bigint(20) NOT NULL DEFAULT 0,
`user_id` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
`display_name` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
`message_id` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_notif`
--
CREATE TABLE `oc_polls_notif` (
`id` bigint(20) NOT NULL,
`poll_id` bigint(20) NOT NULL DEFAULT 0,
`user_id` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_options`
--
CREATE TABLE `oc_polls_options` (
`id` bigint(20) NOT NULL,
`poll_id` bigint(20) NOT NULL DEFAULT 0,
`poll_option_text` varchar(1024) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`timestamp` bigint(20) NOT NULL DEFAULT 0,
`order` bigint(20) NOT NULL DEFAULT 0,
`confirmed` bigint(20) NOT NULL DEFAULT 0,
`duration` bigint(20) NOT NULL DEFAULT 0,
`owner` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`released` bigint(20) NOT NULL DEFAULT 0,
`poll_option_hash` varchar(256) COLLATE utf8mb4_bin DEFAULT '',
`deleted` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_polls`
--
CREATE TABLE `oc_polls_polls` (
`id` bigint(20) NOT NULL,
`type` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT 'datePoll',
`title` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`description` text COLLATE utf8mb4_bin DEFAULT NULL,
`owner` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
`created` bigint(20) NOT NULL DEFAULT 0,
`expire` bigint(20) NOT NULL DEFAULT 0,
`deleted` bigint(20) NOT NULL DEFAULT 0,
`access` varchar(1024) COLLATE utf8mb4_bin NOT NULL DEFAULT 'private',
`anonymous` bigint(20) NOT NULL DEFAULT 0,
`allow_maybe` bigint(20) NOT NULL DEFAULT 1,
`vote_limit` bigint(20) NOT NULL DEFAULT 0,
`show_results` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT 'always',
`admin_access` bigint(20) NOT NULL DEFAULT 0,
`option_limit` bigint(20) NOT NULL DEFAULT 0,
`allow_comment` bigint(20) NOT NULL DEFAULT 1,
`hide_booked_up` bigint(20) NOT NULL DEFAULT 1,
`allow_proposals` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT 'disallow',
`use_no` bigint(20) NOT NULL DEFAULT 1,
`proposals_expire` bigint(20) NOT NULL DEFAULT 0,
`misc_settings` text COLLATE utf8mb4_bin DEFAULT NULL,
`last_interaction` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_preferences`
--
CREATE TABLE `oc_polls_preferences` (
`id` bigint(20) NOT NULL,
`user_id` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`timestamp` bigint(20) NOT NULL DEFAULT 0,
`preferences` text COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_share`
--
CREATE TABLE `oc_polls_share` (
`id` bigint(20) NOT NULL,
`token` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`type` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`poll_id` bigint(20) NOT NULL DEFAULT 0,
`user_id` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`invitation_sent` bigint(20) NOT NULL DEFAULT 0,
`display_name` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
`email_address` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
`reminder_sent` bigint(20) NOT NULL DEFAULT 0,
`misc_settings` text COLLATE utf8mb4_bin DEFAULT NULL,
`locked` bigint(20) NOT NULL DEFAULT 0,
`label` varchar(256) COLLATE utf8mb4_bin DEFAULT '',
`deleted` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_votes`
--
CREATE TABLE `oc_polls_votes` (
`id` bigint(20) NOT NULL,
`poll_id` bigint(20) NOT NULL DEFAULT 0,
`user_id` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`vote_option_id` bigint(20) NOT NULL DEFAULT 0,
`vote_option_text` varchar(1024) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`vote_answer` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`vote_option_hash` varchar(256) COLLATE utf8mb4_bin DEFAULT '',
`deleted` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `oc_polls_watch`
--
CREATE TABLE `oc_polls_watch` (
`id` bigint(20) NOT NULL,
`poll_id` bigint(20) NOT NULL DEFAULT 0,
`table` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`updated` bigint(20) NOT NULL DEFAULT 0,
`session_id` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
--
-- Indizes der exportierten Tabellen
--
--
-- Indizes für die Tabelle `oc_polls_preferences`
--
ALTER TABLE `oc_polls_preferences`
ADD UNIQUE KEY `UNIQ_preferences` (`user_id`);
COMMIT;All tables exist on the database.
dartcafe
commented
2 days ago There is no hint inside the table structure, which may help to analyze the error.
I need more details about the error line, to get an idea, what is wrong. It seems the error does not describe a failure on creating the table but attaching the fk. There should be more information within the printed error which names the failing fk structure.
m8ichael
commented
1 day ago OK, now I got an error, when I try to use occ polls:db:rebuild:
Step 3. Create or update tables to current shema
- Set db structure
Validating table oc_polls_polls
Validating table oc_polls_options
Validating table oc_polls_votes
Validating table oc_polls_comments
Validating table oc_polls_share
Validating table oc_polls_notif
Validating table oc_polls_log
Validating table oc_polls_watch
Validating table oc_polls_preferences
In DbalException.php line 54:
An exception occurred while executing a query: SQLSTATE[42000]: Syntax erro
r or access violation: 1075 Incorrect table definition; there can be only o
ne auto column and it must be defined as a key
In ExceptionConverter.php line 118:
An exception occurred while executing a query: SQLSTATE[42000]: Syntax erro
r or access violation: 1075 Incorrect table definition; there can be only o
ne auto column and it must be defined as a key
In Exception.php line 28:
SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def
inition; there can be only one auto column and it must be defined as a key
In Connection.php line 33:
SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def
inition; there can be only one auto column and it must be defined as a key
⚠️ This issue respects the following points: ⚠️
What went wrong, what did you observe?
When I updated to NC 30.0.2, I got a problem updating polls.
nextcloud.oc_polls_comments(errno: 150 "Foreign key constraint is incorrectly formed")I first tried to disable polls, delete the tables ocpolls*, restore them from backup an re-enabled polls, but the problem persists.
What did you expect, how polls should behave instead?
No error message when the upgrade-command is executed
What steps does it need to replay this bug?
Installation method
Installed/updated from the appstore (Apps section of your site)
Installation type
Updated from previous major version (i.e. 3.x.x to 4.x.x)
Affected polls version
7.2.4
Which browser did you use, when experiencing the bug?
Other browser
No response
Add your browser log here
Additional client environment information
No response
NC version
Nextcloud 30
Other Nextcloud version
No response
PHP engine version
PHP 8.3
Other PHP version
No response
Database engine
MariaDB
Database Engine version or other Database
10.5.8
Which user-backends are you using?
Add your nextcloud server log here
Additional environment informations
No response
Configuration report
List of activated Apps
Nextcloud Signing status
Additional Information
No response