iRODS 4.0.3 iCat Installation issue "Specified key was too long" on Ubuntu 14.04 with MySQL 5.6.22

140 views
Skip to first unread message

Andrey Belyaevskiy

unread,
Jan 19, 2015, 10:18:59 AM1/19/15
to irod...@googlegroups.com
Hi All!

I have server with Ubuntu 14.04 with MySQL 5.6.22. I installed iRODS 4.0.3 (with mysql database plugin) on it.

After that I created database user "irods" , database "ICAT" with following commands:
mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database ICAT character set latin1 collate latin1_general_cs;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'irods'@'localhost' IDENTIFIED BY 'irods';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON ICAT.* TO 'irods'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye


Then I launched configuration script setup_irods.sh and got error message "ERROR 1071 (42000) at line 499: Specified key was too long; max key length is 767 bytes" :
sudo ./setup_irods.sh
sk@irods-icat:/var/lib/irods/packaging$ sudo ./setup_irods.sh
===================================================================

You are installing iRODS.

The iRODS server cannot be started until it has been configured.

iRODS server's zone name [tempZone]:

iRODS server's port [1247]:

iRODS port range (begin) [20000]:

iRODS port range (end) [20199]:

iRODS Vault directory [/var/lib/irods/iRODS/Vault]:

iRODS server's LocalZoneSID [TEMP_LOCAL_ZONE_SID]:

iRODS server's agent_key [temp_32_byte_key_for_agent__conn]:

iRODS server's administrator username [rods]: rods

iRODS server's administrator password:

-------------------------------------------
iRODS Zone:             tempZone
iRODS Port:             1247
Range (Begin):          20000
Range (End):            20199
Vault Directory:        /var/lib/irods/iRODS/Vault
LocalZoneSID:           TEMP_LOCAL_ZONE_SID
agent_key:              temp_32_byte_key_for_agent__conn
Administrator Username: rods
Administrator Password: Not Shown
-------------------------------------------
Please confirm these settings [yes]:


Updating /etc/irods/irods.config...
Updating /var/lib/irods/iRODS/server/icat/src/icatSysInserts.sql...
Updating /etc/irods/server.config...
===================================================================

You are installing an iRODS database plugin.

The iRODS server cannot be started until its database
has been properly configured.

Database server's hostname or IP address [10.76.243.45]:

Database server's port [3306]:

Database name [ICAT]:

Database username [irods]:

Database password:

-------------------------------------------
Database Type:     mysql
Hostname or IP:    10.76.243.45
Database Port:     3306
Database Name:     ICAT
Database User:     irods
Database Password: Not Shown
-------------------------------------------
Please confirm these settings [yes]:


===================================================================
Updating irods.config...


Stopping iRODS server...
Found 0 processes:
        There are no iRODS servers running.

-----------------------------
Running irods_setup.pl...

Step 1 of 5:  Configuring database user...
    Skipped.  For MySQL, database configured by DBA.

Step 2 of 5:  Creating database and tables...
    CreateDatabase Skipped.  For MySQL, DBA creates the instance.
    Updating the .odbc.ini...
    Creating iCAT tables...
        Inserting iCAT tables...

Install problem:
    Could not create the iCAT tables.
        Warning: Using a password on the command line interface can be insecure.
        ERROR 1071 (42000) at line 499: Specified key was too long; max key length is 767 bytes
Found 0 processes:
        There are no iRODS servers running.

Abort.


I tried to find solution for this eeror, but my attempts failed.

Can anyone help me with the issue?

Andrey Belyaevskiy

unread,
Jan 20, 2015, 4:32:42 AM1/20/15
to irod...@googlegroups.com
Hi! I have some updates.

When I changed "latin1" character set to "utf8":
mysql> create database ICAT character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.01 sec)


I got same but a little bit different error during iCAT setup:
Install problem:
    Could not create the iCAT tables.
        Warning: Using a password on the command line interface can be insecure.
        ERROR 1118 (42000) at line 122: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Does anyone have idea how to fix the issue? It becames to look like bug in iRODS setup scripts.

For following debug I need to understand step on which I got the error. Can you tell me which log/script I need to look through to find closest place where issue appears? 


понедельник, 19 января 2015 г., 18:18:59 UTC+3 пользователь Andrey Belyaevskiy написал:

Andrey Belyaevskiy

