RE: MySQL 5.0 error after upgrade
Thanks, I'll look to see how to use the system. But you are right that there is an issue somewhere given that the automatic code produced by Administrator, Control Centre and Browser all have problems with communicating with the tables following the upgrade. Kerry -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 18 January 2006 15:57 To: mysql@lists.mysql.com Subject: Re: MySQL 5.0 error after upgrade Hello. ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER CHARACTER SET latin1 COLLATE latin1_general_ci; This seems like a bug. MySQL Administrator should not assign character set to integer columns. See: http://dev.mysql.com/doc/refman/5.0/en/bug-reports.html Kerry Frater wrote: Can someone help me. I was running v4 and just upgraded the version to v5.0. Most of my tables are MyISAM with some InnoDb. Most of the MyISAM tables are char fields but a few have integer columns. When trying to create a new table using the Administrator 1.1 program it is fine creating columns which are of type char but it fails when creating a column of type integer. The error message is ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER CHARACTER SET latin1 COLLATE latin1_general_ci; Can someone advise me what has changed in the upgrade and how I can get back to using integers? kERRY -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem on mysql.sock and mysql.host
i cant start mysql. error in the log says: Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist service configuration says: mysqld dead but subsys locked. i can't find any mysql.sock in /var/lib/mysql i reinstalled MySQL through yum but error is still the same. i was able to run MySQL before. i was even able to replicate. i dont know what's wrong. i tried many things in different forums but all is still the same.. please can anyone help? =( im using FC4. thanks... chEn Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem on mysql.sock and mysql.host
i cant start mysql. error in the log says: Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist service configuration says: mysqld dead but subsys locked. i can't find any mysql.sock in /var/lib/mysql i reinstalled MySQL through yum but error is still the same. i was able to run MySQL before. i was even able to replicate. i dont know what's wrong. i tried many things in different forums but all is still the same.. please can anyone help? =( im using FC4. thanks... chEn Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about recovery with binlog
Mysqlbinlog throw out a error ERROR 1231 (42000) at line 10: Variable 'sql_mode' can't be set to the value of '501481487' when i recovery a binlog. What can i do?
Re: question about recovery with binlog
Hello. Are you sure that the bug is thrown by mysqlbinlog? May be you're getting this while importing the output produced by mysqlbinlog? Have a look here: http://bugs.mysql.com/bug.php?id=13897 Check that you're using the same versions of mysql client and mysqlbinlog. wangxu wrote: Mysqlbinlog throw out a error ERROR 1231 (42000) at line 10: Variable 'sql_mode' can't be set to the value of '501481487' when i recovery a binlog. What can i do? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem on mysql.sock and mysql.host
Hello. Have you run mysql_install_db? See: http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html Chen Abella wrote: i cant start mysql. error in the log says: Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist service configuration says: mysqld dead but subsys locked. i can't find any mysql.sock in /var/lib/mysql i reinstalled MySQL through yum but error is still the same. i was able to run MySQL before. i was even able to replicate. i dont know what's wrong. i tried many things in different forums but all is still the same.. please can anyone help? =( im using FC4. thanks... chEn Send instant messages to your online friends http://uk.messenger.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timezone settings
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html [EMAIL PROTECTED] wrote: Dear Friends, I need to do the timezone settings so that now() gives the system time.Actually first i have installed mysql on a different timezone han changed the system time zone but perhaps mysql shows the previous time zone or the default time zone only. Pl. tell me how to change that . I shall be very grateful. -- Regards Abhishek jain mail2web - Check your email from the web at http://mail2web.com/ . -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 16 vs 41 byte password hashes
Hello. Most probably the reason is in old_passwords in your configuration file. You can check this with the following statement: show variables like 'old_passwords'; Gary Huntress wrote: I have a new installation of MySQL 5.0 (I did not port an old ver). I am running a Ruby on Rails application that uses this db. I have grants for [EMAIL PROTECTED], root@localhost and root@'192.168.0.63'. The passwords for these 3 grants are old style 16 byte hashes. There was one single grant for root that had a 41 byte new style hash. I thought it was redundant and deleted it. I can log in using the mysql client but my rails application can no longer log in. I'm not 100% sure that the problem is because I removed that grant but I'm fairly sure (no other configuration info has changed) My question is, why when I GRANT all on *.* to root@'localhost' identified by 'xxx' do I get a 16 byte hash and not a 41 byte hash? Since my theory is the lack of a grant with a 41 byte hash I'd like to test that. How do I create 41 byte password hashes ? Thanks, Gary -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install help on Linux: I cant obtain access
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/access-denied.html Wade Smart wrote: 01182006 1627 GMT-6 Im on Ubuntu. I have mysql 4.0.24. I have phpmyadmin installed. Im a little frustrated at this point so bear with me. Mysql is running. My book says type in: mysql -h localhost testto see if the install is working. That provides me with Access denied. I then tried: mysqladmin -u root password newpassword and that failed with Access denied. I tried doing that as (computer) root and again I received that error. I just do not understand what it is that I am supposed to do. Can someone provide some light? Wade -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting Query Problem
Hello. Perhaps this will work (depends on the version of MySQL you're using): select question_id , count(*) from Records group by question_id having question_id not in ( select distinct question_id from Records r where member_id = @current_member_id); @current_member_id equals to current_user G G wrote: Hello, I have a simple Records table with two columns, member_id and question_id. The object of the query is to retrieve the question_id, as well as how many times it's been answered - as long as the current user hasn't answered it (member_id). So, the query shouldn't return any question_id's (and counts) if it has been answered by the current user. Right now I have this: SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY question_id; I've tried throwing in different variants of 'WHERE member_id != X', but all that seems to return is the count of questions answered, minus the amount of times the particular user has answered them. For example, if user X has answered a question that had been answered another 50 times, my query will still return that question_id, but with a count of 49. Your help is appreciated in advance. Thanks! Kind Regards, Gerald Glickman G2 Innovations.com, Inc. http://www.g2innovations.com http://www.g2innovations.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting Query Problem
Hi, An alternative for any MySQL version (from 3.23.??) would be: SELECT r1.question_id,count(r1.member_id) FROM Records r1 LEFT JOIN Records r2 ON r1.question_id=r2.question_id AND r2.member_id=member_id WHERE r2.question_id IS NULL; member_id must be the member name. mpneves On Thursday 19 January 2006 11:18, Gleb Paharenko wrote: Hello. Perhaps this will work (depends on the version of MySQL you're using): select question_id , count(*) from Records group by question_id having question_id not in ( select distinct question_id from Records r where member_id = @current_member_id); @current_member_id equals to current_user G G wrote: Hello, I have a simple Records table with two columns, member_id and question_id. The object of the query is to retrieve the question_id, as well as how many times it's been answered - as long as the current user hasn't answered it (member_id). So, the query shouldn't return any question_id's (and counts) if it has been answered by the current user. Right now I have this: SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY question_id; I've tried throwing in different variants of 'WHERE member_id != X', but all that seems to return is the count of questions answered, minus the amount of times the particular user has answered them. For example, if user X has answered a question that had been answered another 50 times, my query will still return that question_id, but with a count of 49. Your help is appreciated in advance. Thanks! Kind Regards, Gerald Glickman G2 Innovations.com, Inc. http://www.g2innovations.com http://www.g2innovations.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT encrypted data
Hello. What doesn't work? In case you want more help please, provide the results you want obtain from your query and CREATE statement for you table. sharif islam wrote: mysql insert into ccard values(AES_ENCRYPT(123453535,'uiwuerw'),'10/2003'); Query OK, 1 row affected (0.00 sec) mysql select * from ccard - ; +--+-+ | crypt| expire | +--+-+ | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | +--+-+ 4 rows in set (0.00 sec) why doesn't this work? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import from another db....
Hi everyone, how can i import a database export from another database. I´ve got alway a permission error... I used mysqlimport mysqldump... Thanks in advance Marco Schierhorn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best Configuratuion ( my.cnf ) for a DB with many users and large columns ( Images in BLOB Columns ) on a Website
Hey, what do you think is the best configuration ( my.cnf ) for running a database, which many users access at the same time ( Website - Portal ). We´ve also saved our Pictures ( nearly 3.500 rows ) in our database. Every coloumn is round about 60-70 Kbyte, so i had to use mediumblob columns. There are 116 Tables and we have a primary key and an index on every table and use them in ( i hope so ;-) ) every statement. Here i´ve a my.cnf from another website. Would that be an good configuration ? Thanks in advance Marco My.cnf : # The MySQL server [mysqld] port= 3307 socket = /tmp/mysql.sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_cache = 500 sort_buffer_size = 24M net_buffer_length = 8K read_buffer_size = 1M read_rnd_buffer_size = 256K myisam_sort_buffer_size = 24M record_buffer=1M log-slow-queries long_query_time = 3 query_cache_size = 512M max_connections = 500 join_buffer_size = 16M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 25M sort_buffer_size = 25M read_buffer = 6M write_buffer = 6M [myisamchk] key_buffer = 25M sort_buffer_size = 25M read_buffer = 6M write_buffer = 6M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about CONTAINS SQL
I am copying the rest of the list with this so that everyone may benefit from the discussion. If your routine modifies data, in other words if it does SQL Update, Insert or Delete but your routine definition says only CONTAINS SQL, I would expect your routine to fail at runtime. I can't say this with certaintly because I don't have one of the newer versions of MySQL that supports these routines but I'm pretty sure that you will have a runtime failure. After all, CONTAINS SQL implies only that you are creating objects like tables within your routine; CONTAINS SQL does not permit the execution of Insert, Update, or Delete. Therefore, I expect that you will get a runtime error as soon as you do your first Insert, Update, or Delete. If you want to avoid the error, use MODIFIES SQL instead of CONTAINS SQL. Of course the best way to be sure is to try this for yourself. Try the routine with CONTAINS SQL and see what happens at runtime. If it fails, as I strongly expect, change CONTAINS SQL to MODIFIES SQL DATA and your error will almost certainly go away. Rhino - Original Message - From: wangxu [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Thursday, January 19, 2006 2:54 AM Subject: Re: question about CONTAINS SQL If I create a routine with modification operation and not spectify characteristic in CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA. In the maunal,the default value is CONTAINS SQL if i haven't spectify a values. If it's meaning that the routine with CONTAINS SQL include modification operation? Should many problem happen? - Original Message - From: Rhino [EMAIL PROTECTED] To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, January 19, 2006 12:21 AM Subject:Re: question about CONTAINS SQL If you are writing something that does INSERT, UPDATE, or DELETE, you need to use the MODIFIES SQL DATA option. Rhino - Original Message - From: wangxu [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, January 18, 2006 3:05 AM Subject: Re: question about CONTAINS SQL But what is INSERT OR UPDATE need? - Original Message - From: Rhino [EMAIL PROTECTED] To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 17, 2006 9:49 PM Re: question about CONTAINS SQL - Original Message - From: wangxu [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 17, 2006 12:35 AM Subject: question about CONTAINS SQL I notice there are one section in the manual: CONTAINS SQL indicates that the routine does not contain statements that read or write data. And that the option is default. It's true? If i wouldn't do read or write in routine.What can i do yet? Commands like GRANT or REVOKE or CREATE TABLE don't read or write data within tables but they involve SQL so commands like this need CONTAINS SQL, rather than the NO SQL, READS SQL DATA or MODIFIES SQL DATA options. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.19/231 - Release Date: 16/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error from mysqldump
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
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 wrong with my database but I'm darned if I know what the problem
could be. When I do 'select *' against each of the five small tables in this
database, each returns exactly the right data and there are no errors or
warnings of any kind.
Can anyone suggest queries or commands that would reveal the status of my
database and its tables to make sure something is not messed up?
Any suggestions on resolving this problem would be greatly appreciated.
---
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
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 wrong with my database but I'm darned if I know
what the problem could be. When I do 'select *' against each of the
five small tables in this database, each returns exactly the right
data and there are no errors or warnings of any kind.
Can anyone suggest queries or commands that would reveal the status of
my database and its tables to make sure something is not messed up?
Any suggestions on resolving this problem would be greatly appreciated.
---
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By over many colums
Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott
RE: Group By over many colums
I would suggest a union SELECT name, count(*) FROM (SELECT name1 as name from mytable union select name2 as name from mytable union select name3 as name from table) GROUP BY name but perhaps there's a better way... Regards, Patrick -Original Message- From: Critters [mailto:[EMAIL PROTECTED] Sent: Thursday, 19 January 2006 16:17 To: mysql@lists.mysql.com Subject: Group By over many colums Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By over many colums
Hi, To this on I just see a solution, that depends on sub-selects, so it's available from Mysql 4.1 forward: SELECT name,count(*) from ((SELECT name1 name FROM tablename) UNION ALL (SELECT name2 name FROM tablename) UNION ALL (SELECT name3 name FROM tablename)) tab GROUP by name; Hope this solves you problem. mpneves On Thursday 19 January 2006 15:16, Critters wrote: Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 wrong with my database but I'm darned if I know what
the problem could be. When I do 'select *' against each of the five small
tables in this database, each returns exactly the right data and there
are no errors or warnings of any kind.
Can anyone suggest queries or commands that would reveal the status of my
database and its tables to make sure something is not messed up?
Any suggestions on resolving this problem would be greatly appreciated.
---
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding the date values loading into the tables from a text file
Hi, From the front end I need to use double slashes i.e (\\) to enter one slash (\) into the MySQL database. I.e, if I enter Gelb\Paha, it stores in the mySQL as GlebPaha, If I enter S\\Greeen, it stores as S\Green in the database. Is there any way in MySQL so that I can enter any number of slases between the name with out escaping with another slash?. If I retrive the same value with the slash (\), not able to display properly in the GUI. Do we need to use any MySQL specific functions to select such values?. Please guide me for a solution here. Thanking you in advance. Thanks, Narasimha The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By over many colums
The actual table is called sends and the data is like this: | id | f1 | f2 | f3 | | 3 | foo.com | yahoo.com| | | 4 | dsl.pipex.com | foo.com| foo.com| | 5 | vodafone.com| btinternet.com| co-op.co.uk | I tired: SELECT domain, count(*) FROM ( (SELECT f1 as domain from sends) union all (SELECT f2 as domain from sends) union all (SELECT f3 as domain from sends) ) GROUP BY domain But I get: [localhost] 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 'SELECT f1 as domain from sends) union all (SELECT f2 as domain Can you spot where I am going wrong? - David Scott - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Critters [EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:34 PM Subject: Re: Group By over many colums Hi, To this on I just see a solution, that depends on sub-selects, so it's available from Mysql 4.1 forward: SELECT name,count(*) from ((SELECT name1 name FROM tablename) UNION ALL (SELECT name2 name FROM tablename) UNION ALL (SELECT name3 name FROM tablename)) tab GROUP by name; Hope this solves you problem. mpneves On Thursday 19 January 2006 15:16, Critters wrote: Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By over many colums
Hi, Before anything else I would verify that your mysql-server is 4.1 or superior, as before that MySQL didn't suported sub-selects. It looks to me that the error should be that. After that I only spot the missing table alias before the Group by. mpneves On Thursday 19 January 2006 16:01, Critters wrote: The actual table is called sends and the data is like this: | id | f1 | f2 | f3 | | 3 | foo.com | yahoo.com| | | 4 | dsl.pipex.com | foo.com| foo.com| | 5 | vodafone.com| btinternet.com| co-op.co.uk | I tired: SELECT domain, count(*) FROM ( (SELECT f1 as domain from sends) union all (SELECT f2 as domain from sends) union all (SELECT f3 as domain from sends) ) GROUP BY domain But I get: [localhost] 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 'SELECT f1 as domain from sends) union all (SELECT f2 as domain Can you spot where I am going wrong? - David Scott - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Critters [EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:34 PM Subject: Re: Group By over many colums Hi, To this on I just see a solution, that depends on sub-selects, so it's available from Mysql 4.1 forward: SELECT name,count(*) from ((SELECT name1 name FROM tablename) UNION ALL (SELECT name2 name FROM tablename) UNION ALL (SELECT name3 name FROM tablename)) tab GROUP by name; Hope this solves you problem. mpneves On Thursday 19 January 2006 15:16, Critters wrote: Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT encrypted data
On 1/19/06, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. What doesn't work? In case you want more help please, provide the results you want obtain from your query and CREATE statement for you table. Sorry for not being clear. The data is getting saved as NULL instead of being encrypted. sharif islam wrote: mysql insert into ccard values(AES_ENCRYPT(123453535,'uiwuerw'),'10/2003'); Query OK, 1 row affected (0.00 sec) mysql select * from ccard - ; +--+-+ | crypt| expire | +--+-+ | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | +--+-+ 4 rows in set (0.00 sec) why doesn't this work? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By over many colums
Thanks for the replies Marco... mysql Ver 12.22 Distrib 4.0.21 So that could be it? By the way... (SELECT f1 as 'domain' from sends) union (SELECT f2 as 'domain' from sends) union (SELECT f3 as 'domain' from sends) union (SELECT f4 as 'domain' from sends) Works, and returns a list where f1, f2, f3 and f4 are all in the column domain But as soon as I add GROUP BY domain to the end it fails. I have also tried GROUP BY 'domain' -- David Scott - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Critters [EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:34 PM Subject: Re: Group By over many colums Hi, To this on I just see a solution, that depends on sub-selects, so it's available from Mysql 4.1 forward: SELECT name,count(*) from ((SELECT name1 name FROM tablename) UNION ALL (SELECT name2 name FROM tablename) UNION ALL (SELECT name3 name FROM tablename)) tab GROUP by name; Hope this solves you problem. mpneves On Thursday 19 January 2006 15:16, Critters wrote: Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 wrong with my database but I'm darned if I know
what the problem could be. When I do 'select *' against each of the five
small tables in this database, each returns exactly the right data and
there are no errors or warnings of any kind.
Can anyone suggest queries or commands that would reveal the status of
my database and its tables to make sure something is not messed up?
Any suggestions on resolving this problem would be greatly appreciated.
---
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date:
18/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date:
18/01/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By over many colums
Hi Critters, The problem is that as your MySQL is 4.0.21 don't suport the subselect you would need to do the group. I was thinking and you have another alternative: CREATE TEMPORARY table tdata (SELECT f1 as 'domain' from sends) union all (SELECT f2 as 'domain' from sends) union all (SELECT f3 as 'domain' from sends) union all (SELECT f4 as 'domain' from sends); -- This would create an temporary table with all the data SELECT domain,count(*) from tdata GROUP by domain; -- This sould do the trick. mpneves On Thursday 19 January 2006 16:14, Critters wrote: Thanks for the replies Marco... mysql Ver 12.22 Distrib 4.0.21 So that could be it? By the way... (SELECT f1 as 'domain' from sends) union (SELECT f2 as 'domain' from sends) union (SELECT f3 as 'domain' from sends) union (SELECT f4 as 'domain' from sends) Works, and returns a list where f1, f2, f3 and f4 are all in the column domain But as soon as I add GROUP BY domain to the end it fails. I have also tried GROUP BY 'domain' -- David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show Description options??
Hi I was looking for a command that will list the names of my columns only. I have investigated show columns but there seems to be no way to return just the names. Any suggestions?? Thanks Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By over many colums
Thanks! I wrote: DELETE FROM t_sends; CREATE TEMPORARY table IF NOT EXISTS t_sends (SELECT f1 as 'domain' from sends WHERE gameID = 1) union all (SELECT f2 as 'domain' from sends WHERE gameID = 1) union all (SELECT f3 as 'domain' from sends WHERE gameID = 1) union all (SELECT f4 as 'domain' from sends WHERE gameID = 1) ; SELECT MID(domain,INSTR(domain,'@')+1) AS 'domain' , count(*) from t_sends GROUP by 'domain' And that does the trick Is there any way to destroy the t_sends table? -- Dave - Original Message - From: Marco Neves [EMAIL PROTECTED] To: Critters [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, January 19, 2006 4:20 PM Subject: Re: Group By over many colums Hi Critters, The problem is that as your MySQL is 4.0.21 don't suport the subselect you would need to do the group. I was thinking and you have another alternative: CREATE TEMPORARY table tdata (SELECT f1 as 'domain' from sends) union all (SELECT f2 as 'domain' from sends) union all (SELECT f3 as 'domain' from sends) union all (SELECT f4 as 'domain' from sends); -- This would create an temporary table with all the data SELECT domain,count(*) from tdata GROUP by domain; -- This sould do the trick. mpneves On Thursday 19 January 2006 16:14, Critters wrote: Thanks for the replies Marco... mysql Ver 12.22 Distrib 4.0.21 So that could be it? By the way... (SELECT f1 as 'domain' from sends) union (SELECT f2 as 'domain' from sends) union (SELECT f3 as 'domain' from sends) union (SELECT f4 as 'domain' from sends) Works, and returns a list where f1, f2, f3 and f4 are all in the column domain But as soon as I add GROUP BY domain to the end it fails. I have also tried GROUP BY 'domain' -- David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By over many colums
Hi, If it didn't existed and was created with the create temporary table it will desapear when you close your corrent session. otherwise you can drop it with DROP TABLE t_sends; mpneves On Thursday 19 January 2006 16:35, Critters wrote: Thanks! I wrote: DELETE FROM t_sends; CREATE TEMPORARY table IF NOT EXISTS t_sends (SELECT f1 as 'domain' from sends WHERE gameID = 1) union all (SELECT f2 as 'domain' from sends WHERE gameID = 1) union all (SELECT f3 as 'domain' from sends WHERE gameID = 1) union all (SELECT f4 as 'domain' from sends WHERE gameID = 1) ; SELECT MID(domain,INSTR(domain,'@')+1) AS 'domain' , count(*) from t_sends GROUP by 'domain' And that does the trick Is there any way to destroy the t_sends table? -- Dave - Original Message - From: Marco Neves [EMAIL PROTECTED] To: Critters [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, January 19, 2006 4:20 PM Subject: Re: Group By over many colums Hi Critters, The problem is that as your MySQL is 4.0.21 don't suport the subselect you would need to do the group. I was thinking and you have another alternative: CREATE TEMPORARY table tdata (SELECT f1 as 'domain' from sends) union all (SELECT f2 as 'domain' from sends) union all (SELECT f3 as 'domain' from sends) union all (SELECT f4 as 'domain' from sends); -- This would create an temporary table with all the data SELECT domain,count(*) from tdata GROUP by domain; -- This sould do the trick. mpneves On Thursday 19 January 2006 16:14, Critters wrote: Thanks for the replies Marco... mysql Ver 12.22 Distrib 4.0.21 So that could be it? By the way... (SELECT f1 as 'domain' from sends) union (SELECT f2 as 'domain' from sends) union (SELECT f3 as 'domain' from sends) union (SELECT f4 as 'domain' from sends) Works, and returns a list where f1, f2, f3 and f4 are all in the column domain But as soon as I add GROUP BY domain to the end it fails. I have also tried GROUP BY 'domain' -- David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Show Description options??
If you are using mysql 5.x you must be able to retrieve that you want using information_schema database Look: mysql select version(); +-+ | version() | +-+ | 5.0.16-standard | +-+ 1 row in set (0.00 sec) mysql use information_schema; Database changed mysql SELECT column_name,data_type FROM COLUMNS C where table_schema = 'erp' and table_name='TAnticipo'; +-+---+ | column_name | data_type | +-+---+ | idTAnticipo | int | | TEmpleado_idTEmpleado | int | | TViaje_idTViaje | int | | TTipoGasto_idTTipoGasto | int | | no_vale | char | | fecha | date | | tipo| char | | cantidad| tinyint | | importe | double| | iva | double| | observacion | char | | activo | char | +-+---+ 12 rows in set (0.01 sec) mysql Regards! ++ | ISC Edwin Cruz [EMAIL PROTECTED]| | Desk: 52 (449) 910 30 90 x3054| | MX Mobile: 52 (449) 111 29 03 | | Skype: e-cruz | ++ -Mensaje original- De: Mike OK [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 19 de Enero de 2006 10:34 a.m. Para: mysql@lists.mysql.com Asunto: Show Description options?? Hi I was looking for a command that will list the names of my columns only. I have investigated show columns but there seems to be no way to return just the names. Any suggestions?? Thanks Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best Configuratuion ( my.cnf ) for a DB with many users and large columns ( Images in BLOB Columns ) on a Website]
Hey, what do you think is the best configuration ( my.cnf ) for running a database, which many users access at the same time ( Website - Portal ). We´ve also saved our Pictures ( nearly 3.500 rows ) in our database. Every coloumn is round about 60-70 Kbyte, so i had to use mediumblob columns. There are 116 Tables and we have a primary key and an index on every table and use them in ( i hope so ;-) ) every statement. Here i´ve a my.cnf from another website. Would that be an good configuration ? Thanks in advance Marco My.cnf : # The MySQL server [mysqld] port= 3307 socket = /tmp/mysql.sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_cache = 500 sort_buffer_size = 24M net_buffer_length = 8K read_buffer_size = 1M read_rnd_buffer_size = 256K myisam_sort_buffer_size = 24M record_buffer=1M log-slow-queries long_query_time = 3 query_cache_size = 512M max_connections = 500 join_buffer_size = 16M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 25M sort_buffer_size = 25M read_buffer = 6M write_buffer = 6M [myisamchk] key_buffer = 25M sort_buffer_size = 25M read_buffer = 6M write_buffer = 6M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select records added in last hour
I have a table containing a timestamp field, `insert_time` The manual is clear when it comes to selecting records from the past week or month, I can use: SELECT COUNT(*) FROM statistics WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) = `insert_time`; What I am less sure about is how I would select records that had been inserted to thee table in the previous hour. I understand the date functions will ignore the hh:mm:ss - will the time functions ignore the date? Can anyone suggest what function I need for this select? Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select records added in last hour
Russell Horn wrote: I have a table containing a timestamp field, `insert_time` The manual is clear when it comes to selecting records from the past week or month, I can use: SELECT COUNT(*) FROM statistics WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) = `insert_time`; What I am less sure about is how I would select records that had been inserted to thee table in the previous hour. I understand the date functions will ignore the hh:mm:ss - will the time functions ignore the date? Can anyone suggest what function I need for this select? Have you tried any of the functions in the manual? select NOW(); select DATE_SUB(NOW(),INTERVAL 60 MINUTE); select DATE_SUB(NOW(),INTERVAL 60 MINUTE)+0; select DATE_SUB(NOW(),INTERVAL 1 HOUR); select DATE_SUB(NOW(),INTERVAL 60 HOUR)+0; Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select records added in last hour
Russell Horn wrote: I have a table containing a timestamp field, `insert_time` The manual is clear when it comes to selecting records from the past week or month, I can use: SELECT COUNT(*) FROM statistics WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) = `insert_time`; What I am less sure about is how I would select records that had been inserted to thee table in the previous hour. I understand the date functions will ignore the hh:mm:ss - will the time functions ignore the date? Can anyone suggest what function I need for this select? Thanks, Russell. Rather than the CURDATE() function, just use NOW(). Ex.: mysql select now(); +-+ | now() | +-+ | 2006-01-19 14:47:50 | +-+ 1 row in set (0.00 sec) mysql select date_sub(now(), interval 1 hour); +--+ | date_sub(now(), interval 1 hour) | +--+ | 2006-01-19 13:47:53 | +--+ 1 row in set (0.00 sec) Cheers :) -- Devananda vdv http://devananda-vdv.blogspot.com/ http://mycat.sourceforge.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select records added in last hour
On Thu, 2006-01-19 at 11:49 -0800, Devananda wrote: Rather than the CURDATE() function, just use NOW(). This is perfect. Thanks. Russell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is UNION allowed in a MySQL stored procedure?
I have a simple stored procedure which works as intended. As soon as I add a UNION in the SELECT I get the error message 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 'select phon_Lvl INTO Lvl Are UNION's currently not allowed in a stored procedure? mysql delimiter // mysql create procedure ph() - BEGIN - DECLARE LVL Char(10); - select phon_Lvl INTO Lvl - FROM phones - - limit 1; - SET @Lvl:=Lvl; - END// Query OK, 0 rows affected (0.00 sec) mysql delimiter ; mysql mysql call ph(); Query OK, 0 rows affected (0.00 sec) mysql mysql Select @Lvl; +--+ | @Lvl | +--+ | locn | +--+ 1 row in set (0.00 sec) mysql mysql drop procedure if exists ph; Query OK, 0 rows affected (0.01 sec) mysql delimiter // mysql create procedure ph() - BEGIN - DECLARE LVL Char(10); - select phon_Lvl INTO Lvl - FROM phones - UNION - Select a into LVL - limit 1; - SET @Lvl:=Lvl; - END// 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 'select phon_Lvl INTO Lvl FROM phones UNION Select a into LVL limi' at line 4 mysql delimiter ; mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Show Description options??
If you are on 5.0.x you can use SELECT column_Name FROM INFORMATION_SCHEMA.columns; INFORMATION_SCHEMA is a set of VIEWS that lets you access the database structure. See http://dev.mysql.com/doc/refman/5.0/en/information-schema.html -Original Message- From: Mike OK [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 10:34 AM To: mysql@lists.mysql.com Subject: Show Description options?? Hi I was looking for a command that will list the names of my columns only. I have investigated show columns but there seems to be no way to return just the names. Any suggestions?? Thanks Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
convert help
I am trying to convert binary data to a bigint so I can do bitwise operations on the data, and I'm having trouble doing it. I noticed that if I have binary data and I: select data1; I get 0 (not what I'm expecting). Here is a test procedure I wrote: create procedure test20 () BEGIN DECLARE fdata BLOB; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE tmp_int BIGINT UNSIGNED; SELECT 0xABCDEF0123456789 INTO fdata; SELECT 14 INTO foffset; SELECT 7 INTO flength; SELECT SUBSTR(BINARY(fdata), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))%8)) INTO fdata; SELECT HEX(fdata); SELECT CONVERT(fdata,BIGINT) INTO tmp_int; SELECT HEX(tmp_int); END The last two selects are added to show what I would like to do, but have not been able to get it to work. Any help would be great. Thanks in advance. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - convert help - Bayesian Filter detected spam
I just added a user variable @fdata to get visabilility outside of the procedure and this is what I get. mysql delimiter // mysql create procedure test20 () -BEGIN - DECLARE fdata BLOB; - DECLARE foffset INT UNSIGNED; - DECLARE flength INT UNSIGNED; - DECLARE tmp_int BIGINT UNSIGNED; - - SELECT 0xABCDEF0123456789 INTO fdata; - SELECT 14 INTO foffset; - SELECT 7 INTO flength; - - SELECT SUBSTR(BINARY(fdata), - FLOOR(foffset/8)+1, - CEIL((flength + (foffset %8 ))%8)) - INTO fdata; - set @fdata:=fdata; - END// Query OK, 0 rows affected (0.00 sec) mysql mysql delimiter ; mysql mysql call test20(); Query OK, 0 rows affected (0.00 sec) mysql mysql select @fdata, hex(@fdata) - - ; ++-+ | @fdata | hex(@fdata) | ++-+ | ═∩☺#E | CDEF012345 | ++-+ 1 row in set (0.00 sec) -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:33 PM To: mysql@lists.mysql.com Subject: [SPAM] - convert help - Bayesian Filter detected spam I am trying to convert binary data to a bigint so I can do bitwise operations on the data, and I'm having trouble doing it. I noticed that if I have binary data and I: select data1; I get 0 (not what I'm expecting). Here is a test procedure I wrote: create procedure test20 () BEGIN DECLARE fdata BLOB; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE tmp_int BIGINT UNSIGNED; SELECT 0xABCDEF0123456789 INTO fdata; SELECT 14 INTO foffset; SELECT 7 INTO flength; SELECT SUBSTR(BINARY(fdata), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))%8)) INTO fdata; SELECT HEX(fdata); SELECT CONVERT(fdata,BIGINT) INTO tmp_int; SELECT HEX(tmp_int); END The last two selects are added to show what I would like to do, but have not been able to get it to work. Any help would be great. Thanks in advance. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting based on serialized field...
I'm not sure if this falls under a PHP topic or a MySQL topic, but I have a
table that contains a field that stores serialized data. Is there a way to
order a recordset by a piece of the serialized data? For example, if a
field called data contains serialized data that looks like this:
a:11:{s:7:company;s:12:Some Company;s:8:jobtitle;s:17:Assistant
Manager;s:5:phone;s:12:123-456-7890;s:3:fax;s:12:987-654-3210;s:7:bf_time;i:1135221050;s:8:bf_value;d:2.416246843777560382449109965818934142589569091796875E-5;s:17:signup_email_sent;i:1;s:6:status;a:2:{i:2;i:0;i:1;i:1;}s:9:is_active;i:1;s:9:is_locked;s:0:;s:11:affiliation;s:1:1;}
is there a way to select a recordset ordered by company? Kind of like,
but not quite: SELECT * FROM mytable ORDER BY companyname
(where the companyname is some function that pulls out the company name)
Jenifer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
differences between varchar and text fields
hi all - I was looking for some clarification about the difference between varchar and the text types in MySQL 5.0.18. It seems to me that varchar and text both hold text data, are variable length, and have the same storage requirements (4 + L bytes). Also, longtext adds the ability to hold ~4G of data at the cost of only 2 extra storage bytes? Also, it looks like text data is stored externally from the table, where varchar is stored within its row. So, my question is: what are the pros and cons of using varchar vs. text/longtext? Right now, longtext seems to be the best option (it provides most flexibility in data that can be stored, at only a 2-byte-per-row storage premium) There must be some drawback, what am I missing? Thanks for your help -tucker -- Tucker Cunningham Integration Appliance Inc. email: [EMAIL PROTECTED] cell: 650.387.5980 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting based on serialized field...
is there a way to select a recordset ordered by "company"? Kind
of like,
but not quite: SELECT * FROM mytable ORDER BY companyname
(where the "companyname" is some function that pulls out the
company name)
Yes you can ORDER BY a function result.
PB
-
Subscriptions wrote:
I'm
not sure if this falls under a PHP topic or a MySQL topic, but I have a
table that contains a field that stores serialized data. Is there a
way to order a recordset by a piece of the serialized data? For
example, if a field called "data" contains serialized data that looks
like this:
a:11:{s:7:"company";s:12:"Some Company";s:8:"jobtitle";s:17:"Assistant
Manager";s:5:"phone";s:12:"123-456-7890";s:3:"fax";s:12:"987-654-3210";s:7:"bf_time";i:1135221050;s:8:"bf_value";d:2.416246843777560382449109965818934142589569091796875E-5;s:17:"signup_email_sent";i:1;s:6:"status";a:2:{i:2;i:0;i:1;i:1;}s:9:"is_active";i:1;s:9:"is_locked";s:0:"";s:11:"affiliation";s:1:"1";}
is there a way to select a recordset ordered by "company"? Kind of
like, but not quite: SELECT * FROM mytable ORDER BY companyname
(where the "companyname" is some function that pulls out the company
name)
Jenifer
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 1/19/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with using CONSTRAINT declaration
I have the following table where I have a CHECK CONSTRAINT to check for logical data values but for some reason it's not working on INSERTs to the table. MySQL doesn't give any error message when I CREATE TABLE. Any ideas what I'm doing wrong?... or Is this type of declaration not supported... What command can you issue from the command line to check the existence of CONTRAINT declarations such as this? CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, start_time TIME, end_time TIME, CONSTRAINT end_date_cannot_be_before_start_date CHECK (end_date = start_date), CONSTRAINT end_time_cannot_be_before_start_time CHECK (end_time = start_time), PRIMARY KEY (class_id, start_date, end_date, start_time, end_time) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - convert help - Bayesian Filter detected spam
Gordon, ... SELECT CONVERT(fdata,BIGINT) INTO tmp_int; SELECT HEX(tmp_int); ... Are you looking for... SELECT CAST(0xABCDEF0123456789 AS UNSIGNED); +--+ | CAST(0xABCDEF0123456789 AS UNSIGNED) | +--+ | 12379813738877118345 | +--+ SELECT HEX(12379813738877118345); +---+ | HEX(12379813738877118345) | +---+ | ABCDEF0123456789 | +---+ PB - Gordon Bruce wrote: I just added a user variable @fdata to get visabilility outside of the procedure and this is what I get. mysql delimiter // mysql create procedure test20 () -BEGIN - DECLARE fdata BLOB; - DECLARE foffset INT UNSIGNED; - DECLARE flength INT UNSIGNED; - DECLARE tmp_int BIGINT UNSIGNED; - - SELECT 0xABCDEF0123456789 INTO fdata; - SELECT 14 INTO foffset; - SELECT 7 INTO flength; - - SELECT SUBSTR(BINARY(fdata), - FLOOR(foffset/8)+1, - CEIL((flength + (foffset %8 ))%8)) - INTO fdata; - set @fdata:=fdata; - END// Query OK, 0 rows affected (0.00 sec) mysql mysql delimiter ; mysql mysql call test20(); Query OK, 0 rows affected (0.00 sec) mysql mysql select @fdata, hex(@fdata) - - ; ++-+ | @fdata | hex(@fdata) | ++-+ | ═∩☺#E | CDEF012345 | ++-+ 1 row in set (0.00 sec) -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 19, 2006 3:33 PM To: mysql@lists.mysql.com Subject: [SPAM] - convert help - Bayesian Filter detected spam I am trying to convert binary data to a bigint so I can do bitwise operations on the data, and I'm having trouble doing it. I noticed that if I have binary data and I: select data1; I get 0 (not what I'm expecting). Here is a test procedure I wrote: create procedure test20 () BEGIN DECLARE fdata BLOB; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE tmp_int BIGINT UNSIGNED; SELECT 0xABCDEF0123456789 INTO fdata; SELECT 14 INTO foffset; SELECT 7 INTO flength; SELECT SUBSTR(BINARY(fdata), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))%8)) INTO fdata; SELECT HEX(fdata); SELECT CONVERT(fdata,BIGINT) INTO tmp_int; SELECT HEX(tmp_int); END The last two selects are added to show what I would like to do, but have not been able to get it to work. Any help would be great. Thanks in advance. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 1/19/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 1/19/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can MySQL jobs be posted to this list?
Thank you, Beau Gould Superior Staffing Solutions http://www.superiorss.com/jobs.htm Ruby/Rails Jobs Yahoo Group: http://groups.yahoo.com/group/rubyrails Human-Computer Interaction Jobs: http://groups.yahoo.com/group/HCIJobs Python, Zope Jobs: http://groups.yahoo.com/group/pythonzopejobs Open Source Jobs: http://groups.yahoo.com/group/opensourcejobs LAMP Jobs: http://groups.yahoo.com/group/LAMPjobs Video Game Jobs: http://groups.yahoo.com/group/gamejobz Cold Fusion Jobs: http://groups.yahoo.com/group/coldfusionjobs Wireless Jobs: http://groups.yahoo.com/group/wirelessjobz -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 1/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - convert help - Bayesian Filter detected spam
I forgot to do reply all, sorry peter for the duplicate: Thank you for the responses to my question, however with a little poking around after the suggestions, I still am unable to do the conversion. mysql select 0xABCDEF0123456789 into @fdata; Query OK, 1 row affected (0.00 sec) mysql select hex(@fdata); +--+ | hex(@fdata) | +--+ | ABCDEF0123456789 | +--+ 1 row in set (0.00 sec) mysql select cast(@fdata AS UNSIGNED); +--+ | cast(@fdata AS UNSIGNED) | +--+ |0 | +--+ 1 row in set, 1 warning (0.00 sec) mysql Essentially what I am trying to accomplish is I have a BLOB column that can have arbitrarily large data, and I am using SUBSTR to pull out sections of it, and some of it needs to be masked and bit shifted. I can pull out the data I'm interested in, however to do the masking and bit shifting it appears I need it to be an integer of some kind. So I am taking blob data and trying to convert it to an integer type (unsigned) so I can do those bitwise operations. So I am putting the data into a variable like: SELECT SUBSTR(BINARY(blob_col),offset,length)) into fdata; //example This works fine. I can view this data. The problem I am having is I can't convert it to UNSIGNED to do some further bitwise operations on the data. Thanks for any further help. Gordon, ... SELECT CONVERT(fdata,BIGINT) INTO tmp_int; SELECT HEX(tmp_int); ... Are you looking for... SELECT CAST(0xABCDEF0123456789 AS UNSIGNED); +--+ | CAST(0xABCDEF0123456789 AS UNSIGNED) | +--+ | 12379813738877118345 | +--+ SELECT HEX(12379813738877118345); +---+ | HEX(12379813738877118345) | +---+ | ABCDEF0123456789 | +---+ PB - Gordon Bruce wrote: I just added a user variable @fdata to get visabilility outside of the procedure and this is what I get. mysql delimiter // mysql create procedure test20 () -BEGIN - DECLARE fdata BLOB; - DECLARE foffset INT UNSIGNED; - DECLARE flength INT UNSIGNED; - DECLARE tmp_int BIGINT UNSIGNED; - - SELECT 0xABCDEF0123456789 INTO fdata; - SELECT 14 INTO foffset; - SELECT 7 INTO flength; - - SELECT SUBSTR(BINARY(fdata), - FLOOR(foffset/8)+1, - CEIL((flength + (foffset %8 ))%8)) - INTO fdata; - set @fdata:=fdata; - END// Query OK, 0 rows affected (0.00 sec) mysql mysql delimiter ; mysql mysql call test20(); Query OK, 0 rows affected (0.00 sec) mysql mysql select @fdata, hex(@fdata) - - ; ++-+ | @fdata | hex(@fdata) | ++-+ | ââ©âº#E | CDEF012345 | ++-+ 1 row in set (0.00 sec) -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:33 PM To: mysql@lists.mysql.com Subject: [SPAM] - convert help - Bayesian Filter detected spam I am trying to convert binary data to a bigint so I can do bitwise operations on the data, and I'm having trouble doing it. I noticed that if I have binary data and I: select data1; I get 0 (not what I'm expecting). Here is a test procedure I wrote: create procedure test20 () BEGIN DECLARE fdata BLOB; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE tmp_int BIGINT UNSIGNED; SELECT 0xABCDEF0123456789 INTO fdata; SELECT 14 INTO foffset; SELECT 7 INTO flength; SELECT SUBSTR(BINARY(fdata), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))%8)) INTO fdata; SELECT HEX(fdata); SELECT CONVERT(fdata,BIGINT) INTO tmp_int; SELECT HEX(tmp_int); END The last two selects are added to show what I would like to do, but have not been able to get it to work. Any help would be great. Thanks in advance. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 1/19/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 1/19/2006 --
Re: question about recovery with binlog
My mysql version is 5.0.16.
My problem is similar to the bug.
My sql mode is ANSI and TRADITIONAL
If mysql can't do recovery with mysqlbinlog at my sql mode until the bug will
be fixed?
Follow is the information of my binlog.
--
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#060119 13:55:30 server id 1 end_log_pos 98Start: binlog v 4, server v
5.0.16-standard-log created 060119 13:55:30
# Warning: this binlog was not closed properly. Most probably mysqld crashed
writing it.
# at 98
#060119 13:57:41 server id 1 end_log_pos 165 Query thread_id=4
exec_time=0 error_code=0
use wangxu;
SET TIMESTAMP=1137650261;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1;
SET @@session.sql_mode=501481487;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
insert ht_detail
values('79NK0006','79NK0006',1),('79NK0007','79NK0007',1),('79NK0008','79NK0008',1);
# at 263
#060119 13:57:41 server id 1 end_log_pos 290 Xid = 215
COMMIT;
# at 290
#060119 14:08:30 server id 1 end_log_pos 445 Query thread_id=7
exec_time=0 error_code=0
SET TIMESTAMP=1137650910;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wangxu` /*!40100 DEFAULT CHARACTER
SET latin1 */;
# at 445
#060119 14:08:30 server id 1 end_log_pos 542 Query thread_id=7
exec_time=0 error_code=0
SET TIMESTAMP=1137650910;
DROP TABLE IF EXISTS `ht_detail`;
# at 542
#060119 14:08:40 server id 1 end_log_pos 639 Query thread_id=8
exec_time=0 error_code=0
SET TIMESTAMP=1137650920;
DROP TABLE IF EXISTS `ht_detail`;
# at 639
#060119 14:23:13 server id 1 end_log_pos 733 Query thread_id=11
exec_time=0 error_code=0
SET TIMESTAMP=1137651793;
SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
SET @@session.sql_mode=501481487;
create table ht_header(a int);
# at 733
#060119 14:23:53 server id 1 end_log_pos 818 Query thread_id=12
exec_time=0 error_code=0
SET TIMESTAMP=1137651833;
drop table ht_header;
# at 818
#060119 14:28:10 server id 1 end_log_pos 915 Query thread_id=15
exec_time=0 error_code=0
SET TIMESTAMP=1137652090;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
DROP TABLE IF EXISTS `ht_detail`;
# at 915
#060119 14:29:44 server id 1 end_log_pos 998 Query thread_id=16
exec_time=0 error_code=0
use test;
SET TIMESTAMP=1137652184;
SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
SET @@session.sql_mode=501481487;
drop table ht_detail;
# at 998
#060119 14:30:29 server id 1 end_log_pos 1160 Query thread_id=16
exec_time=0 error_code=0
SET TIMESTAMP=1137652229;
create table ht_detail(hth varchar(30),sbh varchar(15),sbsl int,primary
key(hth,sbh)) type = innodb;
# at 1160
#060119 14:54:50 server id 1 end_log_pos 1243 Query thread_id=20
exec_time=0 error_code=0
SET TIMESTAMP=1137653690;
drop schema wangxu;
# at 1243
#060119 14:54:57 server id 1 end_log_pos 1328 Query thread_id=20
exec_time=0 error_code=0
SET TIMESTAMP=1137653697;
create schema wangxu;
# at 1328
#060119 14:55:17 server id 1 end_log_pos 1422 Query thread_id=20
exec_time=0 error_code=0
use wangxu;
SET TIMESTAMP=1137653717;
create table ht_detail(a int);
# at 1422
#060119 17:10:13 server id 1 end_log_pos 1505 Query thread_id=24
exec_time=0 error_code=0
SET TIMESTAMP=1137661813;
drop schema wangxu;
# at 1505
#060119 17:10:17 server id 1 end_log_pos 1590 Query thread_id=24
exec_time=0 error_code=0
SET TIMESTAMP=1137661817;
create schema wangxu;
# at 1590
#060119 17:10:47 server id 1 end_log_pos 1687 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
DROP TABLE IF EXISTS `ht_detail`;
# at 1687
#060119 17:10:47 server id 1 end_log_pos 1959 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
CREATE TABLE `ht_detail` (
`hth` varchar(30) NOT NULL default '',
`sbh` varchar(15) NOT NULL default '',
`sbsl` int(11) default NULL,
PRIMARY KEY (`hth`,`sbh`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# at 1959
#060119 17:10:47 server id 1 end_log_pos 2072 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
/*!4 ALTER TABLE `ht_detail` DISABLE KEYS */;
# at 2072
#060119 17:10:47 server id 1 end_log_pos 188 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
INSERT INTO `ht_detail` VALUES
('79NK0001/0003','79NK0001',1),('79NK0001/0003','79NK0002',1),('79NK0001/0003','79NK0003',1);
# at 2260
#060119 17:10:47 server id
Re: question about CONTAINS SQL
Thank you Rhino.
Your description is very detailed.
But my try was fail.No error has been throw out.
Follow is the process.
Please help me to analyse the result.
--
[EMAIL PROTECTED] bin]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.16-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql show databases;
++
| Database |
++
| information_schema |
| db1|
| db2|
| mysql |
| test |
| wangxu |
++
6 rows in set (0.03 sec)
mysql use wangxu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql show tables;
+--+
| Tables_in_wangxu |
+--+
| ht_detail|
+--+
1 row in set (0.00 sec)
mysql select * from ht_detail;
+---+--+--+
| hth | sbh | sbsl |
+---+--+--+
| 79NK0001/0003 | 79NK0001 |1 |
| 79NK0001/0003 | 79NK0002 |1 |
| 79NK0001/0003 | 79NK0003 |1 |
+---+--+--+
3 rows in set (0.01 sec)
mysql delimiter //
mysql create procedure test()
- deterministic
- contains sql
- begin
- insert ht_detail values('sdf','ser',4);
- end
- //
Query OK, 0 rows affected (0.02 sec)
mysql delimiter ;
mysql show create procedure test \G
*** 1. row ***
Procedure: test
sql_mode:
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
Create Procedure: CREATE PROCEDURE test()
DETERMINISTIC
begin
insert ht_detail values('sdf','ser',4);
end
1 row in set (0.00 sec)
mysql call test();
Query OK, 1 row affected (0.00 sec)
mysql select * from ht_detail;
+---+--+--+
| hth | sbh | sbsl |
+---+--+--+
| 79NK0001/0003 | 79NK0001 |1 |
| 79NK0001/0003 | 79NK0002 |1 |
| 79NK0001/0003 | 79NK0003 |1 |
| sdf | ser |4 |
+---+--+--+
4 rows in set (0.00 sec)
mysql
--
- Original Message -
From: Rhino [EMAIL PROTECTED]
To: wangxu [EMAIL PROTECTED]
Cc: mysql mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 9:29 PM
Subject:Re: question about CONTAINS SQL
I am copying the rest of the list with this so that everyone may benefit
from the discussion.
If your routine modifies data, in other words if it does SQL Update, Insert
or Delete but your routine definition says only CONTAINS SQL, I would
expect your routine to fail at runtime. I can't say this with certaintly
because I don't have one of the newer versions of MySQL that supports these
routines but I'm pretty sure that you will have a runtime failure. After
all, CONTAINS SQL implies only that you are creating objects like tables
within your routine; CONTAINS SQL does not permit the execution of Insert,
Update, or Delete. Therefore, I expect that you will get a runtime error as
soon as you do your first Insert, Update, or Delete. If you want to avoid
the error, use MODIFIES SQL instead of CONTAINS SQL.
Of course the best way to be sure is to try this for yourself. Try the
routine with CONTAINS SQL and see what happens at runtime. If it fails, as I
strongly expect, change CONTAINS SQL to MODIFIES SQL DATA and your error
will almost certainly go away.
Rhino
- Original Message -
From: wangxu [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]
Sent: Thursday, January 19, 2006 2:54 AM
Subject: Re: question about CONTAINS SQL
If I create a routine with modification operation and not spectify
characteristic in CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL
DATA.
In the maunal,the default value is CONTAINS SQL if i haven't spectify
a values.
If it's meaning that the routine with CONTAINS SQL include
modification operation?
Should many problem happen?
- Original Message -
From: Rhino [EMAIL PROTECTED]
To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 12:21 AM
Subject:Re: question about CONTAINS SQL
If you are writing something that does INSERT, UPDATE, or DELETE, you
need
to use the MODIFIES SQL DATA option.
Rhino
- Original Message -
From: wangxu [EMAIL PROTECTED]
To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, January 18, 2006 3:05 AM
Subject: Re: question about CONTAINS SQL
But what is
Re: question about CONTAINS SQL
At 8:29 -0500 1/19/06, Rhino wrote: I am copying the rest of the list with this so that everyone may benefit from the discussion. If your routine modifies data, in other words if it does SQL Update, Insert or Delete but your routine definition says only CONTAINS SQL, I would expect your routine to fail at runtime. I can't say this with certaintly because I don't have one of the newer versions of MySQL that supports these routines but I'm pretty sure that you will have a runtime failure. After all, CONTAINS SQL implies only that you are creating objects like tables within your routine; CONTAINS SQL does not permit the execution of Insert, Update, or Delete. Therefore, I expect that you will get a runtime error as soon as you do your first Insert, Update, or Delete. If you want to avoid the error, use MODIFIES SQL instead of CONTAINS SQL. Of course the best way to be sure is to try this for yourself. Try the routine with CONTAINS SQL and see what happens at runtime. If it fails, as I strongly expect, change CONTAINS SQL to MODIFIES SQL DATA and your error will almost certainly go away. No, these characteristics are merely advisory. The server doesn't impose any restraints based on them. Rhino - Original Message - From: wangxu [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Thursday, January 19, 2006 2:54 AM Subject: Re: question about CONTAINS SQL If I create a routine with modification operation and not spectify characteristic in CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA. In the maunal,the default value is CONTAINS SQL if i haven't spectify a values. If it's meaning that the routine with CONTAINS SQL include modification operation? Should many problem happen? - Original Message - From: Rhino [EMAIL PROTECTED] To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, January 19, 2006 12:21 AM Subject:Re: question about CONTAINS SQL If you are writing something that does INSERT, UPDATE, or DELETE, you need to use the MODIFIES SQL DATA option. Rhino - Original Message - From: wangxu [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, January 18, 2006 3:05 AM Subject: Re: question about CONTAINS SQL But what is INSERT OR UPDATE need? - Original Message - From: Rhino [EMAIL PROTECTED] To: wangxu [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 17, 2006 9:49 PM Re: question about CONTAINS SQL - Original Message - From: wangxu [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 17, 2006 12:35 AM Subject: question about CONTAINS SQL I notice there are one section in the manual: CONTAINS SQL indicates that the routine does not containstatements that read or write data. And that the option is default. It's true? If i wouldn't do read or write in routine.What can i do yet? Commands like GRANT or REVOKE or CREATE TABLE don't read or write data within tables but they involve SQL so commands like this need CONTAINS SQL, rather than the NO SQL, READS SQL DATA or MODIFIES SQL DATA options. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.19/231 - Release Date: 16/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 18/01/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date: 18/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
union/collation problem, error 1267: feature or bug?
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 | auto_increment | +--+--+--+-+---++ 16 rows in set (0.00 sec) localhost.addresses2 3. following character-sets and collations: localhost.addresses2 show session variables like character%; +--++ | Variable_name| Value | +--++ | character_set_client | utf8
