MYSQL :: Query Error (AES

Using AES_ENCRYPT For 256 Bit Encryption
I just started using AES_ENCRYPT to encrypt records in my databases. For the most part, the default 128 bit encryption is fine. But, I would like to use 256 bit for one of my forms. According to the MySQL documentation you can use the function for either 128 or 256 but I don't understand how to get it to do 256.

The documentation says:"Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source." - http://dev.mysql.com/doc/refman/4.1/...functions.html

What source do I have to modify to get it to use 256?

AES_ENCRYPT Function
Essentially I am playing around with the aes_encrypt fucntion to securly store some data in the db. The problem I am having is that I get the following error : Data truncated for column 'foo' at row 1 and the table creating was done with:

CREATE TABLE `foobar` (
`foo` varchar(10000) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

with a varchar(10000) and doing and AES_ENCRYPT('foo','bar') one would think that it would work. But I suspect that I am doing something incorrectly.

Aes_encrypt Values
I guess if explain my situation first it should help a little better...

I have a table with some empty fields and some numbers I wish to encrypt.
I perform a select statement to check how many fields are not empty (I get 56), e.g

"SELECT number from customer WHERE NOT number = '' ";

I then encode this using AES_ENCRYPT(number,keystr)

Then try the count query again as written above but this time I have fewer entries (I get 3) that are not empty.

Which is odd because I can then decrypt these records and I get my 56 records back again.

Any ideas why this may be?
I am using tiny blob as the field by the way, do I need to perform some sort of php string function on the numbers before storing them do you think?

AES_ENCRYPT And AES_DECRYPT
I have recently updated my system from using MySQL 4.0 to MySQL 5.0. The thing is, none of the encrypted fields are being decrypted correctly when using Connector/J. I did get the newest version of it but still no luck.

Aes_encrypt Aes_decrypt Problem
I'm using PHP 4 with mySQL. I'm using aes_encrypt and _decrypt to store customer information as securely as possible. The problem crops up when I go to store (or retrieve -- I'm not sure which) the state that the customer lives in.

AL works for Alabama
GA works for Georgia
fl works for Florida
FL returns an empty string.

Does anyone have any ideas on what's happening here?

AES_ENCRYPT In Visual Basic
I'm writing an app with vb & mysql.

for the user login - i tried aes_encrypt and vb reports function or sub not defined.

How To Calculate The Size Of An AES_ENCRYPT() Result
what I want is to calculate the result string for an AES_ENCRYPT() function, in the mysql manula says:the result string length may be calculated as 16*(trunc(string_length/16)+1) but I dont know what value trunc has.

Could any one please explainme how to calculate the max size of an 10 chars max wide password value?

Query Error
I'm receiving a query error and im not sure why. It is supposed to duplicate a specific set of rows in a table. Which rows is determined by a different table. Is therea clear syntax error?
MySQL
INSERT INTO listings VALUES (SELECT * FROM listings WHERE id = (SELECT list_id FROM groupings WHERE group_id IN(2) ) )

The MySQL error said it was
Quote:
near ''(SELECT * FROM listings WHERE id = '(SELECT list_id FROM g'

e39m5

Many To Many Query Error
I am trying to write a query that pulls data from three tables, so that I can list the purchases of an individual client.

client, client_purchase and purchase

More than one client can be involved in a purchase, so the client_purchase is a linking table.

My bad query is as follows,

select
CLIENTid,
CLIENTfirstname,
CLIENTlastname,
purchase_contract_no,
from client
inner join
client_purchase on
client.CLIENTid = client_purchase.CLIENTid
inner join
purchase on
purchase.purchase_id = client_purchase.purchase_id
WHERE CLIENTid=Ƈ'

The database tables are as follows:

CREATE TABLE `client_purchase` (
`client_purchase_id` int(11) NOT NULL auto_increment,
`CLIENTid` int(11) NOT NULL default Ɔ',
`purchase_id` int(11) NOT NULL default Ɔ',
PRIMARY KEY (`client_purchase_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE `client` (
`CLIENTid` int(11) NOT NULL auto_increment,
`CLIENTtitle` varchar(255) NOT NULL default '',
`CLIENTfirstname` varchar(255) NOT NULL default '',
`CLIENTlastname` varchar(255) NOT NULL default '',
`CLIENTemailaddress` varchar(255) NOT NULL default '',
`CLIENTmobilenumber` varchar(255) NOT NULL default '',
`CLIENTphonenumber` varchar(255) NOT NULL default '',
`CLIENTpassword` varchar(30) NOT NULL default '',
`CLIENTuserlevel` int(11) NOT NULL default Ɔ',
PRIMARY KEY (`CLIENTid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE `purchase` (
`purchase_id` int(11) NOT NULL auto_increment,
`purchase_name` varchar(255) NOT NULL default '',
`purchase_contract_no` int(11) NOT NULL default Ɔ',
PRIMARY KEY (`purchase_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Error With Query
I have a query that I have been using for sometime now. Then all of a sudden today the query is not working. Is there something wrong with the query that you can tell?

my($query) = "SELECT rowid,category,price,name,image,rand((rowid)+curtime()+0) as rand FROM $sc_mysql_prd_table order by rand LIMIT 3";

Query Error
I need to select from a table called almacen all the records that don't exist in the table ped_almacen... here's my query:

SELECT almacen.I_producto FROM ped_almacen,almacen WHERE ped_almacen.I_producto != almacen.I_producto AND ped_almacen.I_pedido = $npedido

it shows nothin' when I have no items in the ped_almacen table (should show me all the records in the almacen table) when I have at least one record or n-1 (n -> total of records) the query shows the rest of the records that aren't in the ped_almacen table (that's fine), when every record in the almacen table is in the ped_almacen table the result is all the records (that's also wrong, should showme none of the records)

Error In Query:
I was sent a query which was in oracle... and now i have to run it in sql... but there is an error and i dont know what it is...
please help me with this error.

create table order
(order_id integer, order_created_date DATE,
customer_id number references customer(customer_id),
billing_address_id int references customer_address(address_id),
order_status varchar(10) check in ('New','Processing','Closed'), primary key (order_id)
);

Query Giving Error
when I try to run the following query,I get the error:
#1064 - You have an error in your SQL syntax near 'select min(dt_startdate) from tbl_bookings)>&#55614;&#57158;-11-09' GROUP BY p.id_property ' at line 1

query: SELECT p. * , f. * , ph.nme_photo, ph.nme_caption, p.id_property
FROM tbl_bookings b, tbl_propertyfeature f, tbl_propertyphoto ph, tbl_property p
WHERE b.id_property = p.id_property
AND f.id_property = p.id_property
AND p.id_property = ph.id_property
AND ph.flg_main =1
AND p.flg_active =1
AND p.blnSpecial = 'Y'
AND b.dt_startdate
IN (

SELECT min( dt_startdate )
FROM tbl_bookings
) > &#55614;&#57158;-11-09'
GROUP BY p.id_property

I am using MySql 3.23.58

Error:Query Was Empty
What could be the cause of the above error
message to appear along with my error echo messages.

Error During Query Executing
Here's my QueryString

Quote:

SQL query:

UPDATE `modules` SET `module_title` = 'Diễn đÃ n' WHERE `module_id` =7

And received error message

Quote:

MySQL said: Documentation
#1406 - Data too long for column 'module_title' at row 1

My modules Table is using latin1_bin collation
and before time, it's work well with mySQL 4.

Query Syntax Error
I'm getting a syntax error near '$from, $max_results' in my basic select statement.

f(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

// Define the number of results per page
$max_results = 3;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

// The basic SELECT statement
$select = 'SELECT DISTINCT id, job_description, companyname'
$from = ' FROM job'
$where = ' WHERE 1=1'
$max = ' LIMIT $from, $max_results'

$eid = $_POST['eid'];
if ($eid != '') { // An employer is selected
$where .= " AND employerid='$eid'";
}

$cid = $_POST['cid'];
if ($cid != '') { // A category is selected
$from .= ', jobcategory'
$where .= " AND id=jobid AND categoryid='$cid'";
}

$lid = $_POST['lid'];
if ($lid != '') { // A city is selected
$where .= " AND cityid='$lid'";
}

$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
$where .= " AND job_description LIKE '%$searchtext%'";
}

$jobs = @mysql_query($select . $from . $where . $max);
if (!$jobs) {
echo '</table>'
exit('<p>Error retrieving jobs from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}

MySQL Error In Query
Can anyone see why this doesn't work? Is it becuase I am joing the . with brackets?

PHP

WHERE tblmember.MONTH(DateOfBirth) = '{$theMonth}') AND `member`='J'

Query Nesting Causes Error
In order to create statistical analysis I use the following statement:

SELECT MIN(tmp.tu), MAX(tmp.to), AVG(tmp.is), STD(tmp.is), AVG(tmp.so) FROM (SELECT * FROM table ORDER BY table.ts LIMIT 1000) tmp WHERE tmp.so IS NOT NULL

Without using the nested select-statement mysql delivers correct results.
If I include the second statement, sql answers throws error 1064 and marks the nested part of the query.

I Need To See Entire Query On Error
Right now I only see a message showing me the point after some error occurs. While that is extremely helpful it would be great to also see the entire query at the end of the error message. Is there some way to tell mysql to return the entire query when there's an error?

Update Query Not Working: SQL Error
i'm trying to do an update such as ...

Error While Connecting To The DB Using Query Browser
I am a newbie to mySQL world. I have sucessfully installed the mySQL and also have created a schema and a few user accounts. However when I try to connect to the DB from a REMOTE machine using the mySQL Query Browser, I get the following error:

"MySQL Error Number 2003 - Cant connect to mySQL server on 'myserver.mydomain.org'". However, I am successfully able to ping the server. Here are the specifics:

1. The Host Server where mySQL has been installed is at my home and is connected to the internet via a Broadband Linksys Router.

2. As I do not have a static IP, I registed for a Dynamin DNS service. I made sure that the port 3306 is opened, both at the router and on the machine where mySQL is installed.

3. I am trying to connect to the mySQL server from my home laptop, which is also behind the same Linksys router. I have also tried connecting from a truely remote machine (the one which is not behind this router, but is connected to internet) - again unsuccessfully.

4. The user account using which I am trying to connect has an 2 entries in the mysql.User table - one with column Host=localhost and another with column Host=%.

5. AFter a bit of googling, I found out that this is a common issue when BIND_ADDRESS and SKIP_NETWORKING are left uncommented in my.cnf file. However, I do not have a my.cnf file in the filesystem. And I have not created one..

6. I am able to connect to this mySQL server using SSH (using mysql -h localhost -u userName -p)even from a remote machine.

However I am not able to connect using mySQL query browser. Am I still missing something here?

Query Error With Alpha Input
I am trying to look for multiple values in a mySQL table. For example, I am trying to pull 3 records based on CPT (5 alpha numneric characters).

For example, I am trying to pull:
99201
A0040
99205

If I use this select criteria, I get the first record but then nothing else:

Possible Error In Mysql Query Optimizer?
We have a complex statement created from a web application which does
not work correctly. During analysis, we could reduce this statement to
the following:

SELECT * FROM table WHERE
((row1>=0 AND row1<=2 AND 1) OR 0) AND row2=1;

"row2=1" is not being computed, we also get results with row2<>1;
When we swap the values of our last "AND" like:

SELECT * FROM table WHERE
row2=1 AND ((row1>=0 AND row1<=2 AND 1) OR 0);

everything works fine, the same when the second OR parameter is "1"
like:

SELECT * FROM table WHERE
((row1>=0 AND row1<=2 AND 1) OR 1) AND row2=1;

Please, don't ask about the strange "... AND 1) OR 1)...", in the
original statement it is not a "1" or "0" but more comparisons, which
in this case evaluate to "1" and "0".

Is there something we do not see or is something wrong with the query
optimizer of mysql? We are using mySQL 4.0.21-nt.

No Index Defined Error When Query
Will anyone tell me what's wrong with the following select statement:
select * from email where (MdnName like '%".$lastName."%') This query does not return anything although there are records in the database that matches the criteria. I'm querying the contents from a php page with this code:

$query = "select * from email where (MdnName like '%".$lastName."%')";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

When I tried runing the same select statement in PHPMyAdmin, I received this error: No Index Define!

UPDATA Query Syntax Error
The following query works in mysql prompt as well as in mysql query browser

update history set empname = 'EMMANUEL SHANTHAKUMAR A.', desgcode = 16, deptcode = 7, stagecode = 19, classcode = 3, pmtmode = 1, pencode = 0 , code80 = (20000.0) where empno=27222 and syear = 2006 and smonth = 7 and hsttype = 1

but the same is not working through jdbc and it gives the following message.

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 'where empno=27222 and syear = 2006 and smonth = 7 and hsttype = 1' at line 1

Error 1604 With INSERT Query
I've a problem with an INSERT query i want to launch in Mysql 5 RC. In previous versions this worked correct however it stopped working correctly from Mysql RC5 on.

The error message is the following:

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 'Release,Description,JWRName,DueDate,JWalk,UI) VALUES ('35','TEST','TEST','5.50',' at line 1

This is the query:

Error 1605 : Query Was Empty Please Reply
when I am restoring the database on local machine.

using command :

mysql -u username DatabaseName < pathForResotoreFile -p password.

I am getting error -> "Error 1605 : Query was empty" on line 7 in back up file.

When i checked the backup file at line 7 there are comments.

Error After Installing MySQL Query Browser V. 1.1.20
After installing MySQLQuery Browser on a new computer (Windows XP Pro SP2), it works but there is a constant and rapidly flashing error on the bottom of the application. When you exit the application you get the following error:

Application Error
Exception EAccessViolation in module MySQLQueryBrowser.exe at 0003F211. Access violation at address 00040211. Read of address 00040211.

This application works on 2 other PCs but is acting crazy on the new PC. Any ideas? I know nothing about SQL, I'm just the PC support.

Syntax Error In String In Query Expression '''
The following is my jsp codings:

{//start of readline while
//sb.append(nextLine);
//note: BufferedReader strips the EOL character.
//sb.append(lineSep);

StringTokenizer SToken=new StringTokenizer(br.readLine(),",");
while(SToken.hasMoreTokens())
{
String ADM_NO = SToken.nextToken();
st.executeUpdate ("insert into try (Adm_No) values ('"+ADM_NO+"')");
}

}//end of readline while

There is an error Syntax error in string in query expression ''', is it b'cos of the single or double quotes? What can I do?

MySQL Query Browser - ERROR 1044
I'm new to MySQL and am having a frustrating time figuring out some of the tools. I'm getting ERROR 1044 when I try to execute the following .....

My Query Returns Mysql Error 1267....why?
my query is not working... i am using 2 databases...

Can Error Message Show Entire Sql Query Instead Of Excerpt
I'm running MySQL 3.23.58 on Red Hat Linux 9.

In my web server error log I see things like:

[Thu Jan 18 00:33:56 2007] [error] [client 72.86.29.196] DBD::mysql::st
execute failed: You have an error in your SQL syntax near ' (NOW(),
"Setup [precheck] failed for 72.86.29.196 port 1239 [72.86.29.196]
(Con' at line 1 at /var/www/html/blah/mysql-help.pl line 34.

Is there a MySQL setting I can change somewhere so that it will log the
entire SQL query that generated the error, instead of just an excerpt?
It's a lot easier to figure out what went wrong if I can see the whole
query.

Unknown Column Error With Left Join Query
I can't get my left join operation working whenever my on clause includes a comparison to another table's column. I keep getting errors like this:
#1054 - Unknown column 'pr.risk' in 'on clause'

The query I am trying to execute is shown below. All was working well until I put that top join in. And yes there are risk and issue columns in the pr table....

Is Error Code 1053 Only And Always Related To Killing A Query
The code I am developping is cancelling a SQL SELECT query if the user finds that it's too long. I'd like to display to him that he successfully cancelled it.

Can I rely on the error code 1053 that I receive after I performed a kill process (using the id of the process) ?

The documentation of the error code 1053 says:
SQLSTATE: 08S01 (ER_SERVER_SHUTDOWN) Message: Server shutdown in progress

I am using ConnectorJ..

Error Code : 1044 Cross Database Query
I am using a query editor UI and I am connected to two database and trying to write a query from within one database connection that joins both databases and am receiving the error;

Error Code : 1044
Access denied for user 'user1'@'localhost' to database 'test2'

The query looks something like this where I am in the test1 database UI and trying to included data from the test2 database.
select *
from test1
where test1.id in (select test2.id from test2)

I'm assuming I am having a syntax issue but I cannot find the proper syntax.

Trying Simple Embedded Query ... Get Syntax Error Using PhpMyAdmin
I am trying to do this in the SQL page of phpMyAdmin, but I get a syntax
error. Can someone please help me out ?
Thanks for your time and efforts!
JC

INSERT INTO `Test` ( `pkey` , `col2` , `col3` )
VALUES (
SELECT max( `pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww` )

pkey is the primary key, not null, varchar 11
col2 and col3 are both varchar 25, and can be null.

Also, the "Select Max('pkey') from Test" query works fine as a stand-alone
query.

This is the exact error I get from phpMyAdmin.

Error
SQL-query :
INSERT INTO `Test` ( `pkey` , `col2` , `col3` )
VALUES (

SELECT max( `pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww`
)
MySQL said:

#1064 - You have an error in your SQL syntax near 'SELECT max(
`pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww` )' at line 3

ERROR 2013: Lost Connection To MySQL Server During Query
I get this error when i try to remotely connect to my mysql server, it has been working until i changed the server's ip and isp, when i re-change the mysql server to the old ip address (first isp), it's working again.

Memory Size Exhausted Error - Too Much Info Back From Query - What To Do?
Using PHP and MYSQL I changed one of my MySQL search queries to allow a
situation where the user gets much more information returned then
previously

However, it has now given me the error message:

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to

allocate 42 bytes) in /usr/share/pear/DB/mysql.php on line 290

OK I understand what this means (I assume) that the program is trying
to
allocate more memory to return my query result but it wont let it.

However, I need this query to return this info, so what can I do?

ERROR 2013: Lost Connection To MySQL Server During Query
I have recently installed suse 9.0 linux which has MySQL 4.0.15 as part
of the distribution. After I set up my users I was able to use
mysqldump and pipe it into mysql on my windows machine running 4.0.16 to
dump a database to the linux box. However, now when I try to login to
the linux box from another machine on the network I get a query timed
out error immediately after I enter the command line to start the mysql
client.

mysql -h 192.168.1.2 -u cdw -pxxxxxx
ERROR 2013: Lost connection to MySQL server during query

The error happens with in a small fraction of a second after I type in
the command. I can login from the linux machine just fine though.

Error 2013: Lost Connection To MySQL Server During Query
I have MySQL 4.1.10 running successfully on an office XP machine, but
when I try to set up similar XP box at home I continually get this
error: "Error 2013: Lost connection to MySQL server during query".
The install went fine, and Windows XP is recognizing it as a running
service. But whenever I try to connect via one of my GUI applications
(e.g., MySQLFront), the error appears.

I have scoured this forum and Google looking for this error, but
haven't found anything helpful so far. This post ...
(http://forums.mysql.com/read.php?11,10684,10684) seemed similar, but
my service does not stop when I hit the error.

I've lost a couple days to this problem, and I've uninstalled and
reinstalled the app several times. I don't know where to go next. I
need to have 4.1 running because we need the Unions and Subqueries.

Error 2013: Lost Connection To MySQL Server During Query
I have MySQL 4.1.10 running successfully on an office XP machine, but when I try to set up similar XP box at home I continually get this error: "Error 2013:  Lost connection to MySQL server during query".

The install went fine, and Windows XP says it has started and is running as a service.  But whenever I try to connect via one of my GUI applications (e.g., MySQLFront), the error appears.

I have scoured this forum and Google looking for this error, but haven't found anything helpful so far.  I've lost a couple days to this problem, and I've uninstalled and reinstalled the app several times.  I don't know where to go next.  I need to have 4.1 running because we need the Unions and Subqueries.

Error 2013: Lost Connection To MySQL Server During Query
I get this message instantly (< 1 second) when I try to connect to the MySQL server from a remote host. I am using "mysql" at the remote client. Does not matter where the remote client is: on Solaris, on RH 7.2, on Fedora. Same thing every time.

I am using the the mysqld as it comes out of the box on Red Hat Linux 8.0.

I tried a google search on Error 2013, and there are a lot of responses:

User-defined Function: ERROR 2013 (HY000): Lost Connection To MySQL Server During Query
With mysql-4.1.1-alpha, using any User Defined Function ( UDF) at the
query execution stage, on Linux 2.6, gave me the following error:

"ERROR 2013 (HY000): Lost connection to MySQL server during query"

However, upgrading to mysql-4.1.2-alpha seemed to solve the problem.

Both versions were configured exactly as follows

../configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags
=-rdynamic

The only way I could solve this was by upgrading to 4.1.2-alpha-log. Then,
recompiling the functions, drop the old UDF functions, then, recreate.
So far so good.

Anyway, if you run into this problem, or just want more detail on the sample
User Defined Functions that come with MySQL ( /sql/udf_example.cc), I've put
my notes in the following link ( TIP 27 )

Lost Connection To MySQL Server During Query Socket Error On Read. WSAGetLastError Return 10054($2746)
We just migrated our MySQL database from a NetWare server running version 5.0.21 to a openSUSE box running version 5.0.45.

Everything ran fine for a couple of days, then clients began losing their connections and creating new ones without the old threads being terminated. This behavior is the same for the Delphi client we use to access our database as well as the MySQL Administrator tool.

I've tried adjusting connection_timeout, but overall the settings for this server are the same as the previous one that ran without issues for a year.

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":

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

Query Error (AES_ENCRYPT)