Word **partition** is reserved by mysql. During the database creation this error appears: ``` ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partition INT(11) DEFAULT 0 NOT NULL, CONSTRAINT ruid_idx UNIQUE (ruid) )' at line 24 ERROR: Creating core tables failed at registrar! ```
You can view, comment on, or merge this pull request online at:
https://github.com/kamailio/kamailio/pull/159
-- Commit Summary --
* quoted words reserved by mysql
-- File Changes --
M utils/kamctl/mysql/registrar-create.sql (2) M utils/kamctl/mysql/usrloc-create.sql (2)
-- Patch Links --
https://github.com/kamailio/kamailio/pull/159.patch https://github.com/kamailio/kamailio/pull/159.diff
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159
Those files are automatically generated, so I would say that we should fix the building process and add quotes by default
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101670203
So, why some files have quoted columns some of the not?
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101672222
Hmm, so partition was introduced as reserved word in mysql 5.6 -- I tested with 5.5. I wouldn't have added it with such name just to avoid headaches with other applications. For that reason, wondering if should be renamed now as we don't have a stable release out with this column name.
And yes, we should fix the tool generating the sql script for mysql to quote table and column names. That will be safe for future when other column names may become reserved.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101673886
@adombrovsky have you seen sql creation scripts quoted? Checked just few, but none had quoted column names.
We quote them inside Kamailio C code when we do operations.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101674138
Daniel-Constantin Mierla writes:
Hmm, so partition was introduced as reserved word in mysql 5.6 -- I tested with 5.5. I wouldn't have added it with such name just to avoid headaches with other applications. For that reason, wondering if should be renamed now as we don't have a stable release out with this column name.
I would prefer possibility to add the quotes automatically, since different words may be reserved in different db engines and things can change from version to version as we have witnessed.
I have all my upgrade scripts done and would not like to see any more changes in db schema.
-- Juha
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101678989
On 13/05/15 16:14, juha-h wrote:
Daniel-Constantin Mierla writes:
Hmm, so partition was introduced as reserved word in mysql 5.6 -- I tested with 5.5. I wouldn't have added it with such name just to avoid headaches with other applications. For that reason, wondering if should be renamed now as we don't have a stable release out with this column name.
I would prefer possibility to add the quotes automatically, since different words may be reserved in different db engines and things can change from version to version as we have witnessed.
I have all my upgrade scripts done and would not like to see any more changes in db schema.
Yes, adding quotes has to be done for sure.
I asked about the rename as life using other tools may become easier and see the opinion of other devs.
Cheers, Daniel
@miconda yes i've seen. For example this file has quoted columns ``` kamailio/utils/kamctl/mysql/dialog_ng-create.sql ```
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101688619
@adombrovsky that's an ims module and apparently the IMS devs pushed the sql script directly, not via db schema
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101690820
Guys, i have found another bug related to the **partition** column. Take a look at this log from kamailio:
``` May 13 16:22:01 localhost /usr/local/sbin/kamailio[24862]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partition ) values ('1000','sip:1000@89.209.124.148:64403;transport=tcp','2015-0' at line 1 May 13 16:22:01 localhost /usr/local/sbin/kamailio[24862]: ERROR: <core> [db_query.c:233]: db_do_insert_cmd(): error while submitting query May 13 16:22:01 localhost /usr/local/sbin/kamailio[24862]: ERROR: usrloc [ucontact.c:683]: db_insert_ucontact(): inserting contact in db failed May 13 16:22:01 localhost /usr/local/sbin/kamailio[24862]: ERROR: usrloc [urecord.c:580]: insert_ucontact(): failed to insert in database May 13 16:22:01 localhost /usr/local/sbin/kamailio[24862]: ERROR: registrar [save.c:507]: insert_contacts(): failed to insert contact May 13 16:22:17 localhost /usr/local/sbin/kamailio[24861]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partition ) values ('1000','sip:1000@89.209.124.148:64403;transport=tcp','2015-0' at line 1 May 13 16:22:17 localhost /usr/local/sbin/kamailio[24861]: ERROR: <core> [db_query.c:233]: db_do_insert_cmd(): error while submitting query May 13 16:22:17 localhost /usr/local/sbin/kamailio[24861]: ERROR: usrloc [ucontact.c:683]: db_insert_ucontact(): inserting contact in db failed May 13 16:22:17 localhost /usr/local/sbin/kamailio[24861]: ERROR: usrloc [urecord.c:580]: insert_ucontact(): failed to insert in database May 13 16:22:17 localhost /usr/local/sbin/kamailio[24861]: ERROR: registrar [save.c:507]: insert_contacts(): failed to insert contact May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partition ) values ('1000','sip:1000@89.209.124.148:64403;transport=tcp','2015-0' at line 1 May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: <core> [db_query.c:233]: db_do_insert_cmd(): error while submitting query May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: usrloc [ucontact.c:683]: db_insert_ucontact(): inserting contact in db failed May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: usrloc [urecord.c:580]: insert_ucontact(): failed to insert in database May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: registrar [save.c:507]: insert_contacts(): failed to insert contact May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partition ) values ('1000','sip:1000@89.209.124.148:64403;transport=tcp','2015-0' at line 1 May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: <core> [db_query.c:233]: db_do_insert_cmd(): error while submitting query May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: usrloc [ucontact.c:683]: db_insert_ucontact(): inserting contact in db failed May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: usrloc [urecord.c:580]: insert_ucontact(): failed to insert in database May 13 16:22:23 localhost /usr/local/sbin/kamailio[24861]: ERROR: registrar [save.c:507]: insert_contacts(): failed to insert contact May 13 16:22:24 localhost /usr/local/sbin/kamailio[24861]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partition ) values ('1000','sip:1000@89.209.124.148:64403;transport=tcp','2015-0' at line 1 May 13 16:22:24 localhost /usr/local/sbin/kamailio[24861]: ERROR: <core> [db_query.c:233]: db_do_insert_cmd(): error while submitting query May 13 16:22:24 localhost /usr/local/sbin/kamailio[24861]: ERROR: usrloc [ucontact.c:683]: db_insert_ucontact(): inserting contact in db failed May 13 16:22:24 localhost /usr/local/sbin/kamailio[24861]: ERROR: usrloc [urecord.c:580]: insert_ucontact(): failed to insert in database May 13 16:22:24 localhost /usr/local/sbin/kamailio[24861]: ERROR: registrar [save.c:507]: insert_contacts(): failed to insert contact May 13 16:23:24 localhost /usr/local/sbin/kamailio[24862]: ERROR: db_mysql [km_dbase.c:121]: db_mysql_submit_query(): driver error on query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partition ) values ('1000','sip:1000@89.209.124.148:64403;transport=tcp','2015-0' at line 1 May 13 16:23:24 localhost /usr/local/sbin/kamailio[24862]: ERROR: <core> [db_query.c:233]: db_do_insert_cmd(): error while submitting query May 13 16:23:24 localhost /usr/local/sbin/kamailio[24862]: ERROR: usrloc [ucontact.c:683]: db_insert_ucontact(): inserting contact in db failed May 13 16:23:24 localhost /usr/local/sbin/kamailio[24862]: ERROR: usrloc [urecord.c:580]: insert_ucontact(): failed to insert in database May 13 16:23:24 localhost /usr/local/sbin/kamailio[24862]: ERROR: registrar [save.c:507]: insert_contacts(): failed to insert contact ```
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101736388
Closed #159.
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#event-304354767
Closing this pull request, it will not be merged. The discussion will continue on the issue #160
--- Reply to this email directly or view it on GitHub: https://github.com/kamailio/kamailio/pull/159#issuecomment-101831460
-
Daniel-Constantin Mierla -
Daniel-Constantin Mierla -
juha-h
-
Oleksandr Dombrovskyi
-
Victor Seva