Cakedc/users: register problem with additonal_data column

Plugins
1

Installed latest version of everything. When I try to bin/cake users add_superuser I get: “Column additional_data of table users: The column type json can only be set if the column already exists; can be checked using hasColumn().” Same with clicking Register. The column exists. The db is 10.6.20-MariaDB-cll-lve. The JSON column type is available but appears as “longtext”, if that might have anything to do with it.

Any ideas? Thanks!

2

Make sure to run the migrations from the CakeDC Plugins before doing that.

bin/cake migrations migrate -p CakeDC/Users
3

The column exists: additional_data longtext utf8mb4_bin Yes
NULL

I followed the instructions. Installed the plugin with composer, but none of the extras. The seven migrations I did manually as SQL statements. Copied permissions and users files to config. Loaded in Application.php and uncommented Configure::write('Users.config', ['users']);

Going to any page gets sent to login with the message: “You are not authorized to access that location.”

Clicking Registr, Reset password, or Login gets: Column additional_data of table users: The column type json can only be set if the column already exists.`.

Current users table:

CREATE TABLE users (
id char(36) NOT NULL,
username varchar(255) NOT NULL,
email varchar(255) DEFAULT NULL,
password varchar(255) NOT NULL,
first_name varchar(50) DEFAULT NULL,
last_name varchar(50) DEFAULT NULL,
token varchar(255) DEFAULT NULL,
token_expires datetime DEFAULT NULL,
api_token varchar(255) DEFAULT NULL,
activation_date datetime DEFAULT NULL,
secret varchar(32) DEFAULT NULL,
secret_verified tinyint(1) DEFAULT NULL,
tos_date datetime DEFAULT NULL,
active tinyint(1) NOT NULL DEFAULT 0,
is_superuser tinyint(1) NOT NULL DEFAULT 0,
role varchar(255) DEFAULT ‘user’,
created datetime NOT NULL,
modified datetime NOT NULL,
additional_data text DEFAULT NULL,
last_login datetime DEFAULT NULL,
lockout_time datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4

Tried with a clean Composer installation of Cake and the authentication and authorization plugins, then cakedc/users. This time, I tried to run the migrations (instead of doing them manually as SQL statements as I had the first try) and got this error: SQLSTATE[HY000]: General error: 1005 Can't create table `users` (errno: 150 "Foreign key constraint is incorrectly formed")

5

Which version of CakePHP and CakeDC/Users are you running.

6

Cake 5.1.5
cakedc/users 14.3.4

7

I am unable to reproduce your problem with exactly the same versions:

kevin•CakePHP/Org/app(5.x⚡)» composer info cakephp/cakephp | grep versions                                                                                                                                                              [7:52:38]
versions : * 5.1.5

kevin•CakePHP/Org/app(5.x⚡)» composer info cakedc/users | grep versions                                                                                                                                                                 [7:52:50]
versions : * 14.3.4

kevin•CakePHP/Org/app(5.x⚡)» bin/cake migrations migrate -p CakeDC/Users                                                                                                                                                                [7:52:04]
using connection default
using paths /Users/kevin/Documents/CakePHP/Org/app/vendor/cakedc/users/config/Migrations
ordering by creation time

 == 20150513201111 Initial: migrating 
 == 20150513201111 Initial: migrated 0.0068s

 == 20161031101316 AddSecretToUsers: migrating 
 == 20161031101316 AddSecretToUsers: migrated 0.0019s

 == 20190208174112 AddAdditionalDataToUsers: migrating 
 == 20190208174112 AddAdditionalDataToUsers: migrated 0.0012s

 == 20210929202041 AddLastLoginToUsers: migrating 
 == 20210929202041 AddLastLoginToUsers: migrated 0.0013s

 == 20240328135459 CreateFailedPasswordAttempts: migrating 
 == 20240328135459 CreateFailedPasswordAttempts: migrated 0.0011s

 == 20240328215332 AddLockoutTimeToUsers: migrating 
 == 20240328215332 AddLockoutTimeToUsers: migrated 0.0011s

 == 20240801112143 ChangeAvatarColumnTypeInSocialAccounts: migrating 
 == 20240801112143 ChangeAvatarColumnTypeInSocialAccounts: migrated 0.0011s

All Done. Took 0.0266s

Dumping the current schema of the database to be used while baking a diff

Writing dump file `/Users/kevin/Documents/CakePHP/Org/app/vendor/cakedc/users/config/Migrations/schema-dump-default.lock`...
Dump file `/Users/kevin/Documents/CakePHP/Org/app/vendor/cakedc/users/config/Migrations/schema-dump-default.lock` was successfully written
8

Thanks! I realize that in situations like this, something on the user end is usually triggering the problem. This on a hosted business account, but I’ve installed WordPress and endless plugins with no issue, also, in the past Laravel and several plugins, also without problem, all in this environment. So that makes me extra curious and determined.

One thing: I’m using MariaDB 10.6 and uuid support only happened in 10.7. Can I replace the UUID data types with CHAR, as I did in the initial manual table creation, or something else?

Also, since I’m not using any of the plugin’s extra options, can I just elminate additional_data, even just to see if it will work then? What is that field for?

Cheers!

9

In case it has any usefulness beyond this particular situation, I managed to get it working by kinda hacking it into a form the db didn’t squawk about.

  1. changed the uuid data types to char(36) for compatibility with MariaDB 10.6.
  2. used SQL statements for all of the migrations
  3. commented out setColumnType('additional_data', 'json') in UsersTable (quicker than figuring out how to extend UsersTable)
  4. created a Cake command for password hashing so I could manually create a superuser

Now it seems to be working. No idea what any of the problems noted above were about, but they happened consistently over several attempts and two clean installations.