Cannot add an area on MRBS - SQL error

[jberanek]

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?

Reported by: *anonymous

Original Ticket: mrbs/support-requests/431

[jberanek]

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

Original comment by: redbobalves

[jberanek]

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

Original comment by: jberanek

[jberanek]

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

Original comment by: jberanek

[jberanek]

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

Original comment by: jberanek

[jberanek]

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

Original comment by: jberanek

[jberanek]

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

Original comment by: jberanek

[jberanek]

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

Original comment by: campbell-m

[jberanek]

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

Original comment by: redbobalves

[jberanek]

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

Original comment by: campbell-m

[jberanek]

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

Original comment by: redbobalves

[jberanek]

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

Original comment by: campbell-m

Attachments: https://sourceforge.net/p/mrbs/support-requests/_discuss/thread/e791690e/3472/attachment/test.php

[jberanek]

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

Original comment by: redbobalves

[jberanek]

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

Original comment by: *anonymous

[jberanek]

Here is another test program. Please could you let me know the output.

Thanks,

Campbell

Original comment by: campbell-m

Attachments: https://sourceforge.net/p/mrbs/support-requests/_discuss/thread/e791690e/5162/attachment/test2.php

[jberanek]

Here is result:

Number of rows: 72

Original comment by: redbobalves

[jberanek]

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

Original comment by: campbell-m

[jberanek]

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.

Original comment by: redbobalves

[jberanek]

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

Original comment by: campbell-m

[jberanek]

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_tblprefix, 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

Original comment by: campbell-m

[jberanek]

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.

Original comment by: campbell-m

[jberanek]

Another, safer, short term fix would be to

(a) prepend your $db_tblprefix 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

Original comment by: campbell-m

[jberanek]

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!

Original comment by: redbobalves

[jberanek]

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

Original comment by: campbell-m