MYSQL :: Strange Error On A Mysql

Strange 'unknown Column' Error
PHP

$sql = "SELECT COUNT(*) FROM klanten WHERE username = '$_POST[newid]'";
    $result = mysql_query($sql);
    if (!$result) {
        error('A database error occurred in processing your '.
              'submission.If this error persists, please '.
              'contact xxx.' . mysql_error());

and I swear there is a column called username. Still, I get an error saying that column username is not there.

Anyone has an idea what might be wrong ?

I just added the column. Does the server need time to refresh or sth ?

Strange Error When Trying To Delete A Row From MySQL Via ODBC
Here's another strange error I'm getting when using ODBC to access
MySQL. This time ODBC is being more informative, although all the
documentation I've looked at, claims that the function in question
(SQLSetPos) should not return the error!

I have a result set that has been opened for update.
I am trying to delete the current row with a call to SQLSetPos:

rc = SQLSetPos( hStmt, nRow, SQL_DELETE, SQL_LOCK_NO_CHANGE );

This reports the ODBC Error HY106, Native Error 534.
"Fetch type out of range"
(All the MySQL errors I've seen are in the 1000s or 2000s)

nRow is the current row, retrieved by a call to SQLGetStmtAttr:

rc = SQLGetStmtAttr(hStmt, SQL_ATTR_ROW_NUMBER, &nRow, sizeof(nRow), NULL);

The SQL_ATTR_CONCURRENCY attribute has been set to SQL_CONCUR_LOCK.

I can delete a row by simply executing the SQL statement, but for
various reasons it would be very good if I can delete a row from a
result set. Has anyone seen this odd error before? Any clues?
Or perhaps an alternative approach to deleting a row from a result set?

Mysql_query
I don't know how to explain this but hopefully the example can explain for itself.

$result=mysql_query("select tags from blog ORDER BY tags ASC");
while($row=mysql_fetch_array($result)){
$allthetags = "$row[tags]";
echo "$allthetags "; //will pull out all the tags from all the rows, tags are seperated by a space
}

$tags = "$allthetags"; //this would pull all the tags for only one row

I'm trying to pull out all the tags from all the rows instead of just one row.

Mysql_query
A did a google search and didn't find anything on this. Is there anyway to say:
PHP Code:
Code:
MYSQL_QUERY("select * from myDatabase where myField = '$myString'");
but allow the myString varible to ignore spaces. So if i have a string in a field in a database that has the name "Bob Jones" and in use "BobJones" (no space between the name) as myString, it will still return that field of data. Hope that makes since

C-API Problem With Mysql_query
I compose a query and I send it to mysql server using mysql_query. I get the error

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 CONCAT(...)

but the very same query is working using phpMyAdmin for example.

The concat function seems to be the problem, but I can figure out to solve.

Mysql_query Function
I making a loop of the returned result set from mysql_query, it is very important to know if an update occur during the loop to the table affect the result set of the loop or not ?

I need to loop blindly without see any updates or inserts occur during the loop....

Mysql_query Hang
I had encountered a problem with mysql_query. When mysql_query executed after the database was shutdown, the api will hang for couple of minutes before detecting the mysql server has gone away.

The scenario is that mysql_real_connect will maintain there (without close after query) and send out query if there is any query required. This is to shorten the time and overhead to do connection for every query.

Can You Put Html Tags In Via A Mysql_query?
I need to put table tags like <tr> and <td> within a sql database and everytime i do it it formats it into tablular format within the cell but i need the tags to stay there so when i call the cell it displays it in the formatted way other wise it all comes out in one line.

Example.......

$scanadv = "SOMETHING</td>$anotherplace</tr></table>";

mysql_query("INSERT INTO TABLE (`cell`) VALUES ('$scanadv')");

thats how i have it

and instead of.....

COMMIT And ROLLBACK With C API Mysql_query()
I:m just about to wrap my update queries in transactions and want to confirm something.

I:m using the C API mysql_query() function with the connection opened in CLIENT_MULTI_STATEMENTS mode.

Do I decide to COMMIT or ROLLBACK based on mysql_next_result(), mysql_store_result() and mysql_errno() checks like this:

The query string basically amounts to something like this:

START TRANSACTION;
INSERT something somewhere;
DELETE something else;
UPDATE yet another thing;
etc

... and this is sent to the server, >without< a COMMIT. Notice there are no SELECTs so no data is returned. Is the following code the right way to proceed, only sending the COMIT or ROLLBACK after everything has been checked?

'____int action = commit; // default
'____if (mysql_query(mysql_handle, query_string) != 0)
'____{
'________// Error in the very first statement
'________action = rollback;
'____}
'____else
'____{
'________do
'________{
'____________// Make sure each command in the SQL query string passed
'____________if ((mysql_result = mysql_store_result(mysql_handle)) == NULL)
'____________{
'________________if (mysql_errno(mysql_handle) != 0)
'________________{
'____________________// Something went wrong - rollback
'____________________action=rollback;
'____________________break;
'________________}
'____________}
'____________else
'____________{ // This wont happen for just UPDATEs and INSERTs
'________________mysql_free_result( mysql_result );
'____________}
'____________next_results_status = mysql_next_result( mysql_handle );
'________} while (next_results_status == 0);
'________
'________// Should fall out the do loop without an error
'________if ( next_result_status > 0 )
'____________action = rollback;
'____}
'____
'____// Do whichever action is appropriate
'____mysql_query( mysql_handle, (action==commit) ? "COMMIT" : "ROLLBACK" );

Mysql_query(call Procedure); No Results
i'm trying to use the command mysql_query to execute an store procedure.
If i execute manually the store procedure it works fine with several records back.

The issue is that no result come back if I call the procedure within the mysql_query command: like this:

$proc="call MyProc("$param");"; // echo $proc= call MyProc("A");
$result = mysq_query($proc); // Seems not works.
echo $result; // No value. It is null.

Could you please help me with this command.

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:

Strange Character ...
We were trying to save https://oururl in mysql database.
But got https: / /oururl

We don't know why "/" be changed to " /", any suggestion?

Strange Insert
i hava a basic insert sql: insert into test values(123.34, 321.32, 678.34,32.8);

Here is table structure:

CREATE TABLE `test` (
`data_hba1c` float(3,2) unsigned NULL,
`data_albumiini_kreatiini` float(4,3) unsigned default NULL,
`data_mikroalbuminuria` float(3,2) unsigned default NULL,
`data_sldlkol` float(2,1) unsigned default NULL
) ENGINE=InnoDB;

MySql changes inserted values in database to:

data_hba1c -> 9.99
data_albumiini_kreatiini -> 9.999
data_mikroalbuminuria -> 9.99
data_sldlkol -> 9.9

Strange but true. Any one else had the same problem?

Strange Connection
I'm using MySQL Control Center to connect to a MySQL server which I have configured and started on a remote computer. I have already succeeded connecting and started working on the "test" database. I already had 1 table there, and when I tried to return all its rows, it suddenly could not connect and retrieve the data set (error 2003)! I then disconnected from the database and tried to
re-connect, but then I recieved again error 2003! Then I connected to the remote computer (using SSH) and restarted the MySQL server. After that, I was able to connect to the "test" database. I created a new table, added a few fields and saved it. Then I tried to return all the rows of the new table, and once again got error 2003!!! I tried executing the SQL query "SELECT * FROM <table-name>", but got the same results.

I don't understand what's happening here! Why can I define tables, but not filling them with data? Why, after disconnecting from the database, can't I connect again?

Strange Float
I have a field, with a FLOAT(31,30) as field type, whenever I insert

5.254 the value changes to 5.254000186920166015625000000000

Where do these numbers after the .254 come from, I would logically think it would fill it up with zero's or nothing .. but this is a real problem since quite some calculations depend on these numbers and there is no room for errors in the needed calculations, and Yes, we need calculations up to 30 numbers after the .

Strange Join
I'm hoping someone can help me here.. I have 2 tables..

car (id, driver, navigator, backup)
person (id, name, age, gender)

The driver, navigator, and backup fields are FK's for person ids.

What I want is:
'driver', joe, 27, M
'navigator', sue, 29, F
'backup', jack, 32, M

Is there a way to do this in a single sql statement?

Strange Query
I have the following simple query: select * from person where username='admin' and status='A'. When executed in the person table it executes fine but returns no recordset. In a similar table (user with a username and status field) it executes as expected and returns a result. The problem seems to be the status field (is it a "reserved" word. The only difference between the two tables is that in the user table - username is unique. Does any one have any idea why this behaviour is exhibited?

Strange Cartesian Product
I have a strange cartesian product error. I am doing a query from a db I did not create. I can see the tables in it and have a search query that searches in 2 tables. If I search for "name LIKE ó“%' OR model LIKE '%670%' " I get the cartesian product error.

However, if I search for "name LIKE ó“%' " by itself I get an empty query set, 0 rows returned.
And model LIKE '%670%' pdoduces an expected query of 7 rows.

This makes absolutely no sense to me. If it does not match anything in the name column, how the heck is it creating a cartesian product result???

Furthermore, if I change the query to something that is contained in the name column, then I don't get the error.

This is driving me nuts. To compund the matter further there is a third column involved which is doing the same thing. But I am currently testing this with just the two columns to narrow things down a bit.

Strange Problem With Primary Key
I am unable to add records in tut_db table, its stopped on id 255. Whenever I tried to add a new record I received the message "#1062 - Duplicate entry 饗' for key 1". I wonder why I got this message, it is an auto increment key.
Following is the structure of table

PHP

`id` tinyint(5) unsigned NOT NULL auto_increment,
`s_cat_id` int(4) NOT NULL default Ɔ',
`title` varchar(255) collate latin1_general_ci NOT NULL default '',
`url` varchar(255) collate latin1_general_ci NOT NULL default '',
`des` text collate latin1_general_ci NOT NULL,
`date` date NOT NULL default 񟍰-00-00',
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`)

Remove Strange Characters ('~!@#`)
is there anyway to remove those characters , '~`# when inserting into the database?

Replace Strange Characters
When I made a back-up on my old database and did restore it on a another server then all Ã¥ Ã¤ Ã¶ in all tables where replaced with some strange characters.

How can I use Replace() to replace all these characters in the database (all tabels at the same time)

Sha1 Strange Values
I'm trying to use Sha1() to store a password value in our software. The software is using MyODBC 3.51.10. MySQL version 4.1

Statement is simply:
update mytable set mypass = sha1('thepassword') where myuser = 1

For some reason, when one particular user saves a password, it stores a value that is exactly half of what it should be. It literally stores the first 20 characters of the value.

If I store the same password, I get the full value.

The password is stored in a varbinary column. Used to be in a char column. Both had the same problem (I changed the column type thinking that would help.

Any ideas? I can't find any reason he would get a different value for the same password using the same software, database, server, etc.

We're both running Windows XP. I'm almost certain there is something unique about his machine, but I can't imagine what it could be!

Strange LEFT JOIN
I tried a command (from phpMyAdmin):

SELECT * FROM dttips t left join dtbets b on t.datum=b.datum and t.userid=b.userid where t.tip not like '0000%' and t.datum like '2004%' and (b.sazka='000' or isnull(b.sazka)) and t.userid='pepa'

I've got 30 records in table t, 18 records in table b (with right key), but always get only 18 records !!! I would expect 30 lines (with LEFT JOIN) - 18 regular and 12 with 'b' table NULL 'tail'!!!

Weird / Strange Characters
I recently switched servers, and when i dumped all of my database info, some of the fields that contained single and double quote characters turned into:Ã¢â‚¬â€. (things like this). Does anyone know of an easy way to fix this. Possibly without re importing the data.

Strange Codes Have Appeared! =o(
we all know what access databases are! ;oP

So I converted it to a MYSQL database. I only deal with the webside of the site as opposed to the content, although the guy who does this reported that there were some strange characters which had cropped up (not sure if this was related to the Access changeover or not).
For example some of the ones we have found so far (first is what it should be, then the code which the database has converted it to):

' IS CONVERTED TO Ã¢â‚¬â„¢
-- IS CONVERTED TO Ã¢â‚¬â€
- IS CONVERTED TO Ã¢â‚¬Â¢

At the moment i just have a replace in my asp code when displaying these characters, but he says that more are appearing!?

Should this be happening (as to me it seems like all these characters are used in SQL statements) or is there a problem somewhere along the line?

Strange Query Result
I have a column (tinyint) that has either 1s or 0s in it. When I run this query counting either 1s or 0s it counts all of the rows, not just the ones with 1s or 0s as stated in the query:
SELECT COUNT( cb_paiddues = '0' ) from jos_comprofiler
or
Code:

SELECT COUNT( cb_paiddues = '1' ) from jos_comprofile

Mysql_query("SET NAMES GBK") Problem..
I have my database character set = utf8 but i wanna display the output in gbk.

So i do this before the SELECT query..

mysql_query("SET NAMES GBK");
mysql_query("SET CHARACTER SET GBK");

However, the page somestimes display correctly, sometimes .....

Strange Select Command, Is That Normal?
I am using addslashes before submitting a string to mysql. Means, O'REILLY is getting submitted as O'REILLY.

Now I was using a select command.
PHP

$lname = addslashes(htmlspecialchars($_POST['lname']));
"SELECT * FROM $mytable WHERE lastname = '$lname'"

Problem is that, it's not working.

So I made a function, and after much hit and try, this is what I found.
PHP

function kgbaddslashes($string) {
$string = str_replace("'", "\'", $string);
return $string;
}

and then
PHP

$lname = addslashes(htmlspecialchars($_POST['lname']));
$lname = kgbaddslashes($lname);
"SELECT * FROM $mytable WHERE lastname = '$lname'"

Now, this is working, but I am wondering where's the problem. I mean, why I had to add so many slashes to make it work?

Strange Characters In Mysql Database
I'm currently updating a site and in one of the fields is the content, but in the content I'm noticing strange characters such as:

Quote:

Ã¢â‚¬Å“This is a quoteÃ¢â‚¬?
These are Mr PlumÃ¢â‚¬â„¢s ideas...

The thing is, I need to get rid of them, or replace them (on mass) with the proper character. Is there a way to do this without finding every record that has one of these special characters and then replacing it?

Strange Problem With Connecting To Database
I've installed new server and transferred databases from my home computer to this server. I have no problems with connecting to server's database from my home computer (with MySQL Query Browser), but I can't connect to database from PHP script, placed on same server.

I get this error:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'login'@'host' (using password: YES) in /home/user/script.php on line 20
Could not connect: Access denied for user 'login'@'host' (using password: YES)

PHP code is:

$conn = mysql_connect("server's ip:3306", "login", "pass") or die('Could not connect: ' . mysql_error());
mysql_select_db("database", $conn);

Strange Problem With ORDER Clause
I am having a problem with a site I manage. The site has News Releases stored in a MySQL database. The news releases are displayed on the site, and ordered by DATE DESC which has been working fine for months and months up until today.

Now, for some reason, the news releases are displaying a different order. They seem to be ORDERED by ID rather than DATE, even though the code hasn't changed. There doesn't seem to be any explanation as to why this occured and the code was not changed whatsoever.

I use the same News Release system on a bunch of websites, without problems.

I've checked the code, nothing has changed; I've checked the database, and it seems to be fine. I'm not even sure where to look now.

Any ideas? Anyone been through this?

Here is the code I use to draw the News Releases out:

PHP

<?php include 'inc/db.inc.php' // The basic SELECT statement $select = 'SELECT DISTINCT id, ndate, ntitle, nbody' $from = ' FROM news' $where = ' WHERE 1=1' $sort = ' ORDER BY ndate DESC' $nyear = $_POST['nyear']; if ($nyear != '') { // A year has been chosen $where .= " AND ndate LIKE '%$nyear%'"; } $searchtext = $_POST['searchtext']; if ($searchtext != '') { // Some search text was specified $where .= " AND nbody LIKE '%$searchtext%' OR ntitle LIKE '%$searchtext%'"; } $press = @mysql_query($select . $from . $where . $sort); if (!$press) { echo '</table>' exit('<p>Error retrieving news releases from database!<br />'. 'Error: ' . mysql_error() . '</p>'); } while ($row = mysql_fetch_array($press)) { echo "<tr valign='top'>
"; $id = $row['id']; $ndate = htmlspecialchars($row['ndate']); $ntitle = htmlspecialchars($row['ntitle']); echo "<td width=ྖpx' style='color:#333;padding:7px;border-bottom: 1px solid #ccc;'>$ndate</td>
"; echo "<td style='color:#333;padding:7px;border-bottom: 1px solid #ccc;'><a href='viewnews.php?id=$id'>$ntitle</a></td>
"; echo "</tr>
"; } if (mysql_num_rows($press) < 1) { echo '<p style="padding: 20px 0px 0px 0px;">No results found.' } ?>

Strange Access Denied Problem
Yesterday I signed up for another dedicated linux server with the hosting
company I use and I am having problems getting MySQL 4.1.3 working on it.
I've tried just about everything I can think of to login via the MySQL
client but get a variation of the following error every time:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)

I put skip-grant-tables in to my.cnf and restarted the mysqld service and
logged in to the client as root no problem. I manually cleaned up the User
table by resetting all the passwords using:

UPDATE mysql.user SET Password=PASSWORD('mynewpass');
FLUSH PRIVILEGES;

and granting all priveleges to all users. My User table now looks like this:

+-----------------------+------------+------------------+
| host | user | password |
+-----------------------+------------+------------------+
| localhost | root | *EAA2B914CE13E7B |
| srv01.mysite.net | root | *EAA2B914CE13E7B |
| localhost | wpphpadmin | *EAA2B914CE13E7B |
| localhost | myuser | *EAA2B914CE13E7B |
| localhost.localdomain | myuser | *EAA2B914CE13E7B |
| 127.0.0.1 | myuser | *EAA2B914CE13E7B |
+-----------------------+------------+------------------+

I've cut off the priveleges for readability but they are all set to Y apart
from Grant_Priv which only root has.

When I remove the skip-grant-tables from the my.cnf file and restart the
service I get the following errors when I try to login:

mysql -u root -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)

mysql -h 127.0.0.1 -u root -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'root'@'localhost.localdomain'
(using password: YES)

mysql -h srv01.mysite.net -u root -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'root'@'srv01.mysite.net' (using
password: YES)

mysql -h localhost -u root -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)

mysql -u wpphpadmin -p
Enter password: mynewpass
ERROR 1045 (28000): Access denied for user 'wpphpadmin'@'localhost' (using
password: YES)

etc.

The same goes for the myuser user as well. None of them seem to work.

My setup is:
Redhat Linux 2.4.21-4.0.1
Apache 2.0.46
PHP 4.3.2
MySQL 4.1.3
Ensim WEBppliance Pro 4.0.0-14

Querying From 2 Tables With Strange Results
What I want is to run a query that select the complete.a2 field and echos the value, where dn_records.number='5553331111'

Whats happening is all the values for complete.a2 are echoing back, even if dn_records.number isn't equal to '5553331111'

I am getting same results using the below php script as I am usnig the MySQL Query Browser.

<?php
mysql_connect( 'localhost', 'user', 'pass' )
or die( "Error! Could not connect to database: " . mysql_error() );

mysql_select_db( 'customers' )
or die( "Error! Could not select the database: " . mysql_error() );

$result = mysql_query("select complete.a2, dn_records.number from complete, dn_records where dn_records.number='5553331111'")
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
    echo "<tr><td>";
    echo $row["a2"];
    echo "</td></tr>";
}

?>

Strange Access Denied Message
Maybe i am going completely insane or i just can't see an error, but everytime i try to log in to a database i get an error message like this:

Access denied for user: 'root@%' to database 'myDB'. What confuses me about it is the "%". All the data concerning the server, the password are correct but i get this strange message.

Strange Auto-increment Behavior
If I insert data to a table which has an auto-incrementing id-column, the id does not increase as I expect it woud. In a table with 4 values (numbered 0 to 3), for example, a new row inserted began it's ID at 43, then continued to 44, 45, etc.

Another related issue is that if I restructure a table that has data present in it, the row with an id of 0 (on an auto-increment column) jumps around to a seemingly random high number, again, like 43.

Strange MySql Database Table
I got a very strange MySql database table from my client.

Inside the table there is a columne with datatype char(1), but the default value is '000'. I would really want to know how to create this column.

Strange Alias And Join Problem On Server But Not Dev Box
I'm running MySQL 5.0.45, php5 and apache 1.3 on windows xp for development. All my tables are MyIsam.
The following query works fine :

MySQL
SELECT
i.image_id, image_filename, region_name, monument_name
FROM
image AS i
LEFT JOIN
image2region USING(image_id)
LEFT JOIN
region USING(region_id)
LEFT JOIN image2monument AS i2m ON (i.image_id=i2m.image_id)
LEFT JOIN monument USING(monument_id)
WHERE region_name='egypt'
LIMIT 0, 8
On the server (MySql v5.0.15) where I plan to host the site I get the following error:

#1054 - Unknown column 'image_filename' in 'field list'

If I give all the tables aliases and prepend all the column names after the SELECT with the aliases it works but I don't want to have to do this with all the queries I've written. I don't understand why it doesn't work on my dev box but not the hosts server.

I have also noticed that this problem only seems to arise on queries with more than two joins.

Has anyone else experienced a problem like this?

Or should I be prepending all columns with aliases as a matter of good practise anyway?

Cheers x

Cleaning Rows In The Database From Strange Characters...
I have emails in my customers table. At the end of some of the email addresses I have a strange character:?

email@email.com plus two square looking characters

How can I trim my emails so that they are cleaner?

UNION SELECT Strange Query Result
I want to combine the results of two queries. One query with a combination of tables and one query in one of the tables where the combination does not count for [so that all devices will be shown, and not only devices that will show after the first query, but also devices that are not being showed in the first query].

The query I made: ..........

Strange Time Format On Linked Table
I have an Mysql database and have linked some tables via odbc to an access database.
When i open the tables in access i have some fields that should have a time format, like a phonecalls conect time.
But the strange (for me) is the format of the time in my access table, the format is like this xxxx-xx-xx.
as an example the time 11:56 comes out like: 2015-07-05

Does anyone know how to get the format like xx-xx-xx

Strange Behavior Of Properties Section Of Database
We recently moved our company to a new building, and servers along
with it. Although our mysql database is still on the same server, ever
since the move, I have a lot of difficulty (impossibility) getting the
properties portion of the table to click open so I can see the fields.
I need to get in there to add new fields and so forth. In some cases,
I cannot even browse the tables to view the contents. Can someone
explain what the problem could be, and how to fix?

Strange SELECT Speed Problem Using Mysql 4.0.xx
i have a table with INT columns id,key,b1,b2,c1,c2, having 1.500.000
rows. 'key' and 'id' are indexed (Kardinality 385381) and id
(Kardinality 1541525).

Performing a

SELECT * FROM table WHERE key IN (10,11,12) OR key BETWEEN 20000 AND
28000 AND b1<4500000 AND b2>3954545 AND c1<4543554 AND c2>4400000
ORDER BY id LIMIT 0,100000

on that table (linux) results in ca. 100 lines within 70..80 ms (like
expected), but sometimes it takes 10 sec(!) and more although an
EXPLAIN SELECT tells me that the index of 'key' will be used:

table type possible_keys key key_len ref rows Extra
table range key key 5 NULL 11393 Using where;
Using filesort

Doing the SELECT with changing one of the IN()-values results
afterwards is quite fast SELECT's, seems that the caching of the
underlying file system is responsible for the faster speed.

