MYSQL :: Works In MYSQL 4.1 But Not In 5.0

MySQL Delete: Works In Php 5 But Not Php 4?
I've developed a small login system using Xampp on my local machine. Everything is running great except when the admin tries to delete something. Apparently I have the wrong syntax for my deletion in mysql.

Here's my original delete statement (from mysql5):
DELETE at.* FROM access_table AS at WHERE at.client_id=2

And here's the error I get from mysql4 (I ran the command in phpmyadmin console):
#1066 - Not unique table/alias: 'at'

So I removed the aliases and re wrote my command as such:
DELETE * FROM access_table WHERE at.client_id=2

And got the following error from php4:
#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 '* FROM access_table WHERE at.client_id=2' at line 1

Exporting MS Works For MySQL
I have a MS Works database and I can't find anything useful on the PHP site for working with this type of database. I did a quick search and it seems that it can be exported for OoenOffice.org and other spreadsheet database programs but none of them give a method. Mostly selling books and MS Access.

If there is a way to access the .MWD or .DBF Microsoft works files with PHP that would be great. If that's not possible, preferably not, I'd rther switch to MySQl easily if possible. I'm still looking but it's not looking good.

Mysql.exe Sort Of Works
I have just installed MySQL (not an easy thing to do). I also have installed PHP. I use Dreamweaver8 for 2 static sites, and eventually will figure out how to use that with PHP/MySQL.
Everything seems to work fine, and I'm working my way through Janet Valade's book, "PHP and MySQL for Dummies." Everything I have tried in the book works perfectly except for the mysql.exe command.
I am in the correct folder and type mysql.exe -u root -p and am prompted for my password. When I enter my password the cursor moves to the next line and just sits there. Nothing happens. Fortunately, the window is not frozen and I can just close it.

My MySQL Back-end Works Very Slow
I have installed my MySQL Server in a Window XP platform running in a Pentium4 machine. When I access the database on the machine, it is working in a normal speed. But when I access the database from a client machine (still running in windows platform), it runs very slow.

Error With SQL Statement When Using MySQL 5.0.24 (Works Fine In MySQL 4.0)
Here is my query that worked in mySQL 4.0 but not in MySQL 5.0.24:

INSERT INTO magtallynew SET magempnum=?', magcentre='YVRRC', entrydate=&#55614;&#57158;-08-15', empnum=?', empcentre='YVRRC', connection='Rockwell', service='Express', region='dom', callertype='CSR', otherdept='', transferredcall='No', directrespdept='No', station='Not Applicable', withoutmagassist='Yes', category='Accounting', issue='Advised On Contact', comments='test'

Here is the error message I get:

#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 'connection='Rockwell', service='Express', region='dom', callertype='CSR', otherd' at line 1

Works
I have this and it works just fine but I cant help but think there is a much better way of accomplishing the same thing in one sweep verses 6
PHP Code:
mysql_query("update residential set bed='1' where bed like 'ONE%'");
mysql_query("update residential set bed='2' where bed like 'TWO%'");
mysql_query("update residential set bed='3' where bed like 'THREE%'");
mysql_query("update residential set bed='4' where bed like 'FOUR%'");
mysql_query("update residential set bed='5' where bed like 'FIVE%'");
mysql_query("update residential set bed='6' where bed like 'SIX%'");

How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069.

I want to run a query to return the three items.

My query: Select * from Inventory where Description Like 'w069'

It doesn't seem to work, returning zero row.

UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days.

Notes:
Server language is ColdFusion
gamedate is type DATE

SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate >= now()
UNION (
(SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate < now()
ORDER BY gamedate desc LIMIT 1)
ORDER BY gamedate asc LIMIT 3

The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".

Query Only Works Once
I'm using jsp with mysql and am querying the database for data. Everything works fine on the first try. If I click back on my browser and hit submit again on my form, I get no data.

I've tried removing the close() commands but that doesn't seem to work.

SSN Changes In Database But Still Works As Key?
I have a ssn field in a table but somthing happens to the data. I use an update to enter the data and set the value to 123-12-1234 for a test but when I look in the table or I get the record from a record set it come out like -1123.

Why is this? But when I run a query and ask it to return all records which have a ssn of 123-12-1234 it returns the correct record. If this is correct then how can I display the ssn number in a java text field.

How Decimal Works
Say that we have an variable that is

var1 = 3
var2 = 3.65

var3 = var1 * var2 '--- Sum = 10.95

Now my Question!
How do I input this variable into MySQL in a COL whit DECIMAL (10,2)?
I only get the result 10.00 in the COL?
It seams that MySQL round every resout I get to .00 at the end.

If I make an varible hard ex.
var3 = "10.95"
that works but if I convert an numeric variable to string it doesnt?!?!?!
ex.
var4 = cstr(var3)

UPDATE Works In MS SQL
I use the following stament work with MSSql, but not MySql, why ?

Update mytable set mycol = 'my_Text' + mycol

Failed, mycol is varchar

SQL Query Works Only In Win32!
I use MySQL from my php page and from phpMyAdmin.

In Windows Server, following query works fine. In Linux Server instead it returns error.

The SQL Query is:

SELECT DISTINCT a.id, a.container, b.container FROM folder AS a JOIN folder AS b ON a.container=b.id

The PHP error is:

You have an error in your SQL syntax near 'ON a.container=b.id' at line 1

So, I ask: If in Windows this query work fine, where is the error in the Linux version?

SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing
I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I
did not convert the privilege tables with mysql_fix_privilege_tables.

Suddenly, the mysql started to show all databases to all users (even
though the user had access denied and could not do show tables on
them). So I started to find help and tried the
mysql_fix_privilege_tables. It didn't work properly (more than just
access denied and errors, that are ok) so I did the changes manually
conforming to source code of mysql_fix_privilege tables.

PhpMyAdmin stopped its warning about privilege tables not conforming
to current mysql version. Everything seemed ok except that SHOW
DATABASES still worked.

The users naturally all have Show_db_priv="N" in the tables. Also
trying "safe_show_database", "skip_show_database" in my.cnf and
"--skip-show-database" witch mysqld didn't help.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.

select sc.name,
Sum(if(student.SNO is not null, 1, 0)) nmbr,
from school sc
left outer join student
on sc.school_id = student.school_id
group by sc.school_id

this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13.
i could not find a solution.But i suspect from NULL threatment.

Parentheses: Query Works With 4.1.7, But Not With 4.1.11
Since we updated to the newest MySQL version (Windows) some of our queries do not work any more.

It's reproducable by this (dummy) query:

SELECT B.ID from ((SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID);

This works with 4.1.7, but not with 4.1.11, unless the parentheses are removed:

SELECT B.ID from (SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID;

Does anybody know why MySQL changed its behaviour that way?

I know the parentheses in the first query are not necessary, but it would be quite difficult to search through all our queries for unnecessary parentheses.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.....

Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing.
Code:

SELECT Artists.Artist, Albums.Album_Name
FROM Artists, Albums
WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id

GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version.

The query is this:

Query Help. This Works, But I Don't Think It Is Good Practice
comics:
id
date
location

comments:
id
comicid
datetime
header
comment
author

Every comic can have multiple comments. Each comic will have a comment, but there is a period of time where the comic is posted but the first comment is not yet submitted. So for argument's sake we can't assume there is any comment at all for a given comic.

I want the all the comic ids and dates before a certain date along with the most first comment entered relating to that comic.

select comics.date
, comics.id
, comments.header
from comics
left outer
join comments
on comments.comicid = comics.id
where (comics.date>=񟭇-03-01')
group by comics.id
I never defined WHICH header should be returned when many are grouped together. I just know that mysql will return the first one in this circumstance. I seem to recall reading somewhere that different databases handle this sort of thing differently, with some returning a null in that field?

I keep trying to forumate a useful subquery but keep drawing blanks just as I think I have it figured out in my head. Like I forget how the string starts by the time I mentally formulate the end....

Suddenly A Script No Longer Works
I always get the following message when starting a search in my database:

Search for titles in the mylibrary database.

Search for titles beginning with a:

V. 4.1.7 + Php4 Works, + Php5 Problems
I am running:

Mac OS X 10.3.7
apache 2.0.52
MySQL 4.1.7
php 4.9.3 & php 5.0.3

I have an html form that "POST"s data to a db.
I have a "GET" script to repopulate form for editing.
Everything works fine with php 4.9.3.

With php5.0.3 the data is "POST"ed just fine
(I used a different GUI app to check)
When I try to "GET" said data, form is returned empty?

I have all error reporting turned on = nothing.
I have all backwards-compatibility directives in
php.ini config file turned on.

Two Different Websites Connected To One Database But Only One Works...
I have been recently having problems transferring my website from one domain name to another. The first domain name was used whilst at uni, but the second is my own perosnal website. One the original website, the MySQL database allows the user to login and register new users without any problems.

However, the second website does not allow me to do this. I would consider myself a newbie when its comes to MySQL so I was wondering if the community could help me out on this one. Both websites are hosted by the same company, and both websites use the same database but my limited knowledge cannot solve this problem. Are there certain permissions I need to set or something? Can anyone help me out on this one?

Delete Using Multiple Table: Works On 4.1.20, But Not On 4.0.16
I've a simple delete query delete from joingrp as J using joingrp as J,msggrp as M
where J.msgid=M.msgid and M.parent!=0

When I tried it on phpMyadmin at ixweb, having mysql-4.1.20, it works as desired.
But when I tried it on phpMyadmin at iPowerweb, having 4.0.16, it says
#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 'as J using joingrp as J,msggrp as M where J.msgid=M.msgid Can I solve this without using any server-side loops?

SQL Query Works Fine With Phpmyadmin But Gives Me No Results And EOF With ASP
I have a strange problem: when I perform a sql query on my phpmyadmin it works fine and it gives me the right results. If I perform the _same_ query through my asp application I reach immediately the EOF condition without any results!

Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin.

THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN:

$sql = "update table set color='red' where id='1';";
$sql_do = mysql_query($sql);

THIS QUERY WORKS ONLY IN PHPMYADMIN:

$sql = "update table set color='red' where id='1'; update table set color='green' where id='2';";
$sql_do = mysql_query($sql);

I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.

Quering Works Except Need To Sort Results By Other Table...
I have the following query:

SELECT plugin_id FROM tc_plugin_lists WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC;

This almost works, the problem is the ORDER BY clause. It should order the results by the sort_number field from another table called tc_plugins. Now it throws an error...

SELECT items FROM other table ORDER BY other tables field values where field is same as in 'items' ASC

?

Using "WHERE LIKE '%Word'" That *will* Use An Index... Works But Feels Stupid!
FYI, I'm using INNODB.

Let's say I have a table with a city column containing 30,000 records, and I want to provide a search form 'starts with' and 'ends with fields.

So a user would supply the following:

Starts with: Cha
Ends with: tte

And the search would find a record such as 'Charlotte'

The 'starts with' search can use an index (eg, LIKE 'Cha%'), but ends with can't (eg, LIKE '%tte').

A solution seems to be to create a city_reverse column, add a unique index on that, then perform an 'ends with' search on the city names in reverse.

For instance, the 'Charlotte' record would be stored in the city_reverse column as such:

'ettolrahC'

...and I'd perform an 'ends with' search using the unique index:

LIKE 'ett%'

The above works, but it feels stupid, and adds weight to the database. Is there an alternative?

UNION, Individually Works Fine, But Not In Union
Well, I am using an UNION, and while both of the queries works fine while used individually, they don't work while in the UNION.

Here is the code -

PHP

(
SELECT *
FROM job
WHERE date_fin !=0000-00-00
ORDER BY date_fin ASC
)
UNION (
SELECT *
FROM job
WHERE date_fin =0000-00-00
ORDER BY date_creation ASC
)

What do u think guys?

Note : MySQL version 4.1.21

Note : Not working means, this part is not working => 'ORDER BY date_fin ASC', means, the returned results are not ordered as I want.

MySQL doc. tells,

To apply ORDER BY or LIMIT to an individual SELECT, place
the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

User Problems... Only Root User "works".
i have been trying to figure this strange problem out for a week or so. here it is.

i have a set of users with various permissions for different reasons. about 2 weeks ago, my hardware suffered a fairly nasty failure. i recovered everything. things seemed to work out. i didnt think to check my phpBB forums or my blog (or other mysql based stuff). never had any problems with the DB... so, maybe i am missing something simple here. i hope so.

what my users can do:

they can log in via command line, a gui i use on mac os x and it does not matter if it is local, or remote. they work just fine.

what all but one users cannot do:

none of my users can run phpbb or the blog stuff.. except for the root user. any other user returns the error message written in the code of which ever script i try to access telling me that "could not connect to DB" etc..

root works fine.

now, i thought maybe it was a problem with the forums and other stuff. but i cant be. same thing happens with all things... even a game server i occasionally run that keeps account info in a DB.

all these users are set up properly and have worked just fine in the past. nothing new has been installed, no conf changes..im at a loss...

ERROR 2002 (HY000): Can't Connect To Local MySQL Server Through Socket '/var/lib/mysql/mysql.sock' (13)
I have a problem connecting to sql and can't find anything on the net to solve the problem.

I have some sql databases running on a server. when I'm root on the server and I want to login to sql I do "mysql -ustudent -p -Dwordnet30 and it works fine. when I'm logged in with the user account on the server, i get this error message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)

so with the same user name in mysql, i can log on when i'm root, but not when i'm the user. the grant table for the user looks like this: Code:

Can't Connect To Local MySQL Server Through Socket '/home/data/mysql/mysql.sock' (13)
I just installed mysql on linux. If I open a terminal, su to root,
then type "mysql", I am able to connect to the server and run my
queries.

If I exit out of su so that I am my own id (baisley) and type "mysql",
I get this error: Can't connect to local MySQL server through socket
'/home/data/mysql/mysql.sock' (13)

Any idea why? mysql.sock has full permissions (read/write/execute), so
it should be accessable by user baisley. If I am baisley and I run
"mysql -u mysql" (as mysql is the user that the RPM set up), it will
connect.

What I would like to be able to do is run this without having to be
user mysql all the time. How do I go about setting that up, or is it
better to run all my queries/make tables as 1 user?

ERROR [HY000][MySQL][MyODBC 5.00.11][MySQL] 2006 MySQL Server Has Gone Away
Hosting Package Environment:
ASP.NET ver 2
MySQL 5.0
FP Extensions 2003

Progression:
Developed Website Outline
Developed Database in Access2003
Migrated AccessDB to MySQL @ Hosting Service using Migration Tool.
Can connect using MySQL Admin tools and run queries fine and all data, relations etc. are there.

PROBLEM: (The heart of the matter)
Starting simple, created GridView Control using Microsoft Visual Web Developer 2005 Express, a few glitches here and there, used the MySQL Connector/ODBC v5.00.11 followed instructions. Can see database in Database Connections Window but when I try to connect the GridView Control to it I get:

"Database schema could not be retrieved for this connection. Please make sure the connection settings are correct and that the database is online.

ERROR [HY000][MySQL][MyODBC 5.00.11][MySQL] 2006 MySQL server has gone away"

Had thought that "Microsoft Visual Web Developer 2005 Express" might be messing with the ODBC driver so thought to download the .NET Connector. That's when it all went bad. The documentation does not say how to install or use the connector or where to put the files. It seems to assume that these bits of information are common knowledge and I suppose that if I had kept up with the technology it might be. But alas I didn't and it Isn't and It Don't work.

Warning: Mysql() [function.mysql]: Access Denied For User 'apache'@'localhost' (using
my script is not connecting to my database, i get this error

Warning: mysql() [function.mysql]: Access denied for user 'apache'@'localhost' (using password: NO)

Warning: Mysql_query(): Can't Connect To Local MySQL Server Through Socket '/tmp/mysql.sock' (2)
I'm trying to connect to my MySQL database (sitehosted by GoDaddy) using PHP. I am having these errors and don't understand what they mean

Warning: mysql_query(): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /home/content/n/l/a/nladmin/html/v5.0_3/c_register.php on line 6
Warning: mysql_query(): A link to the server could not be established in /home/content/n/l/a/nladmin/html/v5.0_3/c_register.php on line 6

Notice: Query: SELECT * FROM types MySQL error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /home/content/n/l/a/nladmin/html/v5.0_3/c_register.php on line 6
here4
Warning: mysql_close(): no MySQL-Link resource supplied in /home/content/n/l/a/nladmin/html/v5.0_3/c_register.php on line 13

I've checked paths and connection settings and don't see any problems. My guess is now that the problem is coming from the opening and closing of the DB. my script, c_register.php uses a require_once('../mysql_connect.php') to open the connection to the database. But I also have a function definition in c_register.php that uses require_once('../mysql_connect.php') when called because it needs a connection to the DB. Here's a visual that might help:

MySQL Error Number 2003 :: Can't Connnect To MySQL Server On 'hostname'(10065)
I was running an older version of the query browser a while back to connect to an older version of mySQL (3.0) running on a linux box. The server hasn't changed, but the query browser has been upgraded to 1.1.10. I can't go back to the older version of the query browser because the machine that ran it got re-formatted so I downloaded the new version and installed it.

So ... I get the following error when I try to connect:

MySQL Error Number 2003
Can't connnect to MySQL server on 'hostname'(10065)

Interestingly, I wrote a simple PHP script to connect to the server and pull some data off of it:

How To Configure MySQL ODBC Driver To Connect To A Mysql Server On My Own Side Of The Router?
Using MS Access, I have attached to MySQL servers in other states
and other countries on the other side of my router. But when I use the
MySQL ODBC driver 3.51 to connect to a MySQL server on my own LAN,
the driver tells me it cannot make the connection. Here are the ODBC
driver connection parms:

Data Source Name: (free field - name my "my linux box" will do nicely)
Host/Server Name (or IP) - something like MSQLUserName@ServerName.net
has worked fine for me in the past. Now I'm trying 192.168.1.106
Database Name - whatever, mysqldb works fine, any valid db

User - root
Password - myrootpass

Port - 3306 has worked fine 4 me N the past. Dunno whether 2 use now
SQL Command On Connect - haven't ever put anything here

Perhaps there is some configuration in mysql user privilege for the
mysqluser "root" that would prevent him from logging from a remote PC
to the mysql server?

MySQL Error 2013: Lost Connection To MySQL Server During Query
I'm running sugarcrm on mysql and the following query is returning "MySQL error 2013: Lost connection to MySQL server during query":

Table Rows Inserted By Embeddded MySQL Cannot Be Retrieved By MySQL Tcp Client
I have encountered a weird behavoir of embedded MySQL. Through a
simple program I made (in Delphi 6), I can insert rows to a table
through embedded MySQL and I can retrieve the rows from my program.

But using mysql.exe which points to the same table (through 127.0.0.1,
with the same basedir and datadir), I cannot see the rows I inserted
using my embedded program. Another way around, the rows inserted by
mysql.exe can be seen in my embedded program. But after a row is
inserted by mysql.exe, the rows inserted by the embedde program are
deleted (or cannot be queried).

Can anybody explain why is this? Is there a way to fix it?

Download Of Mysql.sock File Unable To Connect To Mysql Server
Where can i get the download mysql.sock file?

I am unable to connect to local server because i dont have this file?

ERROR 2002 (HY000): Can't Connect To Local MySQL Server Through Socket '/tmp/mysql.sock' (2)
I want to user ports to install phpBB
But I found that phpBB doesn't support mysql 5.x (but the ports
installed mySQL 5.0.0 alpha)

so I deinstall mysql5-server and mysql5-client
And then I install mysql41-server and mysql41-client on FreeBSD 5.3 by
ports but now , mysql is not workable Code:

Can't Connect To Local MySQL Server Through Socket '/tmp/mysql.sock' (2)
I have this error whe I'm trying to connect MySQL from PHP. But I can connect with Query Browser or some other software.
Why I get this error? How can I fix this?

Can't Connect To Local MySQL Server Through Socket '/tmp/mysql.sock' (46)
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (46)

I get this error when I try to communicate with a table I made using a text file ...

I Want To Make Transfer Data Between MySQL Server To MySQL Local .
I want to make transfer data between MySQL Server to MySQL Local .

My Environment.

Server < --- > My PC Client

Server =
::::: MySQL DB Server ( for Linux )
::::: Apache ..
::::: Linux

PC Client =
::::: Window
::::: MySQL run localhost ( for window )

How i transfer data from mysql server to mysql local .?
Caz i don't want to connect via 3306 port ..
Can i use others way?

Can't Connect To Local MySQL Server Through Socket '/tmp/mysql.sock' (2)
I am using a Mac running Mac OS X 10.4.8 and I have successfully installed MySQL on it but when I go to run it using the command

sudo ./bin/mysqld_safe &

all it does is display a random dumber and when I try the command

./bin/mysqlshow -u root -p

I get an error message saying

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Mac OS 10.3 -- Can't Connect To Local MySQL Server Through Socket '/tmp/mysql.sock'
I just downloaded & installed MYSQL. It seemed to work and I can start
it and enter my password and change a directory but that is about all.
I have been ready and playing "hit & miss" all afternoon and it seems
to have something to do with the "mysql_install_db" not being
installed correctly -- I have tried multiple installs with multiple
downloaws.

My log. . .

MySQL Variables, Modifying Microsoft SQL Sequence Insert For Use In Mysql
I have the following microsoft SQL statement that inserts 25 new
records in sequence. However I am trying to modify this to work in
MySQL.

I cant seem to get the variable decleration right. I tried using
DECLARE CurVal int but that generates a mysql error. How can I modify
the code below to work under mysql? Code: