Page 1 of 2 12 LastLast
Results 1 to 10 of 12
  1. #1
    Join Date
    Aug 2009
    Location
    North Idaho, USA
    Posts
    2,008
    Plugin Contributions
    1

    Default Mixed DB collations: should they all match

    I've noticed a sprinkling of various table types and collations
    Since the DB is an upgrade from 154, I can't say if the sprinkling occured before or after the DB update.

    I don't want to continue with the upgrade process if these mix-match are going to cause greater headache in the future.

    All but 1 table is MyISAM. sessions is InnoDB

    Here's the list that I have right now that are not utf8_general_ci

    admin_notifications: utf8mb4_general_ci
    box_news: latin1_swedish_ci
    box_news_content: latin_swedish_ci
    dbio_reports: latin1_swedish_ci
    dbio_reports_description: latin1_swedish_ci
    dbio_stats: latin1_swedish_ci
    ezpages_content: utf8mb4_general_ci
    ip2country: latin1_swedish_ci
    sessions: InnoDB: utf8mb4_general_ci
    square_payments: latin1_swedish_ci
    Rick
    RixStix (dot) com
    aka: ChainWeavers (dot) com

  2. #2
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    12,399
    Plugin Contributions
    87

    Default Re: Mixed DB collations: should they all match

    Yes, they should all match. Check out Convert db2utf8. See also this article on my website.

  3. #3
    Join Date
    Aug 2009
    Location
    North Idaho, USA
    Posts
    2,008
    Plugin Contributions
    1

    Default Re: Mixed DB collations: should they all match

    I went through that drill when I upgraded to 154. The convert db 2 utf8 was a nightmare until DrByte fixed it.

    Not grumpy, just very frustrated.

    Pretty sure square was a fresh install in 156
    Pretty sure dbio was a fresh install in 156
    Pretty sure news_box was a fresh install in 156
    Don't have a clue about the sessions table

    So now, that needs to be done again AFTER upgrading to 156 and AFTER every plugin install? I hate this get things looking good and then backup and start over drill.
    Rick
    RixStix (dot) com
    aka: ChainWeavers (dot) com

  4. #4
    Join Date
    Dec 2007
    Location
    Payson, AZ
    Posts
    1,076
    Plugin Contributions
    15

    Default Re: Mixed DB collations: should they all match

    Quote Originally Posted by RixStix View Post
    I went through that drill when I upgraded to 154. The convert db 2 utf8 was a nightmare until DrByte fixed it.

    Not grumpy, just very frustrated.

    Pretty sure square was a fresh install in 156
    Pretty sure dbio was a fresh install in 156
    Pretty sure news_box was a fresh install in 156
    Don't have a clue about the sessions table

    So now, that needs to be done again AFTER upgrading to 156 and AFTER every plugin install? I hate this get things looking good and then backup and start over drill.
    Convert db2utf8 is quick and easy, but did you set the default collation within your database too? if not then anything you install after well be back to the default setting.

    You can do it all from phpmyadmin by selecting the database you wish to change, clicking on options at the top, and changing the default collation at the bottom, check the two boxes and hit go... BUT, make a backup before doing this just in case... Default collation changes and so well all the table/rows.. future add-ons well be the same collation as the default for that database...
    Name:  collation.png
Views: 105
Size:  11.9 KB
    Dave
    Always forward thinking... Lost my mind!

  5. #5
    Join Date
    Aug 2009
    Location
    North Idaho, USA
    Posts
    2,008
    Plugin Contributions
    1

    Default Re: Mixed DB collations: should they all match

    THANK YOU Dave,

    That seems to have done the trick. The pic helped too.
    I hesitate to perform new-to-me changes for fear that I will muck something up and not realize it for a few days or weeks and then be past the point of no return. That happened with the original db2utf8 conversion routine before DrByte fixed it.
    Rick
    RixStix (dot) com
    aka: ChainWeavers (dot) com

  6. #6
    Join Date
    Jul 2012
    Posts
    16,718
    Plugin Contributions
    17

    Default Re: Mixed DB collations: should they all match

    Quote Originally Posted by RixStix View Post
    THANK YOU Dave,

    That seems to have done the trick. The pic helped too.
    I hesitate to perform new-to-me changes for fear that I will muck something up and not realize it for a few days or weeks and then be past the point of no return. That happened with the original db2utf8 conversion routine before DrByte fixed it.
    The current version of the db2utf conversion tool supposedly is also to change the default collation so that don't have to rerun "every time" or navigate phpmyadmin to do what was described above.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...

  7. #7
    Join Date
    Dec 2007
    Location
    Payson, AZ
    Posts
    1,076
    Plugin Contributions
    15

    Default Re: Mixed DB collations: should they all match

    Quote Originally Posted by mc12345678 View Post
    The current version of the db2utf conversion tool supposedly is also to change the default collation so that don't have to rerun "every time" or navigate phpmyadmin to do what was described above.
    Think I tried version 2 some time back... I see its up to 4 now.. Thanks for the update..

    For me, I like phpmyadmin or command line.. but I started with Oracle databases some time back...
    Dave
    Always forward thinking... Lost my mind!

  8. #8
    Join Date
    May 2006
    Location
    Gardiner, Maine
    Posts
    2,289
    Plugin Contributions
    22

    Default Re: Mixed DB collations: should they all match

    Was getting messages on 7.3 of Illegal mix of collations but all tables are utf8_general_ci and the file only wants to skip all the tables. So it doesn't fix the collation mix. The tables left as innodb at this time are admin only (maybe due to original admin mod?). I gather that I can manually fix this in the database but the original question and answer does not seem to be correct.
    The full-time Zen Cart Guru. WizTech4ZC.com

  9. #9
    Join Date
    May 2006
    Location
    Gardiner, Maine
    Posts
    2,289
    Plugin Contributions
    22

    Default Re: Mixed DB collations: should they all match

    and oof, I found and ran a query that supposedly makes that change using phpmyadmin
    SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;')##
    FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'
    but they still show as innodb.

    Any suggestions? Perhaps this is why the convert file didn't work?
    The full-time Zen Cart Guru. WizTech4ZC.com

  10. #10
    Join Date
    Aug 2009
    Location
    North Idaho, USA
    Posts
    2,008
    Plugin Contributions
    1

    Default Re: Mixed DB collations: should they all match

    Didn't change the innobd for me either but davewest pictures for phpmyadmin took care of that though it took a bit of me muddling around to get to the screen.
    Rick
    RixStix (dot) com
    aka: ChainWeavers (dot) com

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. v151 Database Collation - Can they be mixed?
    By Jeff_Mash in forum General Questions
    Replies: 1
    Last Post: 8 Jan 2013, 07:42 PM
  2. Communications Error Billing Address should match
    By rliddle in forum Built-in Shipping and Payment Modules
    Replies: 16
    Last Post: 29 Mar 2012, 03:48 AM
  3. Mixed collations in database after upgrade
    By arthurdent in forum Upgrading to 1.5.x
    Replies: 4
    Last Post: 9 Nov 2011, 11:04 PM
  4. Advice - Customer Billing / Delivery should NOT match (flower shop)
    By 4dw in forum Managing Customers and Orders
    Replies: 3
    Last Post: 19 Nov 2009, 10:10 AM
  5. Shipping and Billing DO match but Admin says they don't
    By countryneedle in forum Built-in Shipping and Payment Modules
    Replies: 11
    Last Post: 13 Feb 2009, 06:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR