MYSQL :: Error In "create Table Keyword

What Does CREATE TABLE Phpbb_anti_robotic_reg Mean? (was "Mysql Help")
Hey im trying to install a hack onto my forum, which isnt really the important part. Ive done the steps until i come to the MYSQL part which reads the following:

Quote:

CREATE TABLE phpbb_anti_robotic_reg (
session_id char(32) DEFAULT '' NOT NULL,
reg_key char(5) NOT NULL,
timestamp int(11) unsigned NOT NULL,
PRIMARY KEY (session_id));

Single Query For No Of Hits "Today", "This Month", "This Year"
Is it possible to construct a single query that fetches the no of pageviews "today", "this month", "this year" and "total since start"?

The table is very simple, one field for Date (YYYY-MM-DD) and one for the no of pageviews this day.

If not, would it be much slower/faster to divide the query into 4 different queries?

"create Table" Speed
I'm wondering if there's any way to speed up create table queries?
Besides upgrading hardware, that is. The very simplest table creation
query, "create table table1 ( field1 INT(10))" is taking about .03
seconds, which compared to other queries (large inserts at .01 seconds)
and previous experience appears inordinately long. Further, it appears
that most of that .03 seconds is some kind of overhead, since the
complexity of the create table query appears to be irrelevant.

Running: Debian Woody with dotdeb packages.

ERROR 1206 (HY000) At Line 41: "The Total Number Of Locks Exceeds The Lock Table Size"
I'm loading a large file into a table, and I get this error:

"ERROR 1206 (HY000) at line 41: The total number of locks exceeds the lock table size"

I already searched, and the only advice given was to do an explict full-table lock on that table before loading to avoid the row-by-row locks filling up the lock table. Well, I tried that, and it's not helping either.

Here's my log:

