1

10.1.15-MariaDB, PHP 5.4

Code used:

   $table = 'abc';
   mysql_query("ALTER TABLE `$table` ADD PRIMARY KEY (`col`)");

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'ALTER TABLE...

I have tried KEY `PRIMARY` and remove all backquotes to no avail.

Updates: It has nothing to do with extensions whatsoever, tested with mysqli_ same error. However, repeated test using same script in different server environment is fine.

C N
  • 11
  • 3
  • Are you sure there is no primary key set yet? If so you need to drop that first. – colburton Jul 12 '16 at 20:24
  • @colburton Good point, but that shouldn't cause a _syntax_ error. – Don't Panic Jul 12 '16 at 20:25
  • yup very sure its a new table with some dummy content created with create table select from. i had tried copy and paste the script via command line and its working fine. – C N Jul 12 '16 at 20:26
  • i have also tried drop primary key, add primary key – C N Jul 12 '16 at 20:27
  • 1
    Of course it might just be the php engine hinting at the fact that the [mysql_ extension has been deprecated for quite a while now](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) :) – fvu Jul 12 '16 at 20:28
  • btw, don't know if it helps but its on apache 2.4, php-fpm and opcache (yes i clear the cache on every update) – C N Jul 12 '16 at 20:31
  • @fvu perhaps you are right about that. i will try to play with this idea while waiting possible alternative to get it working – C N Jul 12 '16 at 20:43

3 Answers3

0

i have tried this code and it works for me

$table = 'abc';

$query="ALTER TABLE `".$table."` ADD PRIMARY KEY(`col`);";

/* Execute query */
$result=$mysqli->query($query);
/* Verify results */
if(!$result) {
  $ErrMessage  =  "Error : " $mysqli->error . "\n";
  $mysqli->close();
  die($ErrMessage);        
}

Do you have permissions to modify the table?

Martin S.
  • 256
  • 1
  • 10
  • and the col name is `col` ? and you don't have an other primary key defined? – Martin S. Jul 12 '16 at 20:40
  • column name is correct and no key assigned yet while running the script. ty. – C N Jul 12 '16 at 20:46
  • can you post the structure of your table? – Martin S. Jul 12 '16 at 20:56
  • am away from the machine now, but if its working via command line i doubt thats the problem. anyway its a simple test table of 3 columns and col is INT with non duplicates. ty – C N Jul 12 '16 at 20:59
  • am beginning to suspect its the extension problem suggested by @fvu but i will only be able to run some test later and update here – C N Jul 12 '16 at 21:02
  • oh moment... you use mysql or mysqli ? trry my code and see if it is working. – Martin S. Jul 12 '16 at 21:03
0

here a quik connection with mysqli

$mysqli =  new mysqli($db_host, $db_user, $db_pass,$database); 

if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  die ("<h1>can't use database !</h1>");
exit();
 }
/* change character set to utf8 */
if (!$mysqli->set_charset("utf8")) {
printf("error loding 'character set utf8' : %s\n", $mysqli->error);
}
Martin S.
  • 256
  • 1
  • 10
  • Mysql This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. – Martin S. Jul 12 '16 at 21:12
  • it's a bit strange to comment on your own answers... Also, it would be a lot better to fold your two answers into one. – fvu Jul 12 '16 at 21:20
  • how does switching to mysqli magically make the sql work? if it fails under the old mysql_*() functions, it'll fail in mysqli as well. – Marc B Jul 12 '16 at 21:34
0

First of all, thank you all for trying to help. I have the answer after copy and paste the scripts to a different server environment which shows extensive error descriptions.

right syntax to use near '\xef\xbb\xbfALTER TABLE abc ADD PRIMARY KEY

The devil is \xef\xbb\xb

This happened as I have to deal with multi languages. However I do hope some suggestions given to me on how to avoid such silly mistakes in future.

C N
  • 11
  • 3