unread,
Jan 20, 2015, 5:32:12 AM1/20/15
to irod...@googlegroups.com
Looks I found step my configuration process fails at:

icatSysTables.sql
********************
create table R_RULE_MAIN
123  (
124    rule_id bigint not null,
125    rule_version varchar(250) DEFAULT '0',
126    rule_base_name varchar(250) not null,
127    rule_name varchar(2700) not null,
128    rule_event varchar(2700) not null,
129    rule_condition varchar(2700),
130    rule_body varchar(2700) not null,
131    rule_recovery varchar(2700) not null,
132    rule_status bigint DEFAULT 1,
133    rule_owner_name varchar(250) not null,
134    rule_owner_zone varchar(250) not null,
135    rule_descr_1 varchar(2700),
136    rule_descr_2 varchar(2700),
137    input_params varchar(2700),
138    output_params varchar(2700),
139    dollar_vars varchar(2700),
140    icat_elements varchar(2700),
141    sideeffects varchar(2700),
142    r_comment varchar(1000),
143    create_ts varchar(32),
144    modify_ts varchar(32)
145  ) ;

But I still have no idea what is wrong with the script or my process. Any ideas welcome! I will investigate it further.

Andrey Belyaevskiy

unread,
Jan 20, 2015, 6:18:43 AM1/20/15
to irod...@googlegroups.com
I calculated total size for the table and got following result: 34464 + 2*sizeof(bigint) = 34464 + 2*8 = 34480 (bytes?). Which is about 2 times less than "magic" 65535. Perhaps, my issue can be connected to character set encoding or engine type. But I have no idea where to look further.

So, I still need community help to resolve the issue or found workaround (and fill the bug).


Terrell Russell

unread,
Jan 22, 2015, 4:42:36 PM1/22/15
to irod...@googlegroups.com
Hi Andrey,

Sorry for the delay - this wasn't as straightforward as I had hoped it would be to track down...

This definitely has to do with the character set and the length of an index in the database.

> http://stackoverflow.com/a/16820166
> The number of allowed characters just depends on your character set. UTF8 may use up to
> 3 bytes per character, utf8mb4 up to 4 bytes, and latin1 only 1 byte. Thus for utf8 your key
> length is limited to 255 characters, since 3*255 = 765 < 767. –  Stefan Endrullis Jul 23 '14 at 8:25 


I hope you can confirm by looking at your mysql.error log in /var/log/mysql/error.log (or similar).  I'm hoping you see that trying to add the idx_data_main6 and/or the idx_specific_query1 index is the problem.


So, we need to use a subset of those tables for the indices...



Can you please edit your ./plugins/database/src/icatSysTables.sql file from

create index idx_data_main6 on R_DATA_MAIN (data_path);
to
create index idx_data_main6 on R_DATA_MAIN (data_path (767));

and

create index idx_specific_query1 on R_SPECIFIC_QUERY (sqlStr);
to
create index idx_specific_query1 on R_SPECIFIC_QUERY (sqlStr (767));



And then re-try the installation?


Thanks,

Terrell



On Tue, Jan 20, 2015 at 6:18 AM, Andrey Belyaevskiy <ma...@andrey-belyaevskiy.ru> wrote:
>
> I calculated total size for the table and got following result: 34464 + 2*sizeof(bigint) = 34464 + 2*8 = 34480 (bytes?). Which is about 2 times less than "magic" 65535. Perhaps, my issue can be connected to character set encoding or engine type. But I have no idea where to look further.
>
> So, I still need community help to resolve the issue or found workaround (and fill the bug).
>
>
> --
> --
> "iRODS: the Integrated Rule-Oriented Data-management System; A community driven, open source, data grid software solution" https://www.irods.org
>  
> iROD-Chat: http://groups.google.com/group/iROD-Chat
>
> ---
> You received this message because you are subscribed to the Google Groups "iRODS-Chat" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to irod-chat+...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

Vladimir Mencl

unread,
Jan 22, 2015, 9:59:37 PM1/22/15
to irod...@googlegroups.com
Hi there,

I vaguely recall running into similar issues with MySQL before - and I recall the two different engines that come with MySQL (InnoDB and ISAM) have different limits on row size / table structure.

I just tried googling for: mysql "Row size too large"
and I recommend looking at the top ~ 4 links.

Looks like there were some changes in MySQL 5.6 that further extend these size restrictions....

Cheers,
Vlad