2 Questions:

1. Why are there so big time differencies SELECT's like above
_although_ the 'key' index is used ?

2. How can one optimize mysql SELECTS for the table above? Are there
optimizations in mysql´s ini file for large tables (small tables dont
show such speed diefferencies) ?

Strange Results When Trying To Do A Duplicate Entry Check
My goal with this is to check a database for duplicates using the first 25 characters of the "title" column in a database called "jos_content." The query should find the entries whose titles appear more than once and then the second part should take those entries and mark them for trash (state=-2 is trash).

CREATE TEMPORARY TABLE dupes
SELECT Left(title,25)
FROM jos_content
GROUP BY title
HAVING COUNT(*)>1; # Affected rows: 4

UPDATE jos_content
SET state="-2"
WHERE (title) IN (SELECT Left(title,25) FROM dupes); # MySQL returned an empty result set (i.e. zero rows).

The result for the first part finds entries but I suspect they aren't duplicates becuase the second part returns zero entries. The actual database has about 16 duplicate rows based on the first 25 characters of the title column.

Strange Password Setting Behavior In Fedora Core 4 (server 4.1.11)
I have tried adding users aided by several references and this typifies the procedure:

http://dev.mysql.com/doc/mysql/en/adding-users.html

But, I found that after adding them, I could not use them to get into mysql. I found my way to this reference,

