RE: Abt Mysqldump
Hi The only problem I get with mysqldump is that if I have used a reserved word as a column name, then the create starement fails. (eg CREATE TABLE fred ( KEY int(10) not null default '0' ); will fail (but as produced by mysqldump) However, if you edit the file and put ` characters either side of the column name (ie KEY above) then it then works. You can sort this in the first instance if you use the '--quote-names' or '-Q' option to mysqldump (OK, so I've just read the man page for the first time as well) Regards Matthew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 18:39 To: [EMAIL PROTECTED] Subject: Re: Abt Mysqldump use mysql database-name backupfilename I think , the backup file name should have .sql extension , like backup.sql Hoeven, Maarten van der wrote: What are the errors? For example, is the error like unable to create the tables, because the tables still exist? See the dumpfile if tables are created (by default). If so, delete the tables first, and redump back the dumpfile. -Original Message- From: Uma Shankari T. [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 1:33 PM To: [EMAIL PROTECTED] Subject: Abt Mysqldump Hello, I have dumped the database contents as one txt file by using this command mysqldump databasename -uusername -ppasswd textfilename. but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Zie ook/see also: http://www.knmi.nl/maildisclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] . -- Vidhya CS . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Abt Mysqldump
For grant statemnets grant all ON mysql.* to 'albert' is the correct syntax back ticks (`) are for around column names single quotes (') are for around strings/varchars/chars... M -Original Message- From: Albert [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 14:04 To: [EMAIL PROTECTED] Subject: Re: Abt Mysqldump Matthew (UK), I have two questions regarding this: 1. are you using the tick that is under the ~ sign on US keyboards, or the ' which is under the quotes on US keyboards? I tried both and get an error executing the following query in mysql client gui screen GRANT ALL mysql.* TO USER 'albert' mysql client tells me I have an SQL error I tried the other ` (the one under the ~) and that did not work either. 2. I have tried to enter passwords for users including root, yet mysql does not take them in, even though they are listed in my.ini (in clear which is odd) When I use the password in conjunction with the user I get an error that says access denied with password YES or sometimes with password NO. Any clues? Thanks for the help Albert Atlanta - Original Message - From: Matthew Smith [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:29 AM Subject: RE: Abt Mysqldump Hi The only problem I get with mysqldump is that if I have used a reserved word as a column name, then the create starement fails. (eg CREATE TABLE fred ( KEY int(10) not null default '0' ); will fail (but as produced by mysqldump) However, if you edit the file and put ` characters either side of the column name (ie KEY above) then it then works. You can sort this in the first instance if you use the '--quote-names' or '-Q' option to mysqldump (OK, so I've just read the man page for the first time as well) Regards Matthew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 18:39 To: [EMAIL PROTECTED] Subject: Re: Abt Mysqldump use mysql database-name backupfilename I think , the backup file name should have .sql extension , like backup.sql Hoeven, Maarten van der wrote: What are the errors? For example, is the error like unable to create the tables, because the tables still exist? See the dumpfile if tables are created (by default). If so, delete the tables first, and redump back the dumpfile. -Original Message- From: Uma Shankari T. [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 1:33 PM To: [EMAIL PROTECTED] Subject: Abt Mysqldump Hello, I have dumped the database contents as one txt file by using this command mysqldump databasename -uusername -ppasswd textfilename. but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Zie ook/see also: http://www.knmi.nl/maildisclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] . -- Vidhya CS . -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regex and email
Hi, Not sure about php syntax, but check out the perl module (in CPAN) Email::Valid. Here is the code fragment for the regex: # Regular expression built using Jeffrey Friedl's example in # _Mastering Regular Expressions_ (http://www.ora.com/catalog/regexp/). $RFC822PAT = 'EOF'; [\040\t]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\ xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xf f\n\015()]*)*\)[\040\t]*)*(?:(?:[^(\040)@,;:.\\\[\]\000-\037\x80-\x ff]+(?![^(\040)@,;:.\\\[\]\000-\037\x80-\xff])|[^\\\x80-\xff\n\015 ]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015]*)*)[\040\t]*(?:\([^\\\x80-\ xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x80 -\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040\t]* )*(?:\.[\040\t]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\ \\x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\ x80-\xff\n\015()]*)*\)[\040\t]*)*(?:[^(\040)@,;:.\\\[\]\000-\037\x8 0-\xff]+(?![^(\040)@,;:.\\\[\]\000-\037\x80-\xff])|[^\\\x80-\xff\n \015]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015]*)*)[\040\t]*(?:\([^\\\x 80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^ \x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040 \t]*)*)[EMAIL PROTECTED](?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([ ^\\\x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\ \\x80-\xff\n\015()]*)*\)[\040\t]*)*(?:[^(\040)@,;:.\\\[\]\000-\037\ x80-\xff]+(?![^(\040)@,;:.\\\[\]\000-\037\x80-\xff])|\[(?:[^\\\x80- \xff\n\015\[\]]|\\[^\x80-\xff])*\])[\040\t]*(?:\([^\\\x80-\xff\n\015() ]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\ x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040\t]*)*(?:\.[\04 0\t]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\ n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\ 015()]*)*\)[\040\t]*)*(?:[^(\040)@,;:.\\\[\]\000-\037\x80-\xff]+(?! [^(\040)@,;:.\\\[\]\000-\037\x80-\xff])|\[(?:[^\\\x80-\xff\n\015\[\ ]]|\\[^\x80-\xff])*\])[\040\t]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\ x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\01 5()]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040\t]*)*)*|(?:[^(\040)@,;:. \\\[\]\000-\037\x80-\xff]+(?![^(\040)@,;:.\\\[\]\000-\037\x80-\xff] )|[^\\\x80-\xff\n\015]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015]*)*)[^ ()@,;:.\\\[\]\x80-\xff\000-\010\012-\037]*(?:(?:\([^\\\x80-\xff\n\0 15()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x80-\xff][ ^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015()]*)*\)|[^\\\x80-\xff\ n\015]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015]*)*)[^()@,;:.\\\[\]\ x80-\xff\000-\010\012-\037]*)*[\040\t]*(?:\([^\\\x80-\xff\n\015()]*(? :(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80- \xff\n\015()]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040\t]*)*(?:@[\040\t]* (?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015 ()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015() ]*)*\)[\040\t]*)*(?:[^(\040)@,;:.\\\[\]\000-\037\x80-\xff]+(?![^(\0 40)@,;:.\\\[\]\000-\037\x80-\xff])|\[(?:[^\\\x80-\xff\n\015\[\]]|\\ [^\x80-\xff])*\])[\040\t]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\ xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]* )*\))[^\\\x80-\xff\n\015()]*)*\)[\040\t]*)*(?:\.[\040\t]*(?:\([^\\\x80 -\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x 80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040\t ]*)*(?:[^(\040)@,;:.\\\[\]\000-\037\x80-\xff]+(?![^(\040)@,;:.\\ \[\]\000-\037\x80-\xff])|\[(?:[^\\\x80-\xff\n\015\[\]]|\\[^\x80-\xff]) *\])[\040\t]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x 80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80 -\xff\n\015()]*)*\)[\040\t]*)*)*(?:,[\040\t]*(?:\([^\\\x80-\xff\n\015( )]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\ \x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040\t]*)[EMAIL PROTECTED] ]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\0 15()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015 ()]*)*\)[\040\t]*)*(?:[^(\040)@,;:.\\\[\]\000-\037\x80-\xff]+(?![^( \040)@,;:.\\\[\]\000-\037\x80-\xff])|\[(?:[^\\\x80-\xff\n\015\[\]]| \\[^\x80-\xff])*\])[\040\t]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80 -\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015() ]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040\t]*)*(?:\.[\040\t]*(?:\([^\\\x 80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\\x80-\xff\n\015()]*(?:\\[^ \x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x80-\xff\n\015()]*)*\)[\040 \t]*)*(?:[^(\040)@,;:.\\\[\]\000-\037\x80-\xff]+(?![^(\040)@,;:. \\\[\]\000-\037\x80-\xff])|\[(?:[^\\\x80-\xff\n\015\[\]]|\\[^\x80-\xff ])*\])[\040\t]*(?:\([^\\\x80-\xff\n\015()]*(?:(?:\\[^\x80-\xff]|\([^\\ \x80-\xff\n\015()]*(?:\\[^\x80-\xff][^\\\x80-\xff\n\015()]*)*\))[^\\\x 80-\xff\n\015()]*)*\)[\040\t]*)*)*)*:[\040\t]*(?:\([^\\\x80-\xff\n\015
RE: impossible to start mysql
Does the user you wish to run mysqld as have owner read write to /var/lib/mysql and its sub directories? (if you are not sure what I mean, can we see your my.cnf file and do a 'ls -la /var/lib/mysql') Matthew -Original Message- From: antoine druon [mailto:[EMAIL PROTECTED] Sent: 24 July 2003 12:45 To: [EMAIL PROTECTED] Subject: impossible to start mysql I try to install mysql 3.23 on mandrake 9.0 but I have somme problems first I have this problem /usr/share/rpm-helper/add-service: line 27: [: : integer expression expected a the end of install i saw for mandrake 9.1 a patch so i modified my add-service in rpm- helper but after that a the end of install i receive this message /usr/sbin/mysqld: Shutdown Complete and impossible t start mysql with safe_mysqld error message is : shell-init: could not get current directory: getcwd: cannot access parent directories: No such file or directory pwd: could not get current directory: getcwd: cannot access parent directories: No such file or directory Starting mysqld daemon with databases from /var/lib/mysql 030724 13:44:37 mysqld ended please help me thanks -- 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: CSV Formated output
Don't forget ouput with spaces/tabs/quotes etc... The more adventurous solution use perl use DBI use DBD:CSV(or Text::CSV_XS) use DBD:mysql then just take the output of the select and insert into the CSV Matthew On Thu, 3 Jul 2003, Jeff McKeon wrote: Is there a way to output the results of a select query into a CSV or Comma Deliminated format? Thanks, Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -- 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: wierd sort query, how do you do it? (sort by ip proximity guess)
Hi, If you convert the IP address into integers, then do an xor (exclusive or), you could call the resultant value the approximate 'distance' between the values. (think of the IP address in binary, any identical bits will result in 0, any differing will be 1. This means that the most significant '1' will be the first dissimilar bit between the two IP addresses. This is really what you are after. However, including any lesser significant bits may well be OK for your application.) OK, so how in SQL You need to create, on the fly ipAddress1 ^ ipAddress2 (order does not matter) where they are both integers (mysql will use 64bit integer arithmetic). You just need to ORDER BY this value. Regards Matthew -Original Message- From: Ray [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:33 To: [EMAIL PROTECTED] Subject: wierd sort query, how do you do it? (sort by ip proximity guess) currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same a -- 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: Odd thing
Hi, are you using MSDOS/Windows anywhere here - it might be a trailing ^M which Windows uses before ^J as the end of line character. Regards Matthew -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:50 To: 'Jay Blanchard'; '[EMAIL PROTECTED]' Cc: Dickey, Dallas Subject: RE: Odd thing Just creating shell scripts with one line creating each file. This is our first attempt to see how working with the smaller files performs while transferring data between sites. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:43 AM To: Christensen, Dave; [EMAIL PROTECTED] Cc: Dickey, Dallas Subject: RE: Odd thing [snip] We're trying to set up some scripts that will run mysqldump to create export files for each table in a database that can be downloaded to a test server and reloaded. What's weird is that the files are created with the ? character as the trailing character in the file name and we can't open them to do anything with the files. [/snip] How are your creating them? Script language? Jay -- 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: Accessing last_insert_id problem.
LAST_INSERT_ID is held for the database connection, not agaist the server So, as long as you do not do another insert using the same database connection, LAST_INSERT_ID will be fine. (for database connection, $dbh=DBI-connect. ) No table locking required. Regards M On Wed, 18 Dec 2002, Jeff Snoxell wrote: Date: Wed, 18 Dec 2002 12:21:14 + From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Accessing last_insert_id problem. Hi, I'm adding records to a db using the Perl DBI. Subsequent to adding a record I need to know the value of the auto-incrementing 'Ref' field so that I can place a copy of the relavent details into a log file. I could query for the LAST_INSERT_ID but what if another process has added another record in the interim? Is this a job for table locking? Many thanks, Jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Matthew Smith Nominet UK - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: query requiring two results from one table?
Hi, Your query will need to look like: select G.datetime, A.name, B.name from Games as G, Teams as A, Teams as B where A.teamid = G.homeid and B.teamid = G.awayid and G.datetime ? and G.datetime ? order by G.datetime would be a good place to start Regards M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 30 December 2002 17:11 To: [EMAIL PROTECTED] Subject: query requiring two results from one table? Hello, This is my first post to the list, so if I am asking in the wrong place, I apologize. I've got some trouble putting together a query with the following tables Games: Teams: gameid teamid homeid name awayid datetime i want to get all games within a certain timeframe, but also retrieve the team names for both the homeid and awayid (these are both links to the teams.teamid field). Not sure how to get two results from the same table in one query. Can anybody point me in the right direction. thanks gf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: one mysql-test issue
Does it have write permissions for the user running the tests? M -Original Message- From: Yu, Jerry [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 08:03 To: Matthew Smith; Yu, Jerry; [EMAIL PROTECTED] Subject: RE: one mysql-test issue Sure, the directory exists. :) Rgds --Jerry -Original Message- From: Matthew Smith [mailto:[EMAIL PROTECTED]] Sent: 2002Äê11ÔÂ19ÈÕ 15:51 To: Yu, Jerry; [EMAIL PROTECTED] Subject: RE: one mysql-test issue Does the directory /usr/local/mysql/var/run exist? If not, try creating it! Regards Matthew Smith -Original Message- From: Yu, Jerry [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 07:41 To: [EMAIL PROTECTED] Subject: one mysql-test issue Hi,all I built the mysql-4.0.3-beta source on redhat7.3 with gcc2.95.3 using only one option: --prefix=/usr/local/mysql. It is built successfully and installed to /usr/local/mysql. But during running the mysql-test, it always aborted and reported that /usr/local/mysql/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting when it tried to execute the test cases after the test case create. When I use the binary distribution, it's ok. But we have to build it by ourselves due to some reasons. Does anyone meet the same problem before? How to resolve it? Any comments are appreciated! Yours sincerely Jerry Yu, Opinions expressed are those of the author and do not represent Intel Corporation - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: one mysql-test issue
Does the directory /usr/local/mysql/var/run exist? If not, try creating it! Regards Matthew Smith -Original Message- From: Yu, Jerry [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 07:41 To: [EMAIL PROTECTED] Subject: one mysql-test issue Hi,all I built the mysql-4.0.3-beta source on redhat7.3 with gcc2.95.3 using only one option: --prefix=/usr/local/mysql. It is built successfully and installed to /usr/local/mysql. But during running the mysql-test, it always aborted and reported that /usr/local/mysql/mysql-test/var/run/master.pid was not created in 30 seconds; Aborting when it tried to execute the test cases after the test case create. When I use the binary distribution, it's ok. But we have to build it by ourselves due to some reasons. Does anyone meet the same problem before? How to resolve it? Any comments are appreciated! Yours sincerely Jerry Yu, Opinions expressed are those of the author and do not represent Intel Corporation - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Insert default Date
In Mysql (http://www.mysql.com/doc/en/Date_and_time_functions.html) funnily enough: NOW() orSYSDATE() will return what you want. Regards M -Original Message- From: Arthur [mailto:[EMAIL PROTECTED]] Sent: 15 October 2002 12:56 To: MYSQL Subject: Insert default Date Hello MYSQL, In access Now() as table field default sets the field when a new record is created. In SQL Server there is getdate() suser_sname() Is there an equivalent for MySQL? -- Best regards, Arthur mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Math Computations
delz wrote: Hi All, How do I make calculations in php or mysql if the value has a comma like 2,000 + 32,000 = 34,000. I have a problem calculating if one of my values has a comma. It doesn't display the right output. I'll appreciate anyone who can help me with this. Thanks. Delz. Hi Delz Are you out by an order of magnitude? I don't know where abouts in the world you are, but whereas US/UK/AUS uses the comma as a thousands separator and the full stop/period as a decimal separator, in Europe, the reverse is true. I just did a test, inserting the value 1,000 into a float field. I pulled that value out again and it was 1. Repeated the test with a dot instead - same result! So, I would advise: 1) Only store numbers in suitable numeric fields (not varchar/char). 2) Get rid of *all* numeric punctuation before inserting it into a database. Example in Perl: $mynumber =~ s/.//g; $mynumber =~ s/,//g; Cheers M -- Matthew Smith | _ ASCII Ribbon Campaign IT Consultant | ( ) - No HTML/RTF in e-mail Kadina, South Australia | X - No Microsoft docs in e-mail http://www.kbc.net.au | / \ - No vCards Ph: +61 888 212 395 | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql daemon
ps ax | head -1 PID TT STAT TIME COMMAND from 'man ps' TT is the controlling terminal. a ?? in the TT column means that it is not controlled by a terminal. Daemons and some background processes detach from terminals. This is normal and to be expected for mysqld and other daemons started at boot time. M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 27 June 2002 10:41 To: MySQL List Subject: mysql daemon [localhost:~] ll% ps ax | grep mysqld 349 ?? S 0:00.04 sh ./bin/safe_mysqld 385 ?? S 0:00.08 /usr/local/mysql-3.23.51/bin/mysqld 10102 std R+ 0:00.00 grep mysqld [localhost:~] ll% Are these ?? supposed to be there, what do they mean? Maybe this is indicative of my problem. Or maybe it means nothing. Li - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Platforms for MYSQL
MySQL (server) will run on Win9X, WinNt and later platforms. I use it with Delphi on my Win98 laptop and on my Win2K desktop. You will need TCP/IP for your peer to peer network. Much better than Paradox M -Original Message- From: Colin Rooke [mailto:[EMAIL PROTECTED]] Sent: 17 April 2002 16:28 To: '[EMAIL PROTECTED]' Subject: Platforms for MYSQL Sirs, I am looking for a database replacement for Paradox. We have a variety of users of existing Delphi applications using a number of hardware configurations Can you please confirm whether or not MYSQL will run standalone and on a peer to peer network ? Thank you for your assistance Regards, Colin Rooke - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication Oddity
Hi I am running MySQL 3.23.49a, installed from the official RPMs. The master is on a (Linux) machine with a permanent Internet connection and an routeable IP address. The slave is at another location with a *nearly* permanent Internet connection, behind a masquerading firewall. Due to restrictions on ISP firewalls, I have set the MySQL port on both machines to be a non-standard 119 - this is normally the NNTP port, but I have NNTP commented out in inetd.conf to free the port up. What I am finding puzzling is that replication is only working some of the time. There are no errors occuring in the error logs at either end. Master and slave stati show the same position. If I make a change on the master, the slave position always changes to match. *However*, the data on the slave does not necessarily change! All the transactions are updates and they have been tested in char and text fields on two different tables. Sufficient to say, this all worked fine when I tried it out in the lab (two machines on same LAN). Has anyone seen anything like this before, or can they give me any pointers as to what might be happening? Cheers M -- Matthew Smith | _ ASCII Ribbon Campaign IT Consultant | ( ) - No HTML/RTF in e-mail Kadina, South Australia | X - No Microsoft docs in e-mail http://www.kbc.net.au | / \ - No vCards Ph: +61 888 212 395 | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MANY to MANY] relationship with MySQL ???
Hi Robbie, all I have lots of things that need many to many - the way that I do it is this: create table artists ( a_ser int unsigned not null primary key auto_increment, artist_name varchar(64), other stuff... ); create table songs ( s_ser int unsigned not null primary key auto_increment, song_title varchar(64), more other stuff... ); create table song_x_artist ( a_ser int unsigned not null, s_ser int unsigned not null ); ...So the table song_x_artist contains references to all artists in a song and vice-versa. (Field names correspond between tables.) Hope this helps - let me know if not clear. It works for me... Cheers Matthew Smith Robbie Newton wrote: Hello all, [~~I guess I could do something like MySQLselect FROM Songs where artists contains $currentArtist~~] I have a sampling section on a site that I am working on and am trying to find out how to display a table of the artists featured in that song. The sample section works as follows: Go to the samples page and choose an album to sample. Returns the list of song names that result from the album you chose click on one of the song names to open a new window with the shockwave file that streams the song. The client has asked me to add the (one or many) artists featured in that song in the window that loads up. I am not sure how to set this up. It would be a many to many relationship I think... Artists Table (one given artist could be featured on many different songs) [many] to Songs Table (one given song could have many featured artists) [many] My first thout of how to set this up is to just enter comma delimited data into a field in the song database called, artists. But when I query it I don't know how I could make that work. I guess I could do something like MySQLselect FROM Songs where artists contains $currentArtist Would that work? Is there a such thing as contains. (kinda new to SQL). Thanks for your help, Robbie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Encrpt/Decrypt
Manish Mehta wrote: Hi I can Encrypt password in mysql using password() command. what is the way to decrypt the password. Manish Hi Manish / All Somebody correct me if I'm wrong, but I think that what happens is what's called one-way encryption. This is how stuff is done in the world of Unix (and its clones) and others. Authentication is done by taking the password provided, encrypting it, then comparing against the encrypted key in the database. Cheers M -- Matthew Smith | _ ASCII Ribbon Campaign IT Consultant | ( ) - No HTML/RTF in e-mail Kadina, South Australia | X - No Microsoft docs in e-mail http://www.kbc.net.au | / \ - No vCards Ph: +61 888 212 395 | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [MANY to MANY] relationship with MySQL ???
Hi Robbie Reading what I wrote wasn't very clear to me either ;-) How does song_x_artist reference the other databases? Is there a way to set up a field to always be a reference to another table? I think you may be talking foreign keys here - something yet to come in MySQL AFAIK. I am not quite sure what you mean... but here is my take anyways. Is this going around my elbow? Or is this just how you do relationals in SQL? This is a standard way of doing many-to-many relationships in SQL; there may be others. (For each relationship you may want to have in a system, you must set up a table to house that relationship?) For each M2M, basically, yes. Table::song_x_artist * PrimID (can't duplicate) SongID (can duplicate) ArtistID (can duplicate) Yep - I don't use the primary key when I do it, but by using one you can obviously avoid unwanted duplications. (PHP) MySQL * $songID = (the id of the song the user selected to sample) $artistsOnSong = Select * from song_x_artist where songID = $songID while ($artistArray= mySQL_Fetch_Array ($artistsOnSong)){ $currentArtists = Select * from Artists where artistID = $artistArray[ArtistID] while ($artistInfo= mySQL_Fetch_Array ($currentArtists)){ //start to build the html table of the related artists for the song here. } } Would that work? And if it does... I am not so sure about how to write it out, so if you have any suggestions on better syntax, please advise. 'Ang on - I'm not a PHP man so I've got to think my way through the code... Yep - if I read that correctly, should be OK. Assuming that we're starting with a song, the steps are: 1) Grab the song ID (primary key) from the songs table. 2) Select all the Artist IDs from the song_x_artist (x-reference) table and push them into an array. (This is why we want sub-selects in MySQL 4!) 3) Pop each value from the array and use it as the criterion to select all the details that you want from the artists table - what comes back is your final results. So, yes, what you've done looks right. Only one way to find out ;-) Cheers Matthew Smith - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to store an Image on MySQL
Check the archives, it has been covered reacently, but assuming something like perl: $sql=qq{ insert into some_table (some_blob) values ( ?) }; $sth=$dbh-prepare($sql); $sth-execute( $my_blob); Regards M -Original Message- From: Hector Rosas [mailto:[EMAIL PROTECTED]] Sent: 28 February 2002 17:52 To: [EMAIL PROTECTED] Subject: How to store an Image on MySQL Hi Everybody, I have a problem with MySQL when I try to store image file on it. I use a Blob field but the info that must be stored there should be between quotations marks right? Like a string , i mean GIF89kjflH.. more binary symbols... but if the image file information have a quotation in the file the info would get truncate and MySQL will not store it. My Question is How can I store a Image on MySQL, and I know that is't bettter to use a path to the image but I can't do this in that way. Any help would be great Thanks. Hector. -- Get your free email from www.linuxmail.org Powered by Outblaze - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How does one put images into a MySql database
This is an OK solution for a small number of images. Most filesystems (unix/linux et al included) have a severe time penalty in opening a known file in a folder when the folder contains many files. So while easier to hold a reference to a file (filename) it will become slower when your number of files gets large. Next step using files is to use many folders, but then this provides its own problems. I have a small database (growing) of just over 400,000 images. A perl cgi program gets the imags on the fly and outputs them without creating files and is _very_ fast. M -Original Message- From: Brian P. Austin [mailto:[EMAIL PROTECTED]] Sent: 22 January 2002 14:27 To: Rochester, Dean Cc: mysql Subject: RE: How does one put images into a MySql database You can put images in the DB, but the easiest way is to put the path to the images in a text field, and rely on the disk to send the images. hope that helps, Brian -Original Message- From: Rochester, Dean [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 22, 2002 7:50 AM To: MySQL (E-mail) (E-mail) Subject: How does one put images into a MySql database Greeting all Is there a way to put images like jpgs, tifs, gifs, bmp files into a mysql database? I have done this with OLE objects using MS Access... can this be done using the blob data type some how? Thanks in advance Dean-O - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Column Names
What would SELECT 42 FROM SOME_TABLE return? column name 42 or the numerical value 42 ? M -Original Message- From: Sparta Cruz [mailto:[EMAIL PROTECTED]] Sent: 07 January 2002 08:23 To: MySQL Subject: Column Names MySQL's Documentation: A name may start with any character that is legal in a name. In particular, a name may start with a number (this differs from many other database systems!). However, a name cannot consist only of numbers. MyQuestion: Why can't a column name be just a number? For example: CREATE TABLE Odorants( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, odorant VARCHAR(30) NOT NULL, row TINYINT UNSIGNED NOT NULL, 1 DOUBLE(4,20), 2 DOUBLE(4,20), 3 DOUBLE(4,20), 4 DOUBLE(4,20), 5 DOUBLE(4,20), 6 DOUBLE(4,20), 7 DOUBLE(4,20), 8 DOUBLE(4,20), 9 DOUBLE(4,20), 10 DOUBLE(4,20), 11 DOUBLE(4,20), 12 DOUBLE(4,20), 13 DOUBLE(4,20), 14 DOUBLE(4,20), 15 DOUBLE(4,20), 16 DOUBLE(4,20), 17 DOUBLE(4,20), 18 DOUBLE(4,20), 19 DOUBLE(4,20), 20 DOUBLE(4,20), 21 DOUBLE(4,20), 22 DOUBLE(4,20), 23 DOUBLE(4,20), 24 DOUBLE(4,20), 25 DOUBLE(4,20), 26 DOUBLE(4,20), 27 DOUBLE(4,20), 28 DOUBLE(4,20), 29 DOUBLE(4,20), 30 DOUBLE(4,20), 31 DOUBLE(4,20), 32 DOUBLE(4,20), 33 DOUBLE(4,20), 34 DOUBLE(4,20), 35 DOUBLE(4,20), 36 DOUBLE(4,20), 37 DOUBLE(4,20), 38 DOUBLE(4,20), 39 DOUBLE(4,20), 40 DOUBLE(4,20), 41 DOUBLE(4,20), 42 DOUBLE(4,20), 43 DOUBLE(4,20), 44 DOUBLE(4,20), PRIMARY KEY (id), UNIQUE UC_id (id)); ERROR 1064: You have an error in your SQL syntax near '1 DOUBLE(4,20), 2 DOUBLE(4,20), 3 DOUBLE(4,20), 4 DOUBLE(4,20), 5 DOUBLE(4,' at line 5 Other: Any thoughts or suggestions are welcomed. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: creating users ??? I must be blind.
look for 'GRANT SYNTAX' in the manual. Regards M -Original Message- From: Matthew Darcy [mailto:[EMAIL PROTECTED]] Sent: 07 January 2002 13:16 To: MySql List Subject: creating users ??? I must be blind. Hi, the manual referes to creating users using mysqladmin and mysqlaccess, I have looked at these commands and cannot see how I can create mysql users. Am I missing something that is staring me in the face ? Thanks, Matt. (slowing getting the hang of what is going on now) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Password reset.
Initially you will have had no password, so setting root's password to 'test1' would be done with: mysqladmin -u root password test1 To change this password to test2 use: mysqladmin -u root -ptest1 password test2 Regards M -Original Message- From: Matthew Darcy [mailto:[EMAIL PROTECTED]] Sent: 07 January 2002 12:59 To: [EMAIL PROTECTED] Subject: Password reset. I just installed mysql under my new linux system. I did a test command of ./mysqladmin -u root password new-password thus setting the root users password to new-password this appeared to work. I now want to change the root users password now that I have the syntax correct. I do a ./mysqladmin -u root -p password test1 this should set the password to test1 correct ? I get this error ./mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' can anyone explain why this is happening so that I can better understand how this command is working. Thanks, Matt. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select with dbi perl??? Help please!
I would suggest turning logging on ('log' entry in my.cnf under mysqld) do a search and look to see what the actual SELECT parsed by the mysqld server was. My hunch is that it is only seeing '%' and not '123456789123%' as expected. OK, so I now look at your code. It will be looking for '$input{pin}%' and NOT veriable substituted since it has SINGLE quotes (perl treats things in single quotes as literals). A better way is: my $sql = qq{ SELECT * FROM pins WHERE pin LIKE ? OR msisdn LIKE ? OR knd LIKE ? }; my $sql=$dbh-prepare($sql); $sql-execute( %.$input{pin}.%, %.$input{msisdn}.%, %.$input{knd}.%); If you want exect matches, use: $sql-execute( $input{pin}, $input{msisdn}, $input{knd}); This makes quoting problems go away. (Imagine if a single or double quote was in one of the $input variables - what would the SELECT look like... OK, so you could get round the problem with $input{pin}=$dbh-quote($input{pin}) but much easier to bind variables as above. Using '?' makes your code much easier to read. [Note, the '%' in a LIKE expression matches any zero or more characters.] Regards M -Original Message- From: Sascha Kettner [mailto:[EMAIL PROTECTED]] Sent: 07 January 2002 07:38 To: [EMAIL PROTECTED] Subject: Select with dbi perl??? Help please! HI! Thanks for your Help! I´ve tried this, but when inserting the % now all my selections seem to be valid or successful, even if there is no match! What is wrong? My table for testing has the following entries: Pin: 123456789123 msisdn:+491231234567knd:test PIN: 123456789012 msisdn:0knd:test So when i insert into my form the search for test, f.e. i get both entries as a result, but even when i insert the search for 123456789123 i get both results as before but when you look at my table, you see, that only one entry should be displayed! So any other ideas? Regards Sascha Kettner -Ursprüngliche Nachricht- Von: Jesse Shy [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 6. Januar 2002 23:54 An: [EMAIL PROTECTED] Betreff: RE: Select with dbi perl??? Help please! I have found that unless I put a % sign either before, after or both, to denote the match part in question, that I don't get anything. Try this: LIKE '$input{pin}%' that should do it. If not, copy the SQL statement from your script to the mysql client to test. I wouldn't retypr it, I would copy it so you try exactly what is in your script. Good luck! -Original Message- From: Sascha Kettner [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 06, 2002 4:14 PM To: [EMAIL PROTECTED] Subject: Select with dbi perl??? Help please! Hi! I have the following script to be executed via post from a web-form; the var. Pin, msisd and knd are given by the form but however, the script is not working. I always get no results as if there are no matches, but this isnt right! This is regardless which entries i submit with the form! Any ideas to fix the problem? Thanks a lot in advance Regards Sascha Kettner #!/usr/bin/perl use DBI(); # # Get form Data # # parse_form; # Script Variables # $input{knd} = ; $input{pin} = ; $input{msisdn} = ; # What to do on submit # dojob; ## # Lets have a look at the db # ## sub dojob { # Now retrieve data from the table. my $dbh = DBI-connect(DBI:mysql:database=prepaid;host=localhost, root, sascha28, {'RaiseError' = 1}); my $sth = $dbh-prepare(SELECT * FROM pins WHERE pin LIKE '$input{pin}' OR msisdn LIKE '$input{msisdn}' OR knd LIKE '$input{knd}'); print EOF; Content-type: text/html html !--# Include Virtual=/blank.html-- head titleMAKEPINS/title meta http-equiv=refresh content=10;url=https:/index.html /head body h1 align=centerSuche Ausgeführt/h1hrbrbr /body /html EOF $sth-execute(); while (my $ref = $sth-fetchrow_hashref()) { print Eintrag gefunden: pin = $ref-{'pin'}, msisdn = $ref-{'msisdn'}\n, knd = $ref-{'knd'}\n\n; } $sth-finish(); # Disconnect from the database. $dbh-disconnect(); exit; } ## # Get form data function # ## sub parse_form { read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'}); if (length($buffer) 5) { $buffer = $ENV{QUERY_STRING}; } @pairs = split(//, $buffer); foreach $pair (@pairs) { ($name, $value) = split(/=/, $pair); $value =~ tr/+/ /; $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack(C, hex($1))/eg; $input{$name} = $value; } } ### # The end # ### - Before posting, please check: http://www.mysql.com/manual.php (the manual)
RE: Perl Problem!
If you are doing lots of similar sql statements, you could use the following instead: --- start my $dbh = DBI-connect(DBI:mysql:database=prepaid;host=localhost, root, sascha28, {'RaiseError' = 1}); my $sql=qq{ INSERT INTO pins (pin, msisdn, value, knd) VALUES ( ?, ?, ?, ?) }; my $sth=$dbh-prepare($sql); while(my $reader = GENERIERT) { chomp($reader); print GETESTET $reader\n; $sth-execute($reader, 'frei', $wert, $kunde); } # Disconnect from the database. $dbh-disconnect(); --- end This should be faster than many do statements as the SQL needs only be parsed once. It also makes the code slightly cleaner to read. It also gets round the quoting problem. Your actual problem was perhaps: VALUES ($reader, 'frei', $wert, '$kunde') ^^^ ^ maybe $reader and $wert were not quoted strings M -Original Message- From: Amer Neely [mailto:[EMAIL PROTECTED]] Sent: 24 December 2001 15:58 To: MySQL Subject: Re: Perl Problem! Hi! I have a proplem using my perl code to write some data into my mysql database. When running in consolemode anything works fine, but when executed by the wwwserver the scripts is processed without errors, except the data is not written into the database! Any idea how to fixit? Thanks in advance Sascha PS: the code sub generatepins { open (GETESTET, /usr/local/httpd/sms/generated.pin); open (GENERIERT, ./pinanfrage.dat); db connect und write my $dbh = DBI-connect(DBI:mysql:database=prepaid;host=localhost, root, sascha28, {'RaiseError' = 1}); while(my $reader = GENERIERT) { chomp($reader); print GETESTET $reader\n; $dbh-do(INSERT INTO pins (pin, msisdn, value, knd) VALUES ($reader, 'frei', $wert, '$kunde')); } # Disconnect from the database. $dbh-disconnect(); close GENERIERT; close GETESTET; } First try printing your query. I used to use 'do' but switched to the longer method below. It gives you more control over tracing problems I think. Try something like this: mysql_connect($DBhost,$DBuser,$DBpass) or die(Unable to connect to database); @mysql_select_db($DBName) or die(Unable to select database $DBName); $query=(INSERT INTO pins (pin, msisdn, value, knd) VALUES(\$reader\, \frei\, \$wert\, \$kunde\)); # quote everything. singles would work too $result=mysql_query($query); if (!$result) die (Query 953 failed.); # I use the line number for the Query - easy to find If that doesn't work, print out $query to see what it *really* contains. If your column values are going to be variables, try something like: $last=$dbh-quote(Beck); $first=$dbh-quote(Jeff); $query=qq{insert members (last_name,first_name) values ($last,$first); $result=mysql_query($query); Another trick I use for debugging is to put the following at the top of each script: BEGIN { open (STDERR,$0-err.txt); print STDERR \n,scalar localtime,\n; } It merely puts the error messages into a file in the directory the script is running in. That is a lot easier to find than going through server logs. Make sure you comment it out once the script is running well. -- Amer Neely [EMAIL PROTECTED] Softouch Information Services: www.softouch.on.ca/ Perl / PHP / CGI programming for shopping carts, data entry forms. We make web sites work! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problems in Foreign Key Syntax
I do not see this as a feature(documented bug). It is a handy implementation method for potentially saving storage space. Not all database designers (people who design databases as opposed to those who have a qualification to do so) understand the meaning of the various types. One database I have just been given has CHAR(255) for all fields, mainly address, name, telephone, and email. For these people, changing to varchar will save a lot of space. However, it would be nice to be able to (either as a global, database, or table level) be able to disable this silent column change functionality. M -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: 22 December 2001 18:03 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Problems in Foreign Key Syntax Hi! Ok, I looked in the manual and found the following: http://www.mysql.com/doc/S/i/Silent_column_changes.html If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See section 7 MySQL Table Types. So the bug is actually a 'feature'. Of course it would be better if the parser would not change declared types of columns. If a table handler wants to store CHAR columns like VARCHAR columns in some cases, it should make the decision at a lower level. It is not the parser's job to decide this. To avoid that this feature prevents creation of foreign key constraints, I will allow a VARCHAR column to reference a CHAR column, and vice versa, in 3.23.47. Regards, Heikki Tuuri Innobase Oy --- Order commercial MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Date: Sat, 22 Dec 2001 19:45:24 To: [EMAIL PROTECTED] From: Heikki Tuuri [EMAIL PROTECTED] Subject: Re: Problems in Foreign Key Syntax Amit, thank you for a bug report. There seems to be a bug in the MySQL parser, because MySQL internally makes the first column of the table AttributeValue as of type VARCHAR(25), though you have declared it as CHAR(25). Since the internal type of the referenced column is CHAR(25) but the referring column is VARCHAR(25), InnoDB gives an error and does not accept the declaration. Workaround: declare Attribute as VARCHAR(25) in both tables and Scenario as VARCHAR(10) in both tables. I have forwarded this email to Monty so that he can fix the MySQL parser. I tested also with the following declarations, and in these cases the MySQL parser works ok: Attribute is internally a CHAR(25) column. CREATE TABLE AttributeValue6(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute (Attribute) ) TYPE = INNODB; CREATE TABLE AttributeValue100(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute(Attribute) , Scenario CHAR(10) NOT NULL, INDEX sce_ind(Scenario), FOREIGN KEY (Scenario) REFERENCES Scenario(Scenario) ) TYPE = INNODB; Maybe the bug is that the VARCHAR definition of the column Value is in the parser put also on other columns. Regards, Heikki Tuuri Innobase Oy --- Order commercial MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Hi!! I am trying create the following tables:- CREATE TABLE Scenario ( Scenario CHAR(10) NOT NULL PRIMARY KEY, CopyFromScenario CHAR(10)) TYPE = InnoDB; CREATE TABLE Attribute( Attribute CHAR(25) NOT NULL PRIMARY KEY,Type CHAR(25)) TYPE = InnoDB; CREATE TABLE AttributeValue(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute(Attribute) ,Value VARCHAR(150) NOT NULL, Scenario CHAR(10) NOT NULL, INDEX sce_ind(Scenario), FOREIGN KEY (Scenario) REFERENCES Scenario(Scenario) ) TYPE = INNODB; But for the third table AttributeValue it is giving the error as:- ERROR 1005: Can't create table '.\netaps\AttributeValue.frm' (errno: 150 Please help Urgent Thanks Amit Lonkar. Amit CREATE TABLE Scenario (Scenario CHAR(10) NOT NULL PRIMARY KEY, CopyFromScenario CHAR(10)) TYPE = InnoDB; CREATE TABLE Attribute( Attribute CHAR(25) NOT NULL PRIMARY KEY, Type CHAR(25)) TYPE = InnoDB; CREATE TABLE AttributeValue(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute(Attribute) , Value VARCHAR(150) NOT NULL, Scenario CHAR(10) NOT NULL, INDEX sce_ind(Scenario), FOREIGN KEY (Scenario) REFERENCES Scenario(Scenario) ) TYPE = INNODB; CREATE TABLE AttributeValue6(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute (Attribute) ) TYPE = INNODB;
RE: hostname.pid
Does the unix user who wunns mysqld have write permissions to /var/local/var (and does it exist?) M -Original Message- From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]] Sent: 23 December 2001 15:09 To: Mysql maillist Subject: hostname.pid How do I correct this database problem ? Please also reply to my E-Mail address. /usr/local/libexec/mysqld: Can't create/write to file '/usr/loc al/var/hostname.pid' (Errcode: 13) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: hostname.pid
The option --pid-file=/var2/db can be used for mysqld In my.cnf use pid-file=/var2/db Matthew -Original Message- From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]] Sent: 23 December 2001 15:26 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Mysql maillist Subject: RE: hostname.pid Thanks for fast reply's :) How can I change it so that MySQL looks for the file at /var2/db by using the my.cnf file ? Regards Jacob [ www.sharksforum.com | www.eksperten.dk | +45 70 27 07 67 ] -Original Message- From: Almar van Pel [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 23, 2001 4:18 PM To: Mysql maillist Cc: Jacob Friis Larsen Subject: RE: hostname.pid Hi, Check your autorisations for this file for the user mysql runs under. It does not have permissions to access the specified directory. Regards, Almar van Pel -Oorspronkelijk bericht- Van: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]] Verzonden: zondag, december 23, 2001 16.09 Aan: Mysql maillist Onderwerp: hostname.pid How do I correct this database problem ? Please also reply to my E-Mail address. /usr/local/libexec/mysqld: Can't create/write to file '/usr/loc al/var/hostname.pid' (Errcode: 13) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqldump - basic question
You could use one of: 1. .my.cnf with a password set for the client 2. use -ppassword on the command line Depends on how secure you want it - use the appropriate grant for the user. M -Original Message- From: ROry O'Connor [mailto:[EMAIL PROTECTED]] Sent: 20 December 2001 09:36 To: [EMAIL PROTECTED] Subject: mysqldump - basic question this is an absolute newbie question - but when i try to run mysqldump with a cron (like with a shell script) i can't because mysqldump utility stops to ask for a password. how can I either embed the password or set up mysqldump to not ask for one if, for instance, root is running the shell script? thanks, rory - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ERROR 1006: Can't create database 'mynewdb'. (errno: 28)
What does df show? -Original Message- From: John Lepone [mailto:[EMAIL PROTECTED]] Sent: 17 December 2001 20:41 To: '[EMAIL PROTECTED]' Subject: Re: ERROR 1006: Can't create database 'mynewdb'. (errno: 28) I don't think I'm out of room on the volume. Below is the directory listing: -rw-rw1 mysqlmysql 0 Dec 13 07:09 mandrake-bin.001 -rw-rw1 mysqlmysql 0 Dec 13 07:12 mandrake-bin.002 -rw-rw1 mysqlmysql 0 Dec 13 07:17 mandrake-bin.003 -rw-rw1 mysqlmysql 0 Dec 13 14:42 mandrake-bin.004 -rw-rw1 mysqlmysql 0 Dec 13 15:25 mandrake-bin.005 -rw-rw1 mysqlmysql 0 Dec 14 03:20 mandrake-bin.006 -rw-rw1 mysqlmysql 0 Dec 17 02:31 mandrake-bin.007 -rw-rw1 mysqlmysql 0 Dec 17 02:31 mandrake-bin.index -rw-r--r--1 mysqlroot 2874 Dec 17 02:31 mandrake.err -rw-rw1 mysqlmysql 0 Dec 17 02:31 mandrake.pid -rwxr-xr-x1 root root 2530 Dec 13 07:08 my.cnf* drwx--x--x2 mysqlmysql4096 Dec 12 00:24 mysql/ srwxrwxrwx1 mysqlmysql 0 Dec 17 02:31 mysql.sock= drwxr-xr-x2 mysqlmysql4096 Dec 17 09:17 test/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Changing the locations of the tables
I think the key was _without_ reinstalling. I emailed this privately first, but should work with minimum downtime and preserve data: mysqladmin -u . shutdown su cd /var/lib tar -cf - mysql | (cd /usr/lib ; tar xvBpf -) rm -rf mysql vi /etc/my.cnf :%s/\/var\/lib\/mysql/\/usr\/lib\/mysql/g :x ...restart mysqld. -Original Message- From: Bill Blowitz [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 17:54 To: 'Stephen Johnson'; 'MySQL' Subject: RE: Changing the locations of the tables Here you go:-) [root@server src]# rpm -i MySQL-3.XX.XX-1.src.rpm [root@server src]# tar -xvzf ../../src/redhat/SOURCES/mysql-3.XX.XX.tar.gz [root@server src]# cd mysql-3.XX.XX [root@server mysql-3.XX.XX]# ./configure \ --prefix=/usr/lib/mysql [root@server mysql-3.XX.XX]# make [root@server mysql-3.XX.XX]# make install [root@server mysql-3.XX.XX]# scripts/mysql_install_db XX.XX is your mysql version Good Luck! -Original Message- From: Stephen Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 9:19 AM To: MySQL Subject: Changing the locations of the tables Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Changing the locations of the tables
That is what the Bp flags from tar are for, and we did a su beforehand M -Original Message- From: David M. Peak [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 18:34 To: [EMAIL PROTECTED]; 'MySQL' Subject: Re: Changing the locations of the tables Don't forget to ensure that the account that the mysqld is running under has the proper permissions to the new directory. I ran into that one before.. - Original Message - From: Matthew Smith [EMAIL PROTECTED] To: 'MySQL' [EMAIL PROTECTED] Sent: Tuesday, December 18, 2001 10:24 AM Subject: RE: Changing the locations of the tables I think the key was _without_ reinstalling. I emailed this privately first, but should work with minimum downtime and preserve data: mysqladmin -u . shutdown su cd /var/lib tar -cf - mysql | (cd /usr/lib ; tar xvBpf -) rm -rf mysql vi /etc/my.cnf :%s/\/var\/lib\/mysql/\/usr\/lib\/mysql/g :x ...restart mysqld. -Original Message- From: Bill Blowitz [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 17:54 To: 'Stephen Johnson'; 'MySQL' Subject: RE: Changing the locations of the tables Here you go:-) [root@server src]# rpm -i MySQL-3.XX.XX-1.src.rpm [root@server src]# tar -xvzf ../../src/redhat/SOURCES/mysql-3.XX.XX.tar.gz [root@server src]# cd mysql-3.XX.XX [root@server mysql-3.XX.XX]# ./configure \ --prefix=/usr/lib/mysql [root@server mysql-3.XX.XX]# make [root@server mysql-3.XX.XX]# make install [root@server mysql-3.XX.XX]# scripts/mysql_install_db XX.XX is your mysql version Good Luck! -Original Message- From: Stephen Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 9:19 AM To: MySQL Subject: Changing the locations of the tables Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: timestamp additional info
mysql CREATE TABLE tblTEST ( - KeyValue int(10) NOT NULL default 0, - DataValue varchar(255) default NULL, - LastEdited timestamp(14) NOT NULL, - PRIMARY KEY (KeyValue) - ); mysql insert into tblTEST (KeyValue, DataValue) values( 1, 'Hello'); Query OK, 1 row affected (0.00 sec) mysql select * from tblTEST; +--+---++ | KeyValue | DataValue | LastEdited | +--+---++ |1 | Hello | 20011214194514 | +--+---++ 1 row in set (0.00 sec) mysql UPDATE tblTEST SET DataValue='World' WHERE KeyValue=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from tblTEST; +--+---++ | KeyValue | DataValue | LastEdited | +--+---++ |1 | World | 20011214194555 | +--+---++ 1 row in set (0.00 sec) mysql Regards M -Original Message- From: Steve Osborne [mailto:[EMAIL PROTECTED]] Sent: 14 December 2001 20:16 To: MySQL (E-mail) Subject: timestamp additional info Timestamp additional info: INSERT INTO Owners (NameID,ProductsKey,RegNum,ProdRegDate) VALUES ('$NameID','1','$RegNumc','NULL'); ProdRegDate is the field that I want to timestamp. (Again, I've tried passing '', NULL, and 'NULL'). Steve Osborne Database Programmer Chinook Multimedia Inc. [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 4 G table limit?
Do you compress the data? I use perl and Compress::Zlib::memGzip to keep size down. Also, you could split into months by something like: oA column MESSAGE_DATE set on insertion oa cron script run once a month to: o create another table for the month to be archived o INSERT INTO SELECT for the dates to be archived o DELETE WHERE to remove from main table If you are looking at being able to index the archive, you will need a number of tables for word indexes. A simple version would use: Word_Hash INTEGER, Message_Id BIGINT, OccurrencesINTEGER, Message_Date DATE Some nice CRC ish algorithm to return an INTEGER for a word. SELECT Message_Id, Message_Date WHERE Word_Hash=? ORDER BY Occurrences DESC will give the messages with most occurrences of the word at the top of the search. If you do not need to archive, or use another table to store location, such: Message_Id BIGINT, Data_Table varchar(255) if text, BIGINT if you use numbers for tables. Then you do not need to store the Message_Date in the index tables. M -Original Message- From: Tim Wood [mailto:[EMAIL PROTECTED]] Sent: 12 December 2001 23:16 To: [EMAIL PROTECTED] Subject: 4 G table limit? Hi 3 months ago starting using mysql to archive a live news feed that our company is paying for. Being a lazy unit, I am putting all the document bodies into one table. After 3 months, that table has grown to 2G - so its a natural enough assumption to assume that in another 3, it will grow to 4G or so - around the current mysql table size limit ( mysql v. 3.23.36, kernel 2.4.2-2). As mentioned above, I'm a lazy unit, so if possible would like to avoid restructuring the DB to spread the document body table across 1 table (thus enabling 4 G of doc bodies), so am looking for some easier way to do it (eg upgrading mysql, or using a different fs type etc). Does anyone out there know of any - future plans by the mysql development crew to increase table size limits by eg using their own custom filesystem type? - any kernel fs patches I might apply to get around the 4G limit imposed by the linux kernel - any alternative filesystems (JFS?) that might permit greater table sizes under mysql - any other tested and functioning workarounds to this issue? Any suggestions will be appreciated Cheers Tim -- Tim Wood Predictive Technologies ph +61 3 8344 0395 (BH) +61 413 845 317 This is clearly another case of too many mad scientists, and not enough hunchbacks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sequence and nextval
the SELECT LAST_INSERT_ID() gets the last autoincremented number for the current connection. See http://www.mysql.com/doc/G/e/Getting_unique_ID.html The auto_incremenet field is sadly lacking in Oracle (IMHO). M -Original Message- From: Henrik Erlandsson [mailto:[EMAIL PROTECTED]] Sent: 13 December 2001 17:25 To: [EMAIL PROTECTED] Subject: SV: sequence and nextval Thanks, but how secure is this. Is it possible for two computers to do the select at the same time, i.e. get the new incremented value? Computer one insert, computer two insert, computer one select, computer two select? If this is possible how can I solve this? To make the column that insert the new nextval unique and then check for errors (non-unique insert) and if error then re-ask for a new nextval? Is this the best solution? Thanks / Henrik --- create table MySequence ( nextval int(10) not null default '0' auto_increment ) ; insert into MySequence values (''); select LAST_INSERT_ID(); is what I use M -Original Message- From: Henrik Erlandsson [mailto:[EMAIL PROTECTED]] Sent: 13 December 2001 15:41 To: [EMAIL PROTECTED] Subject: sequence and nextval Does the above functions exist in MySQL? If not, when do you think it's going to be implemented and how can I simulate nextval the easiest way? / Thanks Henrik - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php