Bug 365469

Summary:	Error while creating table with field type that does not support UNSIGNED but has it set to TRUE
Product:	[Applications] KEXI	Reporter:	Juan José Morales <juanjosemoralesarias>
Component:	Tables	Assignee:	Jarosław Staniek <staniek>
Status:	CLOSED FIXED
Severity:	major	CC:	adam, inksi, staniek
Priority:	NOR
Version:	3.0 Alpha
Target Milestone:	3.0
Platform:	Ubuntu
OS:	Linux
Latest Commit:	http://commits.kde.org/kdb/4eca476f22dd1c5a1046daffc3b51de0c00590c2	Version Fixed In:	3.0.0
Sentry Crash Report:
Attachments:	attachment-3190-0.html

Description Juan José Morales 2016-07-12 13:34:48 UTC

When I try to create a new database with some fields it fail for sql syntax.

allo al guardar la definición del objeto.
La creación de la tabla falló. Error durante la ejecución de una declaración SQL. 
Mensaje del servidor: 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 'UNSIGNED(255) UNIQUE NOT NULL, `name` VARCHAR(255), last_name VARCHAR(255), birt' at line 1
Sentencia SQL: CREATE TABLE users (id2 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, id INT UNSIGNED UNIQUE NOT NULL, document_type VARCHAR(255), document VARCHAR UNSIGNED(255) UNIQUE NOT NULL, `name` VARCHAR(255), last_name VARCHAR(255), birth DATE, description VARCHAR(255))
Resultado del servidor: 1064

Reproducible: Always

Steps to Reproduce:
1.Create a new project with a local MySql  server
2. Create a simple table with many field including number, text, date and a primary key
3. try to save it

Actual Results:  
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 'UNSIGNED(255) UNIQUE NOT NULL, `name` VARCHAR(255), last_name VARCHAR(255), birt' at line 1

Expected Results:  
Table created sucefull

Comment 1 Jarosław Staniek 2016-07-13 22:23:18 UTC

Thanks Juan!
It helped that you pasted the error message. I found out the reason.

The scenario is as follows:
- you inserted a field with integer type and set UNSIGNED property true for it
- then you changed that type to TEXT (in general any type that does not support UNSIGNED property)
- upon table creation Kexi adds the UNSIGNED specifier when it's illegal in such case; that's a bug in Kexi

In the case you mentioned the filed name is 'document'. There may be more fields like that, you will find this while saving the design.

Current workaround: 
- temporarily change the type of the problematic field back to integer
- UNSIGNED property will re-appear; set the UNSIGNED property to FALSE
- change the type back to the desired type
- do all the above 3 steps for any other type that has the same problem; to see if you're done just try to save the design

Comment 2 Jarosław Staniek 2016-07-13 22:27:03 UTC

Updated the summary for you.

Comment 3 Juan José Morales 2016-07-14 16:51:52 UTC

Thank you for your response.
Im trying to involve in the project. I hope  i can help improving the product. For now Im learning Qt for help solving bugs and reporting bugs i found.

Greetings

Comment 4 Jarosław Staniek 2016-07-14 18:12:32 UTC

Created attachment 100103 [details]
attachment-3190-0.html

On Thursday, 14 July 2016, Juan José Morales via KDE Bugzilla <
bugzilla_noreply@kde.org> wrote:
> Comment # 3 on bug 365469 from Juan José Morales
>
> Thank you for your response.
> Im trying to involve in the project. I hope i can help improving the
product.
> For now Im learning Qt for help solving bugs and reporting bugs i found.
>

Thanks Juan, that's good news! Have you tried to compile Kexi 3?
Regardless of programming skills bug and wish reporting is welcome. We are
also planning to have distribution-independent Kexi releases.

Cheers.

> Greetings
>
> ________________________________
> You are receiving this mail because:
>
> You are on the CC list for the bug.
> You are watching someone on the CC list of the bug.

Comment 5 Jarosław Staniek 2016-07-14 22:20:18 UTC

Git commit 0486e8c17877ce1f964a086c8f71dc4210877217 by Jaroslaw Staniek.
Committed on 14/07/2016 at 22:14.
Pushed by staniek into branch 'master'.

Hide "unsigned" property for fields in Table Designer for non-integer types

Not much sense for FP types

M  +1    -1    src/plugins/tables/kexitabledesignerview_p.cpp

http://commits.kde.org/kexi/0486e8c17877ce1f964a086c8f71dc4210877217

Comment 6 Jarosław Staniek 2016-07-14 22:20:18 UTC

Git commit 4ffa6af5e60263b09087303008777b325d9bc99c by Jaroslaw Staniek.
Committed on 14/07/2016 at 22:14.
Pushed by staniek into branch 'master'.

Table Designer: don't set unsigned/maxLength/precision/scale for incompatible field types

M  +7    -3    src/plugins/tables/kexitabledesignerview.cpp

http://commits.kde.org/kexi/4ffa6af5e60263b09087303008777b325d9bc99c

Comment 7 Jarosław Staniek 2016-07-14 22:24:28 UTC

Git commit 4eca476f22dd1c5a1046daffc3b51de0c00590c2 by Jaroslaw Staniek.
Committed on 14/07/2016 at 22:17.
Pushed by staniek into branch 'master'.

Don't support "unsigned" property for fields that are not of integer types

M  +3    -0    src/KDbField.cpp
M  +2    -1    src/KDbNativeStatementBuilder.cpp

http://commits.kde.org/kdb/4eca476f22dd1c5a1046daffc3b51de0c00590c2

Comment 8 Jarosław Staniek 2016-07-14 22:26:08 UTC

Fixed in Kexi 3.0