Truncate table

#1
$mysql_query="TRUNCATE TABLE myTable";

I tried to use the code above for truncate myTable.
As I open the page with a browser expecting all data in myTable is gone, but all records in myTable still in the table.
How should I use the code “truncate”?

#2

I switched your topic to the databases section. You might get a better response there.

#3

Show us the rest of your code, because something’s amiss.

Also, verify your database account has the DROP permission.

#4

I think, you have no grant on this action.

#5
$mysql_query="TRUNCATE TABLE `myTable` ";

The above doesn’t work either

How can I give grant on the action?

.

#6

https://dev.mysql.com/doc/refman/5.7/en/grant.html

You should to set GRANT ALL or at least GRANT DROP

UPD And here https://dev.mysql.com/doc/refman/5.7/en/show-grants.html you just can check grants you have.

#7

Because TRUNCATE TABLE is equivalent in operation to DROP TABLE ... ; CREATE TABLE ...;, the account doing the truncating must have the rights to execute those operations in your database.

You can investigate what permissions the account has by issuing a SHOW GRANTS query.

If you dont see either “ALL” or “CREATE,DROP” in the response, your account does not have sufficient privilege to execute the command.

GRANT CREATE,DROP ON mydbname.* TO 'myusername'@'myhost', replacing mydbname, myusername, and myhost with the correct values.
(Note that the user issuing this command must themselves have privileges to grant privileges to said database.)

#8

Aside from all the above. STOP using obsolete Mysql_* code. It has been completely removed from Php. Use PDO.

#9

To be fair, it’s a variable name, not a function call, but yes. If you ARE using the old mysql_ functions, dont.

#10

Your right. I saw mysql_query and went blind.

#11

Perhaps that’s the problem, the OP is defining the variable with an equal-sign when they intended to use the (old) function with the string in brackets.

#12

Without knowing whatever error message(s) there may be, one can only guess.

So far:

  • lack of DROP permission
  • missing mysql_ support

Other potential reasons I can think of:

  • the query itself is OK, but there is a problem with the (assumed) PHP code that hasn’t been provided
  • there are FK restraints preventing the TRUNCATE
1 Like