Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    54

    Smile Unanswered: Mysql & case sensitive question!

    Hi guys!
    I have a problem with restore from backup, I use:
    mysql myschema<mypath/script.sql -u mysql --password=mysql

    but I have two rows that have same values except the case senditivive of one letter, so my questions are..

    1) Can i enable case sensitive?
    2) Can i configure mysql to don't stop where there is an error? ..the tables after the "duplicate key" error are empty (


    I use mysql 5 on linux...


    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What collation do you have on the column(s) in question?

  3. #3
    Join Date
    Jun 2007
    Posts
    9
    What version mysql backup from? Also mysql5?

  4. #4
    Join Date
    Apr 2004
    Posts
    54
    Yes..I have MySql v5 on Linux!

    Example:

    I have this table in ORACLE:

    create table pippo (
    idpippo number(18),
    cdesc varchar2(20)
    )
    create unique index udxpippo on pippo (cdesc asc);

    whith this rows inside:
    insert into pippo values (1, 'Malattie oncologiche');
    insert into pippo values (2, 'Malattie Oncologiche');

    In Oracle v10 (Linux) I haven't problem to keep these rows because it's CASE SENSITIVE but when I try to migrate data to PIPPO in Mysql I've got this error:
    ERROR 1062 (23000) at line 17197: Duplicate entry 'Malattie Oncologiche' for key 2

    Can I turn CASE SENSITIVE on in Mysql v5 like Oracle?

    Fabio

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The real question is why you are adding an index to a decription field and then trying to insert duplicate records in the first place.
    Surely the constraint should be on the identifier field (idpippo)?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2004
    Posts
    54
    The scenario is more complicated than this example!

    Sure the administration in this case isn't correct but these rows have some foreign keys so when I try to migrate from Oracle to MySql I would to have identical situation and without error! and I can't remove index!!

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Ah, you didn't specify that you were migrating a DB from Oracle to MySQL.
    What is your create table DDL for MySQL??? And thus what is your column collation for that particular column? Please post your create table statement for the table (it will be contained within your script.sql). You will need to alter the default collation for MySQL so that the tables are created to be case sensitive. Careful what you do with this though (i.e. you might want to change it back after your import).

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It's also worth noting that there is no equivalent to varchar2 in MySQL so if your script contains special types (from Oracle) you will have to manually convert these to MySQL types.

  9. #9
    Join Date
    Apr 2004
    Posts
    54

    Talking

    I'm sorry...here the rest:

    -- ORACLE
    .
    .
    cdesc VARCHAR2(50) NULL
    .

    >

    -- MySQL
    create table pippo(
    idpippo decimal(10) not null ,
    cdesc varchar(50) default null,
    )
    ...I want to thank you because now I know that I must specify the collation at the end of the creation table! (example latin1_general_cs cs -> case sensitive!!)

    Thanks!

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It's worth noting that collations can be set on a per column basis as of MySQL version 5.0

    e.g.

    Code:
    CREATE TABLE Table1
    (
        column1 VARCHAR(5) COLLATE latin1_german1_ci,
        column2 VARCHAR(5) COLLATE latin1_general_cs,
        column3 VARCHAR(5)
    ) COLLATE latin1_swedish_ci;
    As you can see
    column1 is german case sensitive
    column2 is german case insenitive
    and (because we set default table collation to latin1_swedish_ci)
    column3 is swedish case insenitive
    Last edited by aschk; 06-08-07 at 07:00.

Posting Permissions

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