tcitworld
commented
8 months ago @solracsf This isn't related to the calendar app, we don't currently have repair steps. Sounds like a core issue.
Open kpneal opened 8 months ago
tcitworld
commented
8 months ago @solracsf This isn't related to the calendar app, we don't currently have repair steps. Sounds like a core issue.
nbakker
commented
8 months ago Similar setup (FreeBSD in a jail, php-8.2, SQLite backend), similar error since upgrading the port to 28.0.3:
root@nextcloud:/usr/local/www/nextcloud # su -fm www -c 'php occ app:enable polls'
An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1 no such column: -1
occ maintenance:repair complains here:
...
- Add preview cleanup background jobs
- Migrate oauth2_clients table to nextcloud schema
- ERROR: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1 no such column: -1
- Fix potential broken mount points
- No mounts updated
kpneal
commented
8 months ago I didn't know about that command. I also think we may have the same issue. Here's what I get:
$ php ./occ maintenance:repair
kpneal
commented
8 months ago Oh, and I'm not aware of using any oauth2 anything at all. I wonder if that's part of the problem?
nbakker
commented
8 months ago It shouldn't be. The failing call is here: https://github.com/nextcloud/server/blob/master/lib/private/Repair/Owncloud/MigrateOauthTables.php#L55
I can't quite follow the SQL syntax here but it seems "-1" should have been a column name:
# sqlite3 -column -header /usr/local/www/nextcloud/data/owncloud.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> SELECT t.name AS table_name,
...> p.*
...> FROM sqlite_master t
...> JOIN pragma_foreign_key_list(t.name) p
...> ON p."seq" != "-1" WHERE t.type = 'table' AND t.name NOT IN ('geometry_columns', 'spatial_ref_sys', 'sqlite_sequence') ORDER BY t.name, p.id DESC, p.seq
...> ;
Parse error: no such column: -1
sqlite> Maybe because there are no foreign keys defined in any schema and this upsets pdo_sqlite or something?
kpneal
commented
8 months ago Trim it down a bit:
sqlite> SELECT t.name as table_name, p.* FROM sqlite_master t JOIN pragma_foreign_key_list(t.name) p on p."seq" != "-1";
Parse error: no such column: -1
sqlite> SELECT t.name as table_name, p.* FROM sqlite_master t JOIN pragma_foreign_key_list(t.name) p on p."seq" != "-2";
Parse error: no such column: -2
sqlite>If I change the double quotes to single quotes, or eliminate the quotes from the negative one, I get this:
sqlite> SELECT t.name as table_name, p.* FROM sqlite_master t JOIN pragma_foreign_key_list(t.name) p on p."seq" != '-1';
sqlite> SELECT t.name as table_name, p.* FROM sqlite_master t JOIN pragma_foreign_key_list(t.name) p on p."seq" != -1;
sqlite> No error message. I don't know what the "-1" is supposed to be, but could this be as simple as changing those double quotes?
nbakker
commented
8 months ago I think you're on to something. Editing 3rdparty/doctrine/dbal/src/Schema/SqliteSchemaManager.php to change "-1" to '-1' fixes my problem of not being able to install an app. occ maintenance:repair also stopped complaining.
kpneal
commented
8 months ago I looked up SQL syntax. A single quote is a string, a double quote is something in the database. So the code is trying to join two tables with one column matching up against another column, but the column doesn't exist so it fails. Now, why is '"-1"' hard coded as a database something? And it is hard coded at line 747 of that SqliteSchemeManager.php file. I'm a little worried that something as general sounding as "selectForeignKeyColumns()" shouldn't be hacked by changing the type of quotes. Not long term, anyway.
kpneal
commented
8 months ago It's weird that in the same function, close enough to be on screen at the same time, we have 'p."seq"' and 'p.seq'. Wouldn't these be the same thing in SQL?
And if 'p.seq' is supposed to be a sequence number, which one could assume from its use as the last column in an ORDER BY clause, then eliminating the quotes to leave a number behind might actually be correct. A quick grep on '\.seq' doesn't show anything conclusive.
kpneal
commented
8 months ago I checked, and pragma_foreign_key_list() returns a table with the "seq" field being a number. So quotes around the '-1' are incorrect because it is not a string and it is not a column.
This is the bug.
kpneal
commented
8 months ago FreeBSD now has 28.0.4 in the ports tree. With the '"-1"' changed to have no quotes at all I am able to upgrade to 28.0.4 and reinstall the calendar app as well.
kpneal
commented
7 months ago I've opened a PR against nextcloud/3rdparty "Quote error with pragma_foreign_key_list() causes errors and upgrade failures. nextcloud/3rdparty#1767" but reviewers are needed.
kpneal
commented
7 months ago And I can't add any reviewers to my PR. Fantastic.
nbakker
commented
7 months ago You linked to a long closed PR from 2016, was that intentional?
Edit: https://github.com/nextcloud/3rdparty/pull/1767 is the correct link which wasn't in the email GitHub sent but is mentioned here.
joshtrichards
commented
7 months ago doctrine/dbal#6325
paulz-gh
commented
2 months ago Please update this 3rdparty module. They have resolved this issue months ago. Now I have to change "-1" to '-1' in the php file manually every time I update nextcloud.
joshtrichards
commented
2 months ago Fixed via nextcloud/3rdparty#1919 + #48330 (at least in stable30 for the next maintenance release).
Pending for v28/v29+(future)v31.
nbakker
commented
1 month ago Confirmed fixed per 30.0.1 which has recently landed in the FreeBSD ports tree.
⚠️ This issue respects the following points: ⚠️
Bug description
I upgraded calendar 4.6.7 using the FreeBSD pkg system and then ran "occ upgrade" like I normally do. This time I got errors:
Disabling the calendar app makes no difference. Removing the calendar app with the occ command makes no difference. Removing calendar with FreeBSD's "pkg remove" after removing it with occ makes no difference.
One more thing that's odd: The apps page "https://share.neutralgood.org/index.php/settings/apps/installed" will, when I click to disable an arbitrary app, open a sidebar on the right for the "Two-Factor TOTP Provider" app. That's why I had to use the occ command to disable the calendar app.
Steps to reproduce
Expected behavior
I expect not to see any error messages.
Installation method
None
Nextcloud Server version
28
Operating system
Other
PHP engine version
PHP 8.2
Web server
Apache (supported)
Database engine version
SQlite
Is this bug present after an update or on a fresh install?
Updated from a MINOR version (ex. 22.1 to 22.2)
Are you using the Nextcloud Server Encryption module?
None
What user-backends are you using?
Configuration report
List of activated Apps
Nextcloud Signing status
Nextcloud Logs
Additional info
uname -a: FreeBSD share.neutralgood.org 13.2-RELEASE-p8 FreeBSD 13.2-RELEASE-p8 GENERIC amd64
I'm running NextCloud in a FreeBSD jail with a full OS install plus these packages: $ pkg info ImageMagick7-7.1.1.26_3 Image processing tools Imath-3.1.11 C++/Python lib of 2D/3D vector, matrix, math ops for computer graphics aom-3.8.2 AV1 reference encoder/decoder apache24-2.4.58_2 Version 2.4.x of Apache web server apr-1.7.3.1.6.3_1 Apache Portability Library argp-standalone-1.5.0 Standalone version of arguments parsing functions from GLIBC avahi-app-0.8_2 Service discovery on a local network bash-5.2.26_1 GNU Project's Bourne Again SHell bash-completion-2.12.0,2 Programmable completion library for Bash brotli-1.1.0,1 Generic-purpose lossless compression algorithm ca_root_nss-3.93_2 Root certificate bundle from the Mozilla Project cairo-1.17.4_2,3 Vector graphics library with cross-device output support cronolog-1.6.2_7 Web log rotation utility that provides datestamp filenames cups-2.4.7_2 Common UNIX Printing System curl-8.6.0 Command line tool and library for transferring data with URLs cyrus-sasl-2.1.28_4 RFC 2222 SASL (Simple Authentication and Security Layer) dav1d-1.4.0 Small and fast AV1 decoder db18-18.1.40 Oracle Berkeley DB, Release 18.1 db5-5.3.28_9 Oracle Berkeley DB, revision 5.3 dbus-1.14.10_4,1 Message bus system for inter-application communication dbus-glib-0.112_1 GLib bindings for the D-BUS messaging system dejavu-2.37_3 Bitstream Vera Fonts clone with a wider range of characters dialog4ports-0.1.6_2 Console Interface to configure ports e2fsprogs-libuuid-1.47.0 UUID library from e2fsprogs package encodings-1.1.0,1 X.Org Encoding fonts expat-2.6.1 XML 1.0 parser written in C ffmpeg-6.1.1_5,1 Realtime audio/video encoder/converter and streaming server fftw3-3.3.10_5 Fast C routines to compute the Discrete Fourier Transform font-bh-ttf-1.0.3_5 X.Org Bigelow & Holmes TTF font font-misc-ethiopic-1.0.4 X.Org miscellaneous Ethiopic font font-misc-meltho-1.0.3_5 X.Org miscellaneous Meltho font fontconfig-2.15.0_2,1 XML-based font configuration API for X Windows freetype2-2.13.2 Free and portable TrueType font rendering engine fribidi-1.0.13_1 Free Implementation of the Unicode Bidirectional Algorithm gdbm-1.23 GNU database manager gdk-pixbuf2-2.42.10_2 Graphic library for GTK gettext-runtime-0.22.3_1 GNU gettext runtime libraries and programs ghostscript10-10.03.0_1 PostScript and PDF interpreter giflib-5.2.1_1 Tools and library routines for working with GIF images git-2.44.0 Distributed source code management tool glib-2.80.0,2 Some useful routines of C programming (current stable version) gmp-6.3.0 Free library for arbitrary precision arithmetic gnome_subr-1.0 Common startup and shutdown subroutines used by GNOME scripts gnutls-3.7.10_2 GNU Transport Layer Security library gobject-introspection-1.78.1_2,1 Generate interface introspection data for GObject libraries graphite2-1.3.14 Rendering capabilities for complex non-Roman writing systems gsfonts-8.11_8 Standard Fonts for Ghostscript harfbuzz-8.3.1 OpenType text shaping engine highway-1.1.0 Performance-portable, length-agnostic SIMD with runtime dispatch icu-74.2,1 International Components for Unicode (from IBM) indexinfo-0.3.1 Utility to regenerate the GNU info page index jansson-2.14 C library for encoding, decoding, and manipulating JSON data jbig2dec-0.20_1 Decoder implementation of the JBIG2 image compression format jbigkit-2.1_2 Lossless compression for bi-level images such as scanned pages, faxes jpeg-turbo-3.0.2 SIMD-accelerated JPEG codec which replaces libjpeg lame-3.100_5 Fast MP3 encoder kit lcms2-2.16_1 Accurate, fast, and small-footprint color management engine libICE-1.1.0_2,1 Inter Client Exchange library for X11 libSM-1.2.3_1,1 Session Management library for X11 libX11-1.8.7_1,1 X11 library libXau-1.0.9_1 Authentication Protocol library for X11 libXdamage-1.1.6 X Damage extension library libXdmcp-1.1.5 X Display Manager Control Protocol library libXext-1.3.6,1 X11 Extension library libXfixes-6.0.0_1 X Fixes extension library libXft-2.3.7_1 Client-sided font API for X applications libXpm-3.5.17_1 X Pixmap library libXrandr-1.5.2_1 X Resize and Rotate extension library libXrender-0.9.10_2 X Render extension library libXt-1.3.0,1 X Toolkit library libXxf86vm-1.1.4_3 X Vidmode Extension libargon2-20190702_1 Memory hard password hashing program and library libass-0.17.1_1 Portable ASS/SSA subtitle renderer libdaemon-0.14_1 Lightweight C library that eases the writing of UNIX daemons libdatrie-0.2.13_2 Double-array trie implementation library libde265-1.0.12 Open source h.265 video codec libdeflate-1.19 Fast, whole-buffer DEFLATE-based compression library libdrm-2.4.120_1,1 Direct Rendering Manager library and headers libedit-3.1.20230828_1,1 Command line editor library libepoll-shim-0.0.20230411 Small epoll implementation using kqueue libevent-2.1.12 API for executing callback functions on events or timeouts libffi-3.4.4_1 Foreign Function Interface libfontenc-1.1.8 The fontenc Library libgcrypt-1.10.3_1 General purpose cryptographic library based on the code from GnuPG libgd-2.3.3_10,1 Graphics library for fast creation of images libglvnd-1.7.0 GL Vendor-Neutral Dispatch library libgpg-error-1.48 Common error values for all GnuPG components libheif-1.17.6_2 ISO/IEC 23008-12:2017 HEIF file format de- and encoder libiconv-1.17_1 Character set conversion library libidn-1.38_1 Internationalized Domain Names command line tool libidn2-2.3.7 Implementation of IDNA2008 internationalized domain names libimagequant-4.3.0_1 Image Quantization Library libjxl-0.10.2 JPEG XL reference encoder/decoder liblqr-1-0.4.2 Easy to use C/C++ seam carving library libltdl-2.4.7 System independent dlopen wrapper liblz4-1.9.4_1,1 LZ4 compression library, lossless and very fast libnghttp2-1.60.0 HTTP/2.0 C Library libogg-1.3.5,4 Ogg bitstream library libpaper-1.1.28_1 Library providing routines for paper size management libpciaccess-0.18 Generic PCI access library libplacebo-6.338.2 Reusable library for GPU-accelerated video/image rendering libpsl-0.21.5 C library to handle the Public Suffix List libpthread-stubs-0.4 Weak aliases for pthread functions libraqm-0.10.1 Library that encapsulates complex text layout logic librav1e-0.5.1_2 Fast and safe AV1 encoder libraw-0.21.2 Library for manipulating raw images librsvg2-rust-2.58.0 Library for parsing and rendering SVG vector-graphic files libsodium-1.0.18 Library to build higher-level cryptographic tools libssh2-1.11.0_1,3 Library implementing the SSH2 protocol libtasn1-4.19.0_1 ASN.1 structure parser library libthai-0.1.29_1 Thai language support library libtheora-1.1.1_7 Theora video codec for the Ogg multimedia streaming system libudev-devd-0.5.2 libudev-compatible interface for devd libunibreak-5.1,1 Implementation of the line and word breaking algorithm for Unicode libunistring-1.2 Unicode string library libunwind-20240221 Generic stack unwinding library libv4l-1.23.0_3 Video4Linux library libva-2.21.0 VAAPI wrapper and dummy driver libvdpau-1.5 VDPAU wrapper and tracing library libvorbis-1.3.7_2,3 Audio compression codec library libvpx-1.14.0 VP8/VP9 reference encoder/decoder libwmf-0.2.13_2 Tools and library for converting Microsoft WMF (windows metafile) libx264-0.164.3095 H.264/MPEG-4 AVC Video Encoding (Library) libxcb-1.16.1 The X protocol C-language Binding (XCB) library libxml2-2.11.7 XML parser library for GNOME libxshmfence-1.3.2 Shared memory 'SyncFence' synchronization primitive libxslt-1.1.37_1 XML stylesheet transformation library libzip-1.10.1 C library for reading, creating, and modifying ZIP archives mkfontscale-1.2.3 Creates an index of scalable font files for X mpdecimal-4.0.0 C/C++ arbitrary precision decimal floating point libraries nettle-3.9.1 Low-level cryptographic library nextcloud-contacts-php82-5.5.3 Contacts app for Nextcloud nextcloud-php82-28.0.3 Collaboration platform which runs on your own server oniguruma-6.9.9 Regular expressions library compatible with POSIX/GNU/Perl openexr-3.2.2_1 High dynamic-range (HDR) image file format openjpeg-2.5.2 Open-source JPEG 2000 codec openldap26-client-2.6.7 Open source LDAP client implementation opus-1.5.1 IETF audio codec p11-kit-0.25.3_1 Library for loading and enumerating of PKCS#11 modules p5-Authen-SASL-2.17 Perl5 module for SASL authentication p5-CGI-4.62 Handle Common Gateway Interface requests and responses p5-Clone-0.46 Recursively copy Perl datatypes p5-Digest-HMAC-1.04 Perl5 interface to HMAC Message-Digest Algorithms p5-Encode-Locale-1.05 Determine the locale encoding p5-Error-0.17029 Error/exception handling in object-oriented programming style p5-GSSAPI-0.28_2 Perl extension providing access to the GSSAPIv2 library p5-HTML-Parser-3.81 Perl5 module for parsing HTML documents p5-HTML-Tagset-3.20_1 Some useful data table in parsing HTML p5-HTTP-Date-6.06 Conversion routines for the HTTP protocol date formats p5-HTTP-Message-6.45 Representation of HTTP style messages p5-IO-HTML-1.004 Open an HTML file with automatic charset detection p5-IO-Socket-IP-0.42 Drop-in replacement for IO::Socket::INET supporting IPv4 and IPv6 p5-IO-Socket-SSL-2.085 Perl5 interface to SSL sockets p5-LWP-MediaTypes-6.04 Guess media type for a file or a URL p5-Mozilla-CA-20231213 Perl extension for Mozilla CA cert bundle in PEM format p5-Net-SSLeay-1.94 Perl5 interface to SSL p5-TimeDate-2.33,1 Perl5 module containing a better/faster date parser for absolute dates p5-URI-5.27 Perl5 interface to Uniform Resource Identifier (URI) references pango-1.50.14 Open-source framework for the layout and rendering of i18n text pciids-20240202 Database of all known IDs used in PCI devices pcre-8.45_4 Perl Compatible Regular Expressions library pcre2-10.43 Perl Compatible Regular Expressions library, version 2 perl5-5.36.3_1 Practical Extraction and Report Language php82-8.2.17 PHP Scripting Language (8.2.X branch) php82-bcmath-8.2.17 The bcmath shared extension for php php82-bz2-8.2.17 The bz2 shared extension for php php82-ctype-8.2.17 The ctype shared extension for php php82-curl-8.2.17 The curl shared extension for php php82-dom-8.2.17 The dom shared extension for php php82-exif-8.2.17 The exif shared extension for php php82-fileinfo-8.2.17 The fileinfo shared extension for php php82-filter-8.2.17 The filter shared extension for php php82-gd-8.2.17 The gd shared extension for php php82-gmp-8.2.17 The gmp shared extension for php php82-iconv-8.2.17 The iconv shared extension for php php82-intl-8.2.17 The intl shared extension for php php82-ldap-8.2.17 The ldap shared extension for php php82-mbstring-8.2.17 The mbstring shared extension for php php82-opcache-8.2.17 The opcache shared extension for php php82-pcntl-8.2.17 The pcntl shared extension for php php82-pdo-8.2.17 The pdo shared extension for php php82-pdo_mysql-8.2.17 The pdo_mysql shared extension for php php82-pdo_sqlite-8.2.17 The pdo_sqlite shared extension for php php82-pecl-APCu-5.1.23 APC User Caching php82-pecl-imagick-3.7.0_3 PHP wrapper to the ImageMagick/GraphicsMagick library php82-pecl-redis-5.3.7_1 Extension to access Redis php82-posix-8.2.17 The posix shared extension for php php82-session-8.2.17 The session shared extension for php php82-simplexml-8.2.17 The simplexml shared extension for php php82-sodium-8.2.17 The sodium shared extension for php php82-sysvsem-8.2.17 The sysvsem shared extension for php php82-xml-8.2.17 The xml shared extension for php php82-xmlreader-8.2.17 The xmlreader shared extension for php php82-xmlwriter-8.2.17 The xmlwriter shared extension for php php82-xsl-8.2.17 The xsl shared extension for php php82-zip-8.2.17 The zip shared extension for php php82-zlib-8.2.17 The zlib shared extension for php pixman-0.42.2 Low-level pixel manipulation library pkg-1.20.9_1 Package manager pkgconf-2.1.1,1 Utility to help to configure compiler and linker flags png-1.6.43 Library for manipulating PNG images poppler-data-0.4.12 Poppler encoding data py39-packaging-23.2 Core utilities for Python packages python39-3.9.18_2 Interpreted object-oriented programming language readline-8.2.10 Library for editing command lines as they are typed redis-7.2.4 Persistent key-value database with built-in net interface shaderc-2024.0 GLSL/HLSL to SPIR-V shader compiler shared-mime-info-2.2_2 MIME types database from the freedesktop.org project sqlite3-3.45.1,1 SQL database engine in a C library svt-av1-2.0.0 Scalable AV1 encoder tiff-4.4.0_3 Tools and library routines for working with TIFF images urw-base35-fonts-20200910 URW core35 fonts in various formats vmaf-3.0.0 Perceptual video quality assessment based on multi-method fusion vulkan-loader-1.3.280 Driver loader for the Vulkan graphics API wayland-1.22.0 Core Wayland window system code and protocol webp-1.3.2_1 Google WebP image format conversion tool x265-3.5_1 H.265/High Efficiency Video Coding (HEVC) format xorg-fonts-truetype-7.7_1 X.Org TrueType fonts xorgproto-2023.2 X Window System unified protocol definitions xvid-1.3.7,1 Opensource MPEG-4 codec, based on OpenDivx xxhash-0.8.2_1 Extremely fast non-cryptographic hash algorithm zstd-1.5.5_1 Fast real-time compression algorithm $