MySQL CREATE INDEX

[RayYates]

I need to add the index back in for 1 table. Where it may or may not exist.

I'm updating an old module where at some point in time the developer removed all indexes from the database modules. This means in the wild, some versions exist with tables and indexes, and some do not.

Can I just do the following and ignore any resulting error, or is there a better way?

<MIVA MvQUERY_Error = "nonfatal, nodisplay">
<MvQUERY NAME = "Merchant"
             QUERY = "{ 'CREATE INDEX ' ....

[wmgilligan]

Try this:

      <MvQuery name="newtables" QUERY="{ 'ALTER TABLE tableName ADD UNIQUE (`orderNum`);' }">

[RayYates]

Thanks Mr G. What will this do if the index already exists?

[RayYates]

Solution: Ignore errors containing the word Duplicate ( e.g. "Duplicate key name 's01_tablename_1' )

<MvQUERY NAME = "Merchant"
             QUERY = "{ 'ALTER TABLE ' $ g.Store_Table_Prefix $ 'tablename ADD KEY ' $ g.Store_Table_Prefix $ 'tablename_1 (basket_id)' }">
    <MvIF EXPR = "{ g.MvQUERY_Error }">
        <MvIF EXPR="{ NOT ('Duplicate' CIN g.MvQUERY_Error) }">
            <MvFUNCTIONRETURN VALUE = "{ 'Unable to create index tablename_1: ' $ g.MvQUERY_Error }">
        </MvIF>
    </MvIF>