Table Does Not Exist (was "Database Error")
Database error: Invalid SQL: update sponsors set status=-1 where end_date<=&#55614;&#57158;-07-22'
MySQL Error: 1146 (Table 'chinese.sponsors' doesn't exist)
Session halted.

What does this mean ?

"The Table Is Full" Error.
I have been looking through the logs for our content management system, and have found this error: "The table 'CMS_BACKUP_FILES' is full"

This is a table in the mysql database that is running on the same machine as our intranet. Can anyone help me resolve this issue as I have zero to limited MySQL experience and training.

How Can I Re-create "mysql.sock" File?
When I attempt to connect to my MySQL instance, I have been receiving the error:

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

I checked and there is no "mysql.sock" file in the directory "/var/lib/mysql" where it should be.

How can I re-create the mysql.sock file?

"ERROR 1064 (42000): You Have An Error In Your SQL Syntax " When Trying To Import Backup
Just upgraded to the latest version of mySQL. Before I did that, I did a full backup of all tables using the Administrator tool. Now when I try to do a restore, I get the message in the subject. Even if I copy-paste the SQL code into the command line client, I get the error. For example, I use this code:

CREATE TABLE "news" (
"news_id" int(4) NOT NULL auto_increment,
"news_subject" varchar(96) NOT NULL default '',
"news_author" varchar(96) NOT NULL default '',
"news_email" varchar(96) NOT NULL default '',
"news_body" longtext NOT NULL,
"news_time" int(10) NOT NULL default '0',
PRIMARY KEY ("news_id")
) TYPE=MyISAM DEFAULT CHARSET=latin1;

And I get this error:

ERROR 1064 (42000): 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 '"news
" (
"news_id" int(4) NOT NULL auto_increment,
"news_subject" varchar(96' at line 1

"create" Cannot Make Innodb
I'm trying to get mysql installed on OpenBSD 3.4. Unfortunately, it appeares that the port only installes the mysql-client, and one needs to install the -server package seperately. This would be fine with one exception, there is a dependency that breaks when installing from ports. This lead me to install from source, and now I'm having problems just getting mysql to start.

If anyone could look at the error message below:

Arch/version info:

bash-2.05b# uname -a:
OpenBSD orthrus.--------.com 3.4 GENERIC#18 i386

mysql -v :
/usr/local/libexec/mysqld Ver 4.0.20 for unknown-openbsd3.4 on i386
(Source distribution)

Command output:

bash-2.05b# /usr/local/libexec/mysqld -u mysql
040622 15:29:27 InnoDB: Operating system error number 2 in a file
operation.
InnoDB: See http://www.innodb.com/ibman.php for installation help.
InnoDB: The error means the system cannot find the path specified.
InnoDB: In installation you must create directories yourself, InnoDB
InnoDB: does not create them.
InnoDB: File name /usr/local/var/lib/mysql.innodb/ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

Searching For Numbers - "close To" Or "approximate"
I would like to know how to search a table for records where a particular field is "close to" a particular number.

For instance, let's say I have a table containing students and their most recent test scores. I'd like to see students who scored 7/10. That's easy enough (select * from table where score = 7). I'd also like to run another query to identify other students whose scores are not 7 but "close to" 7. Ie students who scored 6 or 8.

As an added bonus, I'd like to be able to do a related search that shows students who scored 7, then shows the other students ordered by how "close" they are to 7. Ie students who scored 6 or 8 are "closest" matches, and students who scored 1 are "furthest". I hope this makes sense!

Is there any pre-existing MySQL command that will do this? Or will I have to run separate queries for each value?

REQ: MySQL 4.0 Equivilent Of "DateDiff("m", Date, Now())=1"
I have a query that works in the rest of the SQL world

"SELECT invoice.*, invoice.Date
FROM invoice
WHERE (DateDiff("m", Date, Now())=1);"

which will give me all of last month's invoices.

However it doesn't work with MySQL 4.0.

While

"SELECT
Invoice.*
FROM
invoice
WHERE
(Month(Invoice.Date) = ((Month(Now()))-1))"

Is a close substituent, it will blowup in January.

Anyone have an equivalent expression for MySQL 4.0?

Maximum Execution Time Of 300 Seconds Exceeded (was "MYSQL Error")
I have a huge database 400mb+ in size which i have exported into a .sql file. I tried to run this export script into a new database and it runs fine until three quarters into the scripts execution and the following error appears:

ERROR 1064 (42000): 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 '<br / >
<b>Fatal error</b>: Maximum execution time of 300 seconds exceeded in <b' at li ne 1
I believe this error may relate to "delayed_insert_timeout = 300". How do I increase this limit in mysql - maybe it may solve the problem but then again it may not .

Any advice is most gratefully appreciated - As you can imagine if the script runs fine for 3/4 of it and then an error appears, stating the error is on line 1, it will take me days to go through the whole script (hundreds of thousands of rows) to find the error as I assume the error does not reside on line 1 as this line would have been executed.

I am running the script on mysql 5, and via source /path/****.sql on linux.

Error "Can't Find FULLTEXT Index Matching The Column List"
I'm trying to use fulltext search with mysql match() against() but i keep getting this error
Can't find FULLTEXT index matching the column list

even though i already altered the table to make the column im searching FULLTEXT

any ideas?

Encountering 2013 Error ("Lost Connection To MySQL Server...")
I have a nightly job that performs backups on my MySQL servers. There are three production MySQL instances on different machines. My backups have been running successfully for months (until now).

However, on last night's backups for one instance, a couple of database/schemas (appear to have) backed up successfully, but the larger schemas failed, as follows:

=======================================
Backup of schema dhunt was successful in 0 seconds
Backup of schema mysql was successful in 0 seconds
Backup of schema support_appl was successful in 0 seconds
Error: Backup of schema support_dict was not successful (1115 seconds):
mysqldump: Got error: 2013: Lost connection to MySQL server during query when retrieving data from server
Error: Backup of schema test was not successful (0 seconds):
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect
=======================================

I re-ran the backup cron job with the same results. Does anyone have any troubleshooting suggestions?

"supplied Argument Is Not A Valid MySQL Result Resource" Error
i am getting this error with the following code:

$query = "SELECT title, firstname, surname, address1, address2, address3, postcode,"
."tel, mobile, email"
."FROM user"
."WHERE username='$valid_user'";

us.skill_id=s.skill_id"

$result=mysql_query($query);
$num_rows=mysql_num_rows($result);
$row=mysql_fetch_array($result);

The error is referring ot the last 2 lines of this script.

Invalid Use Of Group Function (was "Baffled By Query Error")
trying to figure out why I keep getting this error with the following query:

SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins
FROM account a LEFT JOIN calls c ON c.account_id = a.id
WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2
AND SUM(c.agent_product_time) >= '500' GROUP BY c.account_id ORDER BY mins

ERROR: #1111 - Invalid use of group function

ERROR 1045: Access Denied For User: '"root"@localhost' (Using Password: YES)
I have been working on this for three days (about 16 hours) to no avail. I have root access on a dedicated server running whm/cpanel on Redhat 9. I have followed every instruction from mysql.com as well as every suggestion I've been able to find.

These are the two errors I bounce between:

ERROR 1045: Access denied for user: '"root"@localhost' (Using password: YES)

and

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

I'm out of ideas.

How To Change "#1226 - User 'root' Has Exceeded The 'max_questions' Resource" Error
I seem to have set a max_questions variable on my system and my admin console and command prompt don't work with this response:

"#1226 - User 'root' has exceeded the 'max_questions' resource"

How to fix?

And how do I not make thisw mistake again?

Error: MySQL Client Ran Out Of Memory (was "Urgently Need Help")
I'm having a big trouble with this query
select *, UNIX_TIMESTAMP(date_submitted) as postedon,
UNIX_TIMESTAMP(DATE_ADD(date_submitted,
INTERVAL auction_period DAY)) as expiry
from
bidsnbuys_products
where
approved='yes'
It works before but lately we've got a huge records and if
our customers search a product it will give them an error,
I even tested by myself and the error is

MySQL client ran out of memory
Is there a better mysql query with my query above?

User_id And Password Error? (was "mysql Query")
Following Query right or not

$sql = "SELECT * FROM login WHERE user_id = ".$username." AND password = ".$passwd."";

if error then point out error

Error Importing The .sql File (was "MySQL Problem")
I ahve just exported about 8-10 of my tables from one database into a .sql file and then tried importing the .sql file into phpmyadmin on my local localhost serer and am getting this error?

You have an error in your SQL syntax near 'ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

INSERT INTO `atm_areas` ' at line 22

Why?

Getting Dreamweaver 8 To Work With MySQL: The "Unidentified Error"
I set up MySQL on my Macintosh at my job (G4 / OSX 10.3.9) 3 days ago, along with PHP --

all went fine, & I got MySQL started, a DB set up -- until I tried to connect Dreamweaver to it I go to connect to the DB in Dreamweaver 8, and when I try to set up the DB in the "databases" tab and click on the "database" field I get :

"An unidentified error has occurred" Code:

Error "cannot Connect Server On Local Host".
i cannot connect to MySql database at my server.F.Y.I i install MySQL 5.0 in my server 2003.when i try to connect from other pc its show error "cannot connect server on local host".how can i solve it.

DEFAULT CHARSET Error (was "Odd MYSQL Error!! :(")
I keep getting this error

PHP

Error
SQL query:

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 31, 2006 at 09:06 PM
-- Server version: 4.1.19
-- PHP Version: 4.3.11
--
-- Database: `jesse_game`
--
-- Table structure for table `games`
--
CREATE TABLE `games` (

`gameid` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`gametitle` text NOT NULL ,
`gamedesc` text NOT NULL ,
`gamekeywords` text NOT NULL ,
`gamefile` text NOT NULL ,
`gameheight` int( 11 ) NOT NULL default Ɔ',
`gamewidth` int( 11 ) NOT NULL default Ɔ',
`category` int( 11 ) NOT NULL default Ɔ',
`timesplayed` int( 11 ) NOT NULL default Ɔ',
`totalvotes` int( 11 ) NOT NULL default Ɔ',
`totalvotepoints` int( 11 ) NOT NULL default Ɔ',
`rating` int( 11 ) NOT NULL default Ɔ',
`gameicon` text NOT NULL ,
`gamestatus` int( 11 ) NOT NULL default Ɔ',
`playedtoday` int( 11 ) NOT NULL default Ɔ',
`gamelocation` int( 11 ) NOT NULL default Ɔ',
`iconlocation` int( 11 ) NOT NULL default Ɔ',
`instructions` text NOT NULL ,
`filetype` int( 11 ) NOT NULL default Ƈ',
`customcode` text NOT NULL ,
PRIMARY KEY ( `gameid` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1515;

MySQL said:

#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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1515' at line 23

PHP

#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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1515' at line 23

Bugzilla: MySQL Returns "Got Error 127..."
Our bugzilla database started giving this error during last week sometime. "Got error 127 from table handle". I Googled it, and came to believe that "repairing" and "optimizing" the table should sort out my problem, only to be shocked to see that over 10 000 records have been deleted by the repair/optimise. You see, this is not an option. We have over 18 000 bugs logged on our system, and as an organising our size, we need to comply to certain SAS70 requirements. Losing 10 000 records on our bugs database will fail the entire organisation the SAS70 reward, and guess what... it's on my plate, and if I can't do anything about it... people will talk bad about me for long after I've left the company.

I understand that the table records has gone corrupt. How it went corrupt I don't know and why it was working fine with over 10 000 corrputed records for the last 2 years, I also don't know. But right now it's not my job to understand it... I need to fix it, and keep every record in tact.

I've done a restore of the data all the way back to the day just before it crashed. When I ran a repair and optimize then, it did the same thing... over 10 000 records dissapear. PLEASE HELP. I am despirate!

Too Many Tables Error (was "I Feel Like I Am Missing Something")
I am new to administrating servers, and I just got myself a dedicated server and I am trying to do some things...

Right now I wanted to make a backup of a mysql db but I get an err 24 because I the db has too many tables (over 16000).

I want to edit the open_files_limit so I can get past this error, but I dont know where to edit!

All the places I read say I should edit my.cnf, so I use vi /etc/my.cnf and I get this:

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
After searching around this doesnt look right because all other files I've seen have way way more info in them.

Did I miss a configuration step somewhere, or am I editing the wrong file?

I am running Fedora Core6 with plesk.

Subquery Gives Syntax Error (was "A Sql Problem")
i am in deep trouble while executing this query on the server having mysql version " MySQL - 4.0.25-standard"

The query is :- Select * from table1 where column1 IN ( "Select DISTINCT column2 from table2") ORDER BY column1 DESC limit 0,20
it gives no results , i have seen that both tables have records corresponding to them.

i have tried to eliminate quotes from the subqurey then it gives an syntax error

while it is working at my localhost. having mysql 5.0.27-community-nt

The query here runs as Select * from table1 where column1 IN ( Select DISTINCT column2 from table2) ORDER BY column1 DESC limit 0,20

if i put quotes here then it gives no results.

Error : "cannot Be Part Of FULLTEXT Index"
in order to make it possible to do a fulltext search on multiple columns I created a temporary table which holds all the fields I want to search in from different tables. I'm creating a temporary table, create an fullindex on it, search in it, drop it. On our local server this works fine. But when I perform the code on the server of our client I get the following error message:

"Column 'manufacturertitle' cannot be part of FULLTEXT index"

This is caused by:

"CREATE FULLTEXT INDEX full_index ON SearchTemp(producttitle, description, description2, manufacturertitle );"

In the original table the field 'manufacturertitle' has the following properties:
Type: varchar(255)
Collation : utf8_general_ci
Null : Yes

I found the following solution somewhere but I have no to clue what to do now:
"As of MySQL 4.1.1, full-text searches can be used with most multi-byte character sets. The exception is that for Unicode, the utf8 character set can be used, but not the ucs2 character set.".

"Illegal Mix Of Collations" MySQL Error
What I am attempting to do with an SQL query is grab data from multiple tables for outputting to an Excel spreadsheet file using PHP. Each row should show details for each member, as queried from multiple tables (using the field 'PersonalID' as the primary/foreign key). The query I have is as follows:

SELECT tblcoreuser.PersonalID, tblcoreuser.usrForename AS Forename, tblcoreuser.usrSurname AS Surname, tblcoreuser.voucher AS Free_Status, tblcoreuser.Joined AS Date_Joined, tblcoreuser.Package AS Membership_Package, tbladdress.Address1, tbladdress.Address2, tbladdress.County, tbladdress.Postcode, tblcredits.numCredits AS Remaining_Credits

FROM tblcoreuser, tbladdress, tblcredits

WHERE tblcoreuser.PersonalID = tbladdress.PersonalID AND tblcoreuser.PersonalID = tblcredits.PersonalID

ORDER BY tblcoreuser.PersonalID DESC;
The error being returned is:

Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

I've tried removing certain parts of the query, but cannot seem to pinpoint the exact cause of the problem. I'm sure I'm missing something simple -

Why Did INSERT DELAYED Get An Error? (was "Mysql Help!")
I have a mysql databae that gets pounded with INSERTs to basically an address book.

1) Is this high:
Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic Ã¸ per hour
Received 37,793 KB 2,581 KB
Sent 1,128 MB 78,912 KB
Total 1,165 MB 81,493 KB
Connections Ã¸ per hour %
Failed attempts 6 0.41 0.01 %
Aborted 0 0.00 0.00 %
Total 71,281 4,867.81 100.00 %

"Unable To Save Result Set" Error
I keep getting the following error.

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/mysite/public_html/demo.php on line 303
Invalid query

That error is this line, which you can see at the bottom of the code below:

$result = mysql_query($sql) or die("Invalid query") . mysql_error();

Here is the PHP Code:

"system Error 5, Access Denied"
I have just installed mysql, and get this error when I type "net start mysql" in command prompt. I am using ms Vista. Is this due to not having access rights to the directory? Have tried unchecking read only for the relevant directories but although it seems to accept this, when I check again, the read only is still checked!

Value "000" Stored As "0" In Text Field?
I've managed to import my data from a CSV using SQLyog Enterprise, however, I have a problem with the table storing the text value "000", MySQL truncates it to "0", unfortunately, I need the value to be stored as "000", does anyone know of any way that I can force the field to store all the characters, rather than treating it as a number? Surely a text field should treat any numbers stored within it as text?

What Is The Meaning Of The Letters "AB" In "MySQL AB"?
What does "AB" stand for in "MySQL AB".

Return A "true" Or "false"
i wanna do a "select", where if the script finds any matches, i dont wanna know about them. Instead, i just want it to return a a value: true (1) ou false (0).

ie.:

$sth = $dbh->prepare("SELECT * FROM maps WHERE name='algo' LIMIT 1");
$sth->execute();

if (match is true) {
print "True Match. A match was found";
}
else {
print "False Match. No matches were found.";
}

"Group By" Forgets "Order By"
I'm trying to build a simple RSS reader for a client and am running into some serious problems with the GROUP BY in MySQL. Basically I have two tables: one that holds all the entries and one that holds all the feeds. Then what I can do is say:
[MYSQL]SELECT * FROM entries WHERE feed_id='13' ORDER BY date DESC[/MYSQL]
That will select all the entries that belong to feed #13. This is fine, but what I'm doing now is building a library that will display the feed_id with the latest entry with that id. So I would think it would be something like the following:
[MYSQL]SELECT * FROM entries GROUP BY feed_id ORDER BY date DESC[/MYSQL]
That should get me the single latest entry from each feed_id. However it's forgetting the ORDER BY clause all together and seemingly ordering it by the auto-incrememnt value in the entries field.

"Operand Should Contain 1 Column" Error
SELECT actual_time.TimeDiffWithNext,

case when TimeDiffWithNext is null then timediff(EMPShift.DueTime,actual_time.TimeDiffWithNext) else
timediff(EmptranslogP.HistDueTime,actual_time.TimeDiffWithNext)
end as absence ,
case when TimeDiffWithNext is null
then timediff(actual_time.TimeDiffWithNext,EMPShift.DueTime)
else
(actual_time.TimeDiffWithNext,EmptranslogP.HistDueTime)
end as overtime,
timediff(lper.lto,lper.lfrom) as personal_leaves,
timediff(lwork.lto,lwork.lfrom) as work_leaves
why would this part of my query generate and error
"Operand should contain 1 column"

"Encoding Or Conversion Error". DB From 4.0.25 > 4.1.19
I'm attempting to import a 20mb database from a Win2003 IIS6 box which is running PHP 4.3.11 and mySQL 4.0.25-nt to a dedicated Linux (Centos) running 4.1.19. The script is for a image hosting site with around 200,000 images and 10,000 users.

When I run the import, it begins uploading then times out stating "Script timeout passed, if you want to finish import, please resubmit same file and import will resume.", I hit go again then after a few moments I get the following error,

Warning: mb_strpos(): Unknown encoding or conversion error. in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/string.lib.php on line 112 ...

"Error Code : 1449"
when I try to delete from table where idx = 1
I got error

Error Code : 1449
There is no 'username'@'%' registered
(0 ms taken)

I look for this error
but I can get a good solution

how can I solve my prolem.

"Can't Open File" Error
I have dragged two tables from the "data" folder of one computer, put them on a USB key and brought them with me to work. At work I also have mySQL installed, so I simply found the mySQL folder, went into the Data folder and dragged the tables which have .frm extension into a folder within this Data folder.
Now I can see the tables there using "mysql>show tables;"
But when I type "select * from mytable", it gives me this error;

Code:

ERROR 1016 (HY000): Can't open file: 'mytable.ibd' (errno:1)

I don't even know where the .ibd extension come from

How Can I "see" A Table In A Database On A Remote MySQL Server After Creating It
I used the following SQL to create a new table in a database on
a remote MySQL server by copying one already there. I know the
table exists SOMEWHERE in cyberspace. I can read its data, write
to it, delete from it. But I cannot see it. The only way I know it
exists is by running this SQL from Access 97 pass through query:

SELECT ALL new_tbl.name FROM new_tbl

The database resides on a MySQL server that was created with
a single table (named test) in it for testing purposes. I ran the
following SQL to create another copy of the table in the same
database named: new_tbl

CREATE TABLE new_tbl SELECT * FROM test;

I cannot see the new table in the Access 97 database window
under the Tables Tab. Anybody know how to overcome this?

Its a severe drawback to programming efforts not to KNOW what
tables are in your database.

Multi-table UPDATE Problem (was "mysql Question")
$sql = "UPDATE char_lair , char_main m SET l.zip_id='$zip',
l.lair_addr='$address', m.alias='$alias', m.real_name='$name',
m.align='$align' WHERE m.id = $cid AND m.lair_id = l.id";

I am using mysql v.3.23, I got error in this code but can't find the solution.
The message displayed in browser is:
---------------------------------------------------
UPDATE char_lair , char_main USING char_lair l, char_main m SET l.zip_id='z3', l.lair_addr='a3', m.alias='ch3', m.real_name='rn3', m.align='good' WHERE m.id = 2 AND m.lair_id = l.idYou have an error in your SQL syntax near ' char_main USING char_lair l, char_main m SET l.zip_id='z3', l.lair_addr='a3'' at line 1
=========================================
Is there any syntax error?

"table Is Read Only" After Copying Table Files To New Server
I'm trying to port my MySQL tables for a database called "tracerlock" from
one server to another. On the old server, in the /var/lib/mysql/tracerlock
directory, there was a ".MYD", ".MYI" and ".frm" file for every table in
the database. So after creating a database called "tracerlock" on the new
server, I copied these files over to the /var/lib/mysql/tracerlock
directory on the new server. On both servers, all the table files are
owned by user "mysql" in the "mysql" group.

Now, when I connect to MySQL on the new machine and load the tracerlock
database, "show tables" shows all the tables as expected, and I can perform
SELECT statements on them and get the correct results. But when I try
doing INSERT statements on one of the tables that was copied over, I get:

mysql> INSERT INTO test VALUES(3);
ERROR 1036: Table 'test' is read only

However, if I create a new table called test2, I can perform INSERT
statements on it with no error:

mysql> insert into test2 values(3);
Query OK, 1 row affected (0.00 sec)

Code:

Please Explain MySQL Distinct Table Types (was "Where To Use ?")
These are the MySQL distinct table types.

MyISAM
MERGE
ISAM
HEAP
InnoDB
BDB or BerkeleyDB Tables

so please anyone help me to find out the use of each n every type.
it will be very helpfull for me if you people can provide simple meanings rather than technical.
For Ex : why to use MERGE over HEAP or something like this
Your help is most welcome

Search Script That Searches "linked" Table
I'm designing an ecommerce store, and I am on to the "search the site" script. I had a simple script set up, but its missing an important part of the search.

The way the (2 relevant) tables are set up are like this:

product_information
Field Type
ID int(10)
product_code varchar(30)
product_summary text
categoryID tinyint(3)

category_lookup
Field Type
ID tinyint(3)
category varchar(25)
parent tinyint(3)
product_code is the product's unique number, category ID refers to the ID of the category that product belongs to (socks, shoes, etc. etc.), and `categoryID` is equal to the category_lookup field `ID`.

What I would like to be able to do is if someone searches socks, any product that has 'socks' in the product_summary or the category it belongs to would be returned. Is this going to be too taxing on the server? I'm new to doing queries at this level so I can't put the query in to the proper syntax. Is there a way to do this all in one efficient query? Or since I'd have to do a lookup for each product in the database, would it be better to first check to see if any of the keywords are in the category_lookup table, and return the ID's if they are. Then I can include just those ID's in the where clause?

Alternate Solution To "intersect" In A Single Table...
I am having a problem getting my desired outcome with my table.

Table: orders

+-------+-------+------+------+------+------+---------+
| ordno | month | cid | aid | pid | qty | dollars |
+-------+-------+------+------+------+------+---------+
| 1011 | jan | c001 | a01 | p01 | 1000 | 450 |
| 1012 | jan | c001 | a01 | p01 | 1000 | 450 |
| 1019 | feb | c001 | a02 | p02 | 400 | 180 |
| 1017 | feb | c001 | a06 | p03 | 600 | 540 |
| 1018 | feb | c001 | a03 | p04 | 600 | 540 |
| 1023 | mar | c001 | a04 | p05 | 500 | 450 |
| 1022 | mar | c001 | a05 | p06 | 400 | 720 |
| 1025 | apr | c001 | a05 | p07 | 800 | 720 |
| 1013 | jan | c002 | a03 | p03 | 1000 | 880 |
| 1026 | may | c002 | a05 | p03 | 800 | 704 |
| 1015 | jan | c003 | a03 | p05 | 1200 | 1104 |
| 1014 | jan | c003 | a03 | p05 | 1200 | 1104 |
| 1021 | feb | c004 | a06 | p01 | 1000 | 460 |
| 1016 | jan | c006 | a01 | p01 | 1000 | 500 |
| 1020 | feb | c006 | a03 | p07 | 600 | 600 |
| 1024 | mar | c006 | a06 | p01 | 800 | 400 |
+-------+-------+------+------+------+------+---------+

What I am trying to do is list only the entries from cid that have both 'p01' and 'p02' in the pid column.

In oracle(sqlplus) I did it like this:

select cid from orders where pid='p01'
intersect select cid from orders where pid='p02';

Any TABLE TYPE Be Able To Save And Retrieve "Diagram"?
I'm seeking a solution that is able to:

1. Draw a diagram (such as an organization chart or cause-and-effect diagram) and fill nodes with related data.

2. Then, save the data and diagram into MySQL.

3. Finally, retrieve both data and diagram from MySQL and display them in corresponding places.

Error In "create Table Keyword_relevance"