#431 Cannot add an area on MRBS - SQL error

open
nobody
None
1
2014-01-09
2013-12-06
Anonymous
No

Hi, I just installed MRBS and its working, but I cannot add a new area. When I enter in Rooms section with Administrator account, next screen asks for creating a new Area (My system is brand new, so it hasn't any area), but when I fill data and click on "add area", next screen tells me: "Fatal error: unfortunately the database is not available at the moment.". Looking at postgresql-9.1-main.log, it tells me (last occurance):

2013-12-06 12:55:32 AMST ERROR: column "area_name" specified more than once at character 35
2013-12-06 12:55:32 AMST STATEMENT: INSERT INTO mrbs_area (area_name, area_name, timezone, timezone, resolution, resolution, default_duration, default_duration, default_duration_all_day, default_duration_all_day, morningstarts, morningstarts, morningstarts_minutes, morningstarts_minutes, eveningends, eveningends, eveningends_minutes, eveningends_minutes, private_enabled, private_enabled, private_default, private_default, private_mandatory, private_mandatory, private_override, private_override, min_book_ahead_enabled, min_book_ahead_enabled, min_book_ahead_secs, min_book_ahead_secs, max_book_ahead_enabled, max_book_ahead_enabled, max_book_ahead_secs, max_book_ahead_secs, max_per_day_enabled, max_per_day_enabled, max_per_day, max_per_day, max_per_week_enabled, max_per_week_enabled, max_per_week, max_per_week, max_per_month_enabled, max_per_month_enabled, max_per_month, max_per_month, max_per_year_enabled, max_per_year_enabled, max_per_year, max_per_year, max_per_future_enabled, max_per_future_enabled, max_per_future, max_per_future, approval_enabled, approval_enabled, reminders_enabled, reminders_enabled, enable_periods, enable_periods, confirmation_enabled, confirmation_enabled, confirmed_default, confirmed_default) VALUES ('VC1', 'VC1', 'America/Cuiaba', 'America/Cuiaba', 1800, 1800, 3600, 3600, 0, 0, 7, 7, 0, 0, 21, 21, 30, 30, 0, 0, 0, 0, 0, 0, 'none', 'none', 0, 0, 0, 0, 0, 0, 604800, 604800, 0, 0, 1, 1, 0, 0, 5, 5, 0, 0, 10, 10, 0, 0, 50, 50, 0, 0, 100, 100, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1)
2013-12-06 12:55:32 AMST ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-12-06 12:55:32 AMST STATEMENT: SELECT area_id
FROM mrbs_room R, mrbs_area A
WHERE R.id=0
AND R.area_id = A.id
AND R.disabled = 0
AND A.disabled = 0
LIMIT 1
2013-12-06 12:55:32 AMST ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-12-06 12:55:32 AMST STATEMENT: SELECT id
FROM mrbs_area
WHERE disabled=0
ORDER BY area_name
LIMIT 1
2013-12-06 12:55:32 AMST ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-12-06 12:55:32 AMST STATEMENT: SELECT timezone, resolution, default_duration, default_duration_all_day,
morningstarts, morningstarts_minutes,
eveningends, eveningends_minutes,
private_enabled, private_default, private_mandatory, private_override,
min_book_ahead_enabled, max_book_ahead_enabled,
min_book_ahead_secs, max_book_ahead_secs,
max_per_day_enabled, max_per_day,
max_per_week_enabled, max_per_week,
max_per_month_enabled, max_per_month,
max_per_year_enabled, max_per_year,
max_per_future_enabled, max_per_future,
approval_enabled, reminders_enabled, enable_periods,
confirmation_enabled, confirmed_default
FROM mrbs_area
WHERE id=0
LIMIT 1
2013-12-06 12:55:32 AMST ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-12-06 12:55:32 AMST STATEMENT: SELECT COUNT(*) FROM mrbs_area WHERE (((approval_enabled IS NOT NULL) AND (approval_enabled > 0)) OR (approval_enabled IS NULL)) AND disabled=0 LIMIT 1
=======================================
What will I do?

Discussion

  • Robson
    Robson
    2013-12-06

    Hi, Campbell. It's my post. I logged in sourceforge, but don't know why, It gone as anonymous.

     
  • John Beranek
    John Beranek
    2013-12-06

    I can now reproduce this on my test setup, I'll have a look later today...

     
  • John Beranek
    John Beranek
    2013-12-06

    Erm, no, I can't - my test setup permissions are broken - will have to fix those first. ;)

     
  • John Beranek
    John Beranek
    2013-12-06

    2013-12-06 17:51:16 GMT mrbs mrbs ERROR:  permission denied for sequence mrbs_ar
    ea_id_seq
    2013-12-06 17:51:16 GMT mrbs mrbs STATEMENT:  INSERT INTO mrbs_area (area_name,
    timezone, resolution, default_duration, default_duration_all_day, morningstarts,
     morningstarts_minutes, eveningends, eveningends_minutes, private_enabled, priva
    te_default, private_mandatory, private_override, min_book_ahead_enabled, min_boo
    k_ahead_secs, max_book_ahead_enabled, max_book_ahead_secs, max_per_day_enabled,
    max_per_day, max_per_week_enabled, max_per_week, max_per_month_enabled, max_per_
    month, max_per_year_enabled, max_per_year, max_per_future_enabled, max_per_futur
    e, approval_enabled, reminders_enabled, enable_periods, confirmation_enabled, co
    nfirmed_default) VALUES ('test test', 'Europe/London', 1800, 3600, 0, 7, 0, 18,
    30, 0, 0, 0, 'none', 0, 0, 0, 604800, 0, 1, 0, 5, 0, 10, 0, 50, 0, 100, 0, 1, 0,
     1, 1)
    2013-12-06 17:51:16 GMT mrbs mrbs ERROR:  current transaction is aborted, commands ignored until end of transaction block
    
     
  • John Beranek
    John Beranek
    2013-12-06

    No doubling of column names there...

    For reference:

    Meeting Room Booking System:    MRBS 1.4.10+svn
    Database:   PostgreSQL 9.2.4 on i586-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
    System: Linux linda 3.11.6-4-desktop #1 SMP PREEMPT Wed Oct 30 18:04:56 UTC 2013 (e6d4a27) i686
    Server time:    Fri 06 Dec 2013 18:00:54 GMT
    PHP:    5.4.20
    
     
    Last edit: John Beranek 2013-12-06
  • John Beranek
    John Beranek
    2013-12-06

    Indeed, once I fixed my PostgreSQL database permissions, I can add areas fine...so, can you provide your full version details please?

     
  • Looking at the code I wonder if for some reason the function sql_pgsql_field_info() is somehow returning duplicate fields when run against this particular database? It's the only reason I can see that you'd get duplicate column names and also duplicate values.

    By chance I'd been looking at these functions the other day and was trying to remember if there was any good reason I'd written them that way rather than using the standard PHP functions such as pg_field_type() etc.

    Campbell

     
  • Robson
    Robson
    2013-12-16

    No solution yet? Unfortunately I cannot do anything, since the origin of problem is at code.

     
  • What are your full version details (PHP, PostgreSQL, MRBS, Server)? You'll find the information on the Help page.

     
  • Robson
    Robson
    2013-12-17

    OK, Morrison, here it comes:

    Sobre o MRBS
    Reserva de Salas: MRBS 1.4.10
    Base de Dados: PostgreSQL 9.1.10 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 32-bit
    Sistema operativo: Linux srvmrbs-mt 3.8.0-34-generic #49~precise1-Ubuntu SMP Wed Nov 13 18:08:04 UTC 2013 i686
    Data/Horas no servidor: Ter 17 Dez 2013 13:59:20 AMST
    PHP: 5.3.10-1ubuntu3.9

     
  • Can you upload the attached test file to your MRBS directory and run it and let me know what the output is please?

    Thanks,

    Campbell

     
    Attachments
  • Robson
    Robson
    2014-01-03

    Hi, Campbell!

    Follows below the output of test.php you sent:

    id
    id
    disabled
    disabled
    area_name
    area_name
    timezone
    timezone
    area_admin_email
    area_admin_email
    resolution
    resolution
    default_duration
    default_duration
    default_duration_all_day
    default_duration_all_day
    morningstarts
    morningstarts
    morningstarts_minutes
    morningstarts_minutes
    eveningends
    eveningends
    eveningends_minutes
    eveningends_minutes
    private_enabled
    private_enabled
    private_default
    private_default
    private_mandatory
    private_mandatory
    private_override
    private_override
    min_book_ahead_enabled
    min_book_ahead_enabled
    min_book_ahead_secs
    min_book_ahead_secs
    max_book_ahead_enabled
    max_book_ahead_enabled
    max_book_ahead_secs
    max_book_ahead_secs
    max_per_day_enabled
    max_per_day_enabled
    max_per_day
    max_per_day
    max_per_week_enabled
    max_per_week_enabled
    max_per_week
    max_per_week
    max_per_month_enabled
    max_per_month_enabled
    max_per_month
    max_per_month
    max_per_year_enabled
    max_per_year_enabled
    max_per_year
    max_per_year
    max_per_future_enabled
    max_per_future_enabled
    max_per_future
    max_per_future
    custom_html
    custom_html
    approval_enabled
    approval_enabled
    reminders_enabled
    reminders_enabled
    enable_periods
    enable_periods
    confirmation_enabled
    confirmation_enabled
    confirmed_default
    confirmed_default

     

  • Anonymous
    2014-01-03

    Thanks. Can you check using phpPgAdmin or similar the structure of your mrbs_area table and confirm that the column names are not duplicated? It should be impossible to have duplicate column names, but I just want to check before going any further.

    Campbell

     
  • Thanks. I think I understand what's going wrong. Can you try the following patch and let me know if it fixes the problem?

    After line 444 (assuming you are running MRBS 1.4.10) in pgsql.inc can you add the line

                               AND table_schema NOT IN ('information_schema','pg_catalog')
    

    The query should now look like this:

    $res = sql_pgsql_query("SELECT column_name, data_type, numeric_precision,
                                   character_maximum_length, character_octet_length,
                                   is_nullable
                              FROM information_schema.columns
                             WHERE table_name ='$tbl_area'
                               AND table_schema NOT IN ('information_schema','pg_catalog')
                          ORDER BY ordinal_position");
    

    If this works then I will check the fix into the trunk.

    Campbell

     
  • Robson
    Robson
    2014-01-07

    I added the referred line to the script, but we have same error:

    2014-01-07 13:28:02 AMST ERROR: column "area_name" specified more than once at character 35
    2014-01-07 13:28:02 AMST STATEMENT: INSERT INTO mrbs_area (area_name, area_name, timezone, timezone, resolution, resolution, default_duration, default_duration, default_duration_all_day, default_duration_all_day, morningstarts, morningstarts, morningstarts_minutes, morningstarts_minutes, eveningends, eveningends, eveningends_minutes, eveningends_minutes, private_enabled, private_enabled, private_default, private_default, private_mandatory, private_mandatory, private_override, private_override, min_book_ahead_enabled, min_book_ahead_enabled, min_book_ahead_secs, min_book_ahead_secs, max_book_ahead_enabled, max_book_ahead_enabled, max_book_ahead_secs, max_book_ahead_secs, max_per_day_enabled, max_per_day_enabled, max_per_day, max_per_day, max_per_week_enabled, max_per_week_enabled, max_per_week, max_per_week, max_per_month_enabled, max_per_month_enabled, max_per_month, max_per_month, max_per_year_enabled, max_per_year_enabled, max_per_year, max_per_year, max_per_future_enabled, max_per_future_enabled, max_per_future, max_per_future, approval_enabled, approval_enabled, reminders_enabled, reminders_enabled, enable_periods, enable_periods, confirmation_enabled, confirmation_enabled, confirmed_default, confirmed_default) VALUES ('T�rreo', 'T�rreo', 'America/Cuiaba', 'America/Cuiaba', 1800, 1800, 3600, 3600, 0, 0, 7, 7, 0, 0, 18, 18, 30, 30, 0, 0, 0, 0, 0, 0, 'none', 'none', 0, 0, 0, 0, 0, 0, 604800, 604800, 0, 0, 1, 1, 0, 0, 5, 5, 0, 0, 10, 10, 0, 0, 50, 50, 0, 0, 100, 100, 0, 0, 1, 1, 0, 0, 1, 1, 1, 1)
    2014-01-07 13:28:02 AMST ERROR: current transaction is aborted, commands ignored until end of transaction block
    2014-01-07 13:28:02 AMST STATEMENT: SELECT area_id
    FROM mrbs_room R, mrbs_area A
    WHERE R.id=0
    AND R.area_id = A.id
    AND R.disabled = 0
    AND A.disabled = 0
    LIMIT 1
    2014-01-07 13:28:02 AMST ERROR: current transaction is aborted, commands ignored until end of transaction block
    2014-01-07 13:28:02 AMST STATEMENT: SELECT id
    FROM mrbs_area
    WHERE disabled=0
    ORDER BY area_name
    LIMIT 1
    2014-01-07 13:28:02 AMST ERROR: current transaction is aborted, commands ignored until end of transaction block
    2014-01-07 13:28:02 AMST STATEMENT: SELECT timezone, resolution, default_duration, default_duration_all_day,
    morningstarts, morningstarts_minutes,
    eveningends, eveningends_minutes,
    private_enabled, private_default, private_mandatory, private_override,
    min_book_ahead_enabled, max_book_ahead_enabled,
    min_book_ahead_secs, max_book_ahead_secs,
    max_per_day_enabled, max_per_day,
    max_per_week_enabled, max_per_week,
    max_per_month_enabled, max_per_month,
    max_per_year_enabled, max_per_year,
    max_per_future_enabled, max_per_future,
    approval_enabled, reminders_enabled, enable_periods,
    confirmation_enabled, confirmed_default
    FROM mrbs_area
    WHERE id=0
    LIMIT 1
    2014-01-07 13:28:02 AMST ERROR: current transaction is aborted, commands ignored until end of transaction block
    2014-01-07 13:28:02 AMST STATEMENT: SELECT COUNT(*) FROM mrbs_area WHERE ((approval_enabled IS NOT NULL) AND (approval_enabled > 0)) AND disabled=0 LIMIT 1

    Ps: In script the variable is not $tbl_area, but $table. But I think it doesn't make difference to the problem.

     
  • can you try instead adding the line

                               AND table_schema = 'public'
    

    so the query looks like

      $res = sql_pgsql_query("SELECT column_name, data_type, numeric_precision,
                                     character_maximum_length, character_octet_length,
                                     is_nullable
                                FROM information_schema.columns
                               WHERE table_name ='$table'
                                 AND table_schema = 'public'
                            ORDER BY ordinal_position");
    

    (You are right about $table)

    Campbell

     
  • If this doesn't work then please substitute the schema you are using for 'public' in the above query. I think the problem is caused by the fact that you are using multiple schemas in your PostgreSQL database and the same table names appear in more than one schema.

    You are allowed to do this in PostgreSQL, but the problem is that MRBS doesn't currently cater for using schema names and qualified table names of the form schema.table. It isn't too difficult to fix this, though I need to think about the best way to do this. If the fix above works then it should keep you going for the moment, though I don't think it's a proper fix because it assumes that your schema name is called 'public' (and indeed that the 'public' schema exists). I think that what we'll have to do is either allow a schema name to be included in $db_tbl_prefix, eg 'myschema.mrbs_', or else, and at the moment I think this would be the better way to go, introduce a new optional config variable $db_schema.

    Campbell

     
    Last edit: Campbell Morrison 2014-01-08
  • Or else a third way of fixing it would be to use the PostgreSQL current_schema() function to find the current schema when necessary. Mmmm - needs a bit of thought.

     
  • Another, safer, short term fix would be to

    (a) prepend your $db_tbl_prefix with your schema name, eg 'myschema.mrbs_' and also

    (b) in pgsql.inc in the function sql_pgsql_field_info() use the following code

      list($schema, $table) = explode('.', $table, 2);
    
      $res = sql_pgsql_query("SELECT column_name, data_type, numeric_precision,
                                     character_maximum_length, character_octet_length,
                                     is_nullable
                                FROM information_schema.columns
                               WHERE table_name ='$table'
                                 AND table_schema = '$schema'
                            ORDER BY ordinal_position");
    

    in place of

      $res = sql_pgsql_query("SELECT column_name, data_type, numeric_precision,
                                     character_maximum_length, character_octet_length,
                                     is_nullable
                                FROM information_schema.columns
                               WHERE table_name ='$table'
                            ORDER BY ordinal_position");
    

    Note that this isn't a proper fix as it assumes that there is a schema name prepended to $db_tbl_prefix and will fail if there isn't. However if this works I'll put a more robust version in the trunk.

    Campbell

     
  • Robson
    Robson
    2014-01-08

    It works!!! You are correct, Campbell. In fact, there was two Schemas (public and mrbs) each one contained same tables. I dropped all schemas, and recreated schema mrbs, so I applied again the templates suggested on manual (tables.pg.sql and sample-data.sql). After that, I can see twoo areas (Building1 and Building2), where I can do changes and proceed customization. Thanks!

     
  • I've now (Rev 2799) added the ability in MRBS to support multiple schemas in the same database by introducing a new config variable, $db_schema. Thanks for your patience on this one.

    Campbell

     


Anonymous


Cancel   Add attachments