Andrey Belyaevskiy

unread,
Jan 23, 2015, 4:19:52 AM1/23/15
to irod...@googlegroups.com
Hi Terrrell,

  1. I see nothing strange in /var/log/mysqld.log (perhaps, I need to enable some additional logs for mysql but I do not have clear undertsanding which one): 
    [root@als-irods2 irods]# more /var/log/mysqld.log
    150122 23:29:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    150122 23:29:31  InnoDB: Initializing buffer pool, size = 8.0M
    150122 23:29:31  InnoDB: Completed initialization of buffer pool
    InnoDB: The first specified data file ./ibdata1 did not exist:
    InnoDB: a new database to be created!
    150122 23:29:31  InnoDB: Setting file ./ibdata1 size to 10 MB
    InnoDB: Database physically writes the file full: wait...
    150122 23:29:31  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile0 size to 5 MB
    InnoDB: Database physically writes the file full: wait...
    150122 23:29:32  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile1 size to 5 MB
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Doublewrite buffer not found: creating new
    InnoDB: Doublewrite buffer created
    InnoDB: Creating foreign key constraint system tables
    InnoDB: Foreign key constraint system tables created
    150122 23:29:32  InnoDB: Started; log sequence number 0 0
    150122 23:29:32 [Note] Event Scheduler: Loaded 0 events
    150122 23:29:32 [Note] /usr/libexec/mysqld: ready for connections.
    Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
    150123  0:07:10 [Note] /usr/libexec/mysqld: Normal shutdown

    150123  0:07:10 [Note] Event Scheduler: Purging the queue. 0 events
    150123  0:07:10  InnoDB: Starting shutdown...
    150123  0:07:15  InnoDB: Shutdown completed; log sequence number 0 44233
    150123  0:07:15 [Note] /usr/libexec/mysqld: Shutdown complete

    150123 00:07:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
    150123 00:07:16 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    150123  0:07:16  InnoDB: Initializing buffer pool, size = 8.0M
    150123  0:07:16  InnoDB: Completed initialization of buffer pool
    150123  0:07:16  InnoDB: Started; log sequence number 0 44233
    150123  0:07:16 [Note] Event Scheduler: Loaded 0 events
    150123  0:07:16 [Note] /usr/libexec/mysqld: ready for connections.
    Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
    [root@als-irods2 irods]# date
    Fri Jan 23 00:18:31 PST 2015
    [root@als-irods2 irods]#
  2. After perform changes of /var/lib/irods/iRODS/server/icat/src/icatSysTables.sql you described and re-launch setup-irods.sh I got following resuts:
    [root@als-irods2 irods]# /var/lib/irods/packaging/setup_irods.sh
  1. ===================================================================

    You are installing iRODS.

    The iRODS server cannot be started until it has been configured.

    iRODS server's zone name [tempZone]:

    iRODS server's port [1247]:

    iRODS port range (begin) [20000]:

    iRODS port range (end) [20199]:

    iRODS Vault directory [/var/lib/irods/iRODS/Vault]:

    iRODS server's LocalZoneSID [TEMP_LOCAL_ZONE_SID]:

    iRODS server's agent_key [temp_32_byte_key_for_agent__conn]:

    iRODS server's administrator username [rods]:

  1. iRODS server's administrator password:

    -------------------------------------------
    iRODS Zone:             tempZone
    iRODS Port:             1247
    Range (Begin):          20000
    Range (End):            20199
    Vault Directory:        /var/lib/irods/iRODS/Vault
    LocalZoneSID:           TEMP_LOCAL_ZONE_SID
    agent_key:              temp_32_byte_key_for_agent__conn
    Administrator Username: rods
    Administrator Password: Not Shown
    -------------------------------------------
    Please confirm these settings [yes]:


    Updating /etc/irods/irods.config...
    Updating /var/lib/irods/iRODS/server/icat/src/icatSysInserts.sql...
    Updating /etc/irods/server.config...
    ===================================================================

    You are installing an iRODS database plugin.

    The iRODS server cannot be started until its database
    has been properly configured.

  1. Database server's hostname or IP address [10.25.9.191]:

  1. Database server's port [3306]:

    Database name [ICAT]:

    Database username [irods]:

    Database password:

    -------------------------------------------
    Database Type:     mysql
  1. Hostname or IP:    10.25.9.191
  1. Database Port:     3306
    Database Name:     ICAT
    Database User:     irods
    Database Password: Not Shown
    -------------------------------------------
    Please confirm these settings [yes]:


    ===================================================================
    Updating irods.config...


    Stopping iRODS server...
    Found 0 processes:
            There are no iRODS servers running.

    -----------------------------
    Running irods_setup.pl...

    Step 1 of 5:  Configuring database user...
        Skipped.  For MySQL, database configured by DBA.

    Step 2 of 5:  Creating database and tables...
        CreateDatabase Skipped.  For MySQL, DBA creates the instance.
        Updating the .odbc.ini...
        Creating iCAT tables...
  1.         Skipped.  Tables already created.

    Step 3 of 5:  Configuring database security...
        Configuring security...
            Skipped.  MySQL database security configured by DBA.
        Testing database communications...

    Step 4 of 5:  Configuring iRODS server...
        Updating iRODS server.config...
        Starting iRODS server with boot environment...
    Could not start iRODS server.
        Starting iRODS server...
    iRODS server failed to start.


    Install problem:
        Cannot start iRODS server.

    If your network environment is unusual, you may need to update the
        server/config/irodsHost.
  1. Found 0 processes:
            There are no iRODS servers running.

    Abort.
  1. [root@als-irods2 irods]# more /var/lib/irods/iRODS/server/log/
    proc/               rodsLog.2015.01.21
    [root@als-irods2 irods]# more /var/lib/irods/iRODS/server/log/rodsLog.2015.01.21
    Jan 23 00:30:02 pid:6074 NOTICE: environment variable set, irodsEnvFile=/var/lib/irods/iRODS/config/irodsEnv.boot
    Jan 23 00:30:02 pid:6074 NOTICE: created irodsHome=/tempZone/home/rodsBoot
    Jan 23 00:30:02 pid:6074 NOTICE: created irodsCwd=/tempZone/home/rodsBoot
        LocalHostName:  localhost, als-irods2, als-irods2.lss.emc.com, Port Num: 1233.

    Jan 23 00:30:02 pid:6074 NOTICE: cllExecSqlWithResultBV: SQLExecDirect error: -1, sql:select name from mysql.func limit 1
    Jan 23 00:30:02 pid:6074 NOTICE: SQLSTATE: S1000
    Jan 23 00:30:02 pid:6074 NOTICE: SQLCODE: 1142
    Jan 23 00:30:02 pid:6074 NOTICE: SQL Error message: [unixODBC][MySQL][ODBC 5.1 Driver][mysqld-5.1.73]SELECT command denied to user 'irods'@'localhost' for table 'func'
    Jan 23 00:30:02 pid:6074 ERROR: [-]     iRODS/server/icat/src/icatHighLevelRoutines.cpp:144:chlOpen :  status [CAT_SQL_ERR]  errno [] -- message []
            [-]     ../src/db_plugin.cpp:15932:db_start_operation :  status [CAT_SQL_ERR]  errno [] -- message [failed to call sql to determine UDF]

    Jan 23 00:30:02 pid:6074 NOTICE: Warning, pending SQL at cllDisconnect, count: 6
    Jan 23 00:30:02 pid:6074 NOTICE: Warning, pending SQL: begin ...
    Jan 23 00:30:02 pid:6074 NOTICE: Warning, pending SQL: SET SESSION autocommit=0 ...
    Jan 23 00:30:02 pid:6074 NOTICE: Warning, pending SQL: SET SESSION sql_mode='ANSI,ST ...
    Jan 23 00:30:02 pid:6074 NOTICE: Warning, pending SQL: SET character_set_client = ut ...
    Jan 23 00:30:02 pid:6074 NOTICE: Warning, pending SQL: SET character_set_results = u ...
    Jan 23 00:30:02 pid:6074 NOTICE: connectRcat: chlOpen Error. Status = -1808000
    Jan 23 00:30:02 pid:6074 SYSTEM FATAL: initServerInfo: no rcatHost error, status = -1808000
    Jan 23 00:30:02 pid:6074 NOTICE: initServer: initServerInfo error, status = -1808000
    Jan 23 00:30:02 pid:6074 ERROR: initServerMain: initServer error. status = -1808000
    [root@als-irods2 irods]#



