-1

I'm trying to delete all connections to a database, because I made a mistake and forgot to place mysqli_connect($con) in a number of pages.

On StackOverflow I found the following question: How do you kill all current connections to a SQL Server 2005 database?

However, when I use the following query, I get an error in phpMyAdmin:

ALTER DATABASE `dotnettv_db` SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[`dotnettv_db`] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' at line 1

I would appreciate it if you could tell me what I am doing wrong. Thanks!

EDIT: This is what I am getting in phpMyAdmin: http://oi43.tinypic.com/2v0k8rc.jpg - one process, around 50 connections. The biggest problem is with the traffic associated with these connections - 3.3 Terabytes and counting in 6 days: http://oi41.tinypic.com/2lj6n9t.jpg

Community
  • 1
  • 1
MemoNick
  • 352
  • 2
  • 4
  • 18
  • What language are you using? Also, `mysql` is **not** `sql server`! – Matteo Tassinari Oct 16 '13 at 12:22
  • You might want to add the `MySql` tag. Also, since that question is related to MS Sql Server, there could be some differences in syntax and/or capability (hence the error) – valverij Oct 16 '13 at 12:22
  • I'm using mysql, thanks for your interest. I though that I could 'get away' with it. Evidently, I didn't. I would appreciate it if you could point me to the code to use. Thanks :) – MemoNick Oct 16 '13 at 12:23
  • The SQL code will only run on MS SQL Server. Here is your answer, right on SO... http://stackoverflow.com/questions/886209/how-to-exclusive-lock-mysql-database/886569#886569 – ReinhardtB Oct 16 '13 at 12:30

1 Answers1

2

You are not using SQL-Server (from Microsoft) but MySql so the SQL-Command won't work.

Check this for help: how to kill mySQL connections

Community
  • 1
  • 1
OschtärEi
  • 2,175
  • 3
  • 18
  • 39
  • Thanks a lot :) I will take a look at it, and mark your answer as correct if it works out! – MemoNick Oct 16 '13 at 12:25
  • Upon trying the query provided in the link you gave me, I found only one process. However, on phpMyAdmin, I am still being shown that (while there is one process) there are about 50 connections. Print screen: http://oi43.tinypic.com/2v0k8rc.jpg – MemoNick Oct 16 '13 at 12:52
  • well as you can see in your printscreen, there is also only one process (I think), just try to kill him and see what happens then. You can't do much wrong (worst you'd have to restart mysqld I think). But 50 Connections are not so many anyway.. – OschtärEi Oct 16 '13 at 12:58
  • The biggest problem I'm having right now, which I associated with unclosed connections, is a large load of network traffic, which goes into 3 Terabytes: http://oi41.tinypic.com/2lj6n9t.jpg – MemoNick Oct 16 '13 at 13:00
  • I know that 50 aren't too many OschtarEi, but in six days, I've ran into 3 Terabytes of traffic (read previous comment please). Most of the time, the website wasn't being used (it is still in its testing stages). Thanks for your help. PS: I tried to kill it, but it's me - accessing the cpanel ;) – MemoNick Oct 16 '13 at 13:01
  • wait something is very strange there. I thought the problem is, that your connections just stay open, but with the huge traffic it means they're alle being activiely used? My point is: why should the traffic reduce, if these connections are gone? – OschtärEi Oct 16 '13 at 13:10
  • I can't figure how they're being actively used. There are no visitors on the website. Shouldn't traffic be reduced to nothing if there are no visitors? – MemoNick Oct 16 '13 at 13:13
  • it certainly should though I don't know what you're doing. maybe you have a loop in your code or something like that, but that's still trange... maybe it would be a good idea to contact your hoster and ask if they know more / can do anything. – OschtärEi Oct 16 '13 at 13:17
  • Although I had already talked to my host, I went for your advice. The employee told me the exact opposite the previous one had - in fact she said that the 3.3TB are in fact the data transferred within the shared server. I had already raised this with a previous employee, who told me it was not the case. Thanks OschtärEi :) – MemoNick Oct 16 '13 at 15:46
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/39379/discussion-between-oschtarei-and-memonick) – OschtärEi Oct 16 '13 at 18:44