Re: convert help

2006-01-21 Thread Gleb Paharenko
Hello.

 ERROR 1314 (0A000): PREPARE is not allowed in stored procedures

PREPARE in the stored procedures should work in the latest release (5.0.18).




David Godsey wrote:
 Thank you.  I tried this outside of the procedure and it works. However In
 MYSQL 5 I get:
 ERROR 1314 (0A000): PREPARE is not allowed in stored procedures
 
 Is there a way without needing to use prepare?
 
 Any idea why CAST(fdata AS UNSIGNED) doesn't work?
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



Re: union/collation problem, error 1267: feature or bug?

2006-01-21 Thread Gleb Paharenko
Hello.

Some times the cause of these problems is that some fields
have different character sets or it is a bug. I suggest you
the following steps: check if the problem still exists in 4.1.16.
Then provide the create statements of your tables to the list
(they include the character set information). See:
  http://dev.mysql.com/doc/refman/4.1/en/charset-collation-charset.html



schlubediwup wrote:
 Hi mysqllers,
 
 1. following installation
 
 
 localhost.addresses2 show global variables like version%;
 +-+--+
 | Variable_name   | Value|
 +-+--+
 | version | 4.1.14-standard  |
 | version_comment | MySQL Community Edition - Standard (GPL) |
 | version_compile_machine | i686 |
 | version_compile_os  | pc-linux-gnu |
 +-+--+
 4 rows in set (0.00 sec)
 
 localhost.addresses2
 
 
 
 2. following two tables are involved:
 
 localhost.addresses2 describe contacts2;
 +--+--+--+-+---++
 
 | Field| Type | Null | Key | Default   |
 Extra  |
 +--+--+--+-+---++
 
 | name | text | YES  | | NULL 
 ||
 | firm | text | YES  | | NULL 
 ||
 | title| text | YES  | | NULL 
 ||
 | phone| text | YES  | | NULL 
 ||
 | mail | text | YES  | | NULL 
 ||
 | comment  | text | YES  | | NULL 
 ||
 | status   | text | YES  | | NULL 
 ||
 | url  | text | YES  | | NULL 
 ||
 | businesscategory | text | YES  | | NULL 
 ||
 | address  | text | YES  | | NULL 
 ||
 | kanton   | text | YES  | | NULL 
 ||
 | addon| text | YES  | | NULL 
 ||
 | givenname| text | YES  | | NULL 
 ||
 | history  | text | YES  | | NULL 
 ||
 | favorit  | text | YES  | | NULL 
 ||
 | last_update  | timestamp| YES  | | CURRENT_TIMESTAMP
 ||
 | counter  | int(10) unsigned |  | PRI | NULL  |
 auto_increment |
 +--+--+--+-+---++
 
 17 rows in set (0.00 sec)
 
 localhost.addresses2
 
 
 localhost.addresses2 describe tasks2;
 +--+--+--+-+---++
 
 | Field| Type | Null | Key | Default   |
 Extra  |
 +--+--+--+-+---++
 
 | task_name| text | YES  | | NULL 
 ||
 | actiony  | text | YES  | | NULL 
 ||
 | date_start   | date | YES  | | NULL 
 ||
 | date_end | date | YES  | | NULL 
 ||
 | date_last_action | date | YES  | | NULL 
 ||
 | date_next_action | date | YES  | | NULL 
 ||
 | start_time   | time | YES  | | NULL 
 ||
 | end_time | time | YES  | | NULL 
 ||
 | task_address | text | YES  | | NULL 
 ||
 | task_comment | text | YES  | | NULL 
 ||
 | task_responsible | text | YES  | | NULL 
 ||
 | project  | text | YES  | | NULL 
 ||
 | task_history | text | YES  | | NULL 
 ||
 | task_last_update | timestamp| YES  | | CURRENT_TIMESTAMP
 ||
 | contact_link | int(11)  | YES  | | NULL 
 ||
 | task_counter | int(10) unsigned |  | PRI | NULL  |
 

Fw: Error from mysqldump - problem solved

2006-01-21 Thread Rhino
I've resolved my problem with mysqldump. I read the error message yet again 
and it suddenly dawned on me what the problem might be. I made a slight 
modification in my table name and, sure enough, the problem went away.


The problem lay in the fact that my table name was References. That's right, 
the same word that is a keyword in the Foreign Key clause, as in Foreign 
key (id) references tmp.foo(id) on delete restrict. Using References as a 
table name had initially caused me grief when creating the table and when 
defining foreign keys that used it as a primary table. I'd eventually gotten 
past all those problems by putting backtics around each use of References 
as a table name. It didn't occur to me until I finally reread the error 
message this morning that the table name was biting me again within the 
mysqldump command.


All I did was drop the References table then recreate the tables so that the 
former References was now called Reference and everything worked 
perfectly again. I can't believe I didn't see this right from the start. Oh 
well, live and learn


I just thought I should follow up so that anyone following this thread now 
or in the archives will know how it was resolved.


Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]; gerald_clark 
[EMAIL PROTECTED]

Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 11:15 AM
Subject: Re: Error from mysqldump


Just as a followup to my own remarks, I've tried running my backup script 
with the new syntax that Gerald suggested. I was going to wait for the 
normal daily backup but I was eager to see if the new version would work 
better so I just ran it from the command line.


Unfortunately, it came back with the same error. The new syntax is still 
cleaner and I'm going to keep it but I'm back to square one in determining 
why the mysqldump of this one database is giving me trouble.


Does anyone have any ideas?

Rhino

- Original Message - 
From: Rhino [EMAIL PROTECTED]

To: gerald_clark [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 10:53 AM
Subject: Re: Error from mysqldump




- Original Message - 
From: gerald_clark [EMAIL PROTECTED]

To: Rhino [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 9:30 AM
Subject: Re: Error from mysqldump



Rhino wrote:

I have an automated backup script that has been running daily for a 
couple of years now. It has never given me trouble until the last two 
days. For the last two days, I have been getting this message when 
backing up my newest database:


/usr/bin/mysqldump: Got error: 1064: You have an error in your SQL 
syntax. Check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'References READ /*!32311 LOCAL */' at 
line 1 when using LOCK TABLES


This is the relevant portion of my backup script:

for ONE_DBNAME in `echo show databases | mysql -s -u 
$USERID -p$PASSWORD`

do
  echo
  echo Backing up database $ONE_DBNAME;
  /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r 
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


I would look here. This is a dangerous expansion. A space or ';' in any 
of these variables my generate unwanted commands.

Use quotes around the argument to -r.
Try.
 /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql


or /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME}   ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql




Okay, fair enough, I've never claimed to be a bash expert ;-) I think 
your proposed change is an improvement: it is clearer and easier to read. 
I'll give this version a try for the next few days and see if it works 
any better.


But I'm still not sure why this version might solve my problem. Wouldn't 
an expansion issue cause problems for all of my databases, not just one? 
I'm trying to understand why only one database is affected and why only 
the newest one when the script has worked fine for many months with the 
older databases.



  echo  Deleting these old backups for this database...
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display 
old backups (if any)
  /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' 
';' #delete old backups (if any)

done


I'm at a loss to see why I'm getting this error for only one database 
when the exact same logic is applied for each of my databases and works 
fine for all the others.


I've tried doing the backup manually from the command line and found 
that I got the same error when I tried to backup the Maximal database 
that way; a manual backup of another database worked fine.


The only idea I have that seems vaguely plausible is that there is 
something internally 

mysql-4.1.14 + linux kernel 2.6.15.1 = compile error

2006-01-21 Thread renito73
Hello list

I have just upgraded my system and was reinstalling all the programs, but got 
problems with a few ones, including mysql 4.1.14. When compiling I get the 
following error, I think it may be possible because changes in the kernel 
headers but am not sure because it was compiling fine in 2.6.11.11 and now 
have problems with 2.6.15.1

The output is this:

---
if g++ -DDEFAULT_BASEDIR=\/usr/local/mysql\ 
-DDATADIR=\/usr/local/mysql/var\
-DDEFAULT_CHARSET_HOME=\/usr/local/mysql\
-DSHAREDIR=\/usr/local/mysql/share/mysql\
-DHAVE_CONFIG_H -I. -I. -I.. -I../include -I../include
-I.-O3 -DDBUG_OFF-fno-implicit-templates
-fno-exceptions -fno-rtti -MT my_new.o -MD -MP -MF
.deps/my_new.Tpo -c -o my_new.o my_new.cc; \
then mv -f .deps/my_new.Tpo .deps/my_new.Po; else
rm -f .deps/my_new.Tpo; exit 1; fi
In file included from /usr/include/asm/atomic.h:6,
 from ../include/my_global.h:280,
 from mysys_priv.h:17,
 from my_new.cc:22:
/usr/include/asm/processor.h: In function `void
set_in_cr4(long unsigned int)':
/usr/include/asm/processor.h:237: error: `read_cr4'
undeclared (first use this
   function)
/usr/include/asm/processor.h:237: error: (Each
undeclared identifier is
   reported only once for each function it appears
in.)
/usr/include/asm/processor.h:239: error: `write_cr4'
undeclared (first use this
   function)
/usr/include/asm/processor.h: In function `void
clear_in_cr4(long unsigned int)
   ':
/usr/include/asm/processor.h:248: error: `write_cr4'
undeclared (first use this
   function)
make[2]: *** [my_new.o] Error 1
make[2]: Leaving directory `/tmp/mysql-4.1.14/mysys'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/tmp/mysql-4.1.14'
make: *** [all] Error 2
--

In particular the problem is that the following symbols are undeclared

set_in_cr4, read_cr4, write_cr4, clear_in_cr4

My include directory links are so


For kernel 2.6.11.11
/usr/src/linux - /usr/src/linux-2.6.11.11

For kernel 2.6.15.1
/usr/src/linux - /usr/src/linux-2.6.15.1

For the includes
/usr/include/linux - /usr/src/linux/include/linux
/usr/include/asm - /usr/src/linux/include/asm-i386
/usr/include/asm-generic - /usr/src/linux/include/asm-generic

to compile with different kernel versions just set the 
appropiate /usr/src/linux link to 2.6.11.11 or 2.6.15.1 and reboot with that 
kernel to ensure not only the kernel headers but the running kernel are the 
same before compile.

With kernel 2.6.11.11 compiles fine, but when trying 2.6.15.1 simply spits 
errors and aborts. Does anybody have the same kernel version and problem? and 
if so, were you able to solve the problem? I insist that the problem could be 
some modifications in the latest kernel headers and that mysql uses probably 
the previous version... I don't know too much about it but is a simple 
idea...

Any comment, sugestion or idea?

Regards,
Miguel


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



upgrade advice

2006-01-21 Thread Jon Miller
I'm currently running MySQL on RedHat 7.2 and now I'm in the processing of 
finishing testing a new server which has SUSE Linux Enterprise Server 9.
My question is my database currently resides on RH7.2 and 
MySQL-server-4.0.13-0.  I want to install the latest version on the SUSE system 
but I believe this to be  Version: 4.0.18 Release: 32.20 (this is with Service 
Pack 3 from Novell).
How do I do a backup of the existing and restore to the current? Are there any 
changes that may cause my apps to have a fit?

Thanks

Jon


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



assigning variable without printing result?

2006-01-21 Thread Jacek Becla

Hi,

Can someone tell me how to assign result to a variable
inside SELECT without printing the result?

Thanks,
Jacek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]