Re: compilation problems with 4.1.10a on Tru64 5.1
Hi! Am Di, den 15.03.2005 schrieb Heikki Tuuri um 8:21: Douglas, - Original Message - From: Douglas B. Jones [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 14, 2005 11:19 PM Subject: compilation problems with 4.1.10a on Tru64 5.1 [[...]] It failed the compilation with this (I tried to put line breaks in it): gmake gen_lex_hash [[...]] creating gen_lex_hash gmake[5]: Leaving directory `/src/net/db/mysql/mysql-4.1.10a/sql' ./gen_lex_hash lex_hash.h gmake[4]: *** [lex_hash.h] Error 1 [[...]] The file, MYSQLDIR/sql/lex_hash.h is zero bytes. Any idea where I should go from here? Thanks! try deleting that zero-byte lex_hash.h. Right, try that. It seems there is a race condition somewhere deep in the Makefiles that (on rare occasions) causes these problems with gen_lex_hash. As an alternative, you could block the parallel make jobs and force serial execution to avoid these. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
QUOTE() function, what happens here?
Dear list,
I don't understand what happens here:
shell mysql -N -e SHOW VARIABLES LIKE 'version'; test
+-++
| version | 4.0.14-log |
+-++
shell mysql -e CREATE TABLE foo (sometext VARCHAR(255)); test
shell mysql -e INSERT INTO foo VALUES('Pitt\\'s Place'); test
shell mysql -N -e SELECT QUOTE(sometext) FROM foo; test
+-+
| 'Pitt\'s Place' |
+-+
So far so good; exactly what I would expect. The string is nicely escaped
with ONE backslash. But now, look at this:
shell mysql -N -B -e SELECT QUOTE(sometext) FROM foo; test
'Pitt\\'s Place'
Double backslash in batch mode. Same result if I pipe the query into
mysql. Why? This can't be fed into any INSERT query. Bug or feature?
Any comments from the list are very welcome.
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter statement doesn't work in 5.0.2
Hello. Use such alter statement: alter table mytable change a a int default '1'; Every thing works fine on the MySQL 5.0.3 (from the latest bk source): mysql CREATE TABLE `mytable` ( - - `a` int(11) NOT NULL, - - `b` int(11) NOT NULL, - - `c` int(11) default NULL - - ) ENGINE=InnoDB DEFAULT CHARSET=latin1 - ; Query OK, 0 rows affected (0.19 sec) mysql insert into mytable (b, c) values (2, 3); Query OK, 1 row affected (0.01 sec) mysql select * from mytable; +---+---+--+ | a | b | c| +---+---+--+ | 0 | 2 |3 | +---+---+--+ 1 row in set (0.01 sec) mysql alter table mytable change a a int default '1';Query OK, 1 row affected (0.23 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql show create table mytable\G; *** 1. row *** Table: mytable Create Table: CREATE TABLE `mytable` ( `a` int(11) default '1', `b` int(11) NOT NULL, `c` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql insert into mytable (b, c) values (2, 3); Query OK, 1 row affected (0.02 sec) mysql select * from mytable; +--+---+--+ | a| b | c| +--+---+--+ |0 | 2 |3 | |1 | 2 |3 | +--+---+--+ 2 rows in set (0.00 sec) [snip] I am using MySQL 5.0.2 on a Redhat 9 box. I am having problems altering some columns default value and having it show up in the 'show create table x' and backup files. Here is my repeatable example: I issue this create statement: create table mytable ( a int not null, b int not null, c int ); Then I issue this alter table statement: alter table mytable alter a set default '1'; Then I issue: Show create table mytable; The result is: CREATE TABLE `mytable` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 However, the behavior works properly. For instance: insert into mytable (b, c) values (2, 3); select * from mytable; produces: 1, 2, 3 I realize of course that I can simply put the default value clause in my create statement, but I don't want to do that because the entire database has already been established and is up and running in a production environment. Am I doing something wrong or is this a bug? Whatever the case, what is the fix? Thank you, Jason McAffee The Technology Group [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query
Hello. the following query causes the mysqld server to close Yes. MySQL 5.0.2 not yet stable as we want. You may look in the error log to find the clues of shutdowns. See: http://dev.mysql.com/doc/mysql/en/error-log.html But I recommend you to upgrade to the latest bk source, which seems more stable to me. See: http://dev.mysql.com/doc/mysql/en/installing-source-tree.html prabhu bethuraj [EMAIL PROTECTED] wrote: sir, i am using mysql-5.0.2-alpha-win-noinstall.. for my academic project purpose. i am final year engg. student from India. currently i am using mysqld server type. the following query causes the mysqld server to close *** 1.describe syntax eg: DESC 'table_name'; 2.while trying to access information_schema.columns 3.while trying to access information_schema.views looking for fast response.. thank u.. prabhu bethuraj.m Yahoo! India Matrimony: Find your life partner online Go to: http://yahoo.shaadi.com/india-matrimony -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto loading a table
Hello. still stop at the 10M limit on memory tables anyway. What's the value of max_heap_table_size? See: http://dev.mysql.com/doc/mysql/en/server-system-variables.html [EMAIL PROTECTED] wrote: Thanks that gives me options, yes table was already created what I wanted was for the table itself to know that when MySql reloads to go an get all from another table. I was understanding this was just something I did when I created the table the first time as a Character of the table to know on load select * from Test2 This allows me to maintain stability and speed at the same time. While I write to 2 tables I always read from 1 and reading is done 95 times more often at least. I have set the My.cnf to 128M for memory tables as default but it appears I still stop at the 10M limit on memory tables anyway. Should I add something into the creation of the table to override the defaults locally with that table? Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, March 14, 2005 8:28 AM To: [EMAIL PROTECTED] Cc: Gleb Paharenko; mysql@lists.mysql.com Subject: Re: Auto loading a table [EMAIL PROTECTED] wrote: [Donny Lairson] Quick bump I never got an answer I have a table fsearch_temp I use it as a memory table to keep things light and fast but after a restart I want to repopulate some data automatically. So I thought I just said load from TEST2 which would by a myisam table containing the hardbackup I need. But obviously not the right way of saying this. I must be reading the instructions wrong can someone clarify this for me? snip Which instructions are you reading? I expect you get a syntax error, right? From the manual http://dev.mysql.com/doc/mysql/en/create-table.html, the correct syntax is CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] Gleb Paharenko wrote: Hello. ) ENGINE = MEMORY LOAD FROM TEST2 DEFAULT CHARSET = utf8 AUTO_INCREMENT =0 You should use select statement, not LOAD. For example: CREATE TABLE . SELECT * FROM TEST2; And table options like DEFAULT CHARSET you should put before select statement. See: http://dev.mysql.com/doc/mysql/en/create-table.html I think this is accurate but misleading. CREATE ... SELECT adds columns from the SELECT to the columns defined in the CREATE, so you cannot fix this simply by getting the last line right. You have to leave out the column definitions. On the other hand (from the manual page you cite), CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement... so you do need to keep the index definitions. Thus, assuming fsearch_temp's create_definition matches that of table TEST2, to create fsearch_temp as a copy of TEST2, you would CREATE TABLE fsearch_temp ( PRIMARY KEY (fsearchId), KEY fsearchIp (fsearchIp) ) ENGINE = MEMORY DEFAULT CHARACTER SET utf8 SELECT * FROM TEST2; but I don't think this is what you want, either. First, there is this caveat (from the manual): Some conversion of column types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns. To avoid that, you need to first CREATE the table, then populate it with a copy of TEST2 in a separate INSERT ... SELECT statement. See the manual for details http://dev.mysql.com/doc/mysql/en/insert-select.html. In any case, MEMORY tables don't go away unless they are dropped. Only the rows disappear when mysql stops. If you've previously created this table and haven't dropped it, it should still exist as an empty table on startup. In that case, you only need to reload the rows. INSERT INTO fsearch_temp SELECT * FROM TEST2; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems installing MySQL 4.0.24 on a Fedora Core 3 x86 box
Hello. 050314 1:31:06 /usr/sbin/mysqld-max: Shutdown Complete Your error file has this record. And you said that you'd installed only: - I run rpm -ivh MySQL-client-4.0.24-0.i386.rpm - I run rpm -ivh MySQL-server-4.0.24-0.i386.rpm There are no mysqld-max in listed rpms. I suggest you to check your installation. May be you should remove already installed instances of MySQL and try to install it again. You could also install the server using binary distribution. C.F. Scheidecker Antunes [EMAIL PROTECTED] wrote: I do not know what else to do at this point. I even messed with permissions but I realized it is not the problem. Jeff Steinkamp wrote: Yes, and it look pretty much like yours. Form what I can tell, the daemon is never started during the init cycle, or actually aborts for some reason. Since my machine is a dual boot, Winders and Linux, and I am running a test project on the Winders machine, it will be later this weekend before I can get back to the Linux box and putter around further. Jeff Steinkamp - N7YG Tucson, AZ SCUD Missile Coordinates N32-13-52 W110-52-15 http://home.earthlink.net/~jksteinkamp http://n7yg.webhop.org ___ Nothing is foolproof to a sufficiently talented fool. - Original Message - From: C.F. Scheidecker Antunes [EMAIL PROTECTED] To: Jeff Steinkamp [EMAIL PROTECTED] Sent: Monday, March 14, 2005 10:52 Subject: Re: Problems installing MySQL 4.0.24 on a Fedora Core 3 x86 box Jeff, I am in SLC Utah. Do you have your /var/log/mysqld.log file? I've put mine on the list so that anyone can see it. thanks, C.F. Jeff Steinkamp wrote: I've had the same problem and for the last month I've not been able to get the mysqld to run. Tried a number of things I've found on the net and nothing works. If you get a solution, I will be interested. Jeff Steinkamp - N7YG Tucson, AZ SCUD Missile Coordinates N32-13-52 W110-52-15 http://home.earthlink.net/~jksteinkamp http://n7yg.webhop.org ___ At 18, I was ashamed of how ignorant my father was. At 21, I was amazed at how much he had learned in three years. - Original Message - From: C.F. Scheidecker Antunes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, March 13, 2005 21:28 Subject: Problems installing MySQL 4.0.24 on a Fedora Core 3 x86 box Hello, I have Fedora Core 3 kernel 2.6.10-1.770_FC3smp It is a dual Xenon PIII server. I am trying to install MySQL 4.0.24 as I usually do: - There are no other mysql servers present, no 3.x as I did not install it. - I run rpm -ivh MySQL-client-4.0.24-0.i386.rpm - I run rpm -ivh MySQL-server-4.0.24-0.i386.rpm It installs the server Then I try to run MySQL but it does not run. Instead I have a .err file under /var/lib/mysql saying mysqld started and mysqld ended. I have done so many MySQL installs before and so I cannot understand why I cannot install it now on this server. The tables are not created not even if I run mysql_install_db. Is there any dependency that is missing? Any necessary library? Any ideas? Thank you, C.F. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delays in replication and internet latency
Hello. I think that the speed of every single update which slave get from the master will grow, and slave could receive updates more frequently. But only practical usage will show the influence of this option in your case. Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, I can see how this would help with bandwidth (I don't have a bandwidth problem as explained below) but how will this help with the latency I have? Cheers, Andrew=20 -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Fri 11 March 2005 14:51 To: mysql@lists.mysql.com Subject: Re: Delays in replication and internet latency Hello. You may use --slave_compressed_protocol=3D1. See: http://dev.mysql.com/doc/mysql/en/replication-options.html Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, =20 We have replication running here and it has been excellent for a=20 number of years. =3D20 =20 Recently we have been having lag in replication from London to Palo=20 Alto (Plenty of bandwidth but a latency of 300ms round trip). =3D20 =20 The replications binlogs are being written at a rate of about 100MB to 200MB per hour and whilst we have no problems replicating to several=20 servers that are reasonable close to the master, the Palo Alto server=20 is getting over an hour behind at times. =20 Does anyone know if there is something I can do to solve this? =3D20 =20 What experiences have others had in the past? =20 Perhaps there is some way to increase the packet size or something to=20 get over this latency problem... =20 Or any other ideas? =20 Cheers, =20 Andrew =20 SQL, Query =20 =20 =20 -- For technical support contracts, goto https://order.mysql.com/?ref=3Densita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com --=20 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-query-browser-bin: error while loading shared libraries: libtiff.so.3
Hello. libtiff.so.3: cannot open shared object file: No such file or directory I think, you should get the version of library which mysql-query-browser wants. Billy Pilgrim [EMAIL PROTECTED] wrote: Hello, I am trying to run mysql query browser on debian, but... # ./mysql-query-browser ./mysql-query-browser-bin: error while loading shared libraries: libtiff.so.3: cannot open shared object file: No such file or directory Looks like I have libtiff.so.4. # ls libtiff* libtiff.so.4 libtiff.so.4.0.0 What should I do? Mysql administrator works. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access Denied
Hello. Using the general-log you could find if something changes the grant tables. See: http://dev.mysql.com/doc/mysql/en/query-log.html Also I strongly recommend you to upgrade to the latest release, if you don't use it already. so whats the final solution to my problem ? :( -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with a full backup
Hello. You may use --default-character-set=charset command line option for mysqldump. Without this option mysqldump uses UTF8 encoding, which can cause a problem. See: http://dev.mysql.com/doc/mysql/en/mysqldump.html Javier Ballesteros Correa [EMAIL PROTECTED] wrote: Hi! I'm learning mysql and I have a problem when i make a full backup with mysqldump. I have a lot of table rows written in spanish. In the backup file (.sql) I have problems with certain spanish characters like: ? -- ?i ? -- ?¿ ? -- ?- ? -- ?? and so many others like that. However, when I make an incremental backup with the *.bin-* files I don't have this problem. I use MySQL 4.1.X with the default character set(latin1) and the default collation (latin1_swedish_ci). I?ve tried with the latin1_spanish_ci and with another character set (utf8 with the utf8_spanish_ci collation). But the problem doesn?t disappear. Can anybody help me? Thank you very much. __ Renovamos el Correo Yahoo!: ?250 MB GRATIS! Nuevos servicios, m?s seguridad http://correo.yahoo.es -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wrong bytesec nightmare!
Hello. You may use --extend-check option with myisamchk, but usually it finds a lot of garbage, and should be used as a last resort. Francisco Lopez [EMAIL PROTECTED] wrote: Hello all, I am getting quite desperate on this, since I've been trying to recover my diary of a whole year of travelling which was stored in a MyISAM table for a couples of weeks now! The MySQL server was running in a XP machine and suddenly the hard disk crashed starting my particular nightmare. I had to use recovery tools to scan the disk and I thought I was saved when I found the *.frm, *.MYI and *.MYD files and was able to recover them, but when I tried get the tables back into MySQL and backup their contents, I found a series of errors that have kept me down ever since! The first complain I got when I selected the table was: Didn't find any fields in table 'news' I then checked the table for errors, and got: mysql check table news; +--+---+ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reg creating log
Hello. You should also redirect stderr to the output file. The way it's done depends on the used shell. But usually you should use '2' instead of ''. [EMAIL PROTECTED] wrote: Hi, Thank you for your reply. I tried even with --tee option with mysql as follows Mysql -uroot db_name --tee =3D test 'input_file' 'output_file' But in the out put file I am getting as below Logging to file 'test' Actual error is not logging into the ouput file. I am able to see the error messages in the command prompt. How to get these error messages in the output file. Please help me in this. Thanks, Narasimha -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Friday, March 11, 2005 7:34 PM To: mysql@lists.mysql.com Subject: Re: Reg creating log Hello. You may use --tee option of mysql to store the results in the file. Or just redirect the output: mysql -uroot db_name 'input_file' 'output_file'. See: http://dev.mysql.com/doc/mysql/en/mysql-commands.html [EMAIL PROTECTED] wrote: =0D Hi, =0D What is the command to create log file while executing mysql command. =0D I.e I want to run a file using mysql as follows =0D Mysql -uroot db_name Inputfile =0D =0D I want to log the above results into a log file, to do the same what option I have to use here with mysql command. =0D =0D PS: Input file contains some sql statements. =0D =0D Please help me in this. This is very urgent. =0D Thanks, Narasimha =0D -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 09, 2005 11:16 PM To: mysql@lists.mysql.com Subject: Re: Access Denied =0D Hello. =0D Right. No need to FLUSH PRIVILEGES after GRANT.=3D0D =0D My fault, sorry.=3D0D =0D =0D Michael Stassen [EMAIL PROTECTED] wrote: =3D0D Gleb Paharenko wrote: Hello. =3D0D Execute FLUSH PRIVILEGES after granting. See: http://dev.mysql.com/doc/mysql/en/flush.html http://dev.mysql.com/doc/mysql/en/privilege-changes.html =3D0D Yes, please read this one. FLUSH PRIVILEGES is NOT needed after GRANT,=3D0D REVOKE, or SET PASSWORD. You only need to FLUSH if you directly edit the=3D0D grant tables (INSERT, UPDATE, DELETE). Since he/she is using GRANT, FLUSH=3D0D PRIVILEGES is unlikely to be relevant. =3D0D http://dev.mysql.com/doc/mysql/en/access-denied.html http://dev.mysql.com/doc/mysql/en/user-resources.html =3D0D Connect to the server using mysql command line client using specified in GRANT statement username and password to check that the problem not in MySQL server. What output does the=3D0D following statement produce: =3D0D SHOW GRANTS FOR 'metal-host'@'localhost'; =3D0D Run this when it works, and then again when it doesn't. If the result is=3D0D different, then something is changing the privileges assigned to=3D0D [EMAIL PROTECTED], so the solution will involve finding what is doing=3D0D that. If the privs are the same, then we need to look elsewhere. From the=3D0D (not entirely clear) description, I expect the first case. =3D0D What exact version of MySQL server do you use? =3D0D [snip] =3D0D Hello Gleb, no i m not doing flush..i dont even know what flush is. do u have anything in mind ? =3D0D [snip] =3D0D =3D0D Michael =3D0D =3D0D Metal Host Contact wrote: =3D0D I have installed in a single user called metal-host more than 10 databases all with different names and in different php scripts (phpbb phpnuke mambo etc). the problem is that they only work as I install them,after some hour later all the scripts show me that error : Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) then im trying to do : GRANT ALL PRIVILEGES ON somethign.* TO [EMAIL PROTECTED] IDENTIFIED BY password; and then each database that i re-give the GRANT all command works perfectly..but for 1 minute only,then it gives me the same : Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) So is there any limitation for the number of DBases that each user should run ? =3D0D =0D =0D --=3D0D For technical support contracts, goto https://order.mysql.com/?ref=3D3Densita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com =0D =0D =0D =0D --=3D0D MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] =0D =0D =0D =0D Confidentiality Notice=3D0D =0D The information contained in this electronic message and any attachments to=3D this message are intended for the
MySQL vs PostgreSQL
Hi, What do you think about MySQL vs PostgreSQL ? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL vs PostgreSQL
Perhaps a quick search at a list archive will give you the answers you are looking for. This was discussed at length about 6 months ago and the messages are still at gmane. Just page through and you will find many messages with just this subject. http://search.gmane.org/search.php?group=gmane.comp.db.mysql.generalque ry=postgres Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: ninjajs [mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 March 2005 8:20 PM To: mysql@lists.mysql.com Subject: MySQL vs PostgreSQL Hi, What do you think about MySQL vs PostgreSQL ? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to add 2 columns to a table with After ColName???
mos wrote: I've tried just about every syntax combination to try and add columns to a table using Alter Table and I keep running into syntax errors. Alter table MyTable add (newcol1 float after col3, newcol2 float after col3); or Alter table MyTable add (newcol1 float, newcol2 float) after col3; You must separate each action with a comma. Try this: alter table MyTable add newcol1 float after col3, add newcol2 float after col3; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL-Syntax Check
Hello Users does anyone know a tool or a way for validation sqlcode on the command_line??? For example ./sqlsyntaxchecker select * f test -- Error not valid sql syntax thx christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
temporary tables
List, I'm trying to create tables that will store data temporarily, if a php page generates data to fill the table, then any number of queries could be run on the table, and then be automatically be deleted if it's not queries for let's say an hour or something like that. Can this be accomplished with temporary tables, or should i just create static tables and then use a cron job to delete unused ones? What is the best way to approach this? Ted Toporkov [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character encoding problem-have read docs and search mailing list -but..
hi, i am migrating a legacy java application. so i am not suppose to change too much things. the problem is that the app store in multiple language such as korean and english. if i load again the new saved data, then korean language will only be like ',???' old data are ok. displayed in their own language. spec : win2k, mysql 4.1.7(migrated from 4.0.12), mysql-connector-java-3.1.6 show variables like '%char%'; character_set_client | latin1 character_set_connection | latin1 character_set_database | utf8 character_set_results| latin1 character_set_server | utf8 character_set_system | utf8 this is my.ini(cleaned up) [client] port=3306 [mysqld] port=3306 default-character-set=utf8 default-storage-engine=INNODB i read the mysql doc so .. -change my COLUMN table default encoding such as .. alter table newsletter default character set ucs2; but not work. i also read connector/j doc to add characterEncoding=utf8 in my connection string. but i cannot to do that(if tried but failed). the connection is made through java reflection on properties of com.mysql.jdbc.jdbc2.optional.MySqlDAtasource. any suggestion ? thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
Ted Toporkov [EMAIL PROTECTED] wrote on 15/03/2005 11:04:09: List, I'm trying to create tables that will store data temporarily, if a php page generates data to fill the table, then any number of queries could be run on the table, and then be automatically be deleted if it's not queries for let's say an hour or something like that. Can this be accomplished with temporary tables, or should i just create static tables and then use a cron job to delete unused ones? What is the best way to approach this? Temporary tables are private to a single Connection, and would therefore not be an appropriate solution to this problem. If you have to do it, the cron job appears youe best bet. However, I query the requirement. ISTM that you are basically saying that you do not trust MySQL's cachein ability, both to cache recently used table blocks and to cache the result of recent queries. I would take the first approximation of trusting MySQL and only attempting solutions such as that which you propose when you know for certain that the system will not handle them without. Have you fully characterised the behaviour of the system without this kludge in place? Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding problem-have read docs and search mailing list -but..
in addition i also need to save and load korean, simplified chinese, traditional chinese, japanese and english in the same record. have any suggestion ? __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date of end support (security update)
Hi i am searching the date of the end of support for the different versions of mysql where can i find them? thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
Hello. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. So, I think, they won't solve your task. Ted Toporkov [EMAIL PROTECTED] wrote: List, I'm trying to create tables that will store data temporarily, if a php page generates data to fill the table, then any number of queries could be run on the table, and then be automatically be deleted if it's not queries for let's say an hour or something like that. Can this be accomplished with temporary tables, or should i just create static tables and then use a cron job to delete unused ones? What is the best way to approach this? Ted Toporkov [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To extend InnoDB table size
Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen
Re: Optimising COUNT()
Stembridge, Michael [EMAIL PROTECTED] wrote on 03/14/2005 02:18:25 PM: I noticed another listmember used COUNT(fieldname) instead of COUNT(*). Is there a noticeable performance increase with COUNTing a column name instead of all columns? (ie, like SELECTing specific columns instead of using SELECT *) Thanks! COUNT() only counts non-null values. A long time ago, I once had a table where every column could have been null (yes it was a bad design but I was much younger then). COUNT(*) did not count those rows that were COMPLETELY NULL. I haven't tested this recently so I no longer know if it's still true (and it wasn't a MySQL database I was using, either) More on topic...if you say COUNT(fieldname), you say that you want to count all of the non-null values in that column. Is it faster than select(*)? Probably not but you are asking for something different when you phrase your question that way, aren't you. You are asking how many non-null values are in this column and not how many rows are not null. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Multi-Table Query Problem...
Nick Zukin [EMAIL PROTECTED] wrote on 03/14/2005 05:22:38 PM: I'm trying to do a multitable query and am having problems. I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table. I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query: $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; $query .= WHERE (p.productname LIKE '%.$_GET['keyword'].%') ; $query .= OR (p.productfamily LIKE '%.$_GET['keyword'].%') ; $query .= OR (v.vcategory LIKE '%.$_GET['keyword'].%') ; $query .= GROUP BY v.vbusiness ; As an example, it might look like this: SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with. TIA, Nick You are GROUPING when you shouldn't. You only need to GROUP whenever you want to perform some kind of aggregate function (AVG, SUM, COUNT, etc). What you see is a pseudo-random result from all of the possible results that meet your criteria. Drop your GROUP BY clause and your results should come back into line. If you were trying to eliminate duplicate rows, you might want to try SELECT DISTINCT. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: lost connection DURING query?
Crouch, Luke H. [EMAIL PROTECTED] wrote on 03/14/2005 09:34:02 AM: This error message seems a bit different than others I have gotten. it is from a ColdFusion server that uses an ODBC driver... ODBC Error Code = S1000 (General error) [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-log]Lost connection to MySQL server during query that during part threw me, but it may just be the way ODBC states the error? mysql error log shows no errors for the time(s) that this happened, other than a bunch of aborted connections - but that is because I set wait_timeout pretty low to avoid connection problems we were having with other clients. does anyone know if this is the error ODBC gets when trying to use a connection that has been killed by the MySQL server? if that is so, it may be that I just need to put the wait_timeout back up. any help would be great! thanks! -L Luke Crouch 918-461-5326 [EMAIL PROTECTED] I only see this error whenever I send a query that exceeds the server's max_allowed_packet. If you are doing a very large INSERT statement, that's probably what happened. Either split your INSERT into chunks smaller than max_allowed_packet or increase the server's limit until it lets your command through. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: lost connection DURING query?
this query is select * from ogcount which is a table containing 1 record, so probably not the packet size, though that's very good info to know. I'm going to see if I get the same error if I use a JDBC driver instead of ODBC. -L Luke Crouch 918-461-5326 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 15, 2005 8:08 AM To: Crouch, Luke H. Cc: mysql@lists.mysql.com Subject: Re: lost connection DURING query? Crouch, Luke H. [EMAIL PROTECTED] wrote on 03/14/2005 09:34:02 AM: This error message seems a bit different than others I have gotten. it is from a ColdFusion server that uses an ODBC driver... ODBC Error Code = S1000 (General error) [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-log]Lost connection to MySQL server during query that during part threw me, but it may just be the way ODBC states the error? mysql error log shows no errors for the time(s) that this happened, other than a bunch of aborted connections - but that is because I set wait_timeout pretty low to avoid connection problems we were having with other clients. does anyone know if this is the error ODBC gets when trying to use a connection that has been killed by the MySQL server? if that is so, it may be that I just need to put the wait_timeout back up. any help would be great! thanks! -L Luke Crouch 918-461-5326 [EMAIL PROTECTED] I only see this error whenever I send a query that exceeds the server's max_allowed_packet. If you are doing a very large INSERT statement, that's probably what happened. Either split your INSERT into chunks smaller than max_allowed_packet or increase the server's limit until it lets your command through. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: To extend InnoDB table size
Naveen C Joshi wrote: Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. These are MyIsam, not InnoDB. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stuck with older version of MySQL on RH9, want to install latest, but cant
Hi, I am trying to install the latest version of MySQL(i am not sure 4.0.23? currently in the office). The RH9 comes with some 3.2.xx version, I try to run rpm -U to upgrade but that doesnt work, I try to install it doesnt work and I try to remove mysql that doesnt work either. I went to Add and Remove programs removed anything to do with SQL server and still it shows on command line that mysql 3.2.xx is installed. When I try and remove using rpm command line it gives me PHP DBD dependency error. I basically want to remove and upgrade my MySQL, but have not found a simple way of doing it. Any help would be great Thanks Ankur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs PostgreSQL
On Tue, 15 Mar 2005 18:49:38 +0900 ninjajs [EMAIL PROTECTED] wrote: What do you think about MySQL vs PostgreSQL ? Both are great products and have their ups and downs. On a MySQL list you will not get an un-biases answer to this question. If you really want to know what people on the MySQL list think of PG, search the archives (as already recommended). If you also want to know what PG folks think of MySQL, they have list archives as well. Both are easy enough to install (with MySQL being slightly easier on the newbie scale) that you can just install them both, and evaluate for yourself. Have fun, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lost connection DURING query?
Luke - Original Message - This error message seems a bit different than others I have gotten. it is from a ColdFusion server that uses an ODBC driver... ODBC Error Code = S1000 (General error) Which version of CF are you using? If it is MX6.1, surely you can use a native MySQL datasource connection, and dispense with ODBC? I know it doesn't help the immediate problem, but it would remove one less reliable link in the chain. Terry [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-log]Lost connection to MySQL server during query that during part threw me, but it may just be the way ODBC states the error? mysql error log shows no errors for the time(s) that this happened, other than a bunch of aborted connections - but that is because I set wait_timeout pretty low to avoid connection problems we were having with other clients. does anyone know if this is the error ODBC gets when trying to use a connection that has been killed by the MySQL server? if that is so, it may be that I just need to put the wait_timeout back up. any help would be great! thanks! -L Luke Crouch 918-461-5326 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT assistance
I have a table containing three columns: Int intint ++-++ | id | serialbegin | serialend | ++-++ | 1 | 10 | 20 | ++-++ And say we have serial number 11. Is there a way to SELECT any rows where $my_serial is greater than/equal to serialbegin and less than/equal to serialend? I tried this query: SELECT id FROM numbers WHERE serialbegin = '11' AND serialend = '11'; Empty set (0.00 sec) Is this a little more complicated than I'm making it out to be (or am I missing something obvious)? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT assistance
[snip] I have a table containing three columns: Int intint ++-++ | id | serialbegin | serialend | ++-++ | 1 | 10 | 20 | ++-++ And say we have serial number 11. Is there a way to SELECT any rows where $my_serial is greater than/equal to serialbegin and less than/equal to serialend? I tried this query: SELECT id FROM numbers WHERE serialbegin = '11' AND serialend = '11'; [/snip] Since they appear to be INT types, drop the single quotes... SELECT id FROM numbers WHERE serialbegin = 11 AND serialend = 11; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: SELECT assistance
-Original Message- From: Richard Whitney [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 15, 2005 10:54 AM To: Jay Blanchard Subject: RE: SELECT assistance Quoting Jay Blanchard [EMAIL PROTECTED]: [snip] I have a table containing three columns: Int intint ++-++ | id | serialbegin | serialend | ++-++ | 1 | 10 | 20 | ++-++ And say we have serial number 11. Is there a way to SELECT any rows where $my_serial is greater than/equal to serialbegin and less than/equal to serialend? I tried this query: SELECT id FROM numbers WHERE serialbegin = '11' AND serialend = '11'; [/snip] Since they appear to be INT types, drop the single quotes... Change: SELECT id FROM numbers WHERE serialbegin = 11 AND serialend = 11; (will return an empty resultset) To: SELECT id FROM numbers WHERE serialbegin = 11 AND serialend = 11; R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://xendhosting.com rw AT xend.net Net Binder http://netbinder.net 310-943-6498 602-288-5340 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Compressing after Deletion
David, According to the documentation, OPTIMIZE will also work on InnoDB tables. Will that produce the same result as your ALTER TABLE ? Cheers Terry - Original Message - Hi Chris, For MyISAM/BDB tables use OPTIMIZE TABLE your table name; For InnoDB tables try ALTER TABLE your table name TYPE=InnoDB; Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 March 2005 9:19 AM To: mysql@lists.mysql.com Subject: Compressing after Deletion I have looked in the documentation and either I am not looking for the right thing or have simply overlooked it. But my question is this, I have a database with 35 Million records, and I need to delete about 25 million of those. After deletion I would think that I would need to compress, shrink, or otherwise optimize the database. How is that done? do I need to do it? What commands should I be looking up in the docs? Any help is greatly appreciated. Chris Hood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Problem
Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two columns query from a single column table?
Hi there! I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? TIA Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two columns query from a single column table?
SELECT id, id FROM data should work just fine. On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi there! I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? TIA Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- 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: Two columns query from a single column table?
Oh, sorry, I didn't look close enough at your question...never mind :-) On Tue, 15 Mar 2005 16:02:59 -0800, Scott Klarenbach [EMAIL PROTECTED] wrote: SELECT id, id FROM data should work just fine. On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi there! I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? TIA Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- 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: Two columns query from a single column table?
SELECT (SELECT id from data WHERE id=1), (SELECT id from data WHERE id=4); This willl return you the 2 columns in one row. Otherwise, if you're looking to return multiple queries into one result set, then UNION is what you're looking for. ie (select id from data) UNION (select id from data). On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi there! I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? TIA Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- 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: Two columns query from a single column table?
On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] wrote: I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? Do this in whichever scripting language you are using. You can do this in SQL if your list of IDs is monotomously increasing (no gaps), but it is rather ugly: SELECT a.ID, a.Data, b.ID, b.Data FROM table a LEFT JOIN table b ON (a.ID + Ceiling((SELECT MAX(ID) FROM table) / 2) = b.ID) WHERE a.ID = Ceiling((SELECT MAX(ID) FROM table) / 2) ORDER BY a.ID Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two columns query from a single column table?
Hi there! The SELECTs within the SELECT didn't work. : ( I'm looking to return from one table, half on the left side, and half on the right side. This is: from row 1 to row n on the left, and from row n+1 to the end on the right. TIA Mensaje citado por Scott Klarenbach [EMAIL PROTECTED]: SELECT (SELECT id from data WHERE id=1), (SELECT id from data WHERE id=4); This willl return you the 2 columns in one row. Otherwise, if you're looking to return multiple queries into one result set, then UNION is what you're looking for. ie (select id from data) UNION (select id from data). On Tue, 15 Mar 2005 17:43:29 -0600, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi there! I need to do this: From this table +--+ |id|Data | |--|---| | 1|Something 1| | 2|Something 2| | 3|Something 3| | 4|Something 4| | 5|Something 5| | 6|Something 6| +--+ Get this query +-+ |id|Data |id|Data | |--|---|--|---| | 1|Something 1| 4|Something 4| | 2|Something 2| 5|Something 5| | 3|Something 3| 6|Something 6| +-+ Any idea? TIA Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- 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] Servicio de Correo Unidad Central - CETI - http://www.ceti.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Q: Suggested reading
Hi list, I've been using MySQL for small databases for 6 - 7 years (if I recall correctly), from php/web-applications to ODBC-applications in various languages and the native C API from C and C++ applications. The databases so far has varied from a few hundreds kbytes to a few gigabytes. Now I'm working on two new projects. One commercial project that will hava a database with billions of records and an estimated database-size of 1TB. Briefly, it will collect data from a number of MS SQL database-servers in a datacenter, and put everything into one huge database for reporting and analyzing. The big tables has small record-sizes with just a few numerical (32-bit and 64-bit) fields. The other project is an Open Source full-blown Internet news (nntp) server. I plan to store spool-records, header-information and small messages in the SQL-database (and data in large round-robin spool-files or raw disk-partitions). I have no experience with MySQL and databases of this size, so I'm looking for relevant articles and/or books. I have to figure out _if_ MySQL can do the job, how to tune it for top performance, how to make the databases reliable, how to backup/restore such databases, and what hardware and opertating system to reccommend. I'm greatful for any relevant information :) Jarle -- Jarle Aase email: [EMAIL PROTECTED] Author of freeware. http://www.jgaa.com news:alt.comp.jgaa War FTP Daemon: http://www.warftp.org War FTP Daemon FAQ: http://www.warftp.org/faq/warfaq.htm Jgaa's PGP key: http://war.jgaa.com/pgp NB: If you reply to this message, please include all relevant information from the conversation in your reply. Thanks. no need to argue - just kill'em all! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bulk download via php
I know phpmyadmin does this, but I can not find a reference to an example. I need to allow a client daily download of very large batches of mysql records, I would like to stream this data to a download file, perhaps even gzip it in the process, any links? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stuck with older version of MySQL on RH9, want to install latest, but cant
Thanks for helping brian, While I was going through some sites where people had posted some trouble, I found this guy who had successfully installed the server, client and dev package. All I had to do was rpm -Uvh --nodeps pkg_name I am not sure of the --nodeps, but i think it was there, but in any event, did the server first, then the libraries and then client finally stoped the mysql from running, installed the dev pkg and started mysql back up, didnt take much time. Thanks for the help Ankur On Tue, 15 Mar 2005 17:16:27 -0800, Bryan [EMAIL PROTECTED] wrote: Well, When you uninstall something, you have to get rid of the dependencies as well. In OpenBSD, I have to uninstall the PHP DB Module when I uninstall MySQL server. You may be able to force it to uninstall, then uninstall the PHP module, or you can do the PHP module first, then uninstall MySQL. Then, it's just a matter of installing everything in reverse. The module, then the server... Hope I was helpful. Bryan On Tue, 15 Mar 2005 08:46:52 -0600, Ankur G35 Saxena [EMAIL PROTECTED] wrote: Hi, I am trying to install the latest version of MySQL(i am not sure 4.0.23? currently in the office). The RH9 comes with some 3.2.xx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hide password when running mysqldump from a batch
--- What you really want to avoid is having the password on the commandline. File permissions won't matter at all if you end up running a command that puts your password in the output of 'ps'! Command lines are always public information. Put the password for mysqldump in the running user's ~/.my.cnf instead, and tighten the permissions on *that* file. [client] password=Your password goes here I tried that; it does work with mysql but does not appear to work with mysqldump. Is there a way to supply the password to mysqldump when running dump from a batch scipt and avoid showing it on the command line? Thanks in advance __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lost connection DURING query?
I got the same error when my web server and mysql server were separate. And the error was caused bcoz of too many unauthenticated users. The reason behind it was that the reverse dns lookup for the webserver was failing. but as soon as i added the web server to /etc/hosts, the error went off. Hope this might be of some help to ppl with the same error. regards, Sapna
subquery fails when a NOT IN operator tests a subset with NULL valu
Hi. Here is a description of what looks like a serious bug. This is related to bugs #7294 and #6247 Tested against mysql 4.1.9 and 4.1.10. Cheers Giuseppe Maxia Description: operator NOT IN fails when a subquery returns one or more NULL values. How-To-Repeat: simple proof of concept: mysql select 1 in (1,null,3); +-+ | 1 in (1,null,3) | +-+ | 1 | +-+ 1 row in set (0.00 sec) #OK mysql select 2 not in (1,null,3); +-+ | 2 not in (1,null,3) | +-+ |NULL | +-+ 1 row in set (0.00 sec) # NOT OK More complete proof: mysql drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql drop table if exists t2; Query OK, 0 rows affected (0.06 sec) mysql create table t1 (id int not null auto_increment primary key, c1 int); Query OK, 0 rows affected (0.01 sec) mysql mysql create table t2 (id int not null auto_increment primary key, c2 int); Query OK, 0 rows affected (0.02 sec) mysql insert into t1(c1) values (1),(2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql insert into t2(c2) values (2),(null),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from t1; ++--+ | id | c1 | ++--+ | 1 |1 | | 2 |2 | ++--+ 2 rows in set (0.01 sec) mysql select * from t2; ++--+ | id | c2 | ++--+ | 1 |2 | | 2 | NULL | | 3 |3 | ++--+ 3 rows in set (0.00 sec) mysql select t1.* from t1 left join t2 on (c1=c2) where t2.id is null; ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # OK. This is the normal way of checking for non-existence of records in a # related table mysql select t1.* from t1 where c1 not in (select distinct c2 from t2); Empty set (0.01 sec) # NOT OK. This query should have returned the same result as the previous one mysql select t1.* from t1 where c1 not in (select distinct c2 from t2 where c2 is not null); ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # ugly workaround with an express filter mysql select t1.* from t1 where c1 not in (select distinct coalesce(c2,0) from t2 ); ++--+ | id | c1 | ++--+ | 1 |1 | ++--+ 1 row in set (0.01 sec) # yet another ugly workaround Fix: as a temporary workaround, filter off the NULLs with a WHERE clause or a COALESCE function. Submitter-Id: submitter ID Originator: Giuseppe Maxia Organization: Stardata s.r.l MySQL support: Certified Consulting Partner Synopsis: subquery fails on test with NOT IN and NULL values Severity: serious Priority: high Category: mysql Class: sw-bug Release:mysql-4.1.10-standard (MySQL Community Edition - Standard (GPL)) Server: /usr/local/mysql/bin/mysqladmin Ver 8.41 Distrib 4.1.10, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult 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 Server version 4.1.10-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 41 min 36 sec Threads: 2 Questions: 111 Slow queries: 0 Opens: 32 Flush tables: 1 Open tables: 2 Queries per second avg: 0.044 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux ltstardata 2.6.9-1.667 #1 Tue Nov 2 14:41:25 EST 2004 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Feb 12 14:24 /lib/libc.so.6 - libc-2.3.3.so -rwxr-xr-x 1 root root 1504728 Oct 28 01:00 /lib/libc-2.3.3.so -rw-r--r-- 1 root root 2404716 Oct 28 00:46 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Oct 28 00:08 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared'