http://dev.mysql.com/doc/mysql/en/access-denied.html

and on a hunch, set the password to '', that is, no password at all and find that now I can use them to get in. What is going wrong here? Also, I am finding that I cannot get into my database from a remote host, even though I use the '%' variable or even more directly, the actual 'RemoteHost' name.

Mysql Is Giving The Previous Month, Not This Month, Strange!
PHP Code:

$myquery = @mysql_query("select year(sendon) as yr, month(sendon) as mth, count(*) as hits from ebook_user group by yr, mth");
    while ($myrow = mysql_fetch_array($myquery)) {
    $mymonth =  $myrow['mth'];
    $myyear =  $myrow['yr'];
    $myhits = $myrow['hits'];
    print '<td bgcolor="#E7E7E5">'.date("M", mktime(0, 0, 0, $mymonth, 0, $myyear)).' '.$myyear.' </td><td bgcolor="#E7E7E5"> '.$myhits.'</td>'
    }

The above code is printing the previous month instead of current month ... can any one help?

Create Table Error. #1064 - You Have An Error In Your SQL Syntax; Check The Manual That Corresponds
I have been trying to create two tables with the SQL below. I have the SQL in file and tried to import it to PhpMysqlAdmin in my control panel. The wierd thing is that the first table gets created while the second one is not created and error is thrown instead. The error message is

