Announcement

Collapse
No announcement yet.

MySQL, UTF-8 and SET NAMES

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    MySQL, UTF-8 and SET NAMES

    I'm glad to see that the upcoming PR8 explicitly defines a uniform character set in both the frontend and the admin - this was one of the major obstacles in getting full UTF-8 support for a store I'm currently upgrading from 4.x to 5.5. There is, however, another problem that wasn't mentioned in the changlelog, which is the MySQL connection and client encoding.

    The nice thing about MySQL is that it's easily able to work with different database, server and client encodings at the same time and convert between them on the fly. The bad thing about this functionality is that things go totally bonkers when any single part of the system misreports the encoding it intends to use, which includes depending on a possibly unknown default value. This might be the case with a MySQL connection made my Miva for certain server configurations. Now, if at this point anyone reading this thinks something along the lines of "well, just ask the admin to change it", I'm afraid that's not a proper solution, or any solution at all for that matter, if the store is using shared hosting - the store owner cannot expect the hosting provider to change a server default just for them when it might break things for other users of the database server.

    Instead, the correct solution provided and recommended by MySQL is to issue the SET NAMES and SET CHARACTER SET statements [1] immediately after establishing the connection to the MySQL server. This is the only way to be sure that things will work properly regardless of the server configuraion.

    For now, I just downloaded the MMLSK, added two MvQUERY calls to DB_Open and DB_Open_PrivateKey in lib/dbapi_mysql.mv, recompiled it and replaced the release mvc file with the new one. It works and things are, hopefully, not going down in flames, but it's a horrible hack that will break as soon as I upgrade to a new version and the file gets overwritten by a Miva-provided one. And I'm still not sure if it's not going to combust spontaneously as soon as I look the other way.

    Instead, Merchant should issue those statements out of the box, with the encoding configurable in some way - another field in merchdb.dat, or whatever will be used for the charset header in admin? Or, if you're actually opting for hardcoded UTF-8 (not a bad choice in 2011, the US-based ASCII users won't see a difference anyway), just hardcode it there, too.

    Anyway, I'd be glad to see this on the way to a future update. I guess it's too late for such a change in PR8, but I'd be even more glad to be proven wrong here.

    [1] http://dev.mysql.com/doc/refman/5.0/...onnection.html

    Regards,
    Remigiusz "Enleth" Marcinkiewicz

    #2
    Re: MySQL, UTF-8 and SET NAMES

    It's definitely too late to do this for PR8, but we will do something along these lines in an early PR8 update.

    Comment


      #3
      Re: MySQL, UTF-8 and SET NAMES

      Great, thanks!

      Until then, if I happen to invent a "better" hack that doesn't involve replacing core files, I'll post it there for anyone who might find it useful.
      Remigiusz "Enleth" Marcinkiewicz

      Comment


        #4
        Re: MySQL, UTF-8 and SET NAMES

        You might be able to override the default collation on a table by table basis.

        Comment


          #5
          Re: MySQL, UTF-8 and SET NAMES

          I need to dig up this old thread and ask a related question or two.

          I am in the process of finally converting our store to the proper utf-8 charset and I am running in to the exact same issue, so hopefully either enleth or burch can help me out.

          I converted my database and all of my tables to utf8, but now I'm in this weird limbo. Miva Merchant displays the pages in utf-8 via meta tags and I also a default charset in my htaccess file. I can verify that the browser renders the page in utf-8. If I update my account's address with a utf-8 string ("Iñtërnâtiônàlizætiøn" is my new test data), Miva Merchant is able to update and display the address perfectly. Everything's painless so far.

          The issue I am running in to now is if I log in to the database and view the record, or if I use another program (PHP in my case) to display the address field, it comes across garbled. What I've narrowed it down to is what enleth was talking about. When I connect using PHP, I send basically the same SQL that enleth posted in his fix, which makes it a legitimate utf-8 connection.

          Both apps (PHP and Miva) have no trouble viewing the complex strings that they update, but the two systems can't seem to talk the same language. I think that Enleth's hack would fix my issue, but I really don't want to modify a default library on a production machine.

          Now I am only on PR8-3 and Miva Engine 5.13, so I know I am few behind, but I don't remember seeing this in a release note (After this database migration, the very next thing on my list is upgrading all the way to PR8-10.)

          Burch- Do you know the current status on this problem? Has it been resolved, or is there a better fix out there now?

          Thanks

          Comment


            #6
            Re: MySQL, UTF-8 and SET NAMES

            Originally posted by Brandon MUS View Post
            Burch- Do you know the current status on this problem? Has it been resolved, or is there a better fix out there now?
            Unfortunately, I had completely forgotten about this thread and we haven't done anything to implement a fix yet. I will have someone file a bug on it so we don't forget again. I'm leaning towards a flag you can place in merchdb.dat that will be picked up by dbapi_mysql.mvc and cause it to execute the SET NAMES and SET CHARACTER SET statements.

            Comment


              #7
              Re: MySQL, UTF-8 and SET NAMES

              I think that's exactly the kind of fix I was looking for, burch. Makes sense to keep that with the other connection stuff.

              In the meantime, is the proposed fix above worth doing, or is that dangerous, in your eyes, to rely on?

              Comment


                #8
                Re: MySQL, UTF-8 and SET NAMES

                Originally posted by Brandon MUS View Post
                In the meantime, is the proposed fix above worth doing, or is that dangerous, in your eyes, to rely on?
                It's safe enough--we update dbapi_mysql.mvc fairly infrequently and hopefully the next update will include the fix for the issue.

                Comment


                  #9
                  Re: MySQL, UTF-8 and SET NAMES

                  Perfect. I went and compiled my dbapi_mysql.mv file (my first time using the MivaScript compiler -- which is crazy after all the years I've been using Miva) and it appears like that will be exactly what I need.

                  And Burch, just to help you out when you are ready to do this fix, this post on Stackoverflow explained the process really well: http://stackoverflow.com/questions/1...566908#1566908

                  In enleth's first post, he stated that he did SET NAMES and SET CHARACTER SET queries, but I don't think you want to do the SET CHARACTER SET query at all since it reverts the collation_connection setting that SET NAME just updated. I just did 'SET NAMES utf8' and it's is working great.

                  Comment


                    #10
                    Re: MySQL, UTF-8 and SET NAMES

                    For the record, when we updated to PR8-10, the dbapi_mysql.mvc file was updated/replaced, so I simply had to reupload my custom version. Since I had originally compiled from the PR8-10 LSK anyway, it wasn't an issue.

                    Curious, though, has this been addressed?

                    Comment


                      #11
                      Re: MySQL, UTF-8 and SET NAMES

                      Originally posted by Brandon MUS View Post
                      Curious, though, has this been addressed?
                      Not yet. The fix is scheduled for PR9. dbapi_mysql.mvc wasn't updated in PR8 Update 10 or 10.1 though, the last patch that updated it was PR8 Update 5.

                      Comment


                        #12
                        Re: MySQL, UTF-8 and SET NAMES

                        That's probably my mistake. I think we upgraded from PR8-4 straight to 10.

                        Comment

                        Working...
                        X