пятница, 23 января 2015 г., 0:42:36 UTC+3 пользователь Terrell Russell написал:

Andrey Belyaevskiy

unread,
Jan 23, 2015, 6:43:19 AM1/23/15
to irod...@googlegroups.com
Looks like initial issue was fixed by Terrell's changes:
Can you please edit your ./plugins/database/src/icatSysTables.sql file from

create index idx_data_main6 on R_DATA_MAIN (data_path);
to
create index idx_data_main6 on R_DATA_MAIN (data_path (767));

and

create index idx_specific_query1 on R_SPECIFIC_QUERY (sqlStr);
to
create index idx_specific_query1 on R_SPECIFIC_QUERY (sqlStr (767));



Should I fill a bug for this?

Also, should I continue to write in this thread or create new thread (per each issue)?

But I still have no working iRODS. Since my previous issue I grant "SELECT" permissions for the user:
[root@als-irods2 irods]#
mysql> GRANT SELECT ON mysql.func TO 'irods'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

After that I re-launched setup_irods.sh and got following error:
Database server's hostname or IP address [10.9.25.191]:

Database server's port [3306]:

Database name [ICAT]:

Database username [irods]:

Database password:

-------------------------------------------
Database Type:     mysql
Hostname or IP:    10.9.25.191
[root@als-irods2 irods]# more /var/lib/irods/iRODS/server/log/rodsLog.2015.01.21
Jan 23 02:09:58 pid:13465 NOTICE: environment variable set, irodsEnvFile=/var/lib/irods/iRODS/config/irodsEnv.boot
Jan 23 02:09:58 pid:13465 NOTICE: created irodsHome=/tempZone/home/rodsBoot
Jan 23 02:09:58 pid:13465 NOTICE: created irodsCwd=/tempZone/home/rodsBoot
    LocalHostName:  localhost, als-irods2, als-irods2.lss.domain.com, Port Num: 1233.

