How can I collect a path?

tree.sql

mysql> show create table c\G
*************************** 1. row ***************************
       Table: c
Create Table: CREATE TABLE `c` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `parent` bigint unsigned DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `parent` (`parent`),
  CONSTRAINT `c_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `c` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

INSERT INTO `c` VALUES (1,NULL),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,3);

mysql> with recursive tree as (
    ->   select c.id, c.parent, 0 as level, "x" as path
    ->     from c
    ->    where parent is null
    ->   union all
    ->   select x.id, x.parent, tree.level + 1 as level, concat(tree.path) as path
    ->     from c as x
    ->     join tree
    ->       on tree.id = x.parent
    -> )
    -> select * from tree;
+------+--------+-------+------+
| id   | parent | level | path |
+------+--------+-------+------+
|    1 |   NULL |     0 | x    |
|    2 |      1 |     1 | x    |
|    3 |      1 |     1 | x    |
|    4 |      2 |     2 | x    |
|    5 |      2 |     2 | x    |
|    6 |      3 |     2 | x    |
|    7 |      3 |     2 | x    |
|    8 |      3 |     2 | x    |
+------+--------+-------+------+
8 rows in set (0.00 sec)

mysql> with recursive tree as (
    ->   select c.id, c.parent, 0 as level, "x" as path
    ->     from c
    ->    where parent is null
    ->   union all
    ->   select x.id, x.parent, tree.level + 1 as level, concat(tree.path, ",", x.id) as path
    ->     from c as x
    ->     join tree
    ->       on tree.id = x.parent
    -> )
    -> select * from tree;
ERROR 1406 (22001): Data too long for column 'path' at row 1

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.21-0ubuntu0.20.04.3 |
+-------------------------+
1 row in set (0.00 sec)

Apprently CONVERT() is necessary to define a type, and VARCHAR() is not a type, CHAR() is.

with recursive tree as (
  select c.id, c.parent, 0 as level, convert("1", char(255)) as path
    from c 
   where parent is null
  union all
  select x.id, x.parent, tree.level + 1 as level, concat(tree.path, ",", x.id) as path
    from c as x 
    join tree 
      on tree.id = x.parent
)
select * from tree;

and

+------+--------+-------+-------+
| id   | parent | level | path  |
+------+--------+-------+-------+
|    1 |   NULL |     0 | 1     |
|    2 |      1 |     1 | 1,2   |
|    3 |      1 |     1 | 1,3   |
|    4 |      2 |     2 | 1,2,4 |
|    5 |      2 |     2 | 1,2,5 |
|    6 |      3 |     2 | 1,3,6 |
|    7 |      3 |     2 | 1,3,7 |
|    8 |      3 |     2 | 1,3,8 |
+------+--------+-------+-------+