... it appears to stop during the paypal conversion
If there is no error displayed, It does not end the task at the ubbt_PAYPAL group. It completes working on that table, then it processes the next items in a group containing ubbt_POSTS (the next table group, alphabetically) and works on that for what may seem like a long time to you. You did say your tables were within the 1.5GB range. just wait until it completes. If there is an error during the process, a notice will be displayed. If there is no error, the tool is still working.
Originally Posted by Morgan
Am I supposed to be worried Databas was 1,25 GB is now 1,09GB
When it completes, you may notice that they are reduced in size. From latin1_swedish_ci to utf8mb4_general_ci, your previous character-set collation index column value had 255 characters.
In the case of utf8mb4, 1 character = 4 bytes, the limit is around 191 characters. So only 191 characters will be added to index, other characters will be truncated from the index. (255 vs 191 = 64 fewer)
When you change encoding from latin1_swedish_ci (at that time it was 1 character = 3 bytes) the indexed limit was around 255 characters. It means your column value, all 255 characters, will be moved to the index without truncating.
For utf8mb4_general_ci, the characters from the index decreased from 255 characters to 191 characters, so the total index size was also decreased.
Last edited by isaac; 03/13/20209:41 PM. Reason: added further clarification
Also to note during testing prior to this tool, converting the tables BY HAND for a client took me ~6 hours to process an 8gb posts table... It shouldn't *just stop* it should continue chugging along at that data.
Thanks Gizmo. Your numbers are an good example for this thread
--
To further expand on my previous post:
Visit Control Panel > Database Tools > Information. View the totals at the bottom of the listing. Data + Indexes = Database Size This is how the database size on this page and on the Control Panel's dashboard is calculated.
I was confused The page did not change for hours but The board was re open and all 66 tables had the new collation. I tried several times to make new transition.
Ok I understand now that I was supposed to have left it alone. I got the expectation that it should go Faster after reading isaacs previous example.
Shall I make a new transition with the tool and leave it until it runs through? Even if it takes let say 24 hours or more?
I did start the transition tool again 10 min after above post ruffly 4 hours has passed now and still same result as before. I will let it run on an on and keep that browser window open and computer is on all the time. I see so far in another window that Database table has decreased to 1.08 GB
What browser are you using. To be honest I had always been an advocate of firefox. But with recent updates it has become problematic at my bank, social security and insurance sites. In win 10 there is edge which people think is internet explorer but it is not. It works better on some sites for me. Since you are on apple safari. You may want to try a different browser like chrome or something else and see if it works better.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
Gozmo, Yesterday using Safari after a few seconds it shows several sections and after each section a green tic. Last section read
ALTER TABLE ubbt_PAYPAL_DATA MODIFY `SUBSCR_DATE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYMENT_DATE` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `SUBSCR_EFFECTIVE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `ITEM_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `BUSINESS` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `ITEM_NUMBER` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYMENT_STATUS` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `MC_GROSS` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYMENT_CURRENCY` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `TXN_ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `RECEIVER_EMAIL` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `RECEIVER_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `QUANTITY` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `NUM_CART_ITEMS` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `FIRST_NAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `LAST_NAME` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYMENT_TYPE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYMENT_GROSS` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYMENT_FEE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `SETTLE_AMOUNT` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `MEMO` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYER_EMAIL` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `TXN_TYPE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYER_STATUS` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `ADDRESS_STREET` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `ADDRESS_CITY` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `ADDRESS_STATE` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `ADDRESS_ZIP` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `ADDRESS_COUNTRY` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `ADDRESS_STATUS` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `TAX` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `OPTION_NAME1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `OPTION_SELECTION1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `OPTION_NAME2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `OPTION_SELECTION2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `INVOICE` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `CUSTOM_ID` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `NOTIFY_VERSION` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `VERIFY_SIGN` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYER_BUSINESS_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PAYER_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `MC_CURRENCY` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `MC_FEE` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `EXCHANGE_RATE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `SETTLE_CURRENCY` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PARENT_TXN_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PENDING_REASON` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `REASON_CODE` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `SUBSCR_ID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PERIOD1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PERIOD2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PERIOD3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `AMOUNT1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `AMOUNT2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `AMOUNT3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `MC_AMOUNT1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `MC_AMOUNT2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `MC_AMOUNT3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `RECURRING` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `REATTEMPT` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `RETRY_AT` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `RECUR_TIMES` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `USERNAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `PASSWORD` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
There was no green tic and nothing changed over time.
Today I used Firefox and exactly same result even after 13 hours now. In Firrefox I noticed for a few seconds that an icon was working and then it stopped. Nothing happened after that nothing that indicates that it is working.. nothing.
I have not dared to do anything caused it was mentioned that it is working in the background. I'm still waiting for the firefox page to change..
Oh forget to mention that when I look in Database tools the collation reads correct for all tables and if I enable MySQL Full Unicode Support (utf8mb4) I can only see three private forums out of 35 in total but I cannot open any so the box is disabled.
If it appears to keep getting stuck on one table.(IE paypal_data) Why not do a repair and or optimize on that table. Prior to running the tool. You can do that via phpmyadmin. At least it will usually provide some info on errors for the table. But make a backup first of the database.
Blue Man Group There is no such thing as stupid questions. Just stupid answers
What entries does your Admin Log (/admin/admin_log.php?returntab=1) show regarding the conversion? IIRC it should print when the action starts and ends.
OK I have made a new back up now, Should I test to optimize the paypal table first if so can I do it in CP database tools or should I do it in PHPMyAdmin?
The log entry states it has completed; you would only get this message if it finished. Further, if ALL tables report utf8mb4_general_ci its further proof it completed.
That is good news and it was not easy to understand as the enable MySQL Full Unicode Support (utf8mb4) does not work which I have said here several times. Are we not supposed to enable enable MySQL Full Unicode Support (utf8mb4) ? in Control Panel >> Paths & Database >> MySQL Full Unicode Support (utf8mb4)
If I enable it cripple the forum and show only 3 out of 35 forums which I cannot enter. Also Emoji's are not shown properly but they do when disabled
2020-03-13 15:31:36 Morgan ... DB_COLLATION 66 tables converted.
2020-03-13 16:15:56 Morgan ... DB_COLLATION 66 tables converted.
2020-03-13 16:23:49 Morgan ... DB_COLLATION 66 tables converted.
2020-03-13 17:27:20 Morgan ... DB_COLLATION 66 tables converted.
2020-03-13 18:26:24 Morgan ... DB_COLLATION 66 tables converted.
2020-03-13 18:32:46 Morgan ... DB_COLLATION 66 tables converted.
2020-03-13 21:07:47 Morgan ... DB_COLLATION 66 tables converted.
2020-03-13 21:23:55 Morgan ... DB_COLLATION 66 tables converted.
2020-03-14 07:08:30 Morgan ... DB_COLLATION 66 tables converted.
Those 9 lines tell me that the table conversion was run and completed 9 times.
1st time, latin1_swedish_ci to utf8mb4_general_ci 2nd time, utf8mb4_general_ci to utf8mb4_general_ci 3rd time, utf8mb4_general_ci to utf8mb4_general_ci 4th time, utf8mb4_general_ci to utf8mb4_general_ci ... 9th time, utf8mb4_general_ci to utf8mb4_general_ci
I dont see this as being a problem, since its just taking the current data collation and replacing it with itself.
What I do see as a problem is the following:
Originally Posted by Morgan
oh I have safari Apple
Safari failed to keep the communication open between your computer and the webserver. So while your webserver was still running and processing data updates to be sent to your screen, Safari already closed the communication and stopped displaying those updates to you. Safari timed out before the communication was complete.
Your admin log files say the task finished successfully, but Safari prevented the real-time display of that important bit of information from getting to you.
Maybe there is an updated Safari version which resolves that? If not, my advice would be to upgrade to a modern browser.
Also I still have my php max execution time large and it ran faster the second time but Here it is:
Not everyone has access to "max_execution_time", so it has been set right within the script, and only for that single script.
If you need to increase this time for any reason, you can easily do that by updating the values as desired.
Find that setting within forums/admin/convertcollation.php around line 34
Code
// This process may require more time, so we will extend it here
// 600 seconds = 10 minutes
ini_set('max_execution_time', 600);
set_time_limit(600);
Webhosts normally default this to either 30, 60, or 120 seconds for all scripts. We are setting this to 600 seconds just for this single script.
NOTE: Your webhost may not appreciate you taking more time than what they have set per each script, so do not expect to run this task more than once. In fact, this should only ever be run one time, and then you are through with the tool
Check your admin log to confirm that your task was completed, and you're not needlessly running the same process over and over.
It appeared to be an "extended character" bug. I got help from a magic wand and voila it works now. Thank you Isaac I am ever so grateful! 🙏
I don't know what that means without some specific information.
NOTE: This was absolutely unrelated to UBB.threads 7.7.x.
Morgan had an random extended character inserted to his USERS permissions. It has probably been there since a very early version of the software or from an upgrade made from a version of 15+ years ago.
What I did to solve his issue was:
With temporary access granted from Morgan: 1/ I went to his: Control Panel > Groups 2/ Clicked [Site] permission for USERS group 3/ Then I clicked the bottom button, "Update This Group's Permissions". The forum list started displaying correctly again. I did this for each one of his groups. I did not changed anything else.
What this did was just rebuild any missing or any using bad (such as spaces or extended characters) user group permission settings.
After yesterdays struggle and the help I received There was a question in our shoutbox today Why is some characters corrupt
example the word "Don't " in a topic has become "Don’t" so above is one question How can I fix the bad characters?
the second thing is after that just now I was browsing the Control Panel >>Content Rebuilder and managed to click accidental on "Rebuild Posts" link Instinctively I clicked to back page to avoid it to start rebuilding. However I can see in the logs that REBUILD_POSTS Action Started. It closed the board for maintenance so now I guess I will have to wait for a long time for it to process or? its 690000 posts. any comments on this unfortunate click? I cannot see that it runs a process since I closed the page...
As written, the transition tool only converts the table collation. The data was not converted. This means that all characters going forward will be stored in the new character set. All prior data containing extended characters are from your previous character collation, and will remain as such.
When you accidentally clicked on another link (unrelated to the transition tool), the "REBUILD_POSTS Action Started" you see in your Admin Log is stored with the time/date/details when that action is started.
The action goes through the entire task of doing what it was instructed to do.
When complete, it is then followed up with another log entry of when the action is finished.
Because you canceled that action in mid-task, there will not be an "Action Finished" log entry.
Ok and when complete will the forum open again or will it remain closed? it is still closed.
About the first question. The transition tool is one thing the other was enabling the MySQL Full Unicode Support (utf8mb4) I think that it might have changed the characters. it did for the emojis before the bug was removed. Is there a way to fix those now? Thanks
Note that closing a rebuild once it has begun will usually stop the transaction, it generally will not continue and since it'll never hit the end of the transaction it'll never auto open your forum automatically...
Hi Gizmo, I clicked on the Rebuild Posts and instantly hit to open previous page (Back page) It has now gone 2 hours and 15 min since then. The forum is still closed.
The posts table contains 690.000 posts, What is the recommendation for me to do now.
Wait some more time or hit Rebuild posts again or just open the forum? Or anything else.. You and Isaac seems to have different opinion about it so I'm all ears. Thanks
My advice, open the forum and stay out of that section, it's for advanced maintenance and not all tools are as friendly as allowing you to just back out.
You backed out of the rebuild, and THAT rebuild tool does steps incrementally, so it will not process further as additional redirects aren't instructing your browser to issue additional sets of instructions.
Short answer: Your forums are only closed because the rebuild closed them and can't complete the task and turn them back on as you closed the tool.
These are advanced maintenance tools that should be run to completion, and only when you know what you're doing... It is directly maintaining your database...
Hi Isaac, No it never refreshed cause I hit back page direct before it started to show anything. The logs in CP logged it but the web page never started to refresh or show anything. Forum is open now and I posted a reply so it works.
About corrupted characters after changing collation and enabling MySQL Full Unicode Support (utf8mb4) in Control Panel >> Paths & Database Example, Apostrophes becomes "’"
Is there a way to fix this? it seems all older posts then March 14th has "’" and new posts are correct. I know there are more types of characters that are corrupted also but not how many kinds yet.
About corrupted characters after changing collation and enabling MySQL Full Unicode Support (utf8mb4) in Control Panel >> Paths & Database Example, Apostrophes becomes "’"
Is there a way to fix this? it seems all older posts then March 14th has "’" and new posts are correct. I know there are more types of characters that are corrupted also but not how many kinds yet.
Two options are: A) restore your the backup you made and retain your older database collation.
Originally Posted by isaac
As written, the transition tool only converts the table collation. The data was not converted. This means that all characters going forward will be stored in the new character set. All prior data containing extended characters are from your previous character collation, and will remain as such.
or
B) find/replace and/or... continue forward with UTF8, knowing that all of your new posts from the day you choose to convert would be the first day of using modern database encoding standards with your character set.
Defaults in MySQL 8.0 Old Default: latin1 New Default: utf8mb4
Quote
The default value of the character_set_server system variable and command line option –character-set-server changes from latin1 to utf8mb4. This is the server’s default character set. UTF8MB4 is the dominating character encoding for the web, and this change will make life easier for the vast majority of MySQL users. The upgrade from 5.7 to 8.0 will not change any character set for any existing database objects. But unless you specify character_set_server back to your previous default or explicitly set the character set then a new schema/table/column will by default be in utf8mb4. We recommend users to move to utf8mb4 whenever possible.
Two options are: A) restore your the backup you made and retain your older database collation. or B) find/replace and/or... continue forward with UTF8, knowing that all of your new posts from the day you choose to convert would be the first day of using modern database encoding standards with your character set.
Defaults in MySQL 8.0 Old Default: latin1 New Default: utf8mb4
I didn't know that I would get corrupted characters when I converted to utf8mb4. I noticed now when opened the PHPMyAdmin that the General settings is "Server connection collation is utf8mb4_unicode_ci" and of course the britbike database is utf8mb4_general_ci. About the below options A, does not seems to be an option to keep the old collation as the future is here right.. B, find/replace and/or... don't understand what you mean however if I cannot change ’ into ' in the database. My simple not by the book quick fix for this after some thinking was Control Panel >> Features >> Active text.. adding following row
Quote
’|'
and it seems like a short term quick fix. Now it at least looks right. Is this the only thing I can do do you think?
Hmm I wonder what happen if I ask my host to change MYSQL Version 5.6.45 to 5.7 or greater
You could find the relation of the different artifacts and find/replace in the database OR activetext, or you could just live with it like countless other communities have done after converting.
Depending on your character set and the language used on your forums (Asian languages for example use quite a bit of non-Latin characters) there may be a lot more items than I'm including in the below list; but some common characters are:
Code
“ = left quote = “
†= right quote = ”
‘ = left single quote = ‘
’ = right single quote = ’
— = en dash = –
– = em dash = —
• = hyphen = -
… = ellipsis = …