Jan 23 02:09:58 pid:13465 NOTICE: initZone: rsGenQuery error, status = -808000
Jan 23 02:09:58 pid:13465 SYSTEM FATAL: initServerInfo: initZone error, status = -808000
Jan 23 02:09:58 pid:13465 NOTICE: initServer: initServerInfo error, status = -808000
Jan 23 02:09:58 pid:13465 ERROR: initServerMain: initServer error. status = -808000
[root@als-irods2 irods]#

Does anyone have ideas where to look further? 

Terrell Russell

unread,
Jan 23, 2015, 6:58:50 AM1/23/15
to irod...@googlegroups.com
Can you try one more time after dropping the tables in the database?

The SQL that you updated won't be run if the tables are already detected.

Terrell

--

Andrey Belyaevskiy

unread,
Jan 23, 2015, 8:59:08 AM1/23/15
to irod...@googlegroups.com
Hi Terrell,

It's my bad. I'm Sorry.

I deleted ICAT database, created it again and relaunched setup script with modified with your suggestions icatSysTables.sql and got same issue as my second one (corresponded to utf8). 

But I should point that for utf8 character set encoding using for ICAT database I got error not in index creation procedures but on table creation procedure :

  • from iRODS/installLogs/irods_setup.log
    Creating iCAT tables...
        MySQL_ListDB
        List of existing tables (if any):
        Inserting iCAT tables...
        icatSysTables.sql...

    SQL failed:
        ERROR 1118 (42000) at line 122: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

    Abort.
  • icatSysTables.sql

    122 create table R_RULE_MAIN
  • 123  (
    124    rule_id bigint not null,
    125    rule_version varchar(250) DEFAULT '0',
    126    rule_base_name varchar(250) not null,
    127    rule_name varchar(2700) not null,
    128    rule_event varchar(2700) not null,
    129    rule_condition varchar(2700),
    130    rule_body varchar(2700) not null,
    131    rule_recovery varchar(2700) not null,
    132    rule_status bigint DEFAULT 1,
    133    rule_owner_name varchar(250) not null,
    134    rule_owner_zone varchar(250) not null,
    135    rule_descr_1 varchar(2700),
    136    rule_descr_2 varchar(2700),
    137    input_params varchar(2700),
    138    output_params varchar(2700),
    139    dollar_vars varchar(2700),
    140    icat_elements varchar(2700),
    141    sideeffects varchar(2700),
    142    r_comment varchar(1000),
    143    create_ts varchar(32),
    144    modify_ts varchar(32)
    145  ) ;
  • 146

Andrey Belyaevskiy

