Re: can't import sql using databasewhatever.sql
-p without the passwd immediately following is not compatible with input redirection. Either add the password after the -p with NO space, or better yet, create a .my.cnf file in the home directory of the user running the script and have it contain the user and password settings. Victor Spång Arthursson wrote: Hi! I'm having a strange problem - I can't read data using the mysql -u root -p databasepath/to/whatever.sql Not getting any error message, but some kind of introduction text flashes by reading: [powerbook:~] victor% /usr/local/mysql/bin/mysql -u root -p *** databas /path/to/tabell.sql /usr/local/mysql/bin/mysql Ver 12.21 Distrib 4.0.15, for apple-darwin6.4 (powerpc) Copyright (C) 2002 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: /usr/local/mysql/bin/mysql [OPTIONS] [database] -?, --help Display this help and exit. --auto-rehash Enable automatic rehashing. One doesn't need to use [... clip ...] max_allowed_packet16777216 net_buffer_length 16384 select_limit 1000 max_join_size 100 Anyone knowing what to do? It's pretty urgent cause I'm having this big database for a project that is to big to import using phpmyadmin - causes the browser to time out... Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to search by groups efficiently with MySql 4.0.15?
A group_id cannot be both 1 AND 3, but it can be 1 OR 3. Try OR Jouni Hartikainen wrote: Hi. how about: select record.* from link where group_id='x' left join record on record.archive_id=link.archive_id ? The problem with this query is that with simple left join, I can search only by a single group. If I set x to 3 here, I get all records that belong to group 3. But what about if I want to have all records that belong to groups 1 AND 3? If I add condition WHERE group_id='1' AND group_id='3', I get no results at all, since left join doesn't produce such rows that have multiple group_id columns. Sincerely, Jouni Hartikainen [EMAIL PROTECTED] _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1045
1. The command is not properly formed. 2. You don't have grant privileges. 3. Run this as the mysql root user. Stephan Wölfel wrote: When I try to execute grant all on *.* to stw where stw is my user name, I get an error 1045 (Access denied for user: '@127.0.0.1' (Using password: NO). Is there a way to check for user datas ? Or how to resolve this problem ? Stephan Wölfel ___ Schreibsüchtige gesucht! http://my-mail.ch/?redirect=9902 Heute schon Danke gesagt? Mit swissflowers.ch Blumen schenken! http://my-mail.ch/?redirect=9910 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1045
Stephan Wölfel wrote: Thanks for the advise. However I have some additional questions: ad 2) How can I check the grant privileges ? ad 3) How can I run this as the mysql root user ? mysql -u root -p enter the root password you set when you installed mysql use mysql enter the grant command. - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Stephan Wölfel [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 13, 2003 4:47 PM Subject: Re: Error 1045 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1045
You are not entering the right password. If you did NOT set a password for root, then leave the -p option off. Stephan Wölfel wrote: When I start MySQL with mysql -u root -p I get again the error 1045 (Access denied for user: '[EMAIL PROTECTED]' (Using password: YES). What am I doing wrong ? - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Stephan Wölfel [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 13, 2003 5:54 PM Subject: Re: Error 1045 Stephan Wölfel wrote: Thanks for the advise. However I have some additional questions: ad 2) How can I check the grant privileges ? ad 3) How can I run this as the mysql root user ? mysql -u root -p enter the root password you set when you installed mysql use mysql enter the grant command. - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Stephan Wölfel [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 13, 2003 4:47 PM Subject: Re: Error 1045 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ M=FCde auf kleines Postfach? http://my-mail.ch/?redirect=3D9903 Weine aus der Toskana! http://my-mail.ch/?redirect=3D1179 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shell Script to Insert Data
The first line starts mysql. When mysql exits, the second line does nothing, as it is not a valid shell command. try : echo 'INSERT INTO table_name (column_name) VALUES (value)' | mysql --user=root --password= Database_Name Or put the second line in a file, and cat the file through a pipe to mysql. Mike Tuller wrote: I am trying to create a script that will insert data. Right now I am just using something simple to test this out, but I can't get it to work. Here is what I have. mysql --user=root --password= Database_Name; INSERT INTO table_name (column_name) VALUES (value); After I run the script, I check the data, and nothing was entered. When I run each statement on it's own (not from a script file, but in the shell) Everything seems to work. It just doesn't work when you try to run it from a script. Any ideas? Mike Tuller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tricky query
This is a bit vague. How about showing what you have done, and how it is not what you wan't. Colleen Dick wrote: I am building a SELECT using a left join and I almost have what I want. One of the fields that gets sucked in from the left join is either null or has a value in it. if it is null I only want to include the rows from the main table where the field isdemo=1 if that field has a value in it I want all the rows that match otherwise. Is there any way to do it all in one query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem setting root password.
Rob Yale wrote: Hi folks, The following is copied exactly from my machine, except for the munged password. Setting the root password can't be completed, because the host apparently can't connect the mysql server. What am I doing wrong?: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h melody.yalemusic.ca password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting mysqld daemon with databases from /var/lib/mysql [EMAIL PROTECTED] local]# /usr/bin/mysqladmin -u root password 'xxx' You just successfully set the password. [EMAIL PROTECTED] local]# /usr/bin/mysqladmin -u root -h melody.yalemusic.ca password 'xxx' /usr/bin/mysqladmin: connect to server at 'melody.yalemusic.ca' failed error: 'Host 'melody.yalemusic' is not allowed to connect to this MySQL server' Two problems here. 1. You have one password per user. You set it above, but don't use it here. 2. Root can only connect via localhost until you GRANT privileges from elsewhere. You are trying to connect from melody.yale.ca. This is not localhost, even though it is the same machine. [EMAIL PROTECTED] local]# Thanks, Rob Yale -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Join Issue
Leave off the 'WHERE e.Game_ID is NULL' Wayne Helman wrote: How would one join multiple table and selected all records from a joined table whether they exist OR not? I can get the records if they exist in a second table and I can get the records if they don't exist, but is there a way to combine the two? I have, for example, a statement like this (selects where doesn't exist): SELECT c.Game_Date AS Date, c.Game_Time AS Time, a.Team_Name AS Home_Team, b.Team_Name AS Away_Team, e.Game_ID FROM schedule c JOIN Teams a ON c.Home_Team = a.ID JOIN Teams b ON c.Away_Team = b.ID LEFT JOIN broadcasts e ON c.ID = e.Game_ID WHERE e.Game_ID IS NULL Any ideas? __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile question
[EMAIL PROTECTED] wrote: In MYSQL documentation the following line is Some cases are not supported by LOAD DATA INFILE: 1. Fixed-size rows( FIELDS TERMINATED BY and FIELDS ENCLOSED BY both empty) and BLOB or TEXT columns I defined a column in a table as type TEXT. I then loaded values using LOAD DATA INFILE into the table. Since I was able to load data for a TEXT column, what does the above statement mean.. Well, when loading a single column table, there are no field separators. The line terminator is sufficient. Try defining a table with more than one field, and see what happens. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data: odd behaviour
How did you determine this? I hope not by the order returned when you select them. There is no order to the set returned unless you use an ORDER BY clause. Adrian Sill wrote: Morning all, I've been using the same format of text files to import monthly text files using LOAD DATA LOCAL INFILE into a table. It's been working fine until today when it decided to import the rows from the text file in a semi-random order instead of working through the file from start to finish. Anyone heard of this behaviour before? Maybe a mysql config blip? Adrian ** Granada Sky Broadcasting Limited (GSB) Franciscan Court, 16 Hatfields, London SE1 8DJ Tel 020 7578 4040 Fax 020 7578 4035, Registered in England No: 3101815. This e-mail and any attachments may be confidential. If you have received this communication in error please tell us by return e-mail or at the number's above and delete it, and any copies of it. You must not use, disclose, distribute, print or rely on this email. Unless clearly stated that this disclaimer should not apply, this e-mail is not intended to create legally binding commitments on behalf of GSB, nor do its contents reflect the corporate views or policies of GSB. Although GSB routinely screens for viruses GSB makes no representation or warranty as to the absence of viruses in this e-mail or any attachments. Visit our website at http://www.gsb.co.uk ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld error
Error 13 means that the file exista, but mysql has no permissions for
the file.
Make sure mysql is the owner of the data directory and all its
subdirectories and files.
Joseph Donato wrote:
Folks,
When I try to start MySQL the mysqld just crashes. I looked at
/usr/lib/mysql/{hostname}.err file and I see the following:
030925 10:01:47 mysqld started
030925 10:01:47 /usr/libexec/mysqld: Can't find file:
'./mysql/host.frm' (errno: 13)
030925 10:01:47 mysqld ended
I can not make heads or tails out of what it is trying to tell me, or
where the ./mysql/host.frm file is. Does anyone know?
Joseph Donato
-- Joseph Donato RUCS-NB Help Desk [EMAIL PROTECTED]
732-445-8706 A witty saying proves nothing, but saying something
pointless gets people's attention
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up a search
Run explain on the query and see if indexes are being used. John Almberg wrote: I am trying to find records (from the 'stamps' table) that are NOT related to records in the 'links' table. To do this, I'm using a left join. For example: select s.*, l.item_id as lid from stamps as s left join links as l on (( s.item_id=l.item_id)) WHERE (s.sold is null); This worked great when there were just a few records in each table, but now that there are about 4000 records in each table, this simple query has slowed down to a crawl. It currently takes about 2 minutes to execute on a linux machine. Not good enough, especially since there will ultimately be at least 10,000 records in each table. Is there a better technique for doing this kind of search? Any suggestions much appreciated. Brgds: John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld will not start
Joseph Donato wrote: Folks, I am trying to start mysqld and when I do the deamon crashes. mysqld.log has the following: 030919 14:25:06 mysqld started 030919 14:25:08 Can't start server : Bind on unix socket: Permission denied 030919 14:25:08 Do you already have another mysqld server running on socket: /usr/lib/mysql/mysql.sock ? 030919 14:25:08 Aborting 030919 14:25:08 /usr/libexec/mysqld: Shutdown Complete 030919 14:25:08 mysqld ended which says to me that something is already listening on that port. Not port, socket. Acording to netstat nothing is running. Does any one know what is going on with this. This is not a network issue. Does the socket /usr/lib/mysql/mysql.sock exist? Does mysql own /usr/lib/mysql, and all its files? Is /usr/lib/mysql world searchable? Is /usr/lib/mysql/mysql.sock work writeable? Joseph Donato __ Joseph E. Donato New Brunswick Computing Services RUCS-NB Help Desk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join optimization
[EMAIL PROTECTED] wrote: I have two tables and am running a simple join between them to get questions and their repsective response averages from a survey. The question table has 49 rows and the Response table has 126,732. I'd like to cut down on the time its taking to run this specific query...as i'll be running many like it to generate reports. The query below is the selecting the most data, normally this will be limited to specific groups by joining more tables. I am executing the following query SELECT Question.Text_Long, AVG( Response ) FROM `Response` INNER JOIN Question ON Question.Question_Key = Response.Question_Key WHERE Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER BY Question.Question_Key ASC You are doing a string compare on an integer field. Why? Everything i've done so far leaves this query taking about 7-8 seconds to excecute...and i'm trying to cut that time down. If i leave out the join and just execute --SELECT Question_Key, AVG( Response ) FROM `Response` GROUP BY Question_Key it takes about 3 seconds...is there anything i can do to speed the join up? i've tried using string functions instead of LIKE, but none of them proved to be faster. i've also changed the table that i'm requesing the data from and grouping by(Question and response)...all with mimimal impact. I'm running MySQL. 3.23 Thanks for any help/thoughts you may have. have a good weekend. Jeff the table layout is mysql describe Response; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe Question; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | NULL| | | Text_Short | varchar(255) | YES | | NULL| | | Category_ID | int(11) | YES | | NULL| | | SurveyID| int(11) | YES | | NULL| | | End_Date| datetime | YES | | NULL| | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ 7 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Different users try to access different mysql.sock
You need to add the new socket location to the paragraphs in my.cnf that start with [client] and [mysql] /etc/my.cnf needs to be world readable. Check for private .my.cnf files in the home directories of the users having the problem. michael watson (IAH-C) wrote: Here is an interesting one! I have changed datadir in my.cnf to point to /data/mysql. I also have the socket file as /data/mysql/mysql.sock This is all fine and works. My server starts up with no complaints, /data/mysql/mysql.sock springs into existence and everyone is happy. Except my users. If I execute mysql as root: /usr/bin/mysql -p I get in and everything works. If I try and get in as a user (mwatson = me) /usr/bin/mysql I get: Cannot connect to local MySQL server through socket /var/lib/mysql/mysql.sock Now, /var/lib/mysql/mysql.sock was where the socket file used to exist before I edited my.cnf and restarted the server. The permissions on my /data/mysql directory are 775 (rwxrwxr-x) for owner mysql and group users. Either way I should be able to get to /data/mysql/mysql.sock So why are different users (root and mwatson) trying to get to different sockets on the same installation? Thanks in advance for your help Mick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: more on don't work.
Is the server running? SWIT wrote: badboy# ./mysql -u root -p Enter password: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' ( 2) that file is not there. should I touch it ? argggh ! and ya say windows sucks. (ok the beer is talking now) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL API C leaks
Andreï V. FOMITCHEV wrote:
Hello everyone,
I use Valgrind to check my programs and this last found leaks in
libmysqlclient.so.
My code is simple:
char * requete = SELECT * FROM NOM_TABLE_1;
Looks like a misplaced '' .
MYSQL * mysql = mysql_init((MYSQL *)NULL);
MYSQL_RES * mysql_resultat;
MYSQL_ROW mysql_ligne;
if(mysql == NULL)
{
return(-1);
}
if (!mysql_real_connect(mysql, ADRESSE_BDD, LOGIN_BDD, PASSWORD_BDD,
NOM_BDD, 0, NULL, 0))
{
fprintf(stderr, Impossible de se connecter au serveur %s\n
Error=%s\n, ADRESSE_BDD, mysql_error(mysql));
mysql_close(mysql);
return(-1);
}
if(mysql_query(mysql, requete) != 0)
{
fprintf(stderr, Impossible d'executer la requête %s\nError=%s\n,
requete, mysql_error(mysql));
}
else
{
mysql_resultat = mysql_store_result(mysql);
if(mysql_resultat != NULL)
{
for(i = 0; i mysql_resultat-row_count; i++)
{
mysql_ligne = mysql_fetch_row(mysql_resultat);
// traitement
}
}
mysql_free_result(mysql_resultat);
}
mysql_close(mysql);
Result of Valgrind: ==3691== 64 bytes in 1 blocks are still reachable
in loss record 1 of 2
==3691== at 0x4015E310: malloc (vg_clientfuncs.c:103)
==3691== by 0x40254A2D: my_malloc (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025A218: init_dynamic_array (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025AE96: init_available_charsets (in /usr/lib/mysql/
libmysqlclient.so.10.0.0)
==3691== ==3691== 4088 bytes in 1 blocks are still reachable in loss
record 2 of 2
==3691== at 0x4015E310: malloc (vg_clientfuncs.c:103)
==3691== by 0x4025A64C: my_once_alloc (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025ACB0: read_charset_index (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025AEAB: init_available_charsets (in /usr/lib/mysql/
libmysqlclient.so.10.0.0)
Is it a BUG or did I something?
Best regards,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TCP on Solaris 8
Do you have skip-networking in your /etc/my.cnf file? Scott Barron wrote: Hello, I am attempting to get MySQL to listen on a TCP socket on Solaris 8. From what I've seen in the documentation, and my experience with MySQL on Linux this should happen automatically. I've tried 4.0.14 from source and binaries as well as 3.23.57 binaries without success. If I compile with the --with-tcp-port set to a certain port, just starting mysqld_safe leaves the following in the logs: mysqld: ready for connections. Version: '4.0.14' socket: '/tmp/mysql.sock.2' port: 0 If i specify -P 3306 on the command line it logs: mysqld: ready for connections. Version: '4.0.14' socket: '/tmp/mysql.sock.2' port: 3306 Yet I cannot connect on port 3306 and netstat does not show anything for port 3306 (or any port that I might specify at build or run time). I'm pretty lost because everything I do on the solaris box I can repeat on the Linux box and have it work like I expect. Has anyone else had a similar problem? Am I just missing something? Thanks, -Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
Joshua Spoerri wrote: On Tue, 5 Aug 2003, gerald_clark wrote: You are ORing on two different fields. The index cannot be used to check the value of z for an OR. ORing on two different fields is what I have been asking about :). Using a composite index was suggested, which strangely seems to work only when there are no other columns in the table. When there are no other columns in the table, it can scan the complete index file to satisfy the query. If there are other fields, it would have to scan the entire index file, and then access the data file to pick up the other fields. It is faster to just scan the entire data file. Why are you cross posting? Initially because I didn't know which list was appropriate, and later in response. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: heading off in mysql?
Of course, you tried mysql --help ? [EMAIL PROTECTED] wrote: Hi all, Just a quick question here. May I know how to get a result without heading in Mysql (like set heading off in Oracle) ? Thanks and regards, Helen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection
You don't say what your problem is. aaldrik groenewold wrote: I just installed mysql 4.0 .There were many problems with upgrading from 3.23 to 4.0 so I deleted 3.23 and made a fresh start with 4.0. Everything works fine now but the only problem to connect to the server is via the /usr/local/mysql/bin directory. Is there a way to connect if I am root. thanks for your help. -aaldrik __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count(*) Problem in PowerBuilder Thru MySQL ODBC 3.51
You are not doing select count(*) You are doing select count ( *) Get rid of the spaces before the ( Fatt Shin wrote: Hi, I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB Connector 3.51. I'm facing a problem where whenever I issue a SELECT COUNT(*) statement from PowerBuilder, I always get SQL syntax error back from MySQL. (Refer to ODBC Trace I captured below). metrohouse af8-b94 ENTER SQLExecDirect HSTMT 014D2360 UCHAR * 0x020A0EA2 [ -3] select count ( *) from code \ 0 SDWORD-3 metrohouse af8-b94 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 014D2360 UCHAR * 0x020A0EA2 [ -3] select count ( *) from code \ 0 SDWORD-3 DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( *) from code' at line 1 (1064) metrohouse af8-b94 ENTER SQLErrorW HENV014D12A0 HDBC014D14B0 HSTMT 014D2360 WCHAR * 0x0012E65C (NYI) SDWORD *0x0012E6A8 WCHAR * 0x0012E25C SWORD 512 SWORD * 0x0012E6B0 metrohouse af8-b94 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV014D12A0 HDBC014D14B0 HSTMT 014D2360 WCHAR * 0x0012E65C (NYI) SDWORD *0x0012E6A8 (1064) WCHAR * 0x0012E25C [ 208] [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-nt]You have an error in your SQL syntax. Check the manual t SWORD 512 SWORD * 0x0012E6B0 (208) The same statement actually working fine whether I ran it using mysql or sql yog or even using the same ODBC connector thru Microsoft Access. (Refer to ODBC Trace below) MSACCESSfd4-ff4 ENTER SQLExecDirectW HSTMT 09BB18C8 WCHAR * 0x0B431048 [ -3] SELECT COUNT(* ) FROM `code` \ 0 SDWORD-3 MSACCESSfd4-ff4 EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS) HSTMT 09BB18C8 WCHAR * 0x0B431048 [ -3] SELECT COUNT(* ) FROM `code` \ 0 SDWORD-3 Anybody have any idea what may cause the error here ??? Thanks a lot. Regards, FattShin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem, Confused by Left Join.
You have not shown us anything that would indicate that your output is not correct. If you think something is missing you have to show us what is missing, and why you think it should not be. John Wards wrote: I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? Cheers John Wards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
You are ORing on two different fields. The index cannot be used to check the value of z for an OR. Why are you cross posting? Joshua Spoerri wrote: On Tue, 5 Aug 2003, Alexander Keremidarski wrote: MySQL will never use any index for small tables. With just few rows using index adds overhead only. Table scan is faster in such cases. This is docummented behaviour. is 100,000 rows small? my simple OR queries take longer than a second. mysql create temporary table x (y int, z int, index (y, z)); insert into x select f1,f2 from myrealtable; alter table x add q int; explain select * from x where y = 1 or z = 1; Query OK, 0 rows affected (0.00 sec) Query OK, 101200 rows affected (1.95 sec) Records: 101200 Duplicates: 0 Warnings: 0 Query OK, 101200 rows affected (1.61 sec) Records: 101200 Duplicates: 0 Warnings: 0 +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++-+ | x | ALL | y | NULL |NULL | NULL | 101200 | Using where | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing large integers properly
How about BIGINT(10) ZEROFILL ? Eben Goodman wrote: I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Any advice is appreciated, thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQuery bug in 4.1
Your primary query has no where clause, so you are setting all Value to a value from a random record for the most recent date. Hardly looks like a 'bug' to me. And why the cross post? Daniel Kiss wrote: Hi all, I have two tables CREATE TABLE main ( ID int not null, Value int ); CREATE TABLE sub ( mainID int not null, KeyDate date not null, SubValue int not null ); I want the Value field in the main table to be set to the latest SubValue in the sub table. I suppose this syntax should work. But it does not, and sets the Value fields to incorrect values. update main set Value = (select SubValue from sub where main.ID = sub.mainID order by KeyDate desc limit 1) Any ideas? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File Permission
Since you don't show your grant statement, it is difficult tell you what the problem is. Since FILE is a global permission you need to grant it on *.* not somedatabase.* David Scott wrote: Greetings all I am not able to GRANT file permission to users. All permissions end up being issued with FILE being left out. What could I be missing? I running 3.23.53 on Mac OS 10.2. Thank you. - David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import databse ?
Vidhya CS wrote: Hi , I am trying to export a database from one machine ie linux, and import the same database to another machine ie ,solaris . I exported the database using the following command . mysqldump -c -u vidhya ifmonitor $HOME/ifmonito.backup. this is ok , ifmonitor-backup has the table creation info as well as the table data . but when I try to import the same database to mysql in solaris m/c using the command mysqldump -u vidhya ifmonitor ifmonitor.backup Mysqldump is only used to dump a database, not restore. Use the mysql client to load it - ex: mysql -u vidhaya ifmonitor ifmonitor.backup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
If all these fields are defined as not NULL, simply use alter table with the ignore option to add a unique key on all pertainant fields. Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. Thanks for your help Kim Mackey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: batch mode
azamka wrote: I am trying to right sql statement on the shell prompt but its not going through. Here is the statement: [EMAIL PROTECTED] usr]# mysql security select * from machine; -bash: select: No such file or directory echo select * from machine | mysql security or put your select statement in a file and: mysql security sqlfile It is asking for the text file. And it works fine with the text file. I am wondering how can we write a sql statement on the command prompt. I looked into the manual but didnt find it. Is there anyone who can tell me the exact syntax. Please help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL shutdown error...
Mysqld is the server, and can be started automatically.
Mysql is a client program. It makes no sense to start it automatically.
-{ Rene Brehmer }- wrote:
Hi gang
Not sure if this is more for the RedHat list than here, but let's try...
I've put MySQL on a RedHat by using the RPM ... haven't yet figured
out if it actually works... but I had to make the thing start
automatically on my own ... so I put mysqld and mysql on the startup
list for runlevel 3, which is what I run at...
But when it shifts to runlevel 0 or 6, it saysStopping MySQL ..
[FAILED]everytime ... now is that essential, or did I do something
wrong???
I basically added mysql and mysqld as startup on level 1-5, and on the
kill list for level 0 and 6...
Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to retrieve integer in C prog?
Use atoi();
Andy Jackman wrote:
Hi,
I'm new to mySql. I've got a table with an integer field defined like
this:
l_start_wait int(9) not null
I inserted a value into the table using mysql command line client with:
Insert into tbl_wait_list values(12345678);
When I use mysql_fetch_row() to retrieve the column values I am
surprised to see
that mysql_fetch_lengths() returns a length of 8 rather than 4 and
row[0] seems to
point to bytes containing ascii characters 12345678 rather than being
a pointer
to an int.
I've searched the manual and google but apart from occasional references
to blob
data most examples seem to assume that all data is ascii.
What I'd like to be able to do is something like this: int i =
(cast)row[0];
Please tell me what I'm doing wrong - or is this a limitation?
Thanks,
Andy.
Here's my code fragment if it helps:
int checkWaitList(MYSQL *aDb)
{
// aDb connection is already open
MYSQL_RES *rsResult;
MYSQL_ROW row;
char pszSql[256];
int lStartTime;
unsigned long *lengths;
strcpy(pszSql, SELECT l_start_wait FROM tbl_wait_list);
mysql_query(aDb,pszSql);
rsResult = mysql_use_result(aDb);
if (row = mysql_fetch_row(rsResult)) {
lengths = mysql_fetch_lengths(rsResult);
sprintf(pszSql, %d, %12.12s\r\n, lengths[0], row[0]);
// Prints 8, 12345678
return -1;
}
else {
return 0;
}
}
mysql_query(aDb,pszSql);
rsResult = mysql_use_result(aDb);
if (row = mysql_fetch_row(rsResult)) {
lengths = mysql_fetch_lengths(rsResult);
// Found a row
tsOut(Found: );
tsOut(\r\n);
//lStartTime = *(int *)row[1];
sprintf(pszSql, %ud, %12.12s\r\n, lengths[0], row[0]);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Myisachk
Shutdown mysqld before running myisamcheck, or use check/repair table. Jeff McKeon wrote: Hello, When I run a script to issue myismchk against the tables in my database, I get the following.. [EMAIL PROTECTED] scripts]# ./check_mysql_tables myisamchk: MyISAM file /var/lib/mysql/telaurus/Admin.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table '/var/lib/mysql/telaurus/Admin.MYI' is usable but should be fixed myisamchk: MyISAM file /var/lib/mysql/telaurus/Customer.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table '/var/lib/mysql/telaurus/Customer.MYI' is usable but should be fixed This db is ver 3.23 and replicates from another db. What causes the tables to be 1 clients is using or hasn't closed the table properly I do have PHP web sites that pull data from this db, am I perhaps not closing the tables after I access them from the PHP with select queries? If that's the case, why are tables I never touch with the website getting this error, does replication have something to do with it? How can I tell what clients are currently using the tables? Thanks, Jeff McKeon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump isn't working anymore
Did you try the -q option? Jake Johnson wrote: Hello, I have been backing up my db with the mysql dump command and it no longer works. This is an example of the command I was running... mysqldump -u dbuser -p -C mydb out.sql but returns this -- MySQL dump 8.21 -- -- Host: localhostDatabase: mydb - -- Server version 3.23.49-log Does anyone have any ideas why this doesn't work anymore? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Didn't find any fields in table 't_table'???
Jake Johnson wrote: How do I fix this error? My tables have data. But they probably are not owned by mysql. Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to limit COUNT(*)
Perhaps you could post some examples of what you have tried. I don't understand what you are asking. Mojtaba Faridzad wrote: Hi, I guess there is no way to limit COUNT(*). Is that right? We cannot use the result of COUNT in WHERE condition or LIMIT doesn't help. In this case so far I have retrieved a field and used LIMIT. Is there a better way to control it? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to limit COUNT(*)
Mojtaba Faridzad wrote: First of all, I don't wanna let the query run to the end and after that, count the number of records. as I mentioned, some tables are huge and takes a long time to run COUNT(*) query for all records. About LIMIT, it doesn't effect on COUNT query. but for the second query ( retreiving a field ) I can use it and as you mentioned, I use LIMIT v1,v2. this part of query that I tried to use COUNT is before retreiving the all fields of the last page. it's just checking for the number of records, to find the number of pages and show the last page. about why I am not using auto_number or other indexes, because the table and conditions are variable and on run-time. If you are using MYISAM tables, count(*) does not scan the table to get the count. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: store video file into MySQL database server
Your maximum packet size must be greater than the size of the video file. Steven Wu wrote: Hi Need emergency help: I am doing some project use the MySQL to store the video file. The format of video is either avi or mpg. However I can not successfully insert the video file into the cooresponding field of a table by using the LOAD_FILE function. Does anyone know how to store video into MySQL database server ? Please help me, thank you in advance. The following is my table and SQL code. CREATE TABLE GAME ( GAME_ID INTEGER NOT NULL PRIMARY KEY, GAME_NAMEVARCHAR (20), VIDEO LONGBLOB ); INSERT INTO GAME GAME_ID, GAME_NAME, VIDEO VALUES(2, 'Termonator2', LOAD_FILE(/tmp/tm.mpg); My email is [EMAIL PROTECTED] Steven Wu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: safe_mysqld not letting go of tty on remote start
It writes a status message to the screen, and overwrites your shell prompt.
Hit enter, and you will probably find that you have a prompt afterall.
chad kellerman wrote:
Hey guys,
Anyone ever notice that when you restart mysql ( whether is be thru
mysql.server or ${MYSQL}/bin/sae_mysqld the tty stays connected.
Thanks,
Chad
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Working with the text datatype in Mysql
Lekeas GK wrote: Gerald, The version of MySQL is 3.23, That only spans 50 some odd releases. Which one? the platform is Solaris, the language is English Computer language. PHP? Perl? Python? Mysql command line tool? Did the program performing the insert properly quote special characters? Did it check for sucess of the insert? and the way I check whether data has been entered in the database or not is by running a select query on the database. Useing Which language above? Was that of any help? I tried searching the net but couldn't find more information about what might be causing the problem... Thanks, George I think there is a world market for maybe five computers. Thomas Watson, Chairman of IBM, 1943. - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Lekeas GK [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 18, 2003 2:11 PM Subject: Re: Working with the text datatype in Mysql | You supply almost no useful information here. | What version of MySQL are you using? | What platform? | What language? | How do you check to see if anything was entered? | | | Lekeas GK wrote: | | Hi All, | | I am managing a small database where a few fields are defined as being of | type text. In my understanding, this would allow the user to enter up to | about 65,000 characters in the text field. | | However, if the user types about 2,000 characters, then nothing is entered | in the database. I have checked the max_packet_allowed and the | net_buffer_length and the limits are quite high. | | Could somebody advice me on how to go about solving this problem? | | I look forward to hearing from you soon. | | Thanks in advance. | | George | | I think there is a world market for maybe five computers. | Thomas Watson, Chairman of IBM, 1943. | | | | | | | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select via Perl
Ashwin Kutty wrote: I am trying to read a file and see if the contents of it exists in the DB or not and am trying to do it via a Select. I try to do a select * into outfile /tmp/result.txt from table where field like %$var%; but it always goes through the first two lines of the input file and then says the file already exists. Is this because I read each line of a file in a for loop in perl and the mysql query tries to recreate the outfile each time? Yes Is there any other way I can dump the results of the select to a file of some sort? Have the perl program append the /tmp/result.txt to /tmp/ultimateresult.txt , and unlink /tmp/result.txt inside your loop. Thanks.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Working with the text datatype in MySQL
Lekeas GK wrote: Hi All, I am managing a small database and some of the tables need to be strings of an average length of about 2,000 characters. I decided to use the text datatype to store this information knowing that the upper limit is about 65,000 characters. However, the system crashes when the user tries to enter about 2,000 characters. What is your definition of crashes? Are special characters properly quoted before insertion? I checked the max_allowed_package and net_buffer_size variables and they are both set to 1MB. Could you please advice me on what to look for or what to change in order for the system to work as it is supposed to be working? I look forward to hearing from you soon. Thanks in advance. George Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleted index file (.MYI)
Accidently? You just asked if you could delete the .MYI table because of your disk full error. Oswaldo Castro wrote: Hi Everybody I have acidentally deleted an index file (.MYI) from a table. Is there another way, besides its backup, to recover this table ? Thanks for any help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL unsuccesfully installed. Need help!
Compiling MySQL from the source tarball is not for linux newbies. Get the RPMs. root wrote: hi there, I've tried to install mysql-3.23.55.tar.gz but failed. Firstly, I've created directory /home/users/mysql and add group for mysql. Those are the command that I've used previously: shellgroupadd mtsqlid shellmkdir /home/users shelluseradd -d /home/users/mysql -s /bin/false -g mysqlid mysqlid Then i decompressed mysql-3.23.55.tar.gz into /home/users/mysql and one new directory called mysql-3.23.55 created. According to www.mysql.com, mysql installation, i need to run ./configure --prefix=/usr/mysql but this is the message i've got. configure:error: no acceptable C compiler found in $PATH then when i tried to run this command shellscripts/mqsql_install_db it also give me an error that is no such file or directory. I really don't know what to do. Please do help me. Frankly speaking, i'm still new to linux and mySQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13
Nick Boudreau wrote: Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. mysql doesn't have permissions for this file. It is probably owned by root, or its directory is. 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: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select not producing desired results
fsttik has dashes in it and your having does not. Either add dashes to your having or change the alias to min(datein+0) as fsttik. Patrick Shoaf wrote: I am trying to get a SELECT working and not having any luck, can someone please help? SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales,store FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno ORDER BY fsttik DESC LIMIT 20; results in the following data: strref acctno namelastnamefirst phone1 adddate fsttik sales store 1 52 9266BROUNCE GALE 7242586226 2002-01-26 2003-07-08 15.50 52 2 52 2194HOPKINS PATTY 7244838865 2001-09-14 2003-07-08 0.00 52 3 52 15622 CLARK MARGARET/DAN 7248340156 2002-07-09 2003-07-08 85.92 52 4 51 27211 SEDNEY ED7244899547 2003-07-08 2003-07-08 62.50 51 5 51 8854221 KASZAK KIRK 4122764951 1996-11-18 2003-07-07 26.02 51 6 51 27093 GLAZER BONNIE4122761287 2003-07-02 2003-07-02 3.50 51 7 52 26842 HARBAUGHFRAN,DAN 7245377227 2003-06-22 2003-07-01 38.22 52 8 51 26726 KINGRICHARD 4124003773 2003-06-18 2003-07-01 18.72 51 9 51 622 MILLER ROBERT/DENISE 4122578375 2001-08-21 2003-06-30 17.82 51 10 7 8336322 HORNBAKEJOAN 4128336322 2001-03-23 2003-06-27 26.78 51 11 51 26219 BERKO MIKE 4126750648 2003-05-30 2003-06-26 7.60 51 12 51 26736 SCHMULEVICH RAFAEL4122720518 2003-06-18 2003-06-19 6.73 51 13 52 26642 FAWCETT CHUCK/SUSAN 7249423761 2003-06-16 2003-06-19 94.62 52 14 51 26401 ZYWAN JOHN 7248734686 2003-06-05 2003-06-19 35.02 51 15 51 26738 SCOTT BETH 4122573588 2003-06-18 2003-06-19 29.77 51 16 51 26729 KURLANDER CARL 4126820382 2003-06-18 2003-06-18 45.27 51 17 51 26734 MEEKS JULIE 4124295354 2003-06-18 2003-06-18 0.00 51 18 52 8721884 GAUDINO LARRY 7248721884 2001-06-09 2003-06-18 133.90 52 19 51 26730 PRAKASH PREM 4126875411 2003-06-18 2003-06-18 4.95 51 20 51 26732 SOUTHORNLAURIE/ROBERT 4122570383 2003-06-18 2003-06-18 29.23 51 When I change the SQL SELECT to read: SELECT strref,acc.acctno,acc.namelast,acc.namefirst,phone1,adddate,min(datein) as fsttik, sum(amt) as sales FROM acc,sdtik WHERE acc.acctno=sdtik.acctno and voidreason=0 and store=40 and store=59 GROUP by acc.acctno HAVING fsttik=20030701 ORDER BY fsttik DESC LIMIT 20; I do not receive any records back. What is the proper method to retrieve based on MIN(datein)? Any/All help greatly appreciated... Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql error : 1036
Does the mysql user have write permissions on the files? HA. Mooduto wrote: dear all, what is the problem sql error : 1036 ...table is read only. please help me.. thank's... -- This mail sent through PSP: http://www.polinpdg.ac.id/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With RAND()
Since you are ordering by column3, ( in other words sorting your random mumbers ), what do you expect? Scott A. Hammond, Sr. wrote: I am running MySQL 3.23.54. Query: SELECT column1, column2, RAND() AS column3 FROM table WHERE column2 IS NOT NULL ORDER BY column3 LIMIT 10; I am running this query within a PHP page that uses mysql_connect. More then 9 out of 10 times I get the first 10 rows of the database in order, i.e. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Every once in a while, I get it in a different order, however, very rarely. This exact PHP file works on another server which is running an earlier version of MySQL (3.23.41). Moved to this server, I can't seem to pull 10 random rows from the database. I've tried initializing RAND with a number but that didn't help. I'd consider just generating the random numbers myself and selecting the rows, however, I need the WHERE column2 IS NOT NULL as part of the query so it needs to be randomized within MySQL. I've altered the program to echo column3 (the random number) and I get a different set of numbers each time so it isn't a cached result or anything like that. The numbers are simply coming out in the same order as the rows are originally in the database on most attempts. I tried the query from command line (MySQL client) and the results seem to be more randomized then when called via PHP, however, I'm having a hard time finding any clues on either end (MySQL or PHP) as to why I'm not getting random results. This query works on my other servers (which are running earlier versions of MySQL). Any thoughts or help is appreciated. Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a 1005 error (errno: 150), please...
You must create the key (sku), it is not created for you.
Tom Gazzini wrote:
I would appreciate some help with a problem. I'm trying to create two
tables that have referential integrity.
If I try and create the following table it works fine:
CREATE TABLE book
(
sku INT
) TYPE=INNODB;
However, if I creating this table, I get an error:
CREATE TABLE book
(
sku INT,
FOREIGN KEY (sku) REFERENCES stock_item (sku) ON DELETE CASCADE
) TYPE=INNODB;
The error is: ERROR 1005: Can't create table './shop/book.frm' (errno:
150)
The parent table is as follows:
CREATE TABLE stock_item
(
sku CHAR(14) NOT NULL,
PRIMARY KEY (sku),
description TEXT,
publisher_id INT DEFAULT NULL,
pub_date DATE DEFAULT NULL,
type ENUM('OT','BK','CD') NOT NULL,
availability_id TINYINT DEFAULT NULL,
image_id INT DEFAULT NULL,
buy_price FLOAT UNSIGNED,
list_price FLOAT UNSIGNED,
sell_price FLOAT UNSIGNED,
discount TINYINT UNSIGNED,
stock_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
display ENUM('no','yes') DEFAULT 'no'
) TYPE=INNODB;
Any suggestions would be welcome.
Thanks
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X User - Start up help
Mark Depenbrock wrote: I have installed a Mac OS X 'PKG' binary package on my OS version 10.2.6 Mac. I have been instructed that after the installation I can start up MySQL by running the following command lines: [Computer:/usr/local/mysql] mark% sudo ./bin/mysqld_safe Password: Starting mysqld daemon with databases from /usr/local/mysql/data 030707 12:31:53 mysqld ended This means it ended. Check the logfiles in the data directory to see what the problem is. [Computer:/usr/local/mysql] mark% bg bg: No current job. Instructions state that I should now be able to connect to MySQL server, e.g. by running [Computer:/usr/local/mysql] mark% /usr/local/mysql/bin/mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) It appears that the server is not running...any suggestions? I have checked to see if the mysql.sock exists and it appears to be there: [Computer:/usr/local/mysql] mark% ls /tmp 501 mysql.sock printers printing.462 Thank you, Mark D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stopped working after update
You should have stopped mysql before running the update. The update might have removed the socket file, leaving the server with no way to communicate. you may try connecting through the network interface. mysqladmin -h 127.0.0.1 shutdown If that does not work, kill the running mysqld processes along with the safe_mysqld or mysqld_safe. Phil Rotsky wrote: I've just installed various updates to SuSE 8.2 via SuSE's web site. One of these was an update to MySQL to fix a security bug. Now MySQL doesn't work! During boot-up, I get the message that MySQL failed. In the log it says: starting service mysql failed [..]S13mysql start exits with status 1 When I try to start MySQL manually, it also fails. When I looked in mysqld.log it seems to think mysql is already running because port 3306 is taken. In YaST runlevel editor I looked and, sure enough, it believes mysql is up and running. However, when I try to use it I get: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' And sure enough, mysql.sock doesn't exist in that directory. In fact, I can't find it anywhere on the machine! Why would it just vanish? Before I really start screwing things up, any thoughts where I should start...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem: Slave not starting
Andrew Staples wrote: I've setup my my.cnf file on the slave as: [mysql.server] user=mysql basedir=/var/lib socket=/var/lib/mysql/mysql.sock server-id=2 master-host=206.xxx.xxx.xxx master-user=replicateuser master-password=replicatepassword Master.info is: tux-bin.001 3109 206.xxx.xxx.xxx replicateuser replicatepassword 3306 60 This indicates that replication is running. Show slave status indicates NO under Slave_running, and I get: mysql slave start; ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO You would get this message on the master. You are running this command on the slave? Server has been restarted. Version is 3.23.56 Any ideas? Andrew Instead of trying to build newer and bigger weapons of destruction, we should be thinking about getting more use out of the ones we already have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: defunct mysql threads
Does the client close the connection before exiting? Joshua Shapiro wrote: Hello, I am running the binary mysql 4.0.13 pclinux i686 with a linux 2.4.19 kernel. I have the problem that every time a client connects to the server and then exits, a defunct thread is left behind. Eventually the system prevents any further threads from being created. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start MySQL on Mac OS X
If you did not shutdown the server prior to the upgrade, you could have left mysqld running, even after its socket was removed. Use ps to see if mysqld is still running, and kill it if necessary. Todd O'Bryan wrote: Something bad has happened. MySQL was up and running on my machine, but now it's not and I have a chicken and egg problem that I can't seem to solve... I uninstalled any old versions of MySQL and I'm using the package installer of version 4.0.13 on Mac OS 10.2.6. When I cd /usr/local/ and sudo ./bin/mysqld_safe I get the following: Starting mysqld daemon with databases from /usr/local/mysql/data 030702 08:43:04 mysqld ended So, I check the log and here's what it says: 030702 08:43:04 mysqld started 030702 8:43:04 Can't start server : Bind on unix socket: Permission denied 030702 8:43:04 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 030702 8:43:04 Aborting 030702 8:43:04 /usr/local/mysql/bin/mysqld: Shutdown Complete 030702 08:43:04 mysqld ended But, I'm pretty sure nothing's running because /tmp/mysql.sock doesn't exist and when I run mysql I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Any ideas? Thanks, Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE syntax
1 will work. 2 will not work. 3 Use dbf2mysql. ( It should be in the downloads section on mysql.com ) fab wrote: Hi all, I'm quite new in mysql. Despite i've read a part of the doc, i can't fix my prob. Here is my question: I want to convert a DBASE IV file into mysql table: 1) Have i to convert the dbf into flat file then use the LOAD DATA INFILE cmd ? or 2) Can i directly use the LOAD DATA INFILE cmd with my dbf file ? I've tried the second point but it doesn't seem to work. Thanx in advance. fabrice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqlimport.
Idries Hamadi wrote: Hi all, I've just started using mysql and I'm sure that my all problems are something todo with my oracle-ness, so please bear with me if I use case-insensitive table names or somthing ;) Ok. I've been using the mysql interactive command-line interface for a few days now, and there's no problem there. I've made myself a ~/.my.cnf file and it appears to work: [client] user=idries_wedding password=** Since creating it I no longer need to enter username or password details when I run mysql :) Now, I'm trying to use mysqlimport: How about [mysqlimport] user=idries_esdding password=** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is mysql.soc and where is it located
Check the error log in your mysql data directory. This should explain why mysqld ended. suboh wrote: Hi, The following message appeared when I installed mysql server on Linux,,, [2] 10804 [1] Exit 1 ./bin/mysqld -user-mysql [EMAIL PROTECTED] mysql]$ Starting mysqld daemon with databases from /var/lib/mysql 030701 19:59:18 mysqld ended And also sometime the message can't find mysql.sock in /var/lib/mysql/mysql.sock despite that the installation directory and the daemon is in /usr/local/mysql/bin I performed standard installation and also this message appeared if I install the MySql server as part of Redhat installed. Thanks Suboh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access Denied for User: root@127.0.0.1(Using password: NO)
For Mysql, 127.0.0.1 is not the same as localhost. Localhost refers to the socket file on the operating system, and is faster than 127.0.0.1. Use 'root'@'127.0.0.1' in your grant statements. Ola Ogunneye wrote: Please somebody help me. I have installed MySql 4.0.13 and it works up until I try to apply security settings to the root user at localhost. I see the mysql database and I check the privileges and there is no password for the roo user. But when I assign a password to make it secure, I can run phpmyadmin, but I cannot get into the database anymore. All I get is: MySql Said: Access Denied for user: [EMAIL PROTECTED] (Using password: NO) This situation has happened 3 times and I had to reinstall MySql and start from scratch. Can someone please point me in the right direction, this is my first try and it has been quite a serious learning experience. Thank you in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by clause
That can't be the output of the query. The fourth column would me titled 'soma' There is no seperator bar between the data for sw_or_local and soma on the data lines. Fabio Bernardo wrote: hi there I wrote this querie : select pop, prot, sw_or_local, sum(qtd_porta) as soma from clientes where status'C' group by pop,prot,sw_or_local And I have this result : +++-++ | pop| prot | sw_or_local | sum | +++-++ |BHE | R2 | local50 | |BHE | R2 | local 12 | +++-++ . . . . . Have you see,? I would like something like that: | pop| prot | sw_or_local | sum | +++-++ |BHE | R2 | local62 Is my group by querie wrong...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practices for mySQL Backups in Enterprise
Yes, if you have transaction logging turned on. You can edit the transaction log, and run it against the restored database. Subhakar Burri wrote: Can I roll forward if I do backups using Mysqldump? Say, I did backups using Mysqldump @ 8:00 AM and my instance crashed @ 2:00 PM. I can restore the tables (both Innodb and MyISAM tables) from my 8:00AM backup, but can I roll forward the data that changed after 8:00 AM or do I lose the data after 8:00 AM. Pls clarify... a link to relevant information would be just fine too... Thankx in advance SB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query time
Well, without seeing your table structures and key definitions, how could we tell? You are selecting on code=87901 , but there is no indication here about what tbale that column belongs to. Did you run analyze on your tables to update key distribution information so the server could make a better choice? Charles Vos wrote: Thanks to everyone for their help, I actually managed to fix the problem by bypassing MySQL's optimization using STRAIGHT_JOIN. Out of curiosity can anyone tell me why MySQL failed so miserably at optimizing my query? (The two hour long query took 10s with Straight_join). mysql explain select [cols] - from y02m07_acode_table, y02m07_acom_table, y02m07_pats_table - where - code=87901 - AND y02m07_pats_table.pat_id=y02m07_acom_table.pat_id - AND y02m07_acom_table.h_id=y02m07_acode_table.h_id - AND y02m07_acom_table.c_id=y02m07_acode_table.ce_id; ++--+-++-+--+--+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | ++--+-++-+--+--+-+ | y02m07_acom_table | ALL | c,p,h | NULL |NULL | NULL | 46893187 | | | y02m07_acode_table | ref | c | c | 4 | y02m07_acom_table.charge_id |1 | Using where | | y02m07_pats_table | ref | p_id| p_id | 9 | y02m07_acom_table.patient_id |1 | Using where | ++--+-++-+--+--+-+ 3 rows in set (0.01 sec) But when I add Straight Join: ++--+-++-+--++-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | ++--+-++-+--++-+ | y02m07_acode_table | ALL | c | NULL |NULL | NULL | 736010 | Using where | | y02m07_acom_table | ref | c,p,h | c | 4 | y02m07_acode_table.charge_id | 90 | Using where | | y02m07_pats_table | ref | p_id| p_id | 9 | y02m07_acom_table.patient_id | 1 | Using where | ++--+-++-+--++-+ 3 rows in set (0.00 sec) Thanks for whatever insight you can give... -Charlie -Original Message- From: Knepley, Jim [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Sent: Friday, June 27, 2003 10:33 AM To: [EMAIL PROTECTED] Subject: RE: very long query time I have had similar performance concerns, but on a much smaller scale. The data was well indexed, but took far too long to query (particularly with aggregate queries). Check the individual row size of your table. In my case, I had a TEXT field that would frequently be fairly long. Moving that field to another table and indexing back resulted in a massive performance improvement. A query that would take minutes now takes less than a second. I figured it was a question of IO latency, and moved on. J -Original Message- From: Maurice Coyle [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Sent: Friday, June 27, 2003 3:35 AM To: [EMAIL PROTECTED] Subject: very long query time hi all, i have a table in my mysql database with around 66 million rows in it. when i query this table, it takes anywhere from 3 minutes to 10 minutes to return the results. i've tried this both from within the mysql command line and from java programs. Section 1.2.4 in the manual says the maximum table size is 4Gb and when i use the show status command for this table, it says the data_length is 1,585,947,820 and the max_data_length is 4,294,967,295, so the table size seems to be well within the limit. The results for a query to this table can contain up to 11500 hits, so maybe this is the problem? If there's no fix for this, does anyone know how i can query for only the top 100 results, say? i can't see what's wrong, can anyone shed some light on this problem/offer the benefit of your experience in similar matters? i'd really appreciate it if you could. thanks, maurice http://www.incredimail.com/redir.asp?ad_id=309lang=9 http://www.incredimail.com/redir.asp?ad_id=309lang=9 IncrediMail - Email has finally evolved - Click Here http://www.incredimail.com/redir.asp?ad_id=309lang=9
Re: error message during connection
'localhost 127.0.0.1' is not a valid host. Use 'localhost' if you want to use the local socket. Use '127.0.0.1' if you want to use the TCP port. Gantier wrote: Hello, I work with apache, tomcat and linux redhat 7.3 and mysql 4.0.9 When I try to connect to my database from my application with the user XXX with password YYY, the next message appears : INVALID AUTHORIZATION SPECIFICATION : ACCESS DENIED FOR USER '[EMAIL PROTECTED] 127.0.0.1' USING PASSWORD(YES) But I made GRANT ALL ON *.* TO XXX@'%' IDENTIFIED BY 'YYY'; and mysqladmin flush-privileges Please, help me Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Occasional access errors
Did you try increasing max_connections ? Michael Edlund wrote: I've developed a CMS using PHP and MySQL 4.1 alpha. It ran just fine with the standard MySQL 4.1a binaries both on my Powerbook running Mac OS X as well in a production environment on my FreeBSD server. However, after having installed everything on a clean Debian Linux system, there are some occasional glitches. Approximately once in every 100th request from the PHP code, MySQL responds with a... Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) ...error message. It seems very strange to me that this error message occurs so very seldom and it makes no sense to me. Also, it's difficult to figure out what's causing it since it's difficult to provoke it. Does anyone share this experience? /M Michael Edlund - Journalist and Content Management Consultant http://www.edlund.se - http://www.helloworld.se Unix - PHP - MySQL - PostgreSQL - XML - QuickTime VR Authoring -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practices for mySQL Backups in Enterprise
Ok, update log. Jeremy Zawodny wrote: On Fri, Jun 27, 2003 at 08:08:40AM -0500, gerald_clark wrote: Yes, if you have transaction logging turned on. You can edit the transaction log, and run it against the restored database. MyISAM doesn't have transactions. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select * from multiple tables
[EMAIL PROTECTED] wrote: Ok, I trying to get this example... what is table1 t1, table2 t2, table3 t3, table4 t4, I mean, what does the t1, t2, t3, t4 represent? If you say, table 1, table 2, etc well, I assume that, but isn't that there already? Let me, or may I, give ask again with my visual? Here are my tables and keys: table1: person_IDprimary key) lastName firstName table2: machine_ID primary key)person_ID model_ID location OS table3: model_ID primary key)make_ID model table4: make_ID primary Key)make I tried various: SELECT lastName, location, model, make FROM table1, table2, table3, table4 WHERE table2.person_ID = table1.person_ID AND table3.make_ID = table4.make_ID; You are missing a relationship SELECT lastName, location, model, make FROM table1, table2, table3, table4 WHERE table2.person_ID = table1.person_ID AND table3.make_ID = table4.make_ID AND table2.model_ID=table3.model_ID; The relationships are: table1 one-to-many table2 table2 many-to-one table3 table3 many-to-one table4 (help?) Thanks, you all are GREAT! (I googled 4-way JOIN... not a lot of joy -for me anyway.) Ted On Thursday, June 26, 2003, at 02:00 AM, Venkata Srinivasa Rao, Yerra wrote: SELECT t1.key,t2.col,t3.*,t4.col2 FROM table1 t1, table2 t2, table3 t3, table4 t4 WHERE t1.key=t2.key AND t1.key=t3.key AND t1.key=t4.key At 01:23 AM 6/26/2003 -0400, you wrote: I grown my db to 4 tables 8). I'm going to ask this plainly in hopes that my syntax in ok: I know how to SELECT * from 2 related tables and get all the records listed in the resultset. (Either using INNER JOIN or WHERE.) Now... and I have been looking some books! How do get a resultset of all records from 4 related tables? Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_thread_init()
Check the client sources. The mysql client is a great example for the c API. Sean Macmillan wrote: Please bear with me here as I am new to this whole MySQL thing. Does anyone know where I can find a good example of how the C API function call mysql_thread_init() is implemented? Any help would be appreciated. Thanks in advance. Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
mysqlhotcopy does your locking for you. SAQIB wrote: We are in the process of implementing enterprise wide (20,000+ users) application that will use mySQL as the Database engine. I was wondering if the slashdot readers can provide me some details about best practices / experiences for Backing Up and Restoring mySQL Databases. I am planning to setup a cron job, to lock the tables, use msqlhotcopy and then unlock the tables. Is that a good backup strategy? Is there a ready-made perl script that I can use? Is there a commercial solution for Backups? What other things do I need to keep in mind? What do other people do in production use, where the DB is frequently? I have already read mySQL: The definitive guide to to using, programming and administring mySQL 4, but did not find ay good information about backing up and restoring, in the 24x7 operation for Data Center use. Any ideas will highly appreciated. Thanks Saqib Ali -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full disk: ERROR 1016: Can't open file
perror 145 Vikas Gupta wrote: Hello all, My /var partition has become full, and now when I try load my db (the files are in /var/lib/mysql/db/) I get the following message for the table my_table: use db; Didn't find any fields in table 'my_table' I then do describe my_table and I get: ERROR 1016: Can't open file: 'my_table.MYD'. (errno: 145). I freed up some space under /var but I still recieve the above messages. Does this mean that my table has been corrupted? Is there any way to recover this table? Thanks in advance, Vikas _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems - I don't understand
Why would you expect they should have the same results? The second query contains a join and may have many times more rows in the result set. PaT! wrote: Dear All, I have these two queries which are supposed to return the same results for the fields with the same name (ex query 1 sum1 = query 2 sum1): The first query gives me back the correct result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev FROMordini WHERE ordini.numordine = 2302 GROUP BYordini.numordine; ++-+--++--+ | date1 | sum1| sum2 | cod| fromprev | ++-+--++--+ | 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | ++-+--++--+ this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') date2 FROM ordini, carello WHEREordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; ++--+--+-+--+-+- ---+ | date1 | sum1 | sum2 | cod | fromprev | numprev | date2 | ++--+--+-+--+-+- ---+ | 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | 28-05-2003 | ++--+--+-+--+-+- ---+ Help is required. I run Mysql 4.0.13 Thanks Patrizio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
It is a perl program, so you can look at it and see. SAQIB wrote: mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? --- Saqib Ali http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help) -(I got it!)
In the second query, the server may switch the order of the join, producing the same output in different order. Try adding an ORDER BY to both and see what happens. [EMAIL PROTECTED] wrote: Ok, should be this: SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Ted This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Can someone please tell why, what's wrong? (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem setting/activating password
To also add priviliges on the local machine GRANT ALL ON *.* TO username@localhost IDENTIFIED BY password Riaan Oberholzer wrote: Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
And what would that be? Phil Dowson wrote: Hi, I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Newbie: MySQL stops as soon as it starts
What does the error log say? David Shealy wrote: I'm a newbie to MySQL. I'm trying to run it on Red Hat 9. After running mysql_install_db, I switch to the directory where mysql.sock is located and enter safe_mysqld . Here's a copy of the shell output: # safe_mysqld [1] 7549 # Starting mysqld daemon with databases from /var/lib/mysql 030626 02:02:59 mysqld ended The cursor is flashing down on this line, and if I type anything and enter it kicks me back to the prompt. If I do a ps, it doesn't show anything about mysql even running. It appears to run for a split second and then end all by itself. I'm still trying to read all the man page stuff; would appreciate any help. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld question
Linux shows threads. BSD shows processes. The difference is in the way ps runs, not mysqld. Henrik Leghissa wrote: On my server running linux several daemons opens when I run msqld -u mysql or safe_mysqld. A listing of the process shows this: mysql24475 0.5 2.1 83292 2012 ?S15:46 0:03 mysqld -u mysql mysql24476 0.0 2.1 83292 2012 ?S15:46 0:00 mysqld -u mysql mysql24477 0.0 2.1 83292 2012 ?S15:46 0:00 mysqld -u mysql mysql24478 0.0 2.1 83292 2012 ?S15:46 0:00 mysqld -u mysql mysql24479 0.0 2.1 83292 2012 ?S15:46 0:00 mysqld -u mysql mysql24480 0.0 2.1 83292 2012 ?S15:46 0:00 mysqld -u mysql mysql24481 0.0 2.1 83292 2012 ?S15:47 0:00 mysqld -u mysql mysql24482 0.0 2.1 83292 2012 ?S15:47 0:00 mysqld -u mysql mysql24483 0.0 2.1 83292 2012 ?S15:47 0:00 mysqld -u mysql mysql24484 0.0 2.1 83292 2012 ?S15:47 0:00 mysqld -u mysql A friend has a mysqld running on his *bsd-server with this single daemon listed when listing the processes: mysql5602 0.0 1.2 44592 4968 q4 S15Jun03 29:30.21 /usr/local/libexec/mysqld -u mysql Can you please point out wgat I should do to make something similar? (I suspect that these multiple daemons slow down the performance on the server I run) Thank you. / H -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Textfile to a 2 column mysql database
Write a quick perl program. O.S. Bos wrote: Hi there, I have a textfile that I want to get inserted into a database. The textfile consists of Questions and Answers. 1st line is a question. 2nd line of the textfile is the answer. And so on... What is the best way to import these lines into the database with 2 columns. 1 column for the questions and one for the answers? Thx! Unox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Textfile to a 2 column mysql database
Unless, of course, you have quotes in your data. With perl you can use the quote() function to ensure the whole line gets in. Christopher Knight wrote: OR, if you are good at vi, you can insert a ' at the begining and end of every line (if you dont have any 's in the file) then put a , at the end of every odd line then join every other line the put a insert into blah (question, answer) values (at the begin of every line and then a ); at the end of every line Then you hopefully have a file full of insert statements and you can just feed it into a mysql client. If I missed a step or added one by accident or even got one wrong, go ahead and fix it and pretend I told you correctly. ;-) The problem with my solution, is that if you have any wierd characters, they arent escaped. or you could just write a perl program chris -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:21 AM To: O.S. Bos Cc: [EMAIL PROTECTED] Subject: Re: Textfile to a 2 column mysql database Write a quick perl program. O.S. Bos wrote: Hi there, I have a textfile that I want to get inserted into a database. The textfile consists of Questions and Answers. 1st line is a question. 2nd line of the textfile is the answer. And so on... What is the best way to import these lines into the database with 2 columns. 1 column for the questions and one for the answers? Thx! Unox -- 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: mysql ended error
Did you run myisamchk on user.MYI as I suggested last week? (B (BPushpinder Singh Garcha wrote: (B (B Thanks Nils, (B (B I had been having an terrible experience starting up MySQL. I used to (B get this error Can't connect to local MySQL server through socket (B '/tmp/mysql.sock' (2) (B (B I have tried the following: (B (B 1. Manually start the server using sudo ./bin/mysqld_safe (B (B the result was : (B (B [psg:/usr/local/mysql] psgarcha% Starting mysqld daemon with databases (B from /usr/local/mysql/data (B 030623 10:12:40 mysqld ended (B [1] Done sudo ./bin/mysqld_safe (B [psg:/usr/local/mysql] psgarcha% (B (B (B - (B (B 2. Next I tried to reinitialize the MySQL using the intructions given (B on www.entropy.ch , but even that did not help. (B (B My error log file has this in it : (B (B 030623 10:10:48 mysqld started (B 030623 10:10:50 InnoDB: Started (B 030623 10:10:50 Fatal error: Can't open privilege tables: Can't open (B file: 'user.MYI'. (errno: 145) (B 030623 10:10:50 Aborting (B (B 030623 10:10:50 InnoDB: Starting shutdown... (B 030623 10:10:52 InnoDB: Shutdown completed 030623 10:10:52 (B /usr/local/mysql-standard-4.0.13-apple-darwin6.4-powerpc/bin/mysqld: (B Shutdown Complete (B (B 030623 10:10:52 mysqld ended (B (B - (B (B 3. At last I tried to REINSTALL MySQL from the scratch. I used the (B "update" instructions. But there is still NO CHANGE. I get the same (B error. (B 03[psg:/usr/local/mysql] psgarcha% 030623 10:12:40 mysqld ended (B (B (B - (B (B (B Please help me.. I am going crazy !! (B (B (B Thank you again. (B --Pushpinder (B (B (B On Friday, June 20, 2003, at 08:55 PM, Nils Valentin wrote: (B (B Somehow the Index of the mysql.user table is crashed. (B (B mysql is a database within the MYSQL RDBMS that stores the user (B accounts, (B passwords, user rights (privileges). (B (B And somehow you managed to mess up the most important table. (B (B There are 6 tables within the privilege system (mysql database) (B (B user (B host (B db (B tables_priv (B columns_priv (B func (B (B The table user contains the username, hostname and the password etc... (B (B If any of these tables ( but especially the user table) are messed up (B than (B MySQL doesnt startup and even if it would you could not login as the (B user (B table is unreadable at present. (B (B The quick way is to reinstall MySQL. You could try to copy the whole (B folder (B were the privilege database "mysql" is stored from another machine (B (if you (B have one). (B (B Best regards (B (B Nils Valentin (B Tokyo/Japan (B (B (B 2003$BG/(B 6$B7n(B 21$BF|(B $BEZMKF|(B 04:25$B!"(BPushpinder Singh Garcha (B $B$5$s$O=q$-$^$7$?(B: (B (B hello all, (B (B i hve been getting the 'mysql ended' error for over a wek now.when i (B look into the log file this is what I get (B (B This is from the error logs (B (B 030619 17:09:04 mysqld ended (B (B 030620 13:14:06 mysqld started (B 030620 13:14:09 InnoDB: Started (B 030620 13:14:09 Fatal error: Can't open privilege tables: Can't open (B file: 'user.MYI'. (errno: 145) (B 030620 13:14:09 Aborting (B (B 030620 13:14:09 InnoDB: Starting shutdown... (B 030620 13:14:11 InnoDB: Shutdown completed (B 030620 13:14:11 (B /usr/local/mysql-standard-4.0.12-apple-darwin6.4-powerpc/bin/mysqld: (B Shutdown Complete (B (B 030620 13:14:11 mysqld ended (B (B (B Please offer some insight into what migh tbe going on. I have tried to (B re-initialize the DB , but Nothing chnaged! (B (B Thanks (B -Pushpinder (B (B (B -- (B --- (B Valentin Nils (B Internet Technology (B (B E-Mail: [EMAIL PROTECTED] (B URL: http://www.knowd.co.jp (B Personal URL: http://www.knowd.co.jp/staff/nils (B (B (B (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query - 3 tables - 3rd one conatins records to not display
And what happens if you leave off the 'distinct' ? vernon wrote: OK so now I have: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID, penpals_privmsgs_block.user_id, penpals_privmsgs_block.blocked_id FROM useronline, penpals_fav LEFT JOIN penpals_privmsgs_block ON penpals_fav.user_id = penpals_privmsgs_block.user_id WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = colname which comes back with a 1 in every record again and of course when I add the AND penpals_privmsgs_block.blocked_id IS NULL it comes up empty as every row has a 1 in it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql ended error
Pushpinder Singh Garcha wrote: hello all, i hve been getting the 'mysql ended' error for over a wek now.when i look into the log file this is what I get This is from the error logs 030619 17:09:04 mysqld ended 030620 13:14:06 mysqld started 030620 13:14:09 InnoDB: Started 030620 13:14:09 Fatal error: Can't open privilege tables: Can't open file: 'user.MYI'. (errno: 145) perror 145 Error code 145: Unknown error 145 145 = Table was marked as crashed and should be repaired. go to the mysql/mysql directory and run myisamchk on user.MYI. 030620 13:14:09 Aborting 030620 13:14:09 InnoDB: Starting shutdown... 030620 13:14:11 InnoDB: Shutdown completed 030620 13:14:11 /usr/local/mysql-standard-4.0.12-apple-darwin6.4-powerpc/bin/mysqld: Shutdown Complete 030620 13:14:11 mysqld ended Please offer some insight into what migh tbe going on. I have tried to re-initialize the DB , but Nothing chnaged! Thanks -Pushpinder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd thing
Christensen, Dave wrote: Oops! Sorry. Deleted that 'temp' table and didn't remove it from my script file. -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:34 AM To: 'Jay Blanchard'; Dickey, Dallas; '[EMAIL PROTECTED]' Subject: RE: Odd thing Importance: High When I run this script file, I receive errors like this: [EMAIL PROTECTED] work]# ./BackupByTable.bat mysqldump: Got error: 1146: Table 'AgentAdminDB.MissedPages' doesn't exist when doing LOCK TABLES -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 10:58 AM To: 'Jay Blanchard'; Dickey, Dallas; Christensen, Dave; '[EMAIL PROTECTED]' Subject: RE: Odd thing I've also tried creating the script manually (script follows) and all that's being created is a text header with nothing else in the file. At least in these instances we can use the text files. Just be nice if they were valid... :-) --- Here's the script mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB MissedPages -uroot -ppano4577 MissedPages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_APP_INFO -uroot -ppano4577 UCS_APP_INFO.sql You have options after the tables. All options should precede the database and tables. mysqldump --add-drop-table --compatible-mysql323 --disable-keys -uroot -ppano4577 AgentAdminDB MissedPages MissedPages.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: suggestions - server options/mysql variables
We don't know anything about you structures, your data, or your indicies. You should show them along with the output of EXPLAIN on your query. Derick Smith wrote: Hi! All the select statements have indexes on appropriate fields. Mysql appears to run fast, I was just wondering if there was anyway to make it faster. It is currently running on Windows, I have also ported it to HP-UX 11. The queries are of the type : SELECT * FROM textl WHERE parsetext = and filter = GROUP BY code. I think the query code is optimized, I was wondering is there were any settings I could change in mysql to speed it up? or any other tricks to increase speed? I tried setting set-variable = key_buffer_size=64M, but that did not appear to make a big difference. Thanks Eric From: Mike Hillyer [EMAIL PROTECTED] To: Derick Smith [EMAIL PROTECTED], [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: RE: suggestions - server options/mysql variables Date: Thu, 19 Jun 2003 09:13:33 -0600 If you have queries that are only selects, in small tables, with few rows, you should have very fast performance as is. Are your queries properly indexed? What hardware are you running on? What do these queries look like? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Derick Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:08 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: suggestions - server options/mysql variables Hi! Does anyone know any mysql options I can change in the my.ini file or mysql variables to increase the speed of select statements? About database: -uses only select statements for queries -no transactions -if database becomes corrupt in anyway, not a big deal I can recreate it -it is a small database, I do not need recovery, raid or any other backup mechanism -queries generally return very little data I will test any suggestions people have for me. Thanks Eric _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- 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] _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API Query Semantics
Look at the source for the mysql client. It is a perfect example. Sean Macmillan wrote: Using the C API I have written a program that calls mysql_query() on a table with values I know to be in the table. It returns fine and I then call mysql_store_result. The problem I am having is figuring out how to dump the contents of that query to the screen (printf for example). I have tried mysql_fetch_rows and a few others. What am trying to do is see the data on the terminal once the c program executes. Any suggestions? Sean Mac Millan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlbug
Joanne Yow [bizfront] wrote: Hi, I am trying to install MySQL 3.23 to Solaris 2.8. After the step - scripts/mysql_install_db I try the command - chown -R root, error show usage: chown [-fhR] owner[:group] file... You entered the command incorrectly. You gave it no files or directories to change. Why would you change the ownership to root? and the command - chrgrp -R mysql, error show chgrp [-fhR] group file... Do i need to groupadd for the root? Same problems here. What problems will cause by this? Thanks in advance. Regards, Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'mysqld ended' Error
Pushpinder Singh Garcha wrote: Hello everyone, I am running MySQl and PHP on a Mac OS Jaguar. When I try to restart my MySQL Local Server using mysqld_safe, but I keep getting this error [psg:/usr/local/mysql] psgarcha% sudo echo Password: [psg:/usr/local/mysql] psgarcha% sudo ./bin/mysqld_safe [4] 524 [psg:/usr/local/mysql] psgarcha% Starting mysqld daemon with databases from /usr/local/mysql/data 030618 10:23:41 mysqld ended After that I have tried to look up the error logs on my Mac under /usr/local/mysql/data but I get a 'permission denied error' This is likely your problem. /usr/local/mysql, all its files and subdirectories should be owned by mysql. I suspect ownership and privileges have been changed so that neither you nor mysql can read them. [psg:/usr/local] psgarcha% ls mysqlmysql.bak mysql-standard-4.0.12-apple-darwin6.4-powerpc [psg:/usr/local] psgarcha% cd mysql [psg:/usr/local/mysql] psgarcha% ls COPYING README include manual.txt share COPYING.LIB bin lib manual_toc.html sql-bench ChangeLogconfigureman mysql-test support-files INSTALL-BINARY data manual.html scripts tests [psg:/usr/local/mysql] psgarcha% cd data data: Permission denied. [psg:/usr/local/mysql] psgarcha% I have no clue why this is happening...everything was working fien...untill recently something snapped and the MySQL DB would not start up at all. TIA --Pushpinder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with SELECT
Muazzam Siddiqui wrote: Hi, I am getting the error You have an error in your SQL syntax near 'select max(news_id) from news)' at line 1 while trying to run this query. SELECT * FROM News where News_ID = (SELECT MAX(News_ID) FROM News); You don't say what version of MySQL you are running. You probably do not have sub-selects. Consult your manual. Try : SELECT * FROM News ORDER BY News_ID DESC LIMIT 1; The table type is INNODB. Is it some MySQL related problem because I know the query is right. I tested it on Access. Thanks Muazzam Siddiqui. _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld problem
Pick/Create a different directory. Make it owned by mysql:mysql, and set it mode 775. Mysqld can not write to /usr/local/bin. Mark Colvin wrote: I am having difficulty stopping and starting my mysql server. The mysqld is currently running as all of the client machines can access the database. If I try to invoke the mysql command from the server box (linux 7.2) I get the following - error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! The mysql.sock does not exists int /tmp directory and I am attempting to restart the server to create this. When I run mysqld restart it fails to stop and start. I think the server was last started with the safe_mysqld command. I use the /tmp directory to hold files created by cron jobs and this directory is periodically cleared out. This is obviously a problem as the mysql.sock currently needs to be in here. I have since changed the my.cnf file to read - [mysqld] datadir=/var/lib/mysql socket=/usr/local/bin/mysql.sock thus changing the directory for mysql.sock to resolve the above issue but I can't restart the server to get the mysql.sock recreated. Any ideas? This e-mail is intended for the recipient only and may contain confidential information. If you are not the intended recipient then you should reply to the sender and take no further ation based upon the content of the message. Internet e-mails are not necessarily secure and CCM Limited does not accept any responsibility for changes made to this message. Although checks have been made to ensure this message and any attchments are free from viruses the recipient should ensure that this is the case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create a table with an image item
Since you are providing the web page, it depends on how you write the web page. This really isn't a MySQL question, is it? v7rg8 wrote: Hi all, My table is like this: professor(name, gender, bodyImage) I am not sure if this is correct to set up the table: create table professor (name char(10), gender char(10), bodyImage blob); If it is correct, I was wondering how users are going to load images into the database from the webpage we provide to them. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Open Tables
John Farkas wrote: I am new to mysql so please excuse my ignorance. I am running mysql on windows 2000 and have noticed that when a table is opened to do an insert or update it is not closed after the query is finished but is left open. Then when I shut down the mysql server and do a myisamchk on the tables they show up as damaged with the following message: 1 clients is still using or has not properly closed this table Is this a bug? Is there something I can do to fix the problem? Close the connection before quitting the application. thanx in advance for any help, John Farkas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remote access to MySQL
Shane Bryldt wrote:
Just a thought, what about encryption of passwords? Shouldn't it be:
GRANT ALL ON database TO [EMAIL PROTECTED] IDENTIFIED BY
PASSWORD('password');
No, it should not.
Not sure if that is related to the problem, but it may cause an invalid
password error.
However, your problem is most likely similar to the problem I had. I don't
know about it working on the intranet for you but by default the windows
version does not have TCP connections enabled, only named pipes (localhost
connections). It took me a few minutes to realize this the last time I had
it installed on windows. Check your configuration, as I recall it was
something commented out, perhaps the port number if I recall correctly.
-Shane
- Original Message -
From: Renato [EMAIL PROTECTED]
To: Becoming Digital [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, June 17, 2003 12:11 PM
Subject: RES: Remote access to MySQL
I have all privileges to all the databases and tables, with user
'root'.
User 'root' likely doesn't have access from the desired IP.
Users are configured by both name and approved hosts. Try this:
GRANT ALL ON database TO [EMAIL PROTECTED] IDENTIFIED BY 'password';
Edward Dudlik
Becoming Digital
www.becomingdigital.com
I have all grant statements:
GRANT ALL PRIVILEGES ON *.* to root@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
Still not working...
Do you know if Win98 have some trouble receiving telnet commands?
Thanks again.
Regards,
Renato Uchoa
[EMAIL PROTECTED]
- Original Message -
From: Renato [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, 15 June, 2003 19:28
Subject: Remote access to MySQL
I have installed MySQL in Win98SE, with connection ASDL and Fixed IP.
In the same machine, works web server (Apache2+PHP) and e-mail e ftp
server.
Everything works perfectly accessing from my INTRANET (local network). I
obtain access to MySQL without problems.
In Internet, all servers works perfect, except MySQL. Always I get error
of
access when use:
mysql - h xx.xx.xx.xx - u root - p
ERROR 2003: Can't connect you the MySQL server on 'xx.xx.xx.xx' (10060)
Informations:
* I don't have any firewall
* I have all privileges to all the databases and tables, with user 'root'.
* I have pleny access to the 'server' machine.
* Telnet 'xx.xx.xx.xx:3306 ' also doesn't answer on Internet - In local
network, the reply is positive, what confirms server is on
* I work with PHP and I have access to Mysql with PHP programming, never
directly.
* I use 'mysqld.exe', as MySQL server, without any special configuration
in
my.ini:
[WinMySQLAdmin]
Server=C:/MYSQL/bin/mysqld.exe
[mysqld]
basedir=C:/MYSQL
datadir=C:/MYSQL/data
Summarizing: I can't access to the Mysql from Internet!
Some additional suggestions?
Regards,
Renato Uchoa
[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: Copy database to another Server
William IT wrote: I do mysqlamin shutdown and copy all /var/lib/mysql file from another MySQL server. And then run: chmod -R 660 * Try 770 Mysql needs to be able to scan the directories to find tables. chown -R mysql:mysql * shutdown now -r But I can't logon to Mysql. What's wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Image content via command line
Titu Kim wrote: Hi, I am trying to insert an image directly from command line into mysql table. My table looks like == Create table ImageTab (id int, content longblob); === I login to localhost. I have an image at /tmp. Then i issue this query insert into ImageTab values(1, load_file(/tmp/myimg.gif)); == No error. But when i check the content of the table, the content column is null. I login as root to do this. What have i done wrong? Please help. Thanks. The file must be on the server, and readable by the user 'mysql' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile Without Inserting New Rows
You will have to write a program to read your text files and insert/update the appropriate records. Fernando Gerent wrote: Hi everybody, I need to use the Load Data Infile to insert different columns from different files in a table, but I can't figure out how to keep the number of rows, like for example, if I have a table for people's names, ages and phone numbers, and each of these information is in a different txt file. When I try to put them all together, I get like, if there are 100 people, a 300 row table (100 first rows only their names, 101 to 200 their ages and 201 to 300 their phone numbers). How can I fix this? Thanks in advance Fernando -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Image content via command line
How big is the imge file? How do you check the contents of the table? How are you determining that it is null? Did you try to select the column into an outfile? If so, how big was the outfile? Titu Kim wrote: Yes, the image file is on the server and i already chown and chrgp to mysql for this file. Still the data is null in the table. --- gerald_clark [EMAIL PROTECTED] wrote: Titu Kim wrote: Hi, I am trying to insert an image directly from command line into mysql table. My table looks like == Create table ImageTab (id int, content longblob); === I login to localhost. I have an image at /tmp. Then i issue this query insert into ImageTab values(1, load_file(/tmp/myimg.gif)); == No error. But when i check the content of the table, the content column is null. I login as root to do this. What have i done wrong? Please help. Thanks. The file must be on the server, and readable by the user 'mysql' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get meta data info in MySQL
My database editor uses: describe name; Karen Chu wrote: I want to be able to get all table names in a database and all the column names and associated data type from a particular table. I wonder how to do that in MySQl. I understand I can use show tables to see the table info, but I want to be able to do 'select' in order to get the results back. I want to know if there is something like select name from sysobject in Sybase or select name from all_tables in Oracle. Thanks. I would appreciate any advice. Karen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux install problem
Steve Mansfield wrote: Having run MySQL successfully for a while in a WAMP environment, I'm trying to set up on Linux now (and am a real Linux newbie). I'm using the 4.3.1 version that came as an RPM with SuSE 8.2. The problem is - it doesn't work. Here's where the various elements have installed to: /usr/bin various mysql progs scripts, incl mysql mysqladmin mysql_install_db safe_mysqld etc /usr/sbin mysqld /var/lib/mysql mysql.sock mysql.log linux-bin.001 etc when I run mysql_install_db (in /usr/bin), it creates the 'mysql' dir in var/lib/mysql - so the path to the default database files becomes: /var/lib/mysql/mysql/ I'll bet you didn't run mysql_install_db with the --user=mysql option. chown -R mysql.mysql /var/lib/mysql should do the trick. From /usr/bin, I run: safe_mysqld --user=mysql and get the message Starting mysqld daemon with databases from /var/lib/mysql but then the daemon shuts down straight away. When I look in the log file, I find: /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) This is because mysqld can't access /var/lib/mysql/mysql. perror 13 will show you that it is a permissions problem. It looks as though the mysql_install_db has put the files in the wrong place. No, just installed them owned by the user running the script. I am assuming 'root'. Can anyone give me some advice as to how I sort this out...? Thanks. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: money field
I use Decimal, because I like my money calculations to be correct. Mojtaba Faridzad wrote: Hi, Which type do you usually use to keep money values? Decimal (12,2) or Float? to keep the first one, system takes 12 bytes but for the second one, 8 bytes. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select without using sub-select?
[EMAIL PROTECTED] wrote: Hi, I am running MySQL 4.0.10 gamma. I have records with data: id title version 1a 1 2a 2 3b 1 4b 2 How do I select all the records with highest version for each title? (The records with id 2 and 4) I've been thinking about combinating GROUP BY and max(), but I cant get it to work... SELECT id, title, max(version) FROM table GROUP BY title; returns 1 a 2 3 b 2 Thanks for any help /Ola Because this is not proper SQL. 'id' is in neither the MAX aggragate function nor the GROUP BY. You told the server you want only 1 line per title with GROUP BY title, but the server has no way of knowing which line you want. MySQL allows this syntax where other servers don't, because sometimes it is useful. You have to be careful though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you create ascii line breaks
Daniel Crompton wrote: How do you create ascii line breaks in a text paragraph in mysql?. When displaying text taken from a mysql row it displays it all as a flowing paragraph ie sdf sdfgfj fgjhgfh fgj fgj hfj hj hg gh hgk ghk gj gh kg k hg hg jh j fgj fg When i need to look like sdf sdfgfj fgjhgfh fgj fgj hfj hj hg gh hgk ghk gj gh kg k hg hg jh j fgj fg I am dispalying it in a webbrowser using the below code which will convert ascii line breaks into HTML br tags. but i am unsure how to put in the ascii line breaks into mysql. ?php echo nl2br($row_myRecordset['myField']); ? The string you are storing in the text field must have the line feeds in it before it is quoted and saved. How they get there depends on the language you are using to generate the data. This is not a MySQL question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
