Re: MySQL on Linux
Just to be complete, linux does have limitations depending upon limitations of the file-system, and the kernel. All modern filesystems (XFS, EXT3, ...) all allow files over a terabyte is size. On Tue, 2004-04-06 at 13:39, Ronan Lucio wrote: Uhm, what are you talking about?!? When I put our site on a Linux system, apache stop working when it´s logfile get major than 2 Gb. I was afraid of it´d happen with MySQL, too. Linux has no such limitation. you can grow files as large as you like. right now I have an InnoDB dbase with Mysql on a linux system and the file is over 60 GIGS in size! Great!!! So, I don´t need to worry about it... :-) Thanks Dan, Ronan Benjamin Arai Araisoft Email: [EMAIL PROTECTED] Website: http://www.araisoft.com
Displaying string including Kanji characters
When I use multi-byte code (especially UTF-8) with data including Kanji (Japanese kanji character) and execute SELECT from mysql client command, the vertical lines(|) of output table aren't aligned as straight lines and the output doesn't look like a table. When I use SJIS code, the problem of sheared line doesn't occur. This is because the display width and the byte number of data always match. To display multi-byte code, not only the count of character and byte, but the display width is also very important. The width of Kanji in EUC-JP, SJIS, and UTF-8 is double the width of alphanumeric characters. As far as I could see, the result of functions involving display, such as LPAD and RPAD, doesn't seem to take the display width of Kanji into account. Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_multi kills socket from previous Daemon
Hello all, I would like to run diverent MySQL-Daemons on the same Linux machine, so I use mysqld_multi to start the different processes. The Config looks right to me, I have different Database Directorys, Pid-Files, Ports, IDs usw. and different Socket-Files and all over it seems to work. But! as soon as I start the sekond Daemon my Socket-'File' from the first one, who ist started at the boot time (like RedHat Config), disappears! Both Daemons are running still fine, but the first one without a Socket. I have to connect over the TCP Port. The same happens if i stop the first Daemon and then start it with mysqld_multi. The Socket is here, untill I start the next Daemon with mysqld_multi. Has anybody a Idee where I can look and find a way to keep my Sockets ??? Before I say good by, I would like to apologies about the bat sentens construction and the writing mistakes in English! Best regards, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best practice on table design
Hello, I have the following structure: people - | id | name | - | 1 | John | | 2 | Mary | - cities | id | city| | 1 | Glasgow | | 2 | Madrid | | 3 | Berlin | travel_expenditures --- | id | date | id_people | id_city | per_diem | --- | 1 | 05.08 | 1 | 1 |1.600 | | 2 | 05.09 | 2 | 3 |2.000 | | 3 | 06.12 | 1 | 2 |1.000 | --- The `people` and `cities` tables aren't going to be very populated, so a thought to merge them into something like this: central_data --- | id | name| type | --- | 1 | John| P| | 2 | Glasgow | C| | 3 | Mary| P| | 4 | Madrid | C| | 5 | Berlin | C| --- where central_data.type is P for people and C for cities. Do you think it is a good ideea ? -- Best regards, Ciprian Trofin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get the total as a percentage of two grouped results
Hi List, Can this be done in one sql statment (without the use of sub-queries): SELECT count(*) as total, status FROM table GROUP BY status --- Absent | 40 Present|60 and get this result: Absent: 40% something like: SELECT ((count(total) OF status=Absent) / count(total records) * 100) FROM table I need to be able to divide the number of absent by the total number of results (eg how many times the student attended class). Right now it's in two queries and I was wondering if it's possible. Thanks Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get the total as a percentage of two grouped results
Terence [EMAIL PROTECTED] wrote: Can this be done in one sql statment (without the use of sub-queries): Without subqueries - no. SELECT count(*) as total, status FROM table GROUP BY status --- Absent | 40 Present|60 and get this result: Absent: 40% something like: SELECT ((count(total) OF status=Absent) / count(total records) * 100) FROM table I need to be able to divide the number of absent by the total number of results (eg how many times the student attended class). Right now it's in two queries and I was wondering if it's possible. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
GMT-DST time conversion in (My)SQL ?
Hello, i have a table that contains datetime entries which are stored in GMT. Now I would like to convert these datetime values in a select statement to local time. I first thought of someting like this (in germany DST starts at 2004-03-29 02:00:00 and ends 2004-10-31 03:00:00 this year. The difference from GMT to local time is 1h and from GMT to local DST time 2h): SELECT if(datetime between 2004-03-29 02:00:00 AND 2004-10-31 03:00:00, DATE_ADD(datetime, INTERVAl 2 HOUR), DATE_ADD(datetime, INTERVAL 1 HOUR) ) FROM table ... ... This basically works but of course only if the select statement return rows with datetime entries in the year 2004. Eg if there are rows with a datetime field of 2003-xx-xx then the statement from above will fail. Is there any more general way to do such a timezone conversion or is the only solution to let the application to this work that reads the MYSQL output? Thanks Rainer -- --- Rainer Krienke, Universitaet Koblenz, Rechenzentrum, Raum A022 Universitaetsstrasse 1, 56070 Koblenz, Tel: +49 261287 -1312, Fax: -1001312 Mail: [EMAIL PROTECTED], Web: http://www.uni-koblenz.de/~krienke Get my public PGP key: http://www.uni-koblenz.de/~krienke/mypgp.html --- pgp0.pgp Description: signature
MS 2000 Advance Server
Hi, MySQL 4.0.14 Are there any known issues on windows 2000 advance server? After installation, on running the WinMySQLAdmin the green light comes on for a second or two and then disconnected. We tried running MySQLd-nt on its own, it simply cannot establish connection. What could be wrong here? regards Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting Aggregate Object Data
Lou Olsten [EMAIL PROTECTED] wrote: I'm in the process of trying to move some of our systems to MySQL from SQL Server and Oracle. At this point I'm learning and testing MySQL. One immediate question I have is regarding getting aggregate information. For example, the SHOW DATABASES command would be an example of something I would get from the SQL Server data dictionary table, sysdatabases. It appears that MySQL has no such mechanisms, and that you must rely on many of the SHOW commands. In the case of databases, it works great, but what about other objects? For example, if I want to see all indexes in my sales database? Or do I have to loop through each table with the SHOW INDEXES command? That's one example of what I'm talking about. Yes, in this case you should execute SHOW INDEX for each table from your database. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: MS 2000 Advance Server
A Z [EMAIL PROTECTED] wrote: Hi, MySQL 4.0.14 Are there any known issues on windows 2000 advance server? After installation, on running the WinMySQLAdmin the green light comes on for a second or two and then disconnected. We tried running MySQLd-nt on its own, it simply cannot establish connection. What could be wrong here? Run mysqld-nt.exe from command line with --console option. In this case you can see error message on the screen. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
error with loading table structure and data from a script file
Hi, I'm using mySQL 4.1.18 on Windows XP pro. I tried this syntax and I received error. mysql -u root -p databasename scriptfile.sql What is the right syntax for this. I managed to unpack my table structure from the same script into another machine on Win2000 Server. However, I remember I had to try so many times and I cannot remember the exact syntax. thnx. - Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway - Enter today
Re: error with loading table structure and data from a script file
Thursday, April 08, 2004, 1:07:27 PM, b wrote: hi, Error 1064: You have an error in your SQL syntax. Check the manual that corresponds to your mySQL for the right syntax to use near 'mysql -u root -p databasename scriptfile.sql' at line 1 however, I tried this. \u database name; \. scriptfile.sql; This way I managed to load my table structure and my data. Is the first command obsolete in mySQL ver 4.1.18? mysql -u root -p databasename some_file.sql is not the command of mysql client. You should run it from the command line. For example, if MySQL dir is C:\mysql, you should run C:\mysql\bin mysql -u root -p databasename file.sql You can also execute SQL script file with mysql command source (\.) Victoria Reznichenko [EMAIL PROTECTED] wrote: b b wrote: Hi, I'm using mySQL 4.1.18 on Windows XP pro. I tried this syntax and I received error. mysql -u root -p databasename What is the right syntax for this. I managed to unpack my table structure from the same script into another machine on Win2000 Server. However, I remember I had to try so many times and I cannot remember the exact syntax. Yes, it's correct. What error did you receive? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Corrupted relay log
Is there any way to fix a corrupted relay log on a slave? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Corruption and my.cnf
I've experienced more corruption lately on my main site since I moved to my own server running 4.0.17max. The site is very busy (60GB a month) and the tables are large. I didn't have this level of problems on the rental server. What are the variables to look into regarding why my tables are getting corrupt? Is this a my.cnf issue? Is this a memory issue? Is this a hard drive issue? Is this a too many connections issue? Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Learner Here Getting Frustraighted
Hi I recently began to work through the Book entitled PHP MySQL For Dummies and i am currently stuck towards the end of the forth chapter ive done everything as said so in the book but i keep getting an error message. Here is an outline of the problem: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Now Ive posted my problem on several forums on the internet and have a wide range to replys suggesting what to do but nothing seems to work. Can anyone please help me?? Barry Smith _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Corrupt database file
I seem to have encountered a corrupt database file, and was wondering if anyone could shed some light onto how this occurred. I was able to restore from a backup, so I'm OK now, but I'd like to know how to avoid this in the future. I'm running MySql-max 4.0.18, using InnoDB tables. It looks like the database crashed, and I was able to restart without issue, however if would then periodically crash again because of the same error. The error log looks like this: 040407 03:09:31 mysqld started 040407 3:09:31 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.18-max-log' socket: '/tmp/mysql.sock' port: 3306 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 54349. InnoDB: You may have to recover from a backup. 040408 14:37:38 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex [DUMP OMITTED BY ME] InnoDB: stored checksum 2429221792, prior-to-4.0.14-form stored checksum 3343942943 InnoDB: Page lsn 5 4204235448, low 4 bytes of lsn at page end 4204235448 InnoDB: Page may be an index page where index id is 0 3068 InnoDB: and table raytheon/item_segment_value index PRIMARY InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 54349. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: Look also at section 6.1 of InnoDB: http://www.innodb.com/ibman.html about InnoDB: forcing recovery. InnoDB: Ending processing because of a corrupt database page. Number of processes running now: 0 040408 14:37:38 mysqld restarted 040408 14:37:38 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 6 89092023 InnoDB: Doing recovery: scanned up to log sequence number 6 89092249 InnoDB: Last MySQL binlog file position 0 172112082, file name ./king1-bin.004 040408 14:37:38 InnoDB: Flushing modified pages from the buffer pool... 040408 14:37:38 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.18-max-log' socket: '/tmp/mysql.sock' port: 3306 = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learner Here Getting Frustraighted
On 4/8/04 9:18 AM, Barry . [EMAIL PROTECTED] wrote: Hi I recently began to work through the Book entitled PHP MySQL For Dummies and i am currently stuck towards the end of the forth chapter ive done everything as said so in the book but i keep getting an error message. Here is an outline of the problem: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Now Ive posted my problem on several forums on the internet and have a wide range to replys suggesting what to do but nothing seems to work. Can anyone please help me?? Barry Smith _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess I think the reason that's not working is because that doesn't look like MySQL syntax for a join statement. From the MySQL documentation, try this: SELECT * FROM pet OUTER JOIN color ON pet.petName=petColor,petName; Keep in mind this will only return rows in pet that have corresponding rows in color. I hope that's what you're looking for. The above code is not tested, but that's the proper JOIN syntax in MySQL. Going through the documentation, I don¹t see a USING keyword anywhere. That looks suspiciously like Oracle code. Hope that help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Learner Here Getting Frustraighted
For starters look at pet.petName=petcolor.petName Try changing it to. pet.petName=color.petName You are referencing the color table as petcolor. Thanks, Andrew From: Barry . [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Learner Here Getting Frustraighted Date: Thu, 08 Apr 2004 14:18:10 + Hi I recently began to work through the Book entitled PHP MySQL For Dummies and i am currently stuck towards the end of the forth chapter ive done everything as said so in the book but i keep getting an error message. Here is an outline of the problem: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Now Ive posted my problem on several forums on the internet and have a wide range to replys suggesting what to do but nothing seems to work. Can anyone please help me?? Barry Smith _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Free up your inbox with MSN Hotmail Extra Storage! Multiple plans available. http://join.msn.com/?pgmarket=en-uspage=hotmail/es2ST=1/go/onm00200362ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learner Here Getting Frustraighted
Barry Smith writes: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Unfortunately petcolor is not a table. Based upon your prior information I think you are looking for: Select * from pet left join color on pet.petName=color.petName; Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Impossible to pre-load the MySQL fulltext indexes?
Hi, I tried to load a table's indexes into cache, however I noticed that full-text indexes have a different block-size than others (2048 vs. 1024), which caused load index command to fail. Any idea on how to solve this? Thanks a lot __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hey what, no pity for a new user?
At 8:29 -0600 4/8/04, Kevin Jaques wrote: Thanks for the reply. I feel it is getting me somewhere, but I'm not there yet. The log said, regarding the most recent attempt: 040407 09:55:24 mysqld started 040407 9:55:25 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 040407 09:55:25 mysqld ended What directory is it referring to? What user/group does mysqld need? I The data directory, as you indicate that you already know below. The user account that is relevant here is whatever account you use for running the server. I will suppose that this is mysql on your system? referred to the web site it mentioned, and the page was enormous without an apparent section on installation or authorization issues. A search revealed that it thought the mysql server should have access to 'datadir'. Well, duh. My data directory shows: [iMac-dv:local/mysql/data] kj% ls -al total 8 drwxr-x--- 5 root wheel 170 5 Apr 08:03 ./ drwxr-xr-x 20 root wheel 680 10 Feb 13:05 ../ -rw-rw 1 mysql wheel 2234 7 Apr 09:55 iMac-dv.local.err drwxr-x--- 20 root wheel 680 10 Feb 13:05 mysql/ drwxr-x--- 2 root wheel68 10 Feb 13:05 test/ So, what now? This shows that it's owned by root/wheel. You want it to be owned by mysql/some-group. I don't know what group that will be for you. mysql? Anyway, in that directory, run this command as root: chown -R mysql . That changes the ownership of the directory and everything under it to mysql. Then restart the server, either from the mysql account, or as root with the --user=mysql option. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connecting 2 MySQL databases: how?
Hi, I would like to make a connection between 2 databases in MySQL that are on the same server. The purpose is that a class X in database A uses a class Y in database B by a foreign key. Thus, the table in B can be re-used by other databases which will make my databases more efficient. I've already thought of a solution like FOREIGN KEY c_id REFERENCES A.customer(c_id)ON DELETE CASCADE but I think this will not work. Is there any other solution to do this? I hope I was clear enough... Greetings, Bart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please HELP !!! Can not restart server
Hi, MySQL Gurus, Version of mysql is Distrib 3.23.54, for redhat-linux-gnu (i386). I started mysql server with 'safe_mysqld --user=root ' . Then I found some variables needs to be optimized. SO I shut it down with 'mysqladmin -p shutdown' using root. But I was connected to the mysql server at the moment as ginger thru another connection. But the server is down all right (at least to me). Then I logged out in my connection as ginger to mysql server. Then I changed the variables in /etc/my.cnf and try to bring up the server using 'safe_mysqld --user=root '. Here is what I got: /usr/usr/bin/safe_mysqld --user=root /usrStarting mysqld daemon with databases from /var/lib/mysql 040408 08:00:06 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=root I moved the old mysql under datadir to another place and run 'mysql_install_db'. Then I run 'safe_mysqld --user=root '. Still the same thing. I even tried to restart the server again but it does not work either. I have also tried to restore the my.cnf file and there is no effect. Please help me. I need it to be fixed as soon as I can. Thank you so much ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
Please look in your data directory and post the contents of the host name.ERR file that you find there. That will give folks the information they need to help solve your problem. -Original Message- From: Ginger Cheng [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:21 AM To: [EMAIL PROTECTED] Subject: Please HELP !!! Can not restart server Hi, MySQL Gurus, Version of mysql is Distrib 3.23.54, for redhat-linux-gnu (i386). I started mysql server with 'safe_mysqld --user=root ' . Then I found some variables needs to be optimized. SO I shut it down with 'mysqladmin -p shutdown' using root. But I was connected to the mysql server at the moment as ginger thru another connection. But the server is down all right (at least to me). Then I logged out in my connection as ginger to mysql server. Then I changed the variables in /etc/my.cnf and try to bring up the server using 'safe_mysqld --user=root '. Here is what I got: /usr/usr/bin/safe_mysqld --user=root /usrStarting mysqld daemon with databases from /var/lib/mysql 040408 08:00:06 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=root I moved the old mysql under datadir to another place and run 'mysql_install_db'. Then I run 'safe_mysqld --user=root '. Still the same thing. I even tried to restart the server again but it does not work either. I have also tried to restore the my.cnf file and there is no effect. Please help me. I need it to be fixed as soon as I can. Thank you so much ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
What information is being logged in *.err? -Original Message- From: Ginger Cheng To: [EMAIL PROTECTED] Sent: 4/8/04 10:20 AM Subject: Please HELP !!! Can not restart server Hi, MySQL Gurus, Version of mysql is Distrib 3.23.54, for redhat-linux-gnu (i386). I started mysql server with 'safe_mysqld --user=root ' . Then I found some variables needs to be optimized. SO I shut it down with 'mysqladmin -p shutdown' using root. But I was connected to the mysql server at the moment as ginger thru another connection. But the server is down all right (at least to me). Then I logged out in my connection as ginger to mysql server. Then I changed the variables in /etc/my.cnf and try to bring up the server using 'safe_mysqld --user=root '. Here is what I got: /usr/usr/bin/safe_mysqld --user=root /usrStarting mysqld daemon with databases from /var/lib/mysql 040408 08:00:06 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=root I moved the old mysql under datadir to another place and run 'mysql_install_db'. Then I run 'safe_mysqld --user=root '. Still the same thing. I even tried to restart the server again but it does not work either. I have also tried to restore the my.cnf file and there is no effect. Please help me. I need it to be fixed as soon as I can. Thank you so much ginger -- 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: Please HELP !!! Can not restart server
I don't have such files. Unfortunately. Am I hopeless? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT search in form of MATCH...AGAINST
I can't seem to get a FULLTEXT search in the form of MATCH...AGAINST to work. I even copied verbatim the example listed on mysql.com at (http://www.mysql.com/doc/en/Fulltext_Search.html). When I use... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database') it works as expected. However when I use... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('following') it doesn't return any results. I've got several tables with FULLTEXT indexes and on none of them can I get this syntax to work. What's up? -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search in form of MATCH...AGAINST
Hello Robb, Thursday, April 8, 2004, 4:30:46 PM, you wrote: RK I've got several tables with FULLTEXT indexes and on none of them can I get RK this syntax to work. What's up? Your version of MySQL perhaps? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
Here is the error msg: 040408 08:47:14 mysqld started Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html 040408 8:47:14 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist 040408 08:47:14 mysqld ended But I am not sure how to fix it. Could anyone help. Thanks ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please HELP !!! Can not restart server
I got it fixed with the msg from --err-log. THank you so much for all your help. I couldn't have made it without your hints. ALl the best ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search in form of MATCH...AGAINST
Robb Kerr [EMAIL PROTECTED] wrote: I can't seem to get a FULLTEXT search in the form of MATCH...AGAINST to work. I even copied verbatim the example listed on mysql.com at (http://www.mysql.com/doc/en/Fulltext_Search.html). When I use... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database') it works as expected. However when I use... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('following') it doesn't return any results. I've got several tables with FULLTEXT indexes and on none of them can I get this syntax to work. What's up? Because 'following' is a stopword. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Please HELP !!! Can not restart server
Ginger Cheng [EMAIL PROTECTED] wrote: Here is the error msg: 040408 08:47:14 mysqld started Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html 040408 8:47:14 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist 040408 08:47:14 mysqld ended But I am not sure how to fix it. Could anyone help. If you didn't install privilege tables you must run mysql_install_db script. Otherwise check permissions on the MySQL data dir. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Connecting 2 MySQL databases: how?
* Steijn, B.S. I would like to make a connection between 2 databases in MySQL that are on the same server. If you by connection mean to join tables from the two databases on the same server, you can do that. The purpose is that a class X in database A uses a class Y in database B by a foreign key. Thus, the table in B can be re-used by other databases which will make my databases more efficient. Right. I've already thought of a solution like FOREIGN KEY c_id REFERENCES A.customer(c_id)ON DELETE CASCADE but I think this will not work. Is there any other solution to do this? Right, this will not work. But this is a foreign key constraint, you don't need a constraint to make a join work. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using PHP to copy tables
Is there an easy way to create an HTML page that will copy selected tables to backup copies on the same server? I want to create an administration page for my client to be able to backup their database whenever they see fit. But, I can't give them direct access to the MySQL server and don't want them backing up to their hard drive. I prefer to simply copy the tables to backup versions on the server so that if problems arise, I can log into the server and simply copy the backups to the originals. Thanx in advance. -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using PHP to copy tables
Sound like a great idea! Is there an easy way to create an HTML page that will copy selected tables to backup copies on the same server? I want to create an administration page for my client to be able to backup their database whenever they see fit. But, I can't give them direct access to the MySQL server and don't want them backing up to their hard drive. I prefer to simply copy the tables to backup versions on the server so that if problems arise, I can log into the server and simply copy the backups to the originals. Thanx in advance. -- Robb Kerr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using PHP to copy tables
Try using such command from PHP... system($MYSQLDUMP --opt --user=$USER --password=$PASSWORD $DATABASE_NAME $GZIP - $BKDIR/$newfile.gz,$status); where $MYSQLDUMP is the mysqldump path $GZIP is the path to gzip $BKDIR is the path to the backup folder Looking for a job!? Use the smart search engine!! Find a Job from Millions WorldWide... http://search.jobsgrabber.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using PHP to copy tables
Is there an easy way to create an HTML page that will copy selected tables to backup copies on the same server? I want to create an administration page for my client to be able to backup their database whenever they see fit. But, I can't give them direct access to the MySQL server and don't want them backing up to their hard drive. I prefer to simply copy the tables to backup versions on the server so that if problems arise, I can log into the server and simply copy the backups to the originals. Here is what I use... Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using PHP to copy tables
Is there an easy way to create an HTML page that will copy selected tables to backup copies on the same server? I want to create an administration page for my client to be able to backup their database whenever they see fit. But, I can't give them direct access to the MySQL server and don't want them backing up to their hard drive. I prefer to simply copy the tables to backup versions on the server so that if problems arise, I can log into the server and simply copy the backups to the originals. Here is what I have used before...but this might be for you to run as a cron task and be transparent to them. http://www.silisoftware.com/scripts/index.php?scriptname=backupDB Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learner Here Getting Frustraighted
Typo petColor is meant to be color From: James McConnell [EMAIL PROTECTED] To: Barry . [EMAIL PROTECTED],MySQL Mailing List [EMAIL PROTECTED] Subject: Re: Learner Here Getting Frustraighted Date: Thu, 08 Apr 2004 09:34:36 -0500 On 4/8/04 9:18 AM, Barry . [EMAIL PROTECTED] wrote: Hi I recently began to work through the Book entitled PHP MySQL For Dummies and i am currently stuck towards the end of the forth chapter ive done everything as said so in the book but i keep getting an error message. Here is an outline of the problem: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Now Ive posted my problem on several forums on the internet and have a wide range to replys suggesting what to do but nothing seems to work. Can anyone please help me?? Barry Smith _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess I think the reason that's not working is because that doesn't look like MySQL syntax for a join statement. From the MySQL documentation, try this: SELECT * FROM pet OUTER JOIN color ON pet.petName=petColor,petName; Keep in mind this will only return rows in pet that have corresponding rows in color. I hope that's what you're looking for. The above code is not tested, but that's the proper JOIN syntax in MySQL. Going through the documentation, I don¹t see a USING keyword anywhere. That looks suspiciously like Oracle code. Hope that help! _ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
script to start mysql command center
I'm new to mysql, so I thought I'd use the command center utility, which requires an X server to be running. Is there a way to start the xterm and mysqlcc all at once? I would like a shell script that starts xterm and then runs mysqlcc. Any ideas? TIA. = Thanks. Kevin -- Enjoy Life, Drink and Code Java! __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learner Here Getting Frustraighted
James McConnell wrote: On 4/8/04 9:18 AM, Barry . [EMAIL PROTECTED] wrote: Hi I recently began to work through the Book entitled PHP MySQL For Dummies and i am currently stuck towards the end of the forth chapter ive done everything as said so in the book but i keep getting an error message. Here is an outline of the problem: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Now Ive posted my problem on several forums on the internet and have a wide range to replys suggesting what to do but nothing seems to work. Can anyone please help me?? Barry Smith I think the reason that's not working is because that doesn't look like MySQL syntax for a join statement. From the MySQL documentation, try this: SELECT * FROM pet OUTER JOIN color ON pet.petName=petColor,petName; Keep in mind this will only return rows in pet that have corresponding rows in color. I hope that's what you're looking for. The above code is not tested, but that's the proper JOIN syntax in MySQL. Going through the documentation, I don't see a USING keyword anywhere. That looks suspiciously like Oracle code. Hope that help! USING is documented in the manual http://www.mysql.com/doc/en/JOIN.html. USING expects a list of columns which exist in both tables. The following are quivalent: SELECT * FROM pet JOIN color ON pet.petName = color.petName; SELECT * FROM pet JOIN color USING (petName); Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Configuring SuSE Pro 9.0, MySQL 4.0.18 with ssl
We have been looking and looking for some guidance for installing MySQL with SSL on a SuSE 9.0 box. By searching the archives, the net using Google, etc., we keep finding pieces of the puzzle, but no single piece that covers all of the ground. The problem is that we seem to complete the install, but cannot seem to have the ssl-enabled show 'yes'. Any help that you can offer would be appreciated very much. David Christensen Brokers International, Ltd. 1200 E Main St Panora, IA 50216 (641) 755-2775 [EMAIL PROTECTED]
RE: Perl Modelues
First a big thanks to Mark, BAO, Beacker, Matthais, Rich Ken for their response. My problem was that I thought that I needed to download tar file to start the installation. Rich's response somewhat cleared that. I ran the following commands, and got the following errors: % perl -MCPAN -e shell cpan install Data::Dumper cpan install DBI (no problem upto this point) cpan install DBD::mysql Errors returned: Can't exec mysql_config: No such file or directory at Makefile.PL line 174 readline() on closed filehandle PIPE at Makefile.PL line 176 ... (there are 10 such errors) Then few other lines of output without error. Then I get the following (error) response: Checking if your kit is complete... looks good Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (number of such errors) Note (probabally harmless): No library found for -lmysqlclient Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (again quite a number of such errors) Note (probabally harmless): No library found for -lgz Writing Makefile for DBD::mysql Makefile:89: *** missing separator. Stop. /usr/bin/make --NOT OK Running make test Can't test without successful make Running make install make had returned bad status, install seems impossible cpan What are these errors? How should I fix them? Can I continue installing other software (Apache, qmail, etc.) Thanks. Kirti -Original Message- From: Rich Allen [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 6:02 PM To: Kirti S. Bajwa Cc: '[EMAIL PROTECTED]' Subject: Re: Perl Modelues you can install Perl modules with $ perl -MCPAN -e shell cpan install [module::name] - hcir mysql - hcir On Apr 7, 2004, at 10:57 AM, Kirti S. Bajwa wrote: Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. -- 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]
4.0.12 to 4.0.18
I upgraded from MySQL 4.0.12 to MySQL 4.0.18 last night and found some oddities this morning. Apparently some of the varchar data being input into certain tables had carriage returns: \n in the data. This was an accidental thing, but on 4.0.12, the \n was ignored when matching on that record. However, now in 4.0.18 it seems to include that when determining matches. For example: select * from table where record='abc'; used to match both 'abc' and 'abc\n' but now only matches 'abc'. Did I miss something in the change history that said this should now happen or is this an undocumented difference? Thanks, Brian Stanton Systems Administrator, Belo Interactive
Re: Perl Modelues
iH is mysql installed and running? - hcir On Apr 8, 2004, at 9:53 AM, Kirti S. Bajwa wrote: cpan install DBD::mysql Errors returned: Can't exec mysql_config: No such file or directory at Makefile.PL line 174 readline() on closed filehandle PIPE at Makefile.PL line 176 ... (there are 10 such errors) Then few other lines of output without error. Then I get the following (error) response: Checking if your kit is complete... looks good Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (number of such errors) Note (probabally harmless): No library found for -lmysqlclient Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (again quite a number of such errors) Note (probabally harmless): No library found for -lgz Writing Makefile for DBD::mysql Makefile:89: *** missing separator. Stop. /usr/bin/make --NOT OK Running make test Can't test without successful make Running make install make had returned bad status, install seems impossible cpan What are these errors? How should I fix them? Can I continue installing other software (Apache, qmail, etc.) Thanks. Kirti -Original Message- From: Rich Allen [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 6:02 PM To: Kirti S. Bajwa Cc: '[EMAIL PROTECTED]' Subject: Re: Perl Modelues you can install Perl modules with $ perl -MCPAN -e shell cpan install [module::name] - hcir mysql - hcir On Apr 7, 2004, at 10:57 AM, Kirti S. Bajwa wrote: Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. -- 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]
autoincrement column
Before switching to version 4.0.18 of MySQL the command delete from table was also reseting the autonincrement column value... It seems that this is not happening anymore after we have installed this version... Any idee about this problem? Is there any other way to reset this? Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Corrupted relay log
Here's what I did to fix it... Excert from MySql Manual... When you back up your slave's data, you should back up these two small files as well, along with the relay log files. They are needed to resume replication after you restore the slave's data. If you lose the relay logs but still have the `relay-log.info' file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_RELAY_LOG and MASTER_RELAY_POS options to tell the slave to re-read the binary logs from that point. This requires that the binary logs still exist on the master server. I performed these steps... Read info form relay-log.info file. Mysql: Slave Stop; Mysql: Change master to master_host = '192.168.1.10', Master_User = 'name', Master_Password = 'pass', Master_log_File = 'get this from relay-log.info', Master_log_pos = get this from relay-log.info; Mysql: Slave Start; Does anyone see a problem with this method? Thanks, Jeff -Original Message- From: Jeff McKeon Sent: Thursday, April 08, 2004 8:54 AM To: [EMAIL PROTECTED] Subject: Corrupted relay log Is there any way to fix a corrupted relay log on a slave? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an AS value in the WHERE clause.
Good point. I was focused on the question of using the alias to restrict results, so I left the function in the SELECT part. As you say, in this query, that would just give a useless column of '1's, so you might as well leave it out. In that case, though, the alias question is moot. That is, it doesn't really make any difference whether you put the condition in the WHERE or HAVING clause. On the other hand, we can imagine a query where we want to see a calculated result and use it to screen which rows are returned. Then using the alias in the HAVING clause is the way to go. For example, something like SELECT *, unix_timestamp()-unix_timestamp(last_seen) AS Last_Active FROM wifi_table HAVING Last_Active 600; Michael Adam wrote: Mike, I see what you're saying `active` was the alias name not an actual column. Ironically I was using a HAVING clause because I agree with that last post. Mike, why keep the `IF` statement? You're really saying give me all the records where this expression is true. Why not just move the expression in the `IF` to the HAVING clause? So take my old statement and ditch the where clause. You'll get: SELECT * FROM wifi_table HAVING unix_timestamp()-unix_timestamp(last_seen) 600; A little easier on the eyes no? Cheers, Adam On Apr 6, 2004, at 9:42 PM, Michael Stassen wrote: Adam, That won't work. Daevid doesn't have a column named active. Nor does he have to do the math twice. As was pointed out earlier, he can do what he wants using HAVING instead of WHERE, like this: SELECT *, IF(((unix_timestamp()-unix_timestamp(last_seen)) 600),1,0) active FROM wifi_table HAVING active = 1; Michael Adam wrote: Daevid, SELECT * FROM wifi_table WHERE active = 1 HAVING unix_timestamp()-unix_timestamp(last_seen) 600; Regards, Adam On Apr 5, 2004, at 8:29 PM, Daevid Vincent wrote: I'm curious when will I be able to do something like this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) 600),1,0) as active FROM wifi_table WHERE active = 1; It's so obnoxious, especially since I can do this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) 600),1,0) as active FROM wifi_table WHERE unix_timestamp()-unix_timestamp(last_seen) 600; Why do I have to do the math TWICE?! *sigh* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Learner Here Getting Frustraighted
On 4/8/04 1:05 PM, Michael Stassen [EMAIL PROTECTED] wrote: James McConnell wrote: On 4/8/04 9:18 AM, Barry . [EMAIL PROTECTED] wrote: Hi I recently began to work through the Book entitled PHP MySQL For Dummies and i am currently stuck towards the end of the forth chapter ive done everything as said so in the book but i keep getting an error message. Here is an outline of the problem: i have 2 tables one pet containing petName and petType other table is color containgin petName and petColor. The code which i keep getting errors on is: Select * from pet outer join color using (pet.petName=petcolor.petName) ; Now Ive posted my problem on several forums on the internet and have a wide range to replys suggesting what to do but nothing seems to work. Can anyone please help me?? Barry Smith I think the reason that's not working is because that doesn't look like MySQL syntax for a join statement. From the MySQL documentation, try this: SELECT * FROM pet OUTER JOIN color ON pet.petName=petColor,petName; Keep in mind this will only return rows in pet that have corresponding rows in color. I hope that's what you're looking for. The above code is not tested, but that's the proper JOIN syntax in MySQL. Going through the documentation, I don't see a USING keyword anywhere. That looks suspiciously like Oracle code. Hope that help! USING is documented in the manual http://www.mysql.com/doc/en/JOIN.html. USING expects a list of columns which exist in both tables. The following are quivalent: SELECT * FROM pet JOIN color ON pet.petName = color.petName; SELECT * FROM pet JOIN color USING (petName); Michael Sorry, all! I just did a cursory glance at the docs, but I swear I never saw the USING statement before. /me feels like idiot James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
Hi Kirti, mysql_config tell the perl build module how to link with the driver. The easiest way to fix this is make sure the bin driectory of MySQL is in your path. If you can type 'mysql_config' at the command line and get an answer like: Usage: /usr/local/bin/mysql_config [OPTIONS] Options: etc The perl DBD module should be able to find and use this program to configure itself. If you get a command not found error add the mysql bin driectory to the path. Ken - Original Message - From: Kirti S. Bajwa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, April 08, 2004 1:53 PM Subject: RE: Perl Modelues First a big thanks to Mark, BAO, Beacker, Matthais, Rich Ken for their response. My problem was that I thought that I needed to download tar file to start the installation. Rich's response somewhat cleared that. I ran the following commands, and got the following errors: % perl -MCPAN -e shell cpan install Data::Dumper cpan install DBI (no problem upto this point) cpan install DBD::mysql Errors returned: Can't exec mysql_config: No such file or directory at Makefile.PL line 174 readline() on closed filehandle PIPE at Makefile.PL line 176 ... (there are 10 such errors) Then few other lines of output without error. Then I get the following (error) response: Checking if your kit is complete... looks good Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (number of such errors) Note (probabally harmless): No library found for -lmysqlclient Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (again quite a number of such errors) Note (probabally harmless): No library found for -lgz Writing Makefile for DBD::mysql Makefile:89: *** missing separator. Stop. /usr/bin/make --NOT OK Running make test Can't test without successful make Running make install make had returned bad status, install seems impossible cpan What are these errors? How should I fix them? Can I continue installing other software (Apache, qmail, etc.) Thanks. Kirti -Original Message- From: Rich Allen [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 6:02 PM To: Kirti S. Bajwa Cc: '[EMAIL PROTECTED]' Subject: Re: Perl Modelues you can install Perl modules with $ perl -MCPAN -e shell cpan install [module::name] - hcir mysql - hcir On Apr 7, 2004, at 10:57 AM, Kirti S. Bajwa wrote: Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. -- 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: autoincrement column
Try truncate table. It essentially drops the table and recreates it. -- DVP -Original Message- From: Arthur Radulescu [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:36 AM To: [EMAIL PROTECTED] Subject: autoincrement column Before switching to version 4.0.18 of MySQL the command delete from table was also reseting the autonincrement column value... It seems that this is not happening anymore after we have installed this version... Any idee about this problem? Is there any other way to reset this? Arthur -- 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: Corrupted relay log
All you had to do what CHANGE MASTER TO MASTER_LOG_POS=Exec_master_log_pos, MASTER_HOST=Master_host, MASTER_USER=Master_user, MASTER_LOG_FILE=Relay_Master_Log_File This essentially forces mySQL to redownload the relay logs and stay in sync. Its call rewinding the replication thread. -- DVP -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:43 AM To: [EMAIL PROTECTED] Subject: RE: Corrupted relay log Here's what I did to fix it... Excert from MySql Manual... When you back up your slave's data, you should back up these two small files as well, along with the relay log files. They are needed to resume replication after you restore the slave's data. If you lose the relay logs but still have the `relay-log.info' file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_RELAY_LOG and MASTER_RELAY_POS options to tell the slave to re-read the binary logs from that point. This requires that the binary logs still exist on the master server. I performed these steps... Read info form relay-log.info file. Mysql: Slave Stop; Mysql: Change master to master_host = '192.168.1.10', Master_User = 'name', Master_Password = 'pass', Master_log_File = 'get this from relay-log.info', Master_log_pos = get this from relay-log.info; Mysql: Slave Start; Does anyone see a problem with this method? Thanks, Jeff -Original Message- From: Jeff McKeon Sent: Thursday, April 08, 2004 8:54 AM To: [EMAIL PROTECTED] Subject: Corrupted relay log Is there any way to fix a corrupted relay log on a slave? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [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: autoincrement column
Keep in mind you can't use truncate in a transaction though. I experienced this problem recently when wanting to delete an entire table and reset my auto increment values. While keeping it in a transaction. From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'Arthur Radulescu' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: autoincrement column Date: Thu, 8 Apr 2004 12:50:58 -0700 Try truncate table. It essentially drops the table and recreates it. -- DVP -Original Message- From: Arthur Radulescu [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 10:36 AM To: [EMAIL PROTECTED] Subject: autoincrement column Before switching to version 4.0.18 of MySQL the command delete from table was also reseting the autonincrement column value... It seems that this is not happening anymore after we have installed this version... Any idee about this problem? Is there any other way to reset this? Arthur -- 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] _ Limited-time offer: Fast, reliable MSN 9 Dial-up Internet access FREE for 2 months! http://join.msn.com/?page=dept/dialuppgmarket=en-usST=1/go/onm00200361ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption and my.cnf
I've experienced more corruption lately on my main site since I moved to my own server running 4.0.17max. The site is very busy (60GB a month) and the tables are large. I didn't have this level of problems on the rental server. What are the variables to look into regarding why my tables are getting corrupt? Is this a my.cnf issue? Is this a memory issue? Is this a hard drive issue? Is this a too many connections issue? Looking at my dmesg output.. end_request: I/O error, dev 03:42 (hdb), sector 52228450 hdb: read_intr: status=0x59 { DriveReady SeekComplete DataRequest Error } hdb: read_intr: error=0x40 { UncorrectableError }, LBAsect=56276830, high=3, low=5945182, sector=52228450 Is it possible this is related to my MySQL table corruption issues? There are more of these in the file. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Perl Modelues
Ken: Thank you for your response. I tried the 'mysql_config' command and got an errot message. I will add 'mysql_config' to the path (if I figure out how!!!). I noticed that on my installation the 'mysql_config' is in '/usr/local/mysql/bin' directory whereas you have indicated that it should be in '/usr/local/bin' directory! Please clarify!! Two more questions: (1) Since I have doen partial installation, would it hurt if I re-do the entire Perl instalation or start from the command where the errors were encountered? (2) I also have two Client MySQL servers (which connect to master MySQL server for data). Do I also need to install Perl on these Client MySQL Servers? Please remember, I am an IBM mainframe guy my knowledge of LINUX related soft is pretty low on scale. Thanks. Kirti -Original Message- From: Ken Menzel [mailto:[EMAIL PROTECTED] Sent: Thursday, April 08, 2004 3:29 PM To: Kirti S. Bajwa; [EMAIL PROTECTED] Subject: Re: Perl Modelues Hi Kirti, mysql_config tell the perl build module how to link with the driver. The easiest way to fix this is make sure the bin driectory of MySQL is in your path. If you can type 'mysql_config' at the command line and get an answer like: Usage: /usr/local/bin/mysql_config [OPTIONS] Options: etc The perl DBD module should be able to find and use this program to configure itself. If you get a command not found error add the mysql bin driectory to the path. Ken - Original Message - From: Kirti S. Bajwa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, April 08, 2004 1:53 PM Subject: RE: Perl Modelues First a big thanks to Mark, BAO, Beacker, Matthais, Rich Ken for their response. My problem was that I thought that I needed to download tar file to start the installation. Rich's response somewhat cleared that. I ran the following commands, and got the following errors: % perl -MCPAN -e shell cpan install Data::Dumper cpan install DBI (no problem upto this point) cpan install DBD::mysql Errors returned: Can't exec mysql_config: No such file or directory at Makefile.PL line 174 readline() on closed filehandle PIPE at Makefile.PL line 176 ... (there are 10 such errors) Then few other lines of output without error. Then I get the following (error) response: Checking if your kit is complete... looks good Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (number of such errors) Note (probabally harmless): No library found for -lmysqlclient Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (again quite a number of such errors) Note (probabally harmless): No library found for -lgz Writing Makefile for DBD::mysql Makefile:89: *** missing separator. Stop. /usr/bin/make --NOT OK Running make test Can't test without successful make Running make install make had returned bad status, install seems impossible cpan What are these errors? How should I fix them? Can I continue installing other software (Apache, qmail, etc.) Thanks. Kirti -Original Message- From: Rich Allen [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 6:02 PM To: Kirti S. Bajwa Cc: '[EMAIL PROTECTED]' Subject: Re: Perl Modelues you can install Perl modules with $ perl -MCPAN -e shell cpan install [module::name] - hcir mysql - hcir On Apr 7, 2004, at 10:57 AM, Kirti S. Bajwa wrote: Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
disabling backslash as an escape character in strings
Is there a way to disable the use of the backslash as an escape character in strings? I need to use an application that's designed to work on any database server supporting ANSI SQL. When it generates SQL insert/update queries, it doesn't escape backslashes in strings, because the ANSI SQL standard doesn't require backslashes to be escaped. So to insert the value \, the application generates the following query: INSERT INTO (test) VALUES('\'); Which causes an error in MySQL because it thinks the \ is an escape character and the string is not closed. If I modify the application to escape backslashes by replacing \ by \\, it works with MySQL, but with other databases that don't interpret the backslash as an escape character, it inserts two backslashes instead of one. What could I do to tell MySQL it should interpret strings in the standard way? I tried starting mysqld in ANSI mode (mysqld-max-nt --ansi) but it doesn't solve the problem. I would also prefer a per-connection way to fix this, is there an option I can set when connecting that won't affect other connections? I also have other applications using the same MySQL server, some of which are designed specifically for MySQL, so they may escape backslashes in the MySQL way and switching the whole server to ANSI mode would break them. I'm using MySQL 3.23 but I'm willing to upgrade to the latest 4.0x if it can solve this problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I make index block size all the same?
Why Mysql load index ... into cache require the same size index block when it creates indexes in different block sizes (1k or 2k)? I don't understand. Please help! __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: disabling backslash as an escape character in strings
Are you using a high level library such as Perl::DBI? If so, you should run all your strings the quote method. That will quote it properly for each database you connect to. If you are connecting to all the databases yourself using custom code, I would recommend you find some database neutral libraries and go from there. j- k- On Thursday 08 April 2004 01:37 pm, Christos Karras said something like: Is there a way to disable the use of the backslash as an escape character in strings? I need to use an application that's designed to work on any database server supporting ANSI SQL. When it generates SQL insert/update queries, it doesn't escape backslashes in strings, because the ANSI SQL standard doesn't require backslashes to be escaped. So to insert the value \, the application generates the following query: INSERT INTO (test) VALUES('\'); Which causes an error in MySQL because it thinks the \ is an escape character and the string is not closed. If I modify the application to escape backslashes by replacing \ by \\, it works with MySQL, but with other databases that don't interpret the backslash as an escape character, it inserts two backslashes instead of one. What could I do to tell MySQL it should interpret strings in the standard way? I tried starting mysqld in ANSI mode (mysqld-max-nt --ansi) but it doesn't solve the problem. I would also prefer a per-connection way to fix this, is there an option I can set when connecting that won't affect other connections? I also have other applications using the same MySQL server, some of which are designed specifically for MySQL, so they may escape backslashes in the MySQL way and switching the whole server to ANSI mode would break them. I'm using MySQL 3.23 but I'm willing to upgrade to the latest 4.0x if it can solve this problem. -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
Kirti S. Bajwa wrote: Ken: Thank you for your response. I tried the 'mysql_config' command and got an error message. I will add 'mysql_config' to the path (if I figure out how!!!). You need to add the directory which contains mysql_config to your path. Adding to your PATH isn't difficult, but the method depends on your shell. You can find your shell with echo $SHELL I noticed that on my installation the 'mysql_config' is in '/usr/local/mysql/bin' directory whereas you have indicated that it should be in '/usr/local/bin' directory! Please clarify!! /usr/local/mysql/bin is fine. With tcsh, you can add this to your path with set path=($PATH /usr/local/mysql/bin) With sh or bash, you should be able to add this to your path with export PATH=$PATH:/usr/local/mysql/bin Either way, you should probably also add this to the appropriate shell startup file, so mysql is always in your path. You can check your path with echo $PATH Two more questions: (1) Since I have done partial installation, would it hurt if I re-do the entire Perl instalation or start from the command where the errors were encountered? You didn't break anything, you just didn't succeed in adding the module. Just go back into CPAN and try again. I'd suggest perl -MCPAN -eshell install Bundle::DBI install Bundle::DBD::mysql Installing the bundles will make sure you have everything you need. As you've already installed DBI, you'll already have most of what the bundles contain, and CPAN is smart enough not to reinstall what you already have. (2) I also have two Client MySQL servers (which connect to master MySQL server for data). Do I also need to install Perl on these Client MySQL Servers? I'm not sure what you mean by Client MySQL servers. Do you mean machines which need the mysql client to connect to the machine running the mysql server, or do you mean machines which will run the mysql server as slaves to the master server? In any case, you can run the mysql server and client without perl. On the other hand, a number of the helper apps in /usr/local/mysql/bin are perl scripts. Of course, you will need to install perl on any machine where you want to use perl to connect/manipulate your mysql data, and perl is handy to have around in general. Please remember, I am an IBM mainframe guy my knowledge of LINUX related soft is pretty low on scale. Thanks. Kirti Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Displaying string including Kanji characters
When I use multi-byte code (especially UTF-8) with data including Kanji (Japanese kanji character) and execute SELECT from mysql client command, the vertical lines(|) of output table aren't aligned as straight lines and the output doesn't look like a table. When I use SJIS code, the problem of sheared line doesn't occur. This is because the display width and the byte number of data always match. I captured this result as images and posted them to the following URL. Please check them to see what I'm saying. http://epx.timedia.co.jp/mysql/select_result_html Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Displaying string including Kanji characters
INSERT INTO cp.Items SELECT distinct g.RecordID as id,'' as category_id, '' as pattern_id,'' as manufacturer_id, g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description, g.Desc4 as price,0 as quantity, '' as comments,'Active' as status,'n' AS is_bridal, 'Gallery' AS type, now() as created, now() as last_modified FROM copperlamp.Items g order by g.RecordID asc; and it does not error out, but there are problems with this... for example: ... g.Desc4 as price ... g.Desc4 ($500.00) is a varchar and price is a BigDecimal(10.2). but when it gets inserted the value is 0.00 for every field that's one the other is: ...g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description ... but though they are all varchars i can't seem to incorporate all the values into that one field. any thoughts would be great. dan -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem wil insert statement merging values
dan orlic wrote: INSERT INTO cp.Items SELECT distinct g.RecordID as id,'' as category_id, '' as pattern_id,'' as manufacturer_id, g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description, g.Desc4 as price,0 as quantity, '' as comments,'Active' as status,'n' AS is_bridal, 'Gallery' AS type, now() as created, now() as last_modified FROM copperlamp.Items g order by g.RecordID asc; and it does not error out, but there are problems with this... for example: ... g.Desc4 as price ... g.Desc4 ($500.00) is a varchar and price is a BigDecimal(10.2). but when it gets inserted the value is 0.00 for every field that's one the other is: ...g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description ... but though they are all varchars i can't seem to incorporate all the values into that one field. any thoughts would be great. dan -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting currency as a double
My final issue is inserting a currency, $500.00 for example into a field that is a BigDecimal(10,2). Anyone have any thoughts on how to do that within a SQL statement? dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]