Database maintenance check having problems

[billtomczak]

Steps to reproduce the issue

Install an extension with a sql updater file that modifies a couple fields:

ALTER TABLE #__pixtracker_tags
CHANGE COLUMN state state TINYINT(1) NOT NULL DEFAULT 1,
CHANGE COLUMN script script TEXT NULL DEFAULT NULL;

Although valid mysql syntax, the checker declares the database is not up to date:

Additionally, an error is generated:

I had heard a rumor that multiple changes are a problem for the checker, so I turned this into discreet statements:

ALTER TABLE #__pixtracker_tags CHANGE COLUMN state state TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE #__pixtracker_tags CHANGE COLUMN script script TEXT NULL DEFAULT NULL;

While this doesn't generate the error message, the checker still declares the database out of date for the same reasons as shown above.

Next attempt. Maybe it doesn't like the CHANGE syntax?

ALTER TABLE #__pixtracker_tags MODIFY COLUMN state TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE #__pixtracker_tags MODIFY COLUMN script TEXT NULL DEFAULT NULL;

Now it's declared out of date for slightly different reasons:

The only way to make the checker happy is with this:

ALTER TABLE #__pixtracker_tags CHANGE COLUMN state TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE #__pixtracker_tags CHANGE COLUMN script TEXT NULL DEFAULT NULL;

Which is invalid syntax.

Expected result

Ideally any valid sql statements should be recognized in the update files

Actual result

Most valid syntaxes cause the db checker to declare the tables to be out of date and the only syntax recognized is invalid sql.

System information (as much as possible)

