Importing Users from 5.6 to 5.8
Permalink 1 user found helpful
Hi Folks,
I'm currently upgrading a site from 5.6 to 5.8 manually. Everything has been fine but the original site has around 500 members (users) that I need to import. I have tried to copy the users table from 5.6 to 5.8 and this works to an extent. The users' logins work but whenever I want to edit a user in 5.8, I get the following error:
Whoops\Exception\ErrorException thrown with message "hex2bin(): Hexadecimal input string must have an even length"
Stacktrace:
#27 Whoops\Exception\ErrorException in /home/username/public_html/dev/concrete/src/Utility/IPAddress.php:92
#26 hex2bin in /home/username/public_html/dev/concrete/src/Utility/IPAddress.php:92
#25 Concrete\Core\Utility\IPAddress:hex2bin in /home/username/public_html/dev/concrete/src/Utility/IPAddress.php:66
#24 Concrete\Core\Utility\IPAddress:getIp in /home/username/public_html/dev/concrete/src/User/UserInfo.php:812
#23 Concrete\Core\User\UserInfo:getLastIPAddress in /home/username/public_html/dev/concrete/single_pages/dashboard/users/search.php:99
#22 include in /home/username/public_html/dev/concrete/src/View/View.php:267
#21 Concrete\Core\View\View:renderInnerContents in /home/username/public_html/dev/concrete/src/View/View.php:245
#20 Concrete\Core\View\View:renderViewContents in /home/username/public_html/dev/concrete/src/View/AbstractView.php:164
#19 Concrete\Core\View\AbstractView:render in /home/username/public_html/dev/concrete/src/Http/ResponseFactory.php:148
#18 Concrete\Core\Http\ResponseFactory:view in /home/username/public_html/dev/concrete/src/Http/ResponseFactory.php:215
#17 Concrete\Core\Http\ResponseFactory:controller in /home/username/public_html/dev/concrete/src/Http/ResponseFactory.php:362
#16 Concrete\Core\Http\ResponseFactory:collection in /home/username/public_html/dev/concrete/src/Http/DefaultDispatcher.php:130
#15 Concrete\Core\Http\DefaultDispatcher:handleDispatch in /home/username/public_html/dev/concrete/src/Http/DefaultDispatcher.php:58
#14 Concrete\Core\Http\DefaultDispatcher:dispatch in /home/username/public_html/dev/concrete/src/Http/Middleware/DispatcherDelegate.php:39
#13 Concrete\Core\Http\Middleware\DispatcherDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/ThumbnailMiddleware.php:76
#12 Concrete\Core\Http\Middleware\ThumbnailMiddleware:process in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareDelegate.php:50
#11 Concrete\Core\Http\Middleware\MiddlewareDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/FrameOptionsMiddleware.php:39
#10 Concrete\Core\Http\Middleware\FrameOptionsMiddleware:process in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareDelegate.php:50
#9 Concrete\Core\Http\Middleware\MiddlewareDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/CookieMiddleware.php:35
#8 Concrete\Core\Http\Middleware\CookieMiddleware:process in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareDelegate.php:50
#7 Concrete\Core\Http\Middleware\MiddlewareDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/ApplicationMiddleware.php:29
#6 Concrete\Core\Http\Middleware\ApplicationMiddleware:process in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareDelegate.php:50
#5 Concrete\Core\Http\Middleware\MiddlewareDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareStack.php:86
#4 Concrete\Core\Http\Middleware\MiddlewareStack:process in /home/username/public_html/dev/concrete/src/Http/DefaultServer.php:85
#3 Concrete\Core\Http\DefaultServer:handleRequest in /home/username/public_html/dev/concrete/src/Foundation/Runtime/Run/DefaultRunner.php:128
#2 Concrete\Core\Foundation\Runtime\Run\DefaultRunner:run in /home/username/public_html/dev/concrete/src/Foundation/Runtime/DefaultRuntime.php:102
#1 Concrete\Core\Foundation\Runtime\DefaultRuntime:run in /home/username/public_html/dev/concrete/dispatcher.php:45
#0 require in /home/username/public_html/dev/index.php:3
So it looks like the database tables are not identical but I don't know enough to be able to fix this. Looks as if it doesn't like the format of the recorded IP address. I don't want to have to manually add 504 users if I can help it and I really want to keep the original username/password combinations if possible.
Has anyone else been through this process successfully?
TIA
Cheers,
Duncan
I'm currently upgrading a site from 5.6 to 5.8 manually. Everything has been fine but the original site has around 500 members (users) that I need to import. I have tried to copy the users table from 5.6 to 5.8 and this works to an extent. The users' logins work but whenever I want to edit a user in 5.8, I get the following error:
Whoops\Exception\ErrorException thrown with message "hex2bin(): Hexadecimal input string must have an even length"
Stacktrace:
#27 Whoops\Exception\ErrorException in /home/username/public_html/dev/concrete/src/Utility/IPAddress.php:92
#26 hex2bin in /home/username/public_html/dev/concrete/src/Utility/IPAddress.php:92
#25 Concrete\Core\Utility\IPAddress:hex2bin in /home/username/public_html/dev/concrete/src/Utility/IPAddress.php:66
#24 Concrete\Core\Utility\IPAddress:getIp in /home/username/public_html/dev/concrete/src/User/UserInfo.php:812
#23 Concrete\Core\User\UserInfo:getLastIPAddress in /home/username/public_html/dev/concrete/single_pages/dashboard/users/search.php:99
#22 include in /home/username/public_html/dev/concrete/src/View/View.php:267
#21 Concrete\Core\View\View:renderInnerContents in /home/username/public_html/dev/concrete/src/View/View.php:245
#20 Concrete\Core\View\View:renderViewContents in /home/username/public_html/dev/concrete/src/View/AbstractView.php:164
#19 Concrete\Core\View\AbstractView:render in /home/username/public_html/dev/concrete/src/Http/ResponseFactory.php:148
#18 Concrete\Core\Http\ResponseFactory:view in /home/username/public_html/dev/concrete/src/Http/ResponseFactory.php:215
#17 Concrete\Core\Http\ResponseFactory:controller in /home/username/public_html/dev/concrete/src/Http/ResponseFactory.php:362
#16 Concrete\Core\Http\ResponseFactory:collection in /home/username/public_html/dev/concrete/src/Http/DefaultDispatcher.php:130
#15 Concrete\Core\Http\DefaultDispatcher:handleDispatch in /home/username/public_html/dev/concrete/src/Http/DefaultDispatcher.php:58
#14 Concrete\Core\Http\DefaultDispatcher:dispatch in /home/username/public_html/dev/concrete/src/Http/Middleware/DispatcherDelegate.php:39
#13 Concrete\Core\Http\Middleware\DispatcherDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/ThumbnailMiddleware.php:76
#12 Concrete\Core\Http\Middleware\ThumbnailMiddleware:process in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareDelegate.php:50
#11 Concrete\Core\Http\Middleware\MiddlewareDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/FrameOptionsMiddleware.php:39
#10 Concrete\Core\Http\Middleware\FrameOptionsMiddleware:process in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareDelegate.php:50
#9 Concrete\Core\Http\Middleware\MiddlewareDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/CookieMiddleware.php:35
#8 Concrete\Core\Http\Middleware\CookieMiddleware:process in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareDelegate.php:50
#7 Concrete\Core\Http\Middleware\MiddlewareDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/ApplicationMiddleware.php:29
#6 Concrete\Core\Http\Middleware\ApplicationMiddleware:process in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareDelegate.php:50
#5 Concrete\Core\Http\Middleware\MiddlewareDelegate:next in /home/username/public_html/dev/concrete/src/Http/Middleware/MiddlewareStack.php:86
#4 Concrete\Core\Http\Middleware\MiddlewareStack:process in /home/username/public_html/dev/concrete/src/Http/DefaultServer.php:85
#3 Concrete\Core\Http\DefaultServer:handleRequest in /home/username/public_html/dev/concrete/src/Foundation/Runtime/Run/DefaultRunner.php:128
#2 Concrete\Core\Foundation\Runtime\Run\DefaultRunner:run in /home/username/public_html/dev/concrete/src/Foundation/Runtime/DefaultRuntime.php:102
#1 Concrete\Core\Foundation\Runtime\DefaultRuntime:run in /home/username/public_html/dev/concrete/dispatcher.php:45
#0 require in /home/username/public_html/dev/index.php:3
So it looks like the database tables are not identical but I don't know enough to be able to fix this. Looks as if it doesn't like the format of the recorded IP address. I don't want to have to manually add 504 users if I can help it and I really want to keep the original username/password combinations if possible.
Has anyone else been through this process successfully?
TIA
Cheers,
Duncan
OK, an update in case anyone else is in the same boat!
The way that I did this was to rename the Users table in the c5.8 database to Users_New and then copying the Users table from the c5.6 database to the c5.8 database. I changed the collation of the Users table from utf8 to utf8md4 with this query:
ALTER TABLE Users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Not sure if this was necessary or not but it worked and I had all users in the dashboard and I could create a new user. All looked good at this point until I tried to edit one of the existing users from c5.6 and this started to throw up problems!
Apparently the IP addresses stored for each user record are stored in a different format in c5.8 and so I had to reset all of these fields in Users. To do this I used:
UPDATE Users SET uLastOnline=0, uLastLogin=0, uLastIP='', uPreviousLogin=0
It turns out that by changing the name of the table from Users to User_New, the constraints were updated and so when updating an older account, concrete was trying to update the Users_New table. Something to do with Foreign Keys! I had to find the constraints and alter the related table from User_New to Users. Bingo! Everything worked!
Disclaimer:
1) I am not a database admin! I had some help from a friend and also from Google (for Foreign Keys and Constraints).
2) This worked for me but I cannot guarantee it will work in every case, I take no responsibility for the above suggestions (not instructions!).
3) I backed up my database at every stage in the process and gave the backup a name that made sense eg pre_collate or pre_constraints etc so that I could revert if I cocked up!
4) BACK UP, BACK UP & BACK UP again!!
I hope that this might help someone in the same situation. Please, if anyone knows a better way, or spots anything that I have done wrong, reply to this and let me know?
Cheers,
Duncan
The way that I did this was to rename the Users table in the c5.8 database to Users_New and then copying the Users table from the c5.6 database to the c5.8 database. I changed the collation of the Users table from utf8 to utf8md4 with this query:
ALTER TABLE Users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Not sure if this was necessary or not but it worked and I had all users in the dashboard and I could create a new user. All looked good at this point until I tried to edit one of the existing users from c5.6 and this started to throw up problems!
Apparently the IP addresses stored for each user record are stored in a different format in c5.8 and so I had to reset all of these fields in Users. To do this I used:
UPDATE Users SET uLastOnline=0, uLastLogin=0, uLastIP='', uPreviousLogin=0
It turns out that by changing the name of the table from Users to User_New, the constraints were updated and so when updating an older account, concrete was trying to update the Users_New table. Something to do with Foreign Keys! I had to find the constraints and alter the related table from User_New to Users. Bingo! Everything worked!
Disclaimer:
1) I am not a database admin! I had some help from a friend and also from Google (for Foreign Keys and Constraints).
2) This worked for me but I cannot guarantee it will work in every case, I take no responsibility for the above suggestions (not instructions!).
3) I backed up my database at every stage in the process and gave the backup a name that made sense eg pre_collate or pre_constraints etc so that I could revert if I cocked up!
4) BACK UP, BACK UP & BACK UP again!!
I hope that this might help someone in the same situation. Please, if anyone knows a better way, or spots anything that I have done wrong, reply to this and let me know?
Cheers,
Duncan
I notice that the old table collation is utf8_general_ci whereas the new table (created at install) is utf8mb4_unicode_ci
I'm no db admin so I'm not sure if this could be the reason. If it is, can I simply change the collation of the table?
Thanks again,
Duncan