Problems With Setting Up Table Primary Keys In PhpMyAdmin
Posted by: masaka (85.255.235.---)
Date: October 02, 2017 02:12AM

A problem with setting up table PRIMARY KEYS in the new version of phpMyAdmin versio 4.7.4
I have tried to set up a table PRIMARY Key with the following :
Name : user_id
Type:INT
Attributes:UNSIGNED
Indextongue sticking out smileyRIMARY
A_I:AUTO INCREMENT selected
NOT NULL

I am getting the following error message:
#1089-incorrect prefix key;the used key part isnn't a string, the used length is longer than the key part or the storage engine does not support unique prefix keys
This is a new problem in phpMyAdmin because I have always set up my PRIMARY KEYS like this on Wampserver for three years now and I have not had a problem because the tables have worked as expected. I have gone over the available documentation on setting up PRIMARY keys and I am doing exactly what it says but stillphpMyAdmin won't accept the tables. I have tried the InnoDB engine and the other Mysair engine but the error message persists. I have tried to set up the tables using SQL code instead of using phpMyAdmin but the error message is still the same.
Help with this problem to that I may be able to use phpMyAdmin again would be greatly appreciated.



Edited 1 time(s). Last edit at 10/02/2017 09:44AM by Otomatic.

Options: ReplyQuote
Re: masaka19
Posted by: Otomatic (Moderator)
Date: October 02, 2017 09:12AM

Hi,

See sql-mode : [dev.mysql.com]

PhpMyAdmin 4.7.4 - MySQL 5.7.19 it works !
DROP TABLE IF EXISTS `montest`;
CREATE TABLE IF NOT EXISTS `montest` (
  `user_id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(40) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
COMMIT;

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »



Edited 1 time(s). Last edit at 10/02/2017 10:33AM by Otomatic.

Options: ReplyQuote
Re: Problems With Setting Up Table Primary Keys In PhpMyAdmin
Posted by: masaka (90.254.102.---)
Date: October 02, 2017 03:30PM

Hello Otomatic,
Thank you for your response,
Uising your sample code, I set up the following SQL query
DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (
user_id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
first_name varchar(20) CHARACTER SET utf8,
last_name varchar(30)CHARACTER SET utf8,
email varchar(50)CHARACTER SET utf8,
facebook_url varchar(100) CHARACTER SET utf8,
twitter_url varchar(20) CHARACTER SET utf8,
PRIMARY KEY (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
I could not use quotation marks around column names because I was getting red wavy lines in the code indicating that SQL was not happy with them. Also the last line of "COMMIT; was getting a wavy line aand I left it out.
The table was set up in phpMyAdmin.
I then tried to insert data into it from a web form using the following PHP code which I wrote above the DOCTYPE declaration of the webform:

<?php
if(isset($_POST["go"])){
$first_name=trim($_POST["first_name"]);
$last_name=trim($_POST["last_name"]);
$email=trim($_POST["email"]);
$facebook_url=trim($_POST["facebook_url"]);
$twitter=trim($_POST["twitter_handle"]);
require "../includes/msmconnect.php";
echo "<p style='color:green;font-size:2em;font-weight:bold;font-style:italic;'> We are Conntected to the ".DATABASE ." database.</p>";
$sql="INSERT INTO users (first_name,last_name,email,facebook_url,twitter_handle)
VALUES ('$first_name','$last_name','$email','$facebook_url','$twitter')";
$result=$conn->query($sql);
if(!$result){
echo "<p style='color:red;font-size:2em;font-weight:bold;'>Your data could not be inserted into the users table .".$conn->connect_error." </p>";
}else {
echo "<p style='color:blue;font-size:1.5em;font-weight:bold;'>Your data has been entered!</p>";
}
}

?>
The INSERT query failed to insert the data into the users table .This is the problem I have been having with tables set up in the phpMyAdmin version 4.7.4 .INSERT queries just don't work with them althought I am using the same PHP codes I was using before the new version and they worked in the past, indicating that mybe phpMyAdmin has problems with these tables.Your comments would be greatly appreciated.

Options: ReplyQuote
Re: Problems With Setting Up Table Primary Keys In PhpMyAdmin
Posted by: Otomatic (Moderator)
Date: October 02, 2017 06:40PM

Hi,

This question is not On Topic
This site is for people having issues getting WAMPServer running and not a general HOW DO I site for everything web related.

I have already done too much for a problem that has absolutely nothing to do with Wampserver.
It is a problem of MySQL queries or, possibly of MySQL version used.
See with a forum specializing in MySQL queries.

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Problems With Setting Up Table Primary Keys In PhpMyAdmin
Posted by: masaka (90.254.102.---)
Date: October 02, 2017 07:40PM

Hello Otomatic (Moderator),

I have used the mysqli error property of the connection object to go through the SQL INSERT query line by line to find out what is wrong. Basically, it has thrown out two error messages that the data I was inserting in two of my table collumns was longer than the width allowed for in setting up the table. I have madethe two columns longer and the code you you gave me has worked wonderfully in that the the SQL query has been successfully executed.
Thank you very much. My question is now solved and closed. Thank you once again.

Options: ReplyQuote


Sorry, only registered users may post in this forum.