Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2011
    Location
    Near Houston, TX
    Posts
    429
    Plugin Contributions
    0

    Default Database Restore Issue on XAMPP localhost

    I have a XAMPP localhost set up and was attempting to the database backup created by my site's Database Backup tool. I have never seen this error before. Here is the error I am getting:

    Code:
    MySQL said: Documentation
    
    #1273 - Unknown collation: 'utf8mb4_0900_ai_ci'
    ????

  2. #2
    Join Date
    Feb 2011
    Location
    Near Houston, TX
    Posts
    429
    Plugin Contributions
    0

    Default Re: Database Restore Issue on XAMPP localhost

    I did find one reference that recommends opening the file in a text editor and modifying the entries.

    https://tecadmin.net/resolved-unknow...b4_0900_ai_ci/

    I did open the SQL file in Notepad++ and that collation is used in numerous places throughout the file.
    Last edited by g2ktcf; 23 Oct 2021 at 11:38 PM.

  3. #3
    Join Date
    Jul 2012
    Posts
    16,097
    Plugin Contributions
    17

    Default Re: Database Restore Issue on XAMPP localhost

    May want to provide more information about your XAMP setup, while this site is predominantly for the care of the eCommerce software and database, there may be more assistance available to help.

    Right now it looks like either a table or portion of the database has been defined to a collation that just doesn't exist or to one that is not supported by the installed software... in either case it should be resolvable.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...

  4. #4
    Join Date
    Feb 2011
    Location
    Near Houston, TX
    Posts
    429
    Plugin Contributions
    0

    Default Re: Database Restore Issue on XAMPP localhost

    Quote Originally Posted by mc12345678 View Post
    May want to provide more information about your XAMP setup, while this site is predominantly for the care of the eCommerce software and database, there may be more assistance available to help.

    Right now it looks like either a table or portion of the database has been defined to a collation that just doesn't exist or to one that is not supported by the installed software... in either case it should be resolvable.
    phpAdmin 5.1

    Server: 127.0.0.1 via TCP/IP
    Server type: MariaDB
    Server connection: SSL is not being used Documentation
    Server version: 10.4.21-MariaDB - mariadb.org binary distribution
    Protocol version: 10
    User: [email protected]
    Server charset: UTF-8 Unicode (utf8mb4)

    XAMPP for Windows 7.4.25

    Apache/2.4.51 (Win64) OpenSSL/1.1.1l PHP/7.4.25
    Database client version: libmysql - mysqlnd 7.4.25
    PHP extension: mysqli Documentation curl Documentation mbstring Documentation
    PHP version: 7.4.25

  5. #5
    Join Date
    Feb 2011
    Location
    Near Houston, TX
    Posts
    429
    Plugin Contributions
    0

    Default Re: Database Restore Issue on XAMPP localhost

    Okay, its a new install, so I can always just redo the whole thing. I followed only half the info in the above link replacing all 120 instances of utf8mb4_0900_ai_ci with utf8mb4_general_ci.

    The import seem to go without a hitch. The data seems intact. Does anyone know the specifics between the two different collations?

    Thanks
    Chris

  6. #6
    Join Date
    Aug 2007
    Location
    Gijn, Asturias, Spain
    Posts
    2,304
    Plugin Contributions
    29

    Default Re: Database Restore Issue on XAMPP localhost

    utf8mb4_0900_ai_ci
    Never seen that before....probably because no it is not in a Xampp setup at the moment, so yes it probably would complain when trying to import a database that specifies it.

    No doubt Xampp will add this in the near future and you wont have a problem.
    Steve
    github.com/torvista: Spanish Language Pack, Image Checker, Structured Data, BackupMySQL Admin/Auto, Product Listing Sorter, Redsys payment module...

  7. #7
    Join Date
    Feb 2011
    Location
    Near Houston, TX
    Posts
    429
    Plugin Contributions
    0

    Default Re: Database Restore Issue on XAMPP localhost

    Quote Originally Posted by torvista View Post
    utf8mb4_0900_ai_ci
    Never seen that before....probably because no it is not in a Xampp setup at the moment, so yes it probably would complain when trying to import a database that specifies it.

    No doubt Xampp will add this in the near future and you wont have a problem.
    Just did some checking...that data is hosted on MySQL version 8. XAMPP is using MariaDB so this could be the issue. So far, I have not had any issues making the conversion above.

  8. #8
    Join Date
    Apr 2006
    Location
    West Salem, IL
    Posts
    2,660
    Plugin Contributions
    0

    Default Re: Database Restore Issue on XAMPP localhost

    Found this information

    What is the meaning of the MySQL collation utf8mb4_0900_ai_ci?

    uft8mb4 means that each character is stored as a maximum of 4 bytes in the UTF-8 encoding scheme.
    0900 refers to the Unicode Collation Algorithm version. (The Unicode Collation Algorithm is the method used to compare two Unicode strings that conforms to the requirements of the Unicode Standard).
    ai refers accent insensitivity. That is, there is no difference between e, , , and when sorting.
    ci refers to case insensitivity. This is, there is no difference between p and P when sorting.

    utf8mb4 has become the default character set, with utf8mb4_0900_ai_ci as the default collation in MySQL 8.0.1 and later. Previously, utf8mb4_general_ci was the default collation. Because the utf8mb4_0900_ai_ci collation is now the default, new tables have the ability to store characters outside the Basic Multilingual Plane by default. Emojis can now be stored by default. If accent sensitivity and case sensitivity are required, you may use utf8mb4_0900_as_cs instead.
    Mike
    GeekHost - Zen Cart Certified & PCI Compliant Hosting
    The Zen Cart Forum...Better than a monitor covered with post-it notes!

  9. #9
    Join Date
    Feb 2011
    Location
    Near Houston, TX
    Posts
    429
    Plugin Contributions
    0

    Default Re: Database Restore Issue on XAMPP localhost

    Quote Originally Posted by barco57 View Post
    Found this information

    What is the meaning of the MySQL collation utf8mb4_0900_ai_ci?

    uft8mb4 means that each character is stored as a maximum of 4 bytes in the UTF-8 encoding scheme.
    0900 refers to the Unicode Collation Algorithm version. (The Unicode Collation Algorithm is the method used to compare two Unicode strings that conforms to the requirements of the Unicode Standard).
    ai refers accent insensitivity. That is, there is no difference between e, , , and when sorting.
    ci refers to case insensitivity. This is, there is no difference between p and P when sorting.

    utf8mb4 has become the default character set, with utf8mb4_0900_ai_ci as the default collation in MySQL 8.0.1 and later. Previously, utf8mb4_general_ci was the default collation. Because the utf8mb4_0900_ai_ci collation is now the default, new tables have the ability to store characters outside the Basic Multilingual Plane by default. Emojis can now be stored by default. If accent sensitivity and case sensitivity are required, you may use utf8mb4_0900_as_cs instead.
    Perfect! And my new database is MySQL 8 so this makes total sense. I just did a search and replace to replace utf8mb4_0900_ai_ci with utfmb4_0900_general_ci based on the recommendation in the article above. While it seems to have "worked" I was worried that it would cause an issue somewhere down the line. I do not have any accented characters anyway.

    My mind is much more at ease now. Great Explanation! Thanks!

 

 

Similar Threads

  1. blank zc_install page at localhost xampp
    By LadyoftheCave in forum General Questions
    Replies: 4
    Last Post: 22 Dec 2020, 07:36 PM
  2. v154 Xampp Localhost Plugin Install
    By resqjoc in forum Installing on a Windows Server
    Replies: 0
    Last Post: 12 Dec 2015, 01:56 AM
  3. Can't restore database after moving from localhost
    By Annie_zaz in forum Installing on a Linux/Unix Server
    Replies: 3
    Last Post: 16 Sep 2013, 11:48 AM
  4. Installing on XAMPP localhost--help please !
    By yli1981 in forum Installing on a Windows Server
    Replies: 2
    Last Post: 17 Sep 2008, 06:18 PM
  5. Installing on XAMPP localhost
    By boba in forum Installing on a Windows Server
    Replies: 1
    Last Post: 2 Sep 2008, 06:04 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