SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Upgrade Conflicts

    The latest LAMP package I installed features the latest upgrades, which I thought was pretty cool. Everything seems to work fine on my computer.

    But my host hasn't upgraded to the latest MySQL yet, and that's creating all kinds of problems. First, I can't import SQL files because of collation errors. Fortunately, the phpMyAdmin upgrade allows me to import csv files online - but there's a catch...

    Empty cells in numerical fields are often filled witih 0, when they're supposed to NULL. For example, imagine if I had a table consisting of just two cells - one field and two rows. The first cell is filled with 77, and the second is empty.

    My online table will display 77 and 0, rather than 77 and nothing (null). Do you know how to resolve this problem?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you can update them from zero back to null, but of course that would be very problematic (i.e. wrong) if there are zero values that should be zero values mixed in with zero values that should be null

    you could also text edit the sql files, but this also is dubious, because you're faced with the same issue of identifying what type of zero it is

    the only chance you have is if the sql file actually uses the word NULL and these are getting stored as zeros -- in that case you need to adjust the CREATE TABLE statements to be sure to allow nulls
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you can update them from zero back to null, but of course that would be very problematic (i.e. wrong) if there are zero values that should be zero values mixed in with zero values that should be null

    you could also text edit the sql files, but this also is dubious, because you're faced with the same issue of identifying what type of zero it is.
    I can no longer import SQL files online anyway, because I get "collation" errors. So I can only use csv files to update my online tables.

    the only chance you have is if the sql file actually uses the word NULL and these are getting stored as zeros -- in that case you need to adjust the CREATE TABLE statements to be sure to allow nulls
    How do you adjust "CREATE TABLE statements"? Is this a one-time thing you adjust, or do you have to do it every time you create a table?

    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    collation errors on sql files? just remove the parts of the statements that refer to collation

    yes, it's a one-time thing -- you can only create a table once, after that you have to ALTER it (or DROP it to CREATE it again)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In that case, still look at the SQL file and find out what r937 suggested then, instead of updating the SQL file, update the CSV file. And are you sure the collation error is coming from the new Mysql problems and not the dump from the old mysql?
    Aaron Brazell
    Technosailor



  6. #6
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sketch
    In that case, still look at the SQL file and find out what r937 suggested then, instead of updating the SQL file, update the CSV file. And are you sure the collation error is coming from the new Mysql problems and not the dump from the old mysql?
    Hmmmm... This is all confusing for me. After my computer crashed, I reinstalled LAMP and wound up with upgrades that worked fine locally. I was surprised when I saw my first reference to collation, but I figured the software would make things work by default, and it did. But when I try to publish it online, I run into problems.

    I don't think I can edit my SQL files with Notepad, because I can't even interpret them; they look like this:

     M0Hs$
    ᣅUْvQ]S19('PR[W!3㙱8y$2

    Thanks.


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
  •