Moving columns causes the default uuid() value to get quotes around it

[Nefcanto]

Describe the bug

When I set a default value for a column that is UUID to uuid(), and use PHPMyAdmin to move it in the list of columns, and then use show create table X, I see that the uuid() would become 'uuid()' and if I move it one more time, it becomes '''uuid()'''.

To Reproduce

Steps to reproduce the behavior:

• Create a table with at least 3 columns
• Set two columns data types to be UUID
• Set default values for those two columns to uuid()
• Then use Move column feature to change the order of columns
  • Select the table
  • Go to Structure tab
  • Use Move column link (if you can't find it, search it inside the page)
• Then use show create table X
• You should see quotes around the default value
• If you move columns multiple times, then those quotes will increase in number

Expected behavior

The default value uuid() should remain as is when we move columns (change the order of columns)

Screenshots

From left hand (default state) we get to the right hand after moving columns around

Server configuration

• Operating system:
• Web server:
• Database version:
• PHP version: MariaDB
• phpMyAdmin version: latest

Client configuration

• Browser: Chrome
• Operating system:

Additional context

Add any other context about the bug here.

[williamdes]

Hi @Nefcanto
Can you try the latest 5.2 version in development (phpMyAdmin 5.2+snapshot) also available as a non official docker image ?

Some UUID changes where made recently: https://github.com/phpmyadmin/phpmyadmin/issues?q=+milestone%3A5.2.1+is%3Aclosed+uuid+

[Nefcanto]

@williamdes yes sure. Thank you for responding so fast. I'll try with the 5.2 snapshot's non official docker image and I'll report the results here.

[Nefcanto]

@williamdes I tested using botsudo/phpmyadmin-snapshots:5.3-snapshot and this bug still exists.

This is my docker-compose.yml related to phpMyAdmin:

    pma:
        image: botsudo/phpmyadmin-snapshots:5.3-snapshot
        container_name: FalconPma
        restart: always
        logging:
            driver: none
        ports:
            - 7202:80
        environment: 
            - PMA_HOST=database     
        networks:
            - FalconNetwork

[liviuconcioiu]

In my case, I can't move the columns. I have to remove the single quotes. Looking at this, it seems that uuid is not defined in

https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/classes/Controllers/Table/Structure/MoveColumnsController.php

and the column can't be moved.

07.01.2023_17.49.13_REC.mp4

[iifawzi]

I think, this's most probably related to #17920

[Nefcanto]

@liviuconcioiu does your pull request solve this? Can I test it?

[williamdes]

@liviuconcioiu does your pull request solve this? Can I test it?

I think it should, you can apply the patch from the pull-request and let us know

[Nefcanto]

@williamdes I don't know how to apply the patch from the pull-request. Can I use it as a docker image?

[williamdes]

I could build this some time soon
Or repair @sudo-robot for it to deploy the fix

[liviuconcioiu]

@Nefcanto the PR solves the problem. No more quotes and it allows the columns to be moved.

08.01.2023_11.57.09_REC.mp4

To test it, is very easy. Just go to https://github.com/phpmyadmin/phpmyadmin/pull/18007/files and copy the code from the right side and paste it at the specified line numbers on this file:

libraries/classes/Controllers/Table/Structure/MoveColumnsController.php

[Nefcanto]

@williamdes and @liviuconcioiu you are awesome guys. Thank you so much for the time you spent on this.

[williamdes]

@williamdes and @liviuconcioiu you are awesome guys. Thank you so much for the time you spent on this.

The fix is now into the snapshot and docker snapshot image, you can pull it.

[Nefcanto]

@williamdes thank you

[williamdes]

@williamdes it's not related to the docker pull. The pull works just fine. The bug still exists. Would you like to see it in action? Should I send a Google Meet?

Indeed, sorry for this. I tried the image when I was on my workstation.

I can move columns without any issue. I tested the same as you with an uuid() as default. And other variants.
Can you paste the "preview sql" query?

I do not think a google meet is best for such a small bug :)
Note: on my homepage the version is 5.2.1-dev+20230109.4b5c494aee

[Nefcanto]

@williamdes , it's nice of you that you put time into this, there is no need for excuses. I'm the one who should be grateful here. Thank you so much.

Please create a table using this script:

CREATE TABLE `Temp` (
  `Id` int(11) NOT NULL,
  `First` text NOT NULL DEFAULT uuid(),
  `Second` text NOT NULL DEFAULT uuid()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Then move the second column between the Id and First. The using show create table Temp, do you see this?

CREATE TABLE `Temp` (
  `Id` int(11) NOT NULL,
  `Second` text NOT NULL DEFAULT 'uuid()',
  `First` text NOT NULL DEFAULT uuid()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Then please move the Id to the last column. Do you see this?

CREATE TABLE `Temp` (
  `Second` text NOT NULL DEFAULT '\'uuid()\'',
  `First` text NOT NULL DEFAULT 'uuid()',
  `Id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

I'm sorry, I don't know PHP and I don't know I should put what file where. :( I just use MariaDB with .NET API and React client.

Alos, this is my home page info:

[Nefcanto]

Also I realized that I can change the column's nullability and it fixes the 'uuid()' back to uuid(). Or just hit the Change link for the column and save without any changes. It fixes the problem.

[williamdes]

Thank you for the feedback, I will try using MariaDB 10.10

There is maybe a difference
I had tried on 10.8

[liviuconcioiu]

@williamdes I've attached a video, with @Nefcanto queries. This happens because @Nefcanto uses TEXT as datatype instead of UUID.

11.01.2023_13.49.58_REC.mp4

Technically this isn't a bug, because the value he sees, 'uuid()' is the default value for TEXT.

11.01.2023_14.03.45_REC.mp4

[Nefcanto]

@liviuconcioiu thank you. I have to use text data types because I store slug. But if the slug is empty for a given post, then I should create a unique token there, and there is no better unique token than UUID. That's why I use it as the default value for slugs and keys.

[liviuconcioiu]

@liviuconcioiu thank you. I have to use text data types because I store slug. But if the slug is empty for a given post, then I should create a unique token there, and there is no better unique token than UUID. That's why I use it as the default value for slugs and keys.

For the the column where a uuid is stored, you can change it to UUID.

[williamdes]

So it seems its a bug from the videos I see, the function is moved to an escaped string
It looks like it is the bug #14371

[williamdes]

Could someone contribute to fixing this left over please ? 🙏🏻

As I said on #17830 (comment) the release should occur at the end of the month.
Testers and translators are welcome !

[Nefcanto]

@williamdes I tested with the botsudo/phpmyadmin-snapshots:5.2-snapshot and this bug is not solved. Each time I move a string column that has a uuid() function as its default value, that uuid() function gets a pair of single quotes around it. If I move it once, it becomes 'uuid()'. If I move it twice, it becomes '''uuid'''.

This problem does not exist for uuid column. But it shows itself for string columns which have uuid() function as their default value.

[williamdes]

@williamdes I tested with the botsudo/phpmyadmin-snapshots:5.2-snapshot and this bug is not solved. Each time I move a string column that has a uuid() function as its default value, that uuid() function gets a pair of single quotes around it. If I move it once, it becomes 'uuid()'. If I move it twice, it becomes '''uuid'''.

Okay, got it

This problem does not exist for uuid column.

Awesome

But it shows itself for string columns which have uuid() function as their default value.

Because of that it's maybe more #14371 like I said on #18006 (comment)

[Nefcanto]

Yep, you're right, it's similar to the #14371.