Level 37
Can you run SHOW ENGINE INNODB STATUS and show the logs? this will for a faster debug
Jul 27, 2022
6
Level 10
Strange MariaBD create table issue
Hi all,
10.3.35-MariaDB Do you see any errors here, please? As I keep getting this in PhpMyAdmin: errno: 150 "Foreign key constraint is incorrectly formed
I am trying to run this command manually(not in Laravel migrations)
CREATE TABLE `categories` (
`id` bigint(20) UNSIGNED NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`acronym` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`color` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Not sure what to do as I cannot see an error here:
id bigint(20) UNSIGNED NOT NULL
Thanks
Level 10
@OussamaMater This seems to work"
CREATE TABLE IF NOT EXISTS `categories` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`acronym` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`color` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Now this scares me as I exported this table form my live server and the AUTO INCREMENT did not export but it is set on in PHPMYADMIN.....ouch!
Level 37
@User476820 sorry couldn't really understand what you mean here
Level 10
@OussamaMater The first query at the start of the chat was copied from an sql export of my live database(Using MariaDB) from phpmyadmin(on centos). But the second export was copied from the same app locally installed on Windows (Laragon) using Maria DB as well. If you compare the 2 export, the first one has this missing:
AUTO_INCREMENT (first row)
AUTO_INCREMENT=35 DEFAULT (end of query)
Level 37
@User476820 true, maybe you exported the file before updated the constraints? I can't of a different cause to be honest, only the environment or that you exported before updating, and you can always tweak both of them manually to match.
Just keep in mind if these tables are related to some Laravel project, it's ALWAYS BETTER to stick with migrations as you can do everything using them.
Level 10
@OussamaMater Yes next time I will be careful. I will set your answer as the best answer, this way I can close the thread :-)
1 like
Please or to participate in this conversation.