unread,
Jan 23, 2015, 9:22:50 AM1/23/15
to irod...@googlegroups.com
Terrell,

I've changed varchar(2700) to text as described in Row size too large error in mysql create table query
122 create table R_RULE_MAIN
123  (
124    rule_id bigint not null,
125    rule_version varchar(250) DEFAULT '0',
126    rule_base_name varchar(250) not null,
127    rule_name text not null,
128    rule_event text not null,
129    rule_condition text,
130    rule_body text not null,
131    rule_recovery text not null,
132    rule_status bigint DEFAULT 1,
133    rule_owner_name varchar(250) not null,
134    rule_owner_zone varchar(250) not null,
135    rule_descr_1 text,
136    rule_descr_2 text,
137    input_params text,
138    output_params text,
139    dollar_vars text,
140    icat_elements text,
141    sideeffects text,
142    r_comment varchar(1000),
143    create_ts varchar(32),
144    modify_ts varchar(32)
145  ) ;


After that I repeat setup again and got error somewhere below in the script:

SQL failed:
    ERROR 1071 (42000) at line 489: Specified key was too long; max key length is 767 bytes


Obviously, I will continue to modify script to find complete workaround. But I have no enough knowledge to perform it in correct way. Particularly, I am not sure in correctness of changing varchar(2700) to text obly in one table scheme. Could you please help me withthe script?


Andrey Belyaevskiy

unread,
Jan 23, 2015, 11:11:36 AM1/23/15
to irod...@googlegroups.com
Terrel,

I make following changes in icatSysTables.sql:
[root@als-irods2 irods]# diff ~/icatSysTables.sql.nomodified /var/lib/irods/iRODS/server/icat/src/icatSysTables.sql
124,128c127,131
<    rule_name varchar(2700) not null,
<    rule_event varchar(2700) not null,
<    rule_condition varchar(2700),
<    rule_body varchar(2700) not null,
<    rule_recovery varchar(2700) not null,
---
>    rule_name text not null,
>    rule_event text not null,
>    rule_condition text,
>    rule_body text not null,
>    rule_recovery text not null,
132,138c135,141
<    rule_descr_1 varchar(2700),
<    rule_descr_2 varchar(2700),
<    input_params varchar(2700),
<    output_params varchar(2700),
<    dollar_vars varchar(2700),
<    icat_elements varchar(2700),
<    sideeffects varchar(2700),
---
>    rule_descr_1 text,
>    rule_descr_2 text,
>    input_params text,
>    output_params text,
>    dollar_vars text,
>    icat_elements text,
>    sideeffects text,
466c489
< create unique index idx_coll_main3 on R_COLL_MAIN (coll_name );
---
> create unique index idx_coll_main3 on R_COLL_MAIN (coll_name (250));
468c491
< create unique index idx_coll_main2 on R_COLL_MAIN (parent_coll_name ,coll_name );
---
> create unique index idx_coll_main2 on R_COLL_MAIN (parent_coll_name (250),coll_name (250));
470c493
< create unique index idx_data_main2 on R_DATA_MAIN (coll_id,data_name ,data_repl_num,data_version);
---
> create unique index idx_data_main2 on R_DATA_MAIN (coll_id,data_name (250),data_repl_num,data_version);
472c495
< create index idx_data_main4 on R_DATA_MAIN (data_name );
---
> create index idx_data_main4 on R_DATA_MAIN (data_name (767));
476c499
< create index idx_data_main6 on R_DATA_MAIN (data_path);
---
> create index idx_data_main6 on R_DATA_MAIN (data_path(767));
479,480c502,503
< create index idx_meta_main2 on R_META_MAIN (meta_attr_name );
< create index idx_meta_main3 on R_META_MAIN (meta_attr_value );
---
> create index idx_meta_main2 on R_META_MAIN (meta_attr_name (767));
> create index idx_meta_main3 on R_META_MAIN (meta_attr_value (767));
495c518
< create index idx_specific_query1 on R_SPECIFIC_QUERY (sqlStr);
---
> create index idx_specific_query1 on R_SPECIFIC_QUERY (sqlStr(767));
508c531
< create unique index idx_grid_configuration on R_GRID_CONFIGURATION (namespace , option_name );
---
> create unique index idx_grid_configuration on R_GRID_CONFIGURATION (namespace (250), option_name (250));
[root@als-irods2 irods]#