#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 ....

ERROR 1010 (HY000): Error Dropping Database (can't Rmdir '.a', Errno: 41)
I am using MySql 5.0.21-community Edition for widows XP. I can successfully connect to the MySql Server as a root and create a database 'a'. Which creates a directory 'a' under my mysqldata directory with a single file 'db.opt'. Now dropping the database 'a' gives the following error

ERROR 1010 (HY000): Error dropping database (can't rmdir '.a', errno: 41)

The directory mysqldataa does not have any other files or hidden files. The commands I have executed are:

------------
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6 to server version: 5.0.21-community-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> create database a;
Query OK, 1 row affected (0.09 sec)

mysql> drop database a;
ERROR 1010 (HY000): Error dropping database (can't rmdir '.a', errno: 41)
mysql>
---------

I have searched web, lists, bugs and could not find any.

Error 1064 Restoring PhpMyAdmin Dump (error On Foreign Key Clause)
I am getting an Error 1064 when I try to restore a dump I created using phpMyAdmin from an online database into a newly created database on my home system.

The error points to a section of the dump file that adds foreign key constraints.

I've already disabled the foreign key checks. The problem seems to occur only with tables in which I have more than one foreign key.

The dump file foreign key constraint section looks very odd to me because multiple ALTER TABLE statements to add foreign keys are separated only by commas, not semi-colons, and even weirder, each time a new statement to add a foreign key constraint is created, it is then repeated in the creation of the next foreign key constraint. Code:

Strange Error On A Mysql_query