{
"info": {
"php": "Linux 181198.cloudwaysapps.com 4.9.0-14-amd64 #1 SMP Debian 4.9.240-2 (2020-10-30) x86_64",
"dbserver": "mysql",
"dbversion": "10.4.20-MariaDB-1:10.4.20+maria~stretch-log",
"dbcollation": "utf8_general_ci",
"dbconnectioncollation": "utf8mb4_general_ci",
"dbconnectionencryption": "",
"dbconnencryptsupported": true,
"phpversion": "7.4.26",
"server": "Apache/2.4.25 (Debian)",
"sapi_name": "fpm-fcgi",
"version": "Joomla! 4.0.5 Stable [ Furaha ] 11-December-2021 20:52 GMT",
"useragent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36"
},
"phpSettings": {
"memory_limit": "256M",
"upload_max_filesize": "100M",
"post_max_size": "100M",
"display_errors": true,
"short_open_tag": false,
"file_uploads": true,
"output_buffering": true,
"open_basedir": "",
"session.save_path": "xxxxxx",
"session.auto_start": "0",
"disable_functions": "pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_get_handler,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,pcntl_async_signals,pcntl_unshare,",
"xml": true,
"zlib": true,
"zip": true,
"mbstring": true,
"gd": true,
"iconv": true,
"intl": true,
"max_input_vars": "2500"
},
"phpInfo": {
"apc": {
"APC Compatibility": "1.0.5"
},
"apcu": {
"APCu Support": "Enabled",
"Version": "5.1.21",
"APCu Debugging": "Disabled",
"MMAP Support": "Enabled",
"MMAP File Mask": "no value",
"Serialization Support": "php, igbinary",
"Build Date": "Nov 13 2021 17:18:27",
"Directive": [
"Local Value",
"Master Value"
],
"apc.coredump_unmap": [
"Off",
"Off"
],
"apc.enable_cli": [
"Off",
"Off"
],
"apc.enabled": [
"On",
"On"
],
"apc.entries_hint": [
"4096",
"4096"
],
"apc.gc_ttl": [
"3600",
"3600"
],
"apc.mmap_file_mask": [
"no value",
"no value"
],
"apc.preload_path": [
"no value",
"no value"
],
"apc.serializer": [
"php",
"php"
],
"apc.shm_segments": [
"1",
"1"
],
"apc.shm_size": [
"32M",
"32M"
],
"apc.slam_defense": [
"Off",
"Off"
],
"apc.smart": [
"0",
"0"
],
"apc.ttl": [
"0",
"0"
],
"apc.use_request_time": [
"Off",
"Off"
]
},
"bcmath": {
"BCMath support": "enabled",
"Directive": [
"Local Value",
"Master Value"
],
"bcmath.scale": [
"0",
"0"
]
},
"bz2": {
"BZip2 Support": "Enabled",
"Stream Wrapper support": "compress.bzip2://",
"Stream Filter support": "bzip2.decompress, bzip2.compress",
"BZip2 Version": "1.0.6, 6-Sept-2010"
},
"calendar": {
"Calendar support": "enabled"
},
"cgi-fcgi": {
"php-fpm": "active",
"Directive": [
"Local Value",
"Master Value"
],
"cgi.discard_path": [
"0",
"0"
],
"cgi.fix_pathinfo": [
"1",
"1"
],
"cgi.force_redirect": [
"1",
"1"
],
"cgi.nph": [
"0",
"0"
],
"cgi.redirect_status_env": [
"no value",
"no value"
],
"cgi.rfc2616_headers": [
"0",
"0"
],
"fastcgi.error_header": [
"no value",
"no value"
],
"fastcgi.logging": [
"1",
"1"
],
"fpm.config": [
"no value",
"no value"
]
},
"Core": {
"PHP Version": "7.4.26",
"Directive": [
"Local Value",
"Master Value"
],
"allow_url_fopen": [
"On",
"On"
],
"allow_url_include": [
"Off",
"Off"
],
"arg_separator.input": [
"&",
"&"
],
"arg_separator.output": [
"&",
"&"
],
"auto_append_file": [
"no value",
"no value"
],
"auto_globals_jit": [
"On",
"On"
],
"auto_prepend_file": [
"no value",
"no value"
],
"browscap": [
"no value",
"no value"
],
"default_charset": [
"UTF-8",
"UTF-8"
],
"default_mimetype": [
"text/html",
"text/html"
],
"disable_classes": [
"no value",
"no value"
],
"disable_functions": [
"pcntl_alarm, pcntl_fork, pcntl_waitpid, pcntl_wait, pcntl_wifexited, pcntl_wifstopped, pcntl_wifsignaled, pcntl_wifcontinued, pcntl_wexitstatus, pcntl_wtermsig, pcntl_wstopsig, pcntl_signal, pcntl_signal_get_handler, pcntl_signal_dispatch, pcntl_get_last_error, pcntl_strerror, pcntl_sigprocmask, pcntl_sigwaitinfo, pcntl_sigtimedwait, pcntl_exec, pcntl_getpriority, pcntl_setpriority, pcntl_async_signals, pcntl_unshare,",
"pcntl_alarm, pcntl_fork, pcntl_waitpid, pcntl_wait, pcntl_wifexited, pcntl_wifstopped, pcntl_wifsignaled, pcntl_wifcontinued, pcntl_wexitstatus, pcntl_wtermsig, pcntl_wstopsig, pcntl_signal, pcntl_signal_get_handler, pcntl_signal_dispatch, pcntl_get_last_error, pcntl_strerror, pcntl_sigprocmask, pcntl_sigwaitinfo, pcntl_sigtimedwait, pcntl_exec, pcntl_getpriority, pcntl_setpriority, pcntl_async_signals, pcntl_unshare,"
],
"display_errors": [
"On",
"Off"
],
"display_startup_errors": [
"Off",
"Off"
],
"doc_root": [
"no value",
"no value"
],
"docref_ext": [
"no value",
"no value"
],
"docref_root": [
"no value",
"no value"
],
"enable_dl": [
"Off",
"Off"
],
"enable_post_data_reading": [
"On",
"On"
],
"error_append_string": [
"no value",
"no value"
],
"error_log": [
"xxxxxx",
"xxxxxx"
],
"error_prepend_string": [
"no value",
"no value"
],
"error_reporting": [
"32767",
"32767"
],
"expose_php": [
"Off",
"Off"
],
"extension_dir": [
"xxxxxx",
"xxxxxx"
],
"file_uploads": [
"On",
"On"
],
"hard_timeout": [
"2",
"2"
],
"highlight.comment": [
"#FF8000",
"#FF8000"
],
"highlight.default": [
"#0000BB",
"#0000BB"
],
"highlight.html": [
"#000000",
"#000000"
],
"highlight.keyword": [
"#7700",
"#7700"
],
"highlight.string": [
"#DD0000",
"#DD0000"
],
"html_errors": [
"On",
"On"
],
"ignore_repeated_errors": [
"Off",
"Off"
],
"ignore_repeated_source": [
"Off",
"Off"
],
"ignore_user_abort": [
"Off",
"Off"
],
"implicit_flush": [
"Off",
"Off"
],
"include_path": [
"xxxxxx",
"xxxxxx"
],
"input_encoding": [
"no value",
"no value"
],
"internal_encoding": [
"no value",
"no value"
],
"log_errors": [
"On",
"On"
],
"log_errors_max_len": [
"1024",
"1024"
],
"mail.add_x_header": [
"Off",
"Off"
],
"mail.force_extra_parameters": [
"no value",
"no value"
],
"mail.log": [
"no value",
"no value"
],
"max_execution_time": [
"99",
"99"
],
"max_file_uploads": [
"20",
"20"
],
"max_input_nesting_level": [
"64",
"64"
],
"max_input_time": [
"60",
"60"
],
"max_input_vars": [
"2500",
"2500"
],
"memory_limit": [
"256M",
"256M"
],
"open_basedir": [
"xxxxxx",
"xxxxxx"
],
"output_buffering": [
"4096",
"4096"
],
"output_encoding": [
"no value",
"no value"
],
"output_handler": [
"no value",
"no value"
],
"post_max_size": [
"100M",
"100M"
],
"precision": [
"14",
"14"
],
"realpath_cache_size": [
"4096K",
"4096K"
],
"realpath_cache_ttl": [
"120",
"120"
],
"register_argc_argv": [
"Off",
"Off"
],
"report_memleaks": [
"On",
"On"
],
"report_zend_debug": [
"On",
"On"
],
"request_order": [
"GP",
"GP"
],
"sendmail_from": [
"no value",
"no value"
],
"sendmail_path": [
"xxxxxx",
"xxxxxx"
],
"serialize_precision": [
"-1",
"-1"
],
"short_open_tag": [
"Off",
"Off"
],
"SMTP": [
"localhost",
"localhost"
],
"smtp_port": [
"25",
"25"
],
"sys_temp_dir": [
"no value",
"no value"
],
"syslog.facility": [
"LOG_USER",
"LOG_USER"
],
"syslog.filter": [
"no-ctrl",
"no-ctrl"
],
"syslog.ident": [
"php",
"php"
],
"track_errors": [
"Off",
"Off"
],
"unserialize_callback_func": [
"no value",
"no value"
],
"upload_max_filesize": [
"100M",
"100M"
],
"upload_tmp_dir": [
"xxxxxx",
"xxxxxx"
],
"user_dir": [
"no value",
"no value"
],
"user_ini.cache_ttl": [
"300",
"300"
],
"user_ini.filename": [
".user.ini",
".user.ini"
],
"variables_order": [
"GPCS",
"GPCS"
],
"xmlrpc_error_number": [
"0",
"0"
],
"xmlrpc_errors": [
"Off",
"Off"
],
"zend.assertions": [
"-1",
"-1"
],
"zend.detect_unicode": [
"On",
"On"
],
"zend.enable_gc": [
"On",
"On"
],
"zend.exception_ignore_args": [
"On",
"On"
],
"zend.multibyte": [
"Off",
"Off"
],
"zend.script_encoding": [
"no value",
"no value"
],
"zend.signal_check": [
"Off",
"Off"
]
},
"ctype": {
"ctype functions": "enabled"
},
"curl": {
"cURL support": "enabled",
"cURL Information": "7.52.1",
"Age": "3",
"AsynchDNS": "Yes",
"CharConv": "No",
"Debug": "No",
"GSS-Negotiate": "No",
"IDN": "Yes",
"IPv6": "Yes",
"krb4": "No",
"Largefile": "Yes",
"libz": "Yes",
"NTLM": "Yes",
"NTLMWB": "Yes",
"SPNEGO": "Yes",
"SSL": "Yes",
"SSPI": "No",
"TLS-SRP": "Yes",
"HTTP2": "Yes",
"GSSAPI": "Yes",
"KERBEROS5": "Yes",
"UNIX_SOCKETS": "Yes",
"PSL": "Yes",
"HTTPS_PROXY": "Yes",
"Protocols": "dict, file, ftp, ftps, gopher, http, https, imap, imaps, ldap, ldaps, pop3, pop3s, rtmp, rtsp, scp, sftp, smb, smbs, smtp, smtps, telnet, tftp",
"Host": "xxxxxx",
"SSL Version": "OpenSSL/1.0.2u",
"ZLib Version": "1.2.8",
"libSSH Version": "libssh2/1.7.0",
"Directive": [
"Local Value",
"Master Value"
],
"curl.cainfo": [
"no value",
"no value"
]
},
"date": {
"date/time support": "enabled",
"timelib version": "2018.04",
""Olson" Timezone Database Version": "0.system",
"Timezone Database": "internal",
"Default timezone": "UTC",
"Directive": [
"Local Value",
"Master Value"
],
"date.default_latitude": [
"31.7667",
"31.7667"
],
"date.default_longitude": [
"35.2333",
"35.2333"
],
"date.sunrise_zenith": [
"90.583333",
"90.583333"
],
"date.sunset_zenith": [
"90.583333",
"90.583333"
],
"date.timezone": [
"UTC",
"UTC"
]
},
"dba": {
"DBA support": "enabled",
"libdb header version": "Berkeley DB 5.3.28: (September 9, 2013)",
"libdb library version": "Berkeley DB 5.3.28: (September 9, 2013)",
"Supported handlers": "cdb cdb_make db4 inifile flatfile qdbm lmdb",
"Directive": [
"Local Value",
"Master Value"
],
"dba.default_handler": [
"flatfile",
"flatfile"
]
},
"dom": {
"DOM/XML": "enabled",
"DOM/XML API Version": "20031129",
"libxml Version": "2.9.12",
"HTML Support": "enabled",
"XPath Support": "enabled",
"XPointer Support": "enabled",
"Schema Support": "enabled",
"RelaxNG Support": "enabled"
},
"enchant": {
"enchant support": "enabled",
"Libenchant Version": "1.6.1",
"ispell": [
"Ispell Provider",
"/usr/lib/x86_64-linux-gnu/enchant/libenchant_ispell.so"
],
"zemberek": [
"Zemberek Provider",
"/usr/lib/x86_64-linux-gnu/enchant/libenchant_zemberek.so"
],
"aspell": [
"Aspell Provider",
"/usr/lib/x86_64-linux-gnu/enchant/libenchant_aspell.so"
],
"hspell": [
"Hspell Provider",
"/usr/lib/x86_64-linux-gnu/enchant/libenchant_hspell.so"
],
"myspell": [
"Myspell Provider",
"/usr/lib/x86_64-linux-gnu/enchant/libenchant_myspell.so"
]
},
"exif": {
"EXIF Support": "enabled",
"Supported EXIF Version": "0220",
"Supported filetypes": "JPEG, TIFF",
"Multibyte decoding support using mbstring": "enabled",
"Extended EXIF tag formats": "Canon, Casio, Fujifilm, Nikon, Olympus, Samsung, Panasonic, DJI, Sony, Pentax, Minolta, Sigma, Foveon, Kyocera, Ricoh, AGFA, Epson",
"Directive": [
"Local Value",
"Master Value"
],
"exif.decode_jis_intel": [
"JIS",
"JIS"
],
"exif.decode_jis_motorola": [
"JIS",
"JIS"
],
"exif.decode_unicode_intel": [
"UCS-2LE",
"UCS-2LE"
],
"exif.decode_unicode_motorola": [
"UCS-2BE",
"UCS-2BE"
],
"exif.encode_jis": [
"no value",
"no value"
],
"exif.encode_unicode": [
"ISO-8859-15",
"ISO-8859-15"
]
},
"FFI": {
"FFI support": "enabled",
"Directive": [
"Local Value",
"Master Value"
],
"ffi.enable": [
"preload",
"preload"
],
"ffi.preload": [
"no value",
"no value"
]
},
"fileinfo": {
"fileinfo support": "enabled",
"libmagic": "537"
},
"filter": {
"Input Validation and Filtering": "enabled",
"Directive": [
"Local Value",
"Master Value"
],
"filter.default": [
"unsafe_raw",
"unsafe_raw"
],
"filter.default_flags": [
"no value",
"no value"
]
},
"ftp": {
"FTP support": "enabled",
"FTPS support": "enabled"
},
"gd": {
"GD Support": "enabled",
"GD headers Version": "2.2.5",
"GD library Version": "2.2.5",
"FreeType Support": "enabled",
"FreeType Linkage": "with freetype",
"GIF Read Support": "enabled",
"GIF Create Support": "enabled",
"JPEG Support": "enabled",
"PNG Support": "enabled",
"WBMP Support": "enabled",
"XPM Support": "enabled",
"XBM Support": "enabled",
"WebP Support": "enabled",
"BMP Support": "enabled",
"TGA Read Support": "enabled",
"Directive": [
"Local Value",
"Master Value"
],
"gd.jpeg_ignore_warning": [
"1",
"1"
]
},
"gettext": {
"GetText Support": "enabled"
},
"gmp": {
"gmp support": "enabled",
"GMP version": "6.1.2"
},
"hash": {
"hash support": "enabled",
"Hashing Engines": "md2 md4 md5 sha1 sha224 sha256 sha384 sha512/224 sha512/256 sha512 sha3-224 sha3-256 sha3-384 sha3-512 ripemd128 ripemd160 ripemd256 ripemd320 whirlpool tiger128, 3 tiger160, 3 tiger192, 3 tiger128, 4 tiger160, 4 tiger192, 4 snefru snefru256 gost gost-crypto adler32 crc32 crc32b crc32c fnv132 fnv1a32 fnv164 fnv1a64 joaat haval128, 3 haval160, 3 haval192, 3 haval224, 3 haval256, 3 haval128, 4 haval160, 4 haval192, 4 haval224, 4 haval256, 4 haval128, 5 haval160, 5 haval192, 5 haval224, 5 haval256, 5",
"MHASH support": "Enabled",
"MHASH API Version": "Emulated Support"
},
"iconv": {
"iconv support": "enabled",
"iconv implementation": "glibc",
"iconv library version": "2.24",
"Directive": [
"Local Value",
"Master Value"
],
"iconv.input_encoding": [
"no value",
"no value"
],
"iconv.internal_encoding": [
"no value",
"no value"
],
"iconv.output_encoding": [
"no value",
"no value"
]
},
"igbinary": {
"igbinary support": "enabled",
"igbinary version": "3.2.6",
"igbinary APCu serializer ABI": "0",
"igbinary session support": "yes",
"Directive": [
"Local Value",
"Master Value"
],
"igbinary.compact_strings": [
"On",
"On"
]
},
"imagick": {
"imagick module": "enabled",
"imagick module version": "3.5.1",
"imagick classes": "Imagick, ImagickDraw, ImagickPixel, ImagickPixelIterator, ImagickKernel",
"Imagick compiled with ImageMagick version": "ImageMagick 6.9.7-4 Q16 x86_64 20170114 http://www.imagemagick.org",
"Imagick using ImageMagick library version": "ImageMagick 6.9.7-4 Q16 x86_64 20170114 http://www.imagemagick.org",
"ImageMagick copyright": "\u00a9 1999-2017 ImageMagick Studio LLC",
"ImageMagick release date": "20170114",
"ImageMagick number of supported formats:": "236",
"ImageMagick supported formats": "3FR, AAI, AI, ART, ARW, AVI, AVS, BGR, BGRA, BGRO, BIE, BMP, BMP2, BMP3, BRF, CAL, CALS, CANVAS, CAPTION, CIN, CIP, CLIP, CMYK, CMYKA, CR2, CRW, CUR, CUT, DATA, DCM, DCR, DCX, DDS, DFONT, DJVU, DNG, DOT, DPX, DXT1, DXT5, EPDF, EPI, EPS, EPS2, EPS3, EPSF, EPSI, EPT, EPT2, EPT3, ERF, EXR, FAX, FILE, FITS, FRACTAL, FTP, FTS, G3, G4, GIF, GIF87, GRADIENT, GRAY, GROUP4, GV, H, HALD, HDR, HISTOGRAM, HRZ, HTM, HTML, HTTP, HTTPS, ICB, ICO, ICON, IIQ, INFO, INLINE, IPL, ISOBRL, ISOBRL6, J2C, J2K, JBG, JBIG, JNG, JNX, JP2, JPC, JPE, JPEG, JPG, JPM, JPS, JPT, JSON, K25, KDC, LABEL, M2V, M4V, MAC, MAGICK, MAP, MASK, MAT, MATTE, MEF, MIFF, MKV, MNG, MONO, MOV, MP4, MPC, MPEG, MPG, MRW, MSL, MSVG, MTV, MVG, NEF, NRW, NULL, ORF, OTB, OTF, PAL, PALM, PAM, PANGO, PATTERN, PBM, PCD, PCDS, PCL, PCT, PCX, PDB, PDF, PDFA, PEF, PES, PFA, PFB, PFM, PGM, PICON, PICT, PIX, PJPEG, PLASMA, PNG, PNG00, PNG24, PNG32, PNG48, PNG64, PNG8, PNM, PPM, PREVIEW, PS, PS2, PS3, PSB, PSD, PTIF, PWP, RADIAL-GRADIENT, RAF, RAS, RAW, RGB, RGBA, RGBO, RGF, RLA, RLE, RMF, RW2, SCR, SCT, SFW, SGI, SHTML, SIX, SIXEL, SPARSE-COLOR, SR2, SRF, STEGANO, SUN, SVG, SVGZ, TEXT, TGA, THUMBNAIL, TIFF, TIFF64, TILE, TIM, TTC, TTF, TXT, UBRL, UBRL6, UIL, UYVY, VDA, VICAR, VID, VIFF, VIPS, VST, WBMP, WMF, WMV, WMZ, WPG, X, X3F, XBM, XC, XCF, XPM, XPS, XV, XWD, YCbCr, YCbCrA, YUV",
"Directive": [
"Local Value",
"Master Value"
],
"imagick.allow_zero_dimension_images": [
"0",
"0"
],
"imagick.locale_fix": [
"0",
"0"
],
"imagick.progress_monitor": [
"0",
"0"
],
"imagick.set_single_thread": [
"1",
"1"
],
"imagick.shutdown_sleep_count": [
"10",
"10"
],
"imagick.skip_version_check": [
"1",
"1"
]
},
"imap": {
"IMAP c-Client Version": "2007f",
"SSL Support": "enabled",
"Kerberos Support": "enabled",
"Directive": [
"Local Value",
"Master Value"
],
"imap.enable_insecure_rsh": [
"Off",
"Off"
]
},
"intl": {
"Internationalization support": "enabled",
"ICU version": "65.1",
"ICU Data version": "65.1",
"ICU TZData version": "2019c",
"ICU Unicode version": "12.1",
"Directive": [
"Local Value",
"Master Value"
],
"intl.default_locale": [
"no value",
"no value"
],
"intl.error_level": [
"0",
"0"
],
"intl.use_exceptions": [
"0",
"0"
]
},
"json": {
"json support": "enabled"
},
"libxml": {
"libXML support": "active",
"libXML Compiled Version": "2.9.12",
"libXML Loaded Version": "20912",
"libXML streams": "enabled"
},
"mbstring": {
"Multibyte Support": "enabled",
"Multibyte string engine": "libmbfl",
"HTTP input encoding translation": "disabled",
"libmbfl version": "1.3.2",
"Multibyte (japanese) regex support": "enabled",
"Multibyte regex (oniguruma) version": "6.1.3",
"Directive": [
"Local Value",
"Master Value"
],
"mbstring.detect_order": [
"no value",
"no value"
],
"mbstring.encoding_translation": [
"Off",
"Off"
],
"mbstring.func_overload": [
"0",
"0"
],
"mbstring.http_input": [
"no value",
"no value"
],
"mbstring.http_output": [
"no value",
"no value"
],
"mbstring.http_output_conv_mimetypes": [
"^(text/|application/xhtml\+xml)",
"^(text/|application/xhtml\+xml)"
],
"mbstring.internal_encoding": [
"no value",
"no value"
],
"mbstring.language": [
"neutral",
"neutral"
],
"mbstring.regex_retry_limit": [
"1000000",
"1000000"
],
"mbstring.regex_stack_limit": [
"100000",
"100000"
],
"mbstring.strict_detection": [
"Off",
"Off"
],
"mbstring.substitute_character": [
"no value",
"no value"
]
},
"memcached": {
"memcached support": "enabled",
"Version": "3.1.5",
"libmemcached version": "1.0.18",
"SASL support": "yes",
"Session support": "yes",
"igbinary support": "yes",
"json support": "yes",
"msgpack support": "yes",
"Directive": [
"Local Value",
"Master Value"
],
"memcached.compression_factor": [
"1.3",
"1.3"
],
"memcached.compression_threshold": [
"2000",
"2000"
],
"memcached.compression_type": [
"fastlz",
"fastlz"
],
"memcached.default_binary_protocol": [
"Off",
"Off"
],
"memcached.default_connect_timeout": [
"0",
"0"
],
"memcached.default_consistent_hash": [
"Off",
"Off"
],
"memcached.serializer": [
"php",
"php"
],
"memcached.sess_binary_protocol": [
"On",
"On"
],
"memcached.sess_connect_timeout": [
"0",
"0"
],
"memcached.sess_consistent_hash": [
"On",
"On"
],
"memcached.sess_consistent_hash_type": [
"ketama",
"ketama"
],
"memcached.sess_lock_expire": [
"0",
"0"
],
"memcached.sess_lock_max_wait": [
"not set",
"not set"
],
"memcached.sess_lock_retries": [
"5",
"5"
],
"memcached.sess_lock_wait": [
"not set",
"not set"
],
"memcached.sess_lock_wait_max": [
"150",
"150"
],
"memcached.sess_lock_wait_min": [
"150",
"150"
],
"memcached.sess_locking": [
"On",
"On"
],
"memcached.sess_number_of_replicas": [
"0",
"0"
],
"memcached.sess_persistent": [
"Off",
"Off"
],
"memcached.sess_prefix": [
"memc.sess.key.",
"memc.sess.key."
],
"memcached.sess_randomize_replica_read": [
"Off",
"Off"
],
"memcached.sess_remove_failed_servers": [
"Off",
"Off"
],
"memcached.sess_sasl_password": [
"no value",
"no value"
],
"memcached.sess_sasl_username": [
"no value",
"no value"
],
"memcached.sess_server_failure_limit": [
"0",
"0"
],
"memcached.store_retry_count": [
"2",
"2"
]
},
"mongodb": {
"MongoDB support": "enabled",
"MongoDB extension version": "1.10.0",
"MongoDB extension stability": "stable",
"libbson bundled version": "1.18.0",
"libmongoc bundled version": "1.18.0",
"libmongoc SSL": "enabled",
"libmongoc SSL library": "OpenSSL",
"libmongoc crypto": "enabled",
"libmongoc crypto library": "libcrypto",
"libmongoc crypto system profile": "disabled",
"libmongoc SASL": "enabled",
"libmongoc ICU": "disabled",
"libmongoc compression": "enabled",
"libmongoc compression snappy": "disabled",
"libmongoc compression zlib": "enabled",
"libmongoc compression zstd": "disabled",
"libmongocrypt bundled version": "1.2.1",
"libmongocrypt crypto": "enabled",
"libmongocrypt crypto library": "libcrypto",
"Directive": [
"Local Value",
"Master Value"
],
"mongodb.debug": [
"no value",
"no value"
]
},
"msgpack": {
"MessagePack Support": "enabled",
"Session Support": "enabled",
"extension Version": "2.1.2",
"header Version": "3.2.0",
"Directive": [
"Local Value",
"Master Value"
],
"msgpack.error_display": [
"On",
"On"
],
"msgpack.illegal_key_insert": [
"Off",
"Off"
],
"msgpack.php_only": [
"On",
"On"
],
"msgpack.use_str8_serialization": [
"On",
"On"
]
},
"mysqli": {
"MysqlI Support": "enabled",
"Client API library version": "mysqlnd 7.4.26",
"Active Persistent Links": "0",
"Inactive Persistent Links": "0",
"Active Links": "1",
"Directive": [
"Local Value",
"Master Value"
],
"mysqli.allow_local_infile": [
"Off",
"Off"
],
"mysqli.allow_persistent": [
"On",
"On"
],
"mysqli.default_host": [
"no value",
"no value"
],
"mysqli.default_port": [
"3306",
"3306"
],
"mysqli.default_pw": [
"no value",
"no value"
],
"mysqli.default_socket": [
"xxxxxx",
"xxxxxx"
],
"mysqli.default_user": [
"no value",
"no value"
],
"mysqli.max_links": [
"Unlimited",
"Unlimited"
],
"mysqli.max_persistent": [
"Unlimited",
"Unlimited"
],
"mysqli.reconnect": [
"Off",
"Off"
],
"mysqli.rollback_on_cached_plink": [
"Off",
"Off"
]
},
"mysqlnd": {
"mysqlnd": "enabled",
"Version": "mysqlnd 7.4.26",
"Compression": "supported",
"core SSL": "supported",
"extended SSL": "supported",
"Command buffer size": "4096",
"Read buffer size": "32768",
"Read timeout": "86400",
"Collecting statistics": "Yes",
"Collecting memory statistics": "No",
"Tracing": "n/a",
"Loaded plugins": "mysqlnd, debug_trace, auth_plugin_mysql_native_password, auth_plugin_mysql_clear_password, auth_plugin_caching_sha2_password, auth_plugin_sha256_password",
"API Extensions": "mysqli, pdo_mysql"
},
"odbc": {
"ODBC Support": "enabled",
"Active Persistent Links": "0",
"Active Links": "0",
"ODBC library": "unixODBC",
"ODBCVER": "0x0380",
"ODBC_CFLAGS": "-I/usr/include",
"ODBC_LFLAGS": "-L/usr/lib",
"ODBC_LIBS": "-lodbc",
"Directive": [
"Local Value",
"Master Value"
],
"odbc.allow_persistent": [
"On",
"On"
],
"odbc.check_persistent": [
"On",
"On"
],
"odbc.default_cursortype": [
"Static cursor",
"Static cursor"
],
"odbc.default_db": [
"no value",
"no value"
],
"odbc.default_pw": [
"no value",
"no value"
],
"odbc.default_user": [
"no value",
"no value"
],
"odbc.defaultbinmode": [
"return as is",
"return as is"
],
"odbc.defaultlrl": [
"return up to 4096 bytes",
"return up to 4096 bytes"
],
"odbc.max_links": [
"Unlimited",
"Unlimited"
],
"odbc.max_persistent": [
"Unlimited",
"Unlimited"
]
},
"openssl": {
"OpenSSL support": "enabled",
"OpenSSL Library Version": "OpenSSL 1.1.1j 16 Feb 2021",
"OpenSSL Header Version": "OpenSSL 1.1.0l 10 Sep 2019",
"Openssl default config": "/usr/lib/ssl/openssl.cnf",
"Directive": [
"Local Value",
"Master Value"
],
"openssl.cafile": [
"no value",
"no value"
],
"openssl.capath": [
"no value",
"no value"
]
},
"pcre": {
"PCRE (Perl Compatible Regular Expressions) Support": "enabled",
"PCRE Library Version": "10.39 2021-10-29",
"PCRE Unicode Version": "14.0.0",
"PCRE JIT Support": "enabled",
"PCRE JIT Target": "x86 64bit (little endian + unaligned)",
"Directive": [
"Local Value",
"Master Value"
],
"pcre.backtrack_limit": [
"1000000",
"1000000"
],
"pcre.jit": [
"1",
"1"
],
"pcre.recursion_limit": [
"100000",
"100000"
]
},
"PDO": {
"PDO support": "enabled",
"PDO drivers": "dblib, firebird, mysql, odbc, pgsql, sqlite"
},
"pdo_dblib": {
"PDO Driver for FreeTDS/Sybase DB-lib": "enabled",
"Flavour": "freetds"
},
"PDO_Firebird": {
"PDO Driver for Firebird": "enabled",
"Client Library Version": "LI-V6.3.1.32609 Firebird 3.0"
},
"pdo_mysql": {
"PDO Driver for MySQL": "enabled",
"Client API version": "mysqlnd 7.4.26",
"Directive": [
"Local Value",
"Master Value"
],
"pdo_mysql.default_socket": [
"xxxxxx",
"xxxxxx"
]
},
"PDO_ODBC": {
"PDO Driver for ODBC (unixODBC)": "enabled",
"ODBC Connection Pooling": "Enabled, strict matching"
},
"pdo_pgsql": {
"PDO Driver for PostgreSQL": "enabled",
"PostgreSQL(libpq) Version": "9.6.24"
},
"pdo_sqlite": {
"PDO Driver for SQLite 3.x": "enabled",
"SQLite Library": "3.16.2"
},
"pgsql": {
"PostgreSQL Support": "enabled",
"PostgreSQL(libpq) Version": "9.6.24",
"PostgreSQL(libpq)": "PostgreSQL 9.6.24 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit",
"Multibyte character support": "enabled",
"SSL support": "enabled",
"Active Persistent Links": "0",
"Active Links": "0",
"Directive": [
"Local Value",
"Master Value"
],
"pgsql.allow_persistent": [
"On",
"On"
],
"pgsql.auto_reset_persistent": [
"Off",
"Off"
],
"pgsql.ignore_notice": [
"Off",
"Off"
],
"pgsql.log_notice": [
"Off",
"Off"
],
"pgsql.max_links": [
"Unlimited",
"Unlimited"
],
"pgsql.max_persistent": [
"Unlimited",
"Unlimited"
]
},
"Phar": {
"Phar: PHP Archive support": "enabled",
"Phar API version": "1.1.1",
"Phar-based phar archives": "enabled",
"Tar-based phar archives": "enabled",
"ZIP-based phar archives": "enabled",
"gzip compression": "enabled",
"bzip2 compression": "enabled",
"Native OpenSSL support": "enabled",
"Directive": [
"Local Value",
"Master Value"
],
"phar.cache_list": [
"no value",
"no value"
],
"phar.readonly": [
"On",
"On"
],
"phar.require_hash": [
"On",
"On"
]
},
"posix": {
"POSIX support": "enabled"
},
"readline": {
"Readline Support": "enabled",
"Readline library": "EditLine wrapper",
"Directive": [
"Local Value",
"Master Value"
],
"cli.pager": [
"no value",
"no value"
],
"cli.prompt": [
"\b \> ",
"\b \> "
]
},
"Reflection": {
"Reflection": "enabled"
},
"session": {
"Session Support": "enabled",
"Registered save handlers": "files user memcached",
"Registered serializer handlers": "php_serialize php php_binary igbinary msgpack",
"Directive": [
"Local Value",
"Master Value"
],
"session.auto_start": [
"Off",
"Off"
],
"session.cache_expire": [
"180",
"180"
],
"session.cache_limiter": [
"none",
"nocache"
],
"session.cookie_domain": [
"no value",
"no value"
],
"session.cookie_httponly": [
"1",
"no value"
],
"session.cookie_lifetime": [
"0",
"0"
],
"session.cookie_path": [
"/",
"/"
],
"session.cookie_samesite": [
"no value",
"no value"
],
"session.cookie_secure": [
"0",
"0"
],
"session.gc_divisor": [
"1000",
"1000"
],
"session.gc_maxlifetime": [
"14400",
"1440"
],
"session.gc_probability": [
"0",
"0"
],
"session.lazy_write": [
"On",
"On"
],
"session.name": [
"xxxxxx",
"xxxxxx"
],
"session.referer_check": [
"no value",
"no value"
],
"session.save_handler": [
"user",
"files"
],
"session.save_path": [
"xxxxxx",
"xxxxxx"
],
"session.serialize_handler": [
"php",
"php"
],
"session.sid_bits_per_character": [
"5",
"5"
],
"session.sid_length": [
"26",
"26"
],
"session.upload_progress.cleanup": [
"On",
"On"
],
"session.upload_progress.enabled": [
"On",
"On"
],
"session.upload_progress.freq": [
"1%",
"1%"
],
"session.upload_progress.min_freq": [
"1",
"1"
],
"session.upload_progress.name": [
"PHP_SESSION_UPLOAD_PROGRESS",
"PHP_SESSION_UPLOAD_PROGRESS"
],
"session.upload_progress.prefix": [
"upload_progress_",
"upload_progress_"
],
"session.use_cookies": [
"1",
"1"
],
"session.use_only_cookies": [
"1",
"1"
],
"session.use_strict_mode": [
"0",
"0"
],
"session.use_trans_sid": [
"0",
"0"
]
},
"shmop": {
"shmop support": "enabled"
},
"SimpleXML": {
"SimpleXML support": "enabled",
"Schema support": "enabled"
},
"soap": {
"Soap Client": "enabled",
"Soap Server": "enabled",
"Directive": [
"Local Value",
"Master Value"
],
"soap.wsdl_cache": [
"1",
"1"
],
"soap.wsdl_cache_dir": [
"/tmp",
"/tmp"
],
"soap.wsdl_cache_enabled": [
"1",
"1"
],
"soap.wsdl_cache_limit": [
"5",
"5"
],
"soap.wsdl_cache_ttl": [
"86400",
"86400"
]
},
"sockets": {
"Sockets Support": "enabled"
},
"sodium": {
"sodium support": "enabled",
"libsodium headers version": "1.0.18",
"libsodium library version": "1.0.18"
},
"SPL": {
"SPL support": "enabled",
"Interfaces": "OuterIterator, RecursiveIterator, SeekableIterator, SplObserver, SplSubject",
"Classes": "AppendIterator, ArrayIterator, ArrayObject, BadFunctionCallException, BadMethodCallException, CachingIterator, CallbackFilterIterator, DirectoryIterator, DomainException, EmptyIterator, FilesystemIterator, FilterIterator, GlobIterator, InfiniteIterator, InvalidArgumentException, IteratorIterator, LengthException, LimitIterator, LogicException, MultipleIterator, NoRewindIterator, OutOfBoundsException, OutOfRangeException, OverflowException, ParentIterator, RangeException, RecursiveArrayIterator, RecursiveCachingIterator, RecursiveCallbackFilterIterator, RecursiveDirectoryIterator, RecursiveFilterIterator, RecursiveIteratorIterator, RecursiveRegexIterator, RecursiveTreeIterator, RegexIterator, RuntimeException, SplDoublyLinkedList, SplFileInfo, SplFileObject, SplFixedArray, SplHeap, SplMinHeap, SplMaxHeap, SplObjectStorage, SplPriorityQueue, SplQueue, SplStack, SplTempFileObject, UnderflowException, UnexpectedValueException"
},
"sqlite3": {
"SQLite3 support": "enabled",
"SQLite Library": "3.16.2",
"Directive": [
"Local Value",
"Master Value"
],
"sqlite3.extension_dir": [
"no value",
"no value"
]
},
"standard": {
"Dynamic Library Support": "enabled",
"Path to sendmail": "xxxxxx",
"Directive": [
"Local Value",
"Master Value"
],
"assert.active": [
"1",
"1"
],
"assert.bail": [
"0",
"0"
],
"assert.callback": [
"no value",
"no value"
],
"assert.exception": [
"0",
"0"
],
"assert.quiet_eval": [
"0",
"0"
],
"assert.warning": [
"1",
"1"
],
"auto_detect_line_endings": [
"0",
"0"
],
"default_socket_timeout": [
"60",
"60"
],
"from": [
"no value",
"no value"
],
"session.trans_sid_hosts": [
"no value",
"no value"
],
"session.trans_sid_tags": [
"a=href, area=href, frame=src, form=",
"a=href, area=href, frame=src, form="
],
"unserialize_max_depth": [
"4096",
"4096"
],
"url_rewriter.hosts": [
"no value",
"no value"
],
"url_rewriter.tags": [
"form=",
"form="
],
"user_agent": [
"no value",
"no value"
]
},
"sysvmsg": {
"sysvmsg support": "enabled"
},
"sysvsem": {
"sysvsem support": "enabled"
},
"sysvshm": {
"sysvshm support": "enabled"
},
"tidy": {
"Tidy support": "enabled",
"libTidy Version": "5.6.0",
"libTidy Release": "2017/11/25",
"Directive": [
"Local Value",
"Master Value"
],
"tidy.clean_output": [
"no value",
"no value"
],
"tidy.default_config": [
"no value",
"no value"
]
},
"tokenizer": {
"Tokenizer Support": "enabled"
},
"xml": {
"XML Support": "active",
"XML Namespace Support": "active",
"libxml2 Version": "2.9.12"
},
"xmlreader": {
"XMLReader": "enabled"
},
"xmlrpc": {
"core library version": "xmlrpc-epi v. 0.54",
"author": "Dan Libby",
"homepage": "http://xmlrpc-epi.sourceforge.net",
"open sourced by": "Epinions.com"
},
"xmlwriter": {
"XMLWriter": "enabled"
},
"xsl": {
"XSL": "enabled",
"libxslt Version": "1.1.29",
"libxslt compiled against libxml Version": "2.9.4",
"EXSLT": "enabled",
"libexslt Version": "1.1.29"
},
"Zend OPcache": {
"Opcode Caching": "Up and Running",
"Optimization": "Enabled",
"SHM Cache": "Enabled",
"File Cache": "Disabled",
"Startup": "OK",
"Shared memory model": "mmap",
"Cache hits": "10312",
"Cache misses": "1465",
"Used memory": "31345064",
"Free memory": "102872664",
"Wasted memory": "0",
"Interned Strings Used memory": "3318472",
"Interned Strings Free memory": "2972536",
"Cached scripts": "1460",
"Cached keys": "2219",
"Max keys": "16229",
"OOM restarts": "0",
"Hash keys restarts": "0",
"Manual restarts": "10",
"Directive": [
"Local Value",
"Master Value"
],
"opcache.blacklist_filename": [
"no value",
"no value"
],
"opcache.consistency_checks": [
"0",
"0"
],
"opcache.dups_fix": [
"Off",
"Off"
],
"opcache.enable": [
"On",
"On"
],
"opcache.enable_cli": [
"Off",
"Off"
],
"opcache.enable_file_override": [
"Off",
"Off"
],
"opcache.error_log": [
"no value",
"no value"
],
"opcache.file_cache": [
"no value",
"no value"
],
"opcache.file_cache_consistency_checks": [
"On",
"On"
],
"opcache.file_cache_only": [
"Off",
"Off"
],
"opcache.file_update_protection": [
"2",
"2"
],
"opcache.force_restart_timeout": [
"180",
"180"
],
"opcache.huge_code_pages": [
"Off",
"Off"
],
"opcache.interned_strings_buffer": [
"8",
"8"
],
"opcache.lockfile_path": [
"/tmp",
"/tmp"
],
"opcache.log_verbosity_level": [
"1",
"1"
],
"opcache.max_accelerated_files": [
"10000",
"10000"
],
"opcache.max_file_size": [
"0",
"0"
],
"opcache.max_wasted_percentage": [
"5",
"5"
],
"opcache.memory_consumption": [
"128",
"128"
],
"opcache.opt_debug_level": [
"0",
"0"
],
"opcache.optimization_level": [
"0x7FFEBFFF",
"0x7FFEBFFF"
],
"opcache.preferred_memory_model": [
"no value",
"no value"
],
"opcache.preload": [
"no value",
"no value"
],
"opcache.preload_user": [
"no value",
"no value"
],
"opcache.protect_memory": [
"Off",
"Off"
],
"opcache.restrict_api": [
"no value",
"no value"
],
"opcache.revalidate_freq": [
"2",
"2"
],
"opcache.revalidate_path": [
"Off",
"Off"
],
"opcache.save_comments": [
"On",
"On"
],
"opcache.use_cwd": [
"On",
"On"
],
"opcache.validate_permission": [
"Off",
"Off"
],
"opcache.validate_root": [
"Off",
"Off"
],
"opcache.validate_timestamps": [
"On",
"On"
]
},
"zip": {
"Zip": "enabled",
"Zip version": "1.15.6",
"Libzip headers version": "1.7.3",
"Libzip library version": "1.7.3"
},
"zlib": {
"ZLib Support": "enabled",
"Stream Wrapper": "compress.zlib://",
"Stream Filter": "zlib.inflate, zlib.deflate",
"Compiled Version": "1.2.8",
"Linked Version": "1.2.8",
"Directive": [
"Local Value",
"Master Value"
],
"zlib.output_compression": [
"Off",
"Off"
],
"zlib.output_compression_level": [
"-1",
"-1"
],
"zlib.output_handler": [
"no value",
"no value"
]
}
}
}

Additional comments

[Fedik]

Although valid mysql syntax, the checker declares the database is not up to date

That is known limitation of Joomla database checker. It does not support multi-definition, have to use 1 ALTER per column.
The SQL that will work is:

ALTER TABLE #__pixtracker_tags CHANGE COLUMN state state TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE #__pixtracker_tags CHANGE COLUMN script script TEXT NULL DEFAULT NULL;

[richard67]

See also #36272 . I suggest to close this issue here as expected behaviour.

[Fedik]

ALTER TABLE #__pixtracker_tags CHANGE COLUMN script script TEXT NULL DEFAULT NULL;
While this doesn't generate the error message, the checker still declares the database out of date for the same reasons as shown above.

Just click "update structure" button and it will gone.
I have similar in my component and it works fine.

[billtomczak]

@Fedik -- yes, that was my understanding. My problem is, that the only syntax the db checker will accept is invalid sql syntax.

ALTER TABLE #__pixtracker_tags CHANGE COLUMN state state TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE #__pixtracker_tags CHANGE COLUMN script script TEXT NULL DEFAULT NULL;

Still causes the checker to call the table out of date. the only syntax I've found to work is this:

ALTER TABLE #__pixtracker_tags CHANGE COLUMN state TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE #__pixtracker_tags CHANGE COLUMN script TEXT NULL DEFAULT NULL;

Which is not valid syntax. I detailed all this in my original ticket.

[Fedik]

hm hm, hard to say, I have very similar:

ALTER TABLE #__gallery CHANGE state state SMALLINT DEFAULT NULL;

And it works fine

[alikon]

MariaDB ?

[Fedik]

ah wait, I see, try this:

ALTER TABLE #__pixtracker_tags CHANGE  state state TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE #__pixtracker_tags CHANGE script script TEXT NULL DEFAULT NULL;

without COLUMN

[Fedik]

MySQL example does not have it https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column
I see that COLUMN required for RENAME COLUMN not for CHANGE, it seems an optional thing, and looks like Joomla database checker does not support it.

[richard67]

MySQL example does not have it https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-redefine-column I see that COLUMN required for RENAME COLUMN not for CHANGE, it seems an optional thing, and looks like Joomla database checker does not support it.

That's right.

[billtomczak]

@richard67 @Fedik Thanks so much guys. That works. It would be nice to see the checker code revamped to be less finicky. This is the second time we've been hit with a problem like this. But I understand this would be a low priority.

[richard67]

We should not revamp the checker code, we should abandon it. It does more harm than it fixes.

[billtomczak]

We should not revamp the checker code, we should abandon it. It does more harm than it fixes.

lol - No argument from me