Resetup iRODS and got following error:
Database username [irods]: irods

Database password:

-------------------------------------------
Database Type:     mysql
Hostname or IP:    10.9.25.191
Database Port:     3306
Database Name:     ICAT
Database User:     irods
Database Password: Not Shown
-------------------------------------------
Please confirm these settings [yes]:


===================================================================
Updating irods.config...


Stopping iRODS server...
Found 0 processes:
        There are no iRODS servers running.

-----------------------------
Running irods_setup.pl...

Step 1 of 5:  Configuring database user...
    Skipped.  For MySQL, database configured by DBA.

Step 2 of 5:  Creating database and tables...
    CreateDatabase Skipped.  For MySQL, DBA creates the instance.
    Updating the .odbc.ini...
    Creating iCAT tables...
        Inserting iCAT tables...

Step 3 of 5:  Configuring database security...
    Configuring security...
        Skipped.  MySQL database security configured by DBA.
    Testing database communications...

Step 4 of 5:  Configuring iRODS server...
    Updating iRODS server.config...
    Starting iRODS server with boot environment...
    Opening iRODS connection with boot password...
    Creating iRODS directories...
    Creating iRODS group 'public'...
    Creating iRODS user account...
    Setting iRODS directory ownership...
    Setting iRODS user password...
    Checking for iRODS boot user...
        Removing iRODS boot user...
Found 3 processes:
        Stopping process id 20779
        Stopping process id 20782
        Stopping process id 20890
        Killing process id 20779
        Killing process id 20782
        Killing process id 20890
    Stopping iRODS server...
Found 0 processes:
        There are no iRODS servers running.

Step 5 of 5:  Configuring iRODS user and starting server...
    Updating iRODS user's ~/.irods/.irodsEnv...
    Starting iRODS server...
    Opening iRODS connection...
    Creating default resource...
        ... Success [demoResc] [/var/lib/irods/iRODS/Vault]
    Testing resource...

Done.  Additional detailed information is in the log file:
    /var/lib/irods/iRODS/installLogs/irods_setup.log

-----------------------------
Running update_catalog_schema.py...
Traceback (most recent call last):
  File "./packaging/update_catalog_schema.py", line 214, in <module>
    main()
  File "./packaging/update_catalog_schema.py", line 209, in main
    update_database_to_latest_version()
  File "./packaging/update_catalog_schema.py", line 154, in update_database_to_latest_version
    current_schema_version = get_current_schema_version(cfg)
  File "./packaging/update_catalog_schema.py", line 16, in get_current_schema_version
    and option_name='schema_version';")
  File "/var/lib/irods/packaging/server_config.py", line 218, in exec_sql_cmd
    return self.exec_mysql_cmd(sql)
  File "/var/lib/irods/packaging/server_config.py", line 117, in exec_mysql_cmd
    (returncode, myout, myerr) = self.exec_mysql_file(sqlfile)
  File "/var/lib/irods/packaging/server_config.py", line 138, in exec_mysql_file
    db_host = self.values['Servername']
KeyError: 'Servername'
[root@als-irods2 irods]#

By the moment I still need your help to fix this issue. I'm not sure nor in my fixes in icatSysTables.sql nor in error I got in update_catalog_schema.py run.

But I checked my iRODS installation and it looks working:
[root@als-irods2 irods]# service irods status
iRODS servers:
    Process 20964
iRODS rule servers:
    Process 20967
iRODS Servers associated with this instance, port 1247:
    Process 20964
    Process 20967
    Process 21006
[root@als-irods2 irods]# su irods
[irods@als-irods2 irods]$ ils
/tempZone/home/rods:
[irods@als-irods2 irods]$ ls
iRODS  packaging  plugins  tests  tmpsqlfile  VERSION
[irods@als-irods2 irods]$ iput VERSION
[irods@als-irods2 irods]$ ils
/tempZone/home/rods:
  VERSION
[irods@als-irods2 irods]$ mv VERSION VERSION.bckp
[irods@als-irods2 irods]$ iget VERSION
[irods@als-irods2 irods]$ ls
iRODS  packaging  plugins  tests  tmpsqlfile  VERSION  VERSION.bckp
[irods@als-irods2 irods]$ diff VERSION VERSION.bckp
[irods@als-irods2 irods]$

Could you please help me with the issue and check my fixes?

Terrell Russell

unread,
Jan 23, 2015, 3:00:02 PM1/23/15
to irod...@googlegroups.com
Andrey,

1) I'm not exactly sure of the ramifications of changing your table columns to 'text'.  It might be just fine.  They are varchar(2700) for consistency across the three database types iRODS supports and to change them all to text will require more work and confirmation.

2) Reducing all the index lengths to 'safe' sizes should be fine.  Since you were able to get it installed at least once, I don't see that as the major hurdle now.  I'm still concerned that your diff showed adding length limits to nearly all the long indices -- they should have already been there (except the two that you helped me patch yesterday) when icatSysTables.sql was processed from icatSysTables.sql.pp.

3) The update_catalog_schema.py bug (with 'Servername') has been fixed (in master) since 4.0.3 here:


With a 4.0.3 package install, I expect you can install successfully with latin1 collation (don't change to using 'text' fields), adding the two '(767)' limits, and the patch for 'update_catalog_schema.py' from #3 above.



Can you also share your default collations in your mysql database?

   select * from information_schema.schemata;



Terrell




Andrey Belyaevskiy

unread,
Jan 26, 2015, 3:44:15 AM1/26/15
to irod...@googlegroups.com
Hi Terrell,

  1. I get error for each line where I replaced "varchar(2700)" to "text" if it has not been modified. I found it in iterative way, not just replacing any "varchar(2700)" item. So, we still have no solution for iRODS+MySQL with utf8 charset encoding type. Can you provide me more carefull fix than my one? I agree with you that my "fix" can be too dangerous for stable iRODS operation. 
  2. I tried to reinstall iRODS with latin1 character set encoding in MySQL ICAT table. I picked up  https://github.com/irods/irods/commit/839c2a09f5613928f1e changes but after that I got long chain of dependencies: 
    1. I have to download utils_config.pl 
    2. Install json.pm
    3. After that I got error (for next irods_setup.sh launch) which I do not know how to fix:
      ===================================================================
      Updating irods.config...


      Undefined subroutine &main::loadIrodsConfig called at /var/lib/irods/iRODS/scripts/perl/irodsctl.pl line 114.

    4. As I install iRODS 4.0.3 from rpm packages what should I do to eliminate such issues? Install iRODS from sources?
  3. For latin1 character set you request returns following:
    mysql> select * from information_schema.schemata;
    +--------------+--------------------+----------------------------+------------------------+----------+
    | CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
    +--------------+--------------------+----------------------------+------------------------+----------+
    | NULL         | information_schema | utf8                       | utf8_general_ci        | NULL     |
    | NULL         | ICAT               | latin1                     | latin1_general_cs      | NULL     |
    | NULL         | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
    +--------------+--------------------+----------------------------+------------------------+----------+
    3 rows in set (0.00 sec)

    mysql>
    If you need same output for ICAT with utf8 please write about it and I will recreate the table with proper character set encoding type and send results back to you.

  4. Obviously, you need to update insturctions for iRODS+MySQL installation and configurations because of these issues.

Andrey Belyaevskiy

unread,
Jan 26, 2015, 3:55:22 AM1/26/15
to irod...@googlegroups.com
Hi Terrell (again:) ),

I fixed 2.3 issue but faced to new one:
utils_config.pl:load_json_file: Can't open /etc/irods/server_config.json: No such file or directory

Looks like I need to change all scripts including installation ones to be able to continue with setup. 

So, I still need your help. I checked rpm packages versions. All packages correspond to version 4.0.3 and have been downloaded from iRODS site at 22 of January. 

Can you point me out should I continue with rpm-based installation or switch to installation from sources?

Thanks In Advance for your answer!

Terrell Russell

unread,
Jan 26, 2015, 1:35:25 PM1/26/15
to irod...@googlegroups.com
Andrey,

We have not tested iRODS with utf8 in MySQL - the expected installation is the default 'latin1' character set and case-sensitive collation.  I'd be happy to keep working on a utf8 installation with you off-chat and we can report back when we get things working better.

However, even if we get the database schema up to date and functional for you - the rest of iRODS is not yet utf8 compatible.  Internal strings are using char and will not handle multiple byte characters.

I'd be happy to keep helping you with an RPM-based installation of 4.0.3.  We have it running multiple times per day in continuous integration and so we should be able to get it running for you.

Terrell








Reply all
Reply to author
Forward
0 new messages