Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Red face Unanswered: [Image Included] SQL Import Error

    My SQL host is zymic.com. I owned a forum there with 200 members, but tonight I ran into an issue and deleted my forum files + my database. I have a backup of both, but when I import my SQL database backup, I get this error:

    screenshot of error
    http://s18.postimg.org/ladzxdwm1/Untitled.jpg

    How do I fix this?

    I posted this on sqlteam.com and they told me to come to dbforums because you guys are more into MySQL than they are.
    They also told me THIS may be the problem MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length My Digital Life
    But the thing is, I'll read it seven or eight times, but I don't know how to fix my problem.

    FAQ: My forum is 1 year old, many many members, server has 28,000 member, and if I lose this forum I am totally screwed

    Help me, Obi Wan Kenobi, you're my only hope!

  2. #2
    Join Date
    Jul 2013
    Posts
    4
    Really, you guys on this forum are my only hope..
    I'm going to go to work today feeling sick alll day about this.. and the next week.. and the next week

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The issue here is you are creating an index on a TEXT field. A TEXT type can contain up to 64k of characters. This is too large for an index. What you can do is create an index on a subset i.e. the first 20 characters by doing the following

    ...
    KEY message(message(20)),
    ...

    However, you should look closely at the usefulness of having an index on only the first few characters. I suspect that you would be better off using full text searches which allows you to search for content within the text.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Jul 2013
    Posts
    4
    I changed
    Code:
    KEY `message` (`message`)
    to
    Code:
    KEY `message` (`message(20`))
    and I still get the same error. I think I didn't do exactly what you asked, you guys are experts and I'm a fresh beginner; you'll have to spoon-feed me for the moment, as much as I hate to say.

    I have 1 year of work in this and it would just kill me if I had lost it.

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have you tried

    Code:
    KEY `message` (`message`(20))
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Jul 2013
    Posts
    4
    Sir, you have SAVED my forum! Thank you so so so much!

    screenshot: http://s21.postimg.org/ndamu360l/Untitled.jpg

    Tell me, what brought you to believe adding 20 to this line would fix everything? I am a young guy looking into developing online games in the future.

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    An index will use the contents of the column as a reference which links back to the actual row of data. However, I suspect that this is an index you actually don't ever use and you could also have removed it completely from your create table script.

    If it was needed and you are issuing queries such as WHERE message LIKE 'abc%' then the index will use the first 20 characters of the message column to link back to the actual row.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •