Re: backup table/restore table question
possible but i find a very severe conflict with mysqldump between what the prod server will understand and what the test server will understand.. example: at the bottom of a create table section of mysqldump you will see the line : /*4 ALTER TABLE! LOCK READ--*/ and of course gives error 10064:syntax error so i usually have to edit/comment out those lines so backup tablename is faster and works better usually... - Original Message - From: Riaan Oberholzer [EMAIL PROTECTED] To: Andy B [EMAIL PROTECTED] Sent: Tuesday, April 13, 2004 2:20 AM Subject: Re: backup table/restore table question I use mysqldump which generates sql for you and it works like a charm. Eg, mysqldump --user=root databasename.table output.sql The output.sql file then has SQL statements to generate and exact copy. --- Andy B [EMAIL PROTECTED] wrote: hi... i have a server where there are 5 tables inside a database. i was wondering if i did the following command from inside a script: backup table dbname.tablename to /home/users/my_dir/ then with a different script gzip/tar them, after gzipped ftp them to my test server into say c:/db_backup/db_name, connect to my local test server and then type: restore db_name.tablename from c:/backup/db_name/ and have it work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding configure command after installation
Hi there, I just recompiled mysql and I am wondering if there is something like in php (phpinfo();) where you can see the configure command after the db is installed. It would just be nice to have that in a later time, or even to make sure that the new version has replaced the old one. thanx for and advice on that, Andy ___ ... and the winner is... WEB.DE FreeMail! - Deutschlands beste E-Mail ist zum 39. Mal Testsieger (PC Praxis 03/04) http://f.web.de/?mc=021191 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql on a pentium
There does seem to be a bug in icc- I've posted on intel's message boards and they've confirmed a problem. There is a work around - here is the post: Hi Yonah, I can see the same problem on my system. I will create a support issue for you and will let you when this get fixed. I found that if you take out libmysql.o from libmysqlclient.a and then link it as - icpc -O3 -DDBUG_OFF -O3 -ipo -axWN -march=pentium4 -mcpu=pentium4 -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o mysql mysql.o readline.o sql_string.o completion_hash.o -lreadline -lncurses ../libmysql/.libs/libmysqlclient.a ../libmysql/libmysql.o -lz -lcrypt -lnsl -lm it works fine. you can try this as a workaround for your problem, till it get fixed. The problem is you have to include ../libmysql/libmysql.o explicitly whereever ../libmysql/.libs/libmysqlclient.a is linked. HTH, CP in addition- I got these helpful instructions from the folks on the mysql-packagers list- I specifically asked about 4.0.18 so they should work- I have successfully compiled 4.1.1 several times and the binaries passed the tests although I'm having trouble with the benchmark suite. good luck yonah That really sounds like an icc bug. Here's the line we currently use for our binaries on icc: CFLAGS=-O3 -unroll2 -ip -mp -no-gcc -restrict CC=icc CXX=icc CXXFLAGS=-O3 -unroll2 -ip -mp -no-gcc -restrict ./configure - --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data - --libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex - --enable-thread-safe-client --enable-local-infile --enable-assembler - --disable-shared --with-client-ldflags=-all-static - --with-mysqld-ldflags=-all-static --with-readline --with-embedded-server - --with-innodb You may be able to use higher optimization levels (e.g. by removing -mp, which will however cause some loss in floatingpoint accuracy - some of the test suite tests will fail). I assume the --no-gcc is key here. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer Walter Andreas wrote: Hi there, how to compile mysql 4.0.18 on a pentium for best performance? I searched the net now for 2 days and found lots of hints on compiling with icc and pgcc, but it looks to me that icc is not working with mysql 4.0.18 and pgcc is out of date (maybe gcc already catched up with pgcc?). Setting compiler flags is also a miraqle for me. This is going to be a production server, so it should be really stable and not the trade for performance. Can anybody advice a configure command with compiler settings for a p4 machine with 1G ram? Thank you for your advice, Andy _ Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P! Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY alias
I\'m trying to order by an alias in a multi table SELECT statement(Note I\'ve cut the statement down a bit to make it more readable): SELECT kills.PlayerID, player.DeathsPerMinute, SUM(kills.Kills) AS Total FROM playerweaponkills AS kills, ETPlayerSummary AS player WHERE kills.PlayerID=player.PlayerID AND kills.WeaponID=17 GROUP BY kills.PlayerID ORDER BY (Total*(1-player.DeathsPerMinute)) DESC LIMIT 5 When I run this I get the error: #1054 - Unknown column \'Total\' in \'order clause\' I take it the problem is that MySQL is unable to resolve the alias Total when its used in this way with player.DeathsPerMinute? Is there any way I can prefix Total to help it be resolved? The statement works fine with ordering by either Total or (1-player.DeathsPerMinute) its when you try and combine them in the above statement it freaks out. Any ideas? I ideal want to order by: (Total*(1-player.DeathsPerMinute)) Cheers, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql bench problems
I've been trying to compile a mysql server optimized for a zeon processor and a specific application- I'm compiling with icc. the problem is with the sql benchmarks- the perl regexp for making the detailed report of the benchmark doesn't match the output from the benchmarks so it doesn't create the report but instead says that everything failed- Has anyone had this problem? I could futz around with the regexp but if there is a more correct solution, I'd rather do that. here is a sample output line: Time for alter_table_drop (91): 19 wallclock secs ( 0.02 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.02 CPU) here is the regexp: /^(estimated |)time (to|for) ([^\s:]*)\s*\((\d*)(:\d*)*\)[^:]*:\s*([\d.]+) .*secs \(\s*([^\s]*) usr\s*\+*\s*([^\s]*) sys.*=\s+([\d.]*)\s+cpu/i thanks yonah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Space in multi-byte character set
Here are charcodes of full-width space (IDSP,Ideographic Space) of sjis, ujis and utf8: sjis 81 40 ujis A1 A1 utf8 E3 80 80 String processing functions TRIM, LTRIM, and RTRIM don't recognize full-width space in a string. They don't trim the full-width space and leave it in string. It seems that the functions don't process Japanese full-width space as space. Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to protect MySQL server from intruders ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! If the application is _NOT_ local you can restrict complete acces to one and only one machine, host, or network mask using the grant statement. Also changing mysql default port is not a bad idea, at least it make it ~ more dificult to guess Best Regards! - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAe7xN8WJSBCrOXJ4RAieIAKDRprMb6XdpL0gknILE1iwyusf1VACgvO7K SLbas9lteCXTAv2yVCBSeqk= =Z6Vj -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql on a pentium
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! CFLAGS=-O3 -march=i686 -mcpu=i686 -funroll-loops -fomit-frame-pointer - -fno-exceptions -fno-ftti -felide-constructors CXXFLAGS the same as above. Check out Securing and Optimizing Linux in the tldp.org i think is just what you nedd. Best Regards! - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAe7068WJSBCrOXJ4RAvJCAJ0QIctA0Ov/gPzQww/hE1SFvphNMQCfYPl/ EJLxkX28Kc9Q67z29fHqJx4= =6vBO -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free software and open source
On Sun, 2004-04-11 at 15:58, Saurabh Data wrote: Dear Users Can anyone in your own words clarify the difference between open source and free software. Go see the GNU.org site. http://www.gnu.org/home.html and also check this out http://www.gnu.org/licenses/licenses.html (If you don't know GNU is the name of the FSF (Free Software Foundation) project for free and/or open-source software, they are the ones behind great lot of the importance of free software today) Regards jmf Many Thanks Saurabh Data ___ Saurabh Data School of Computing University of Leeds Leeds LS2 9JT U.K. one who seeketh , will findeth and all door shall open - james Allen ___ _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
turning off binary logging
Hi there, I just found that mysql 4.0.18 is doing binary logging. How can I turn this off? I outcommented the line in my.cnf and restarted the server, but it is still creating those binary loggs inside the data dir. I do not see a nead for this, plus I fear that it might fill up the file system, plus I think it is reducing performance. Thanx for any advice, Andy _ Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P! Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql MAtch against query help
I have a query that searches my database for people
with C++ on their resume .
Query
=
select * ,match(Res_resume) AGAINST ('C++') as
kewyordscore from member,memberprofile,resume left
join stateprovince on stateid = Res_state
where mem_id = mempf_memid and
match(Res_resume) AGAINST ('+C++' IN BOOLEAN MODE)
I have the ft_min_word_len set to 2 , but for some
reason , it still does not return a record when i
search for C++. Is there a way to escape the ++ when i
do a search , or is there something i am not doing
right.
The query works when i do a search for 'PR' or 'P*' .
so i am sure that i reindexed my database.
I am using Mysql version 4.1.1 on windows.
Any help owuld be great ... thx
Dave
__
Do you Yahoo!?
New DSL Internet Access from SBC Yahoo!
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: turning off binary logging
I just found that mysql 4.0.18 is doing binary logging. How can I turn this off? SNIP I do not see a nead for this, plus I fear that it might fill up the file system, plus I think it is reducing performance. The binary log is primarily there to let you restore data. Say you backup every night at 04:00 and your database crashes at 15:00 you can restore from your backup but what about all those changes in the 11 hours after the backup was made? Instead you can use mysqlbinlog to run all the changes made in those 11 hours and get your data back to pretty much exactly where it was before the crash. According to the manual, the performance hit is about 1% - that's peanuts in exchange for the ability to recover your data. The other function of the binary log is to store statements that will be replicated on slave servers. That might not be relevant to you at the moment, but will perhaps be something you need later? If you really want to disable it, read the manual page at: http://dev.mysql.com/doc/mysql/en/Binary_log.html Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql MAtch against query help
Hehe I've also had a problem with searching for something like it's ,
anything with a single quote doesnt return anything, maybe try adding a
slash , C\+\+ ?? heheh maybe i'm wrong, it could be a limitation.
-Original Message-
From: David Taiwo [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 13, 2004 9:32 PM
To: [EMAIL PROTECTED]
Subject: Mysql MAtch against query help
I have a query that searches my database for people
with C++ on their resume .
Query
=
select * ,match(Res_resume) AGAINST ('C++') as
kewyordscore from member,memberprofile,resume left
join stateprovince on stateid = Res_state
where mem_id = mempf_memid and
match(Res_resume) AGAINST ('+C++' IN BOOLEAN MODE)
I have the ft_min_word_len set to 2 , but for some
reason , it still does not return a record when i
search for C++. Is there a way to escape the ++ when i
do a search , or is there something i am not doing
right.
The query works when i do a search for 'PR' or 'P*' .
so i am sure that i reindexed my database.
I am using Mysql version 4.1.1 on windows.
Any help owuld be great ... thx
Dave
__
Do you Yahoo!?
New DSL Internet Access from SBC Yahoo!
http://sbc.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ADO driver?
Hi all, Is there an ADO (NOT ADO.NET) driver for MySQL? If so, where? Thanks in advance. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is the difference Between the mysql HEAP Table type and Views
I know this might sound like a rather funny question to many gurus out here, but I'm a bit confused. The example give in the mysql manual is: mysql CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down - FROM log_table GROUP BY ip; mysql SELECT COUNT(ip),AVG(down) FROM test; mysql DROP TABLE test; ironically the example given in the postgresql manual for views looks like it does the same things as the above sql statements: CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; Please enlighten me. Thanks Abiola Aluko. Lycos Email has 10 MB of FREE storage space. http://mail.lycos.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Carsten, Thanks for the answer (and other thanks go to the other guys that answered me). I think normalization is the way to go. I think it is the right thing to do (in theory). The problem is that theory doesn't fit all. Basically I have some tables with only 2 fields (ID and name), and a central table, joined by a one-to-many relation. The key point here are the 2-field tables. If I keep them separate, I can extend them (add new fields) without problem when need arise. But if there is no need for an extension (my case), all I get is a greater number of tables that I have to take care of. Wouldn't be better (maybe more efficient ?) to put all the 2-field tables in only table, with a separate ENUM field to separate the records on categories ? -- Cip CRD Hi Ciprian, CRD OK, I'm by no means a DB guru, so a) take this with a grain of salt CRD and b) feel free to tear it apart if I'm completely wrong! ;] CRD If in fact your people and city tables aren't going to change very CRD often, then why don't you just go all the way and keep that CRD information somewhere else in your application and write it straight CRD to your travel_expenditures table, e.g.: [..] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding configure command after installation
I just recompiled mysql and I am wondering if there is something like in php (phpinfo();) where you can see the configure command after the db is installed. It would just be nice to have that in a later time, or even to make sure that the new version has replaced the old one. The initial portion of config.log in the directory where the source was built contains the initial config line: It was created by configure, which was generated by GNU Autoconf 2.53. Invocation command line was $ ./configure --prefix=/usr/local/mysql4 Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is the difference Between the mysql HEAP Table type and Views
Have you tried to update an underlying heap table? The heap table will not be updated. A view is updated when any of the underlying table(s) are updated. -Original Message- From: Abiola Aluko To: [EMAIL PROTECTED] Sent: 4/13/04 8:01 AM Subject: What is the difference Between the mysql HEAP Table type and Views I know this might sound like a rather funny question to many gurus out here, but I'm a bit confused. The example give in the mysql manual is: mysql CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down - FROM log_table GROUP BY ip; mysql SELECT COUNT(ip),AVG(down) FROM test; mysql DROP TABLE test; ironically the example given in the postgresql manual for views looks like it does the same things as the above sql statements: CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; Please enlighten me. Thanks Abiola Aluko. Lycos Email has 10 MB of FREE storage space. http://mail.lycos.co.uk ATT145709.txt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ADO driver?
Hi, I've found VBMySQLDirect, go to http://vbmysql.com and look for VBMySQLDirect under the 'Projects' section. As the author itself explains on the documentation, it is not ADO, but very very near to it. I'm using it succesfully on some applications on VB 6, without problems. Thanks for the message... but, I'm not using VB... Can this project be used in place of an ADO driver? I guess not, right? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql bench problems
Yonah Russ writes: here is a sample output line: Time for alter_table_drop (91): 19 wallclock secs ( 0.02 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.02 CPU) here is the regexp: /^(estimated |)time (to|for) ([^\s:]*)\s*\((\d*)(:\d*)*\)[^:]*:\s*([\d.]+) .*secs \(\s*([^\s]*) usr\s*\+*\s*([^\s]*) sys.*=\s+([\d.]*)\s+cpu/i Yonah, It appears that the source has been modified from the original regexp. Primary changes are: time - Time cpu - CPU You may want to run the output thru tr '[A-Z]' '[a-z]' to change upper case to lower case to allow the regexp to work properly. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql MAtch against query help
+ is not part of the definition of a word in MySQL.
One solution is to normalize C++ into CPLUSPLUS
both during index and query time.
Haitao
--- electroteque [EMAIL PROTECTED] wrote:
Hehe I've also had a problem with searching for
something like it's ,
anything with a single quote doesnt return anything,
maybe try adding a
slash , C\+\+ ?? heheh maybe i'm wrong, it could be
a limitation.
-Original Message-
From: David Taiwo
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 13, 2004 9:32 PM
To: [EMAIL PROTECTED]
Subject: Mysql MAtch against query help
I have a query that searches my database for
people
with C++ on their resume .
Query
=
select * ,match(Res_resume) AGAINST ('C++') as
kewyordscore from member,memberprofile,resume left
join stateprovince on stateid = Res_state
where mem_id = mempf_memid and
match(Res_resume) AGAINST ('+C++' IN BOOLEAN
MODE)
I have the ft_min_word_len set to 2 , but for some
reason , it still does not return a record when i
search for C++. Is there a way to escape the ++
when i
do a search , or is there something i am not doing
right.
The query works when i do a search for 'PR' or
'P*' .
so i am sure that i reindexed my database.
I am using Mysql version 4.1.1 on windows.
Any help owuld be great ... thx
Dave
__
Do you Yahoo!?
New DSL Internet Access from SBC Yahoo!
http://sbc.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is the difference Between the mysql HEAP Table type and Views
A heaptable isn't aview,it's just a tablein memory, when you stop mysql thetable disappear. Are usefullfor speed selects; you don't need access hd. In the otherhand views are definitions from other(s) table(s) stored in the db, are permanent and can be updated Alejandro. ---Mensaje original--- De: Victor Pendleton Fecha: 04/13/04 11:30:05 Para: 'Abiola Aluko '; '[EMAIL PROTECTED] ' Asunto: RE: What is the difference Between the mysql HEAP Table type and Views Have you tried to update an underlying heap table? The heap table will not be updated. A view is updated when any of the underlying table(s) are updated. -Original Message- From: Abiola Aluko To: [EMAIL PROTECTED] Sent: 4/13/04 8:01 AM Subject: What is the difference Between the mysql HEAP Table type and Views I know this might sound like a rather funny question to many gurus out here, but I'm a bit confused. The example give in the mysql manual is: mysql CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down - FROM log_table GROUP BY ip; mysql SELECT COUNT(ip),AVG(down) FROM test; mysql DROP TABLE test; ironically the example given in the postgresql manual for views looks like it does the same things as the above sql statements: CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; Please enlighten me. Thanks Abiola Aluko. Lycos Email has 10 MB of FREE storage space. http://mail.lycos.co.uk ATT145709.txt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí
Re: Mysql MAtch against query help
I have a query that searches my database for people with C++ on their resume . ... Hehe I've also had a problem with searching for something like it's , anything with a single quote doesnt return anything, maybe try adding a slash , C\+\+ ?? heheh maybe i'm wrong, it could be a limitation. Couldn't say for sure, but the '+' is definitely a special character in regexp syntax. Another possibility is how text gets broken down into tokens. The '+' symbol will likely be separated from the C when parsing the text into tokens. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Constraining MySQL Replication
Gowtham Jayaram [EMAIL PROTECTED] wrote: I understand that MySQL Replication can be configured to replicate selected tables in a Database. Is there anyway to further constrain the replicate, say based on a query of these tables etc..? No. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ADO driver?
Il giorno 13/apr/04, alle 16:01, Martijn Tonies ha scritto: Hi, I've found VBMySQLDirect, go to http://vbmysql.com and look for VBMySQLDirect under the 'Projects' section. As the author itself explains on the documentation, it is not ADO, but very very near to it. I'm using it succesfully on some applications on VB 6, without problems. Thanks for the message... but, I'm not using VB... Can this project be used in place of an ADO driver? I guess not, right? Well, I think yes, It should follow quite well standard ADO methods and functions, You must test it. anyway... regards, Giulio With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Ciprian Trofin writes: Basically I have some tables with only 2 fields (ID and name), and a central table, joined by a one-to-many relation. The key point here are the 2-field tables. If I keep them separate, I can extend them (add new fields) without problem when need arise. But if there is no need for an extension (my case), all I get is a greater number of tables that I have to take care of. Wouldn't be better (maybe more efficient ?) to put all the 2-field tables in only table, with a separate ENUM field to separate the records on categories ? Ciprian, There are two main purposes for normalization in this case. The first is to provide consistency of data. Going back to your example, placing the city name in each record allows the possibility of multiple spellings for the city name, since each record has its own copy of the data. The second is space savings, since storing an int is usually 4 bytes at worst while a city name is definitely more than 4 bytes. Yes it does generate a second table that only has the mappings from cityID to cityName, but you will likely find it well worth the effort to use the mapping. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I detect the end of a replication cycle?
Hello List: I have been crawling the docs, the mailing lists, and the discussion groups looking for options to my issues for several weeks and have found answers to most of them. I have one last nut to crack and if the answer is out there I must be blind. That's why I finally came to the list for help. Let me give a little background: I need to replicate a warehouse database (sort of an OLAP summary) that combines the reporting output from several branch offices back to those offices. Because of our WAN architecture (and other more political reasons), each branch will write their reports to a local database (on the branch's LAN) that replicates their information to a central office. The central office will have a script/daemon/program (whatever) that will combine the various reports from each of the branches into a coordinated warehouse database. This warehouse database would be replicated in hub-and-spoke fashion back to each of the various branches that need access to that information. I have already determined that I will need multiple instances of MySQL running at the central office to act as slaves to the branches' reporting masters (one instance per branch) and one more instance to act as the master of the compiled warehouse database. Each branch *could* run just one MySQL instance and be both a master to their reports database and a slave to the warehouse (at least that is the working plan). If I have to use two instances of MySQL in each branch, that's acceptable, too. Here is where things get sticky. I would like to be able to merge the replicated reports into the warehouse database with the smallest practical delay (I have some very process-driven branch mangers and they feel they must have this data sooner than later). I think that I will need to hold off merging records from any particular branch until replication completes with that branch (to maintain relational integrity, transaction boundaries, etc. Nobody said the reports were simple.). Somehow, I need to detect the end of my central slaves' replication cycle so that I can trigger the merge processing. I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW PROCESS LIST and watch for their status to change. I could start each slave with --log-slave-updates and watch the slaves' binlogs for changes. I could watch for the creation/deletion of the slave's relay logs. Basically I wind up using a timer to check the status of (something) and I am afraid that I will miss a status flip between timer ticks. Setting my timer too short will just consume excessive CPU cycles and also be counter productive. The target platform for my central server is tentatively Redhat 9.x with MySQL 4.1.xxx (the stable version when we go live). Can Linux help me here to hook into one of those events? If another OS can provide better hooks into this I can work that into the plan. Can anyone tell me why these ideas would or would not work? Is there a better way to synchronize an external process with the end of a replication event? Are there other options I didn't think of? (I even thought of modifying the MySQL source to produce a special merge slave but that will take too many people, too much time, be too task-specific, and may not be compatible with future versions of MySQL to be a good choice.) Please help! Thanks in advance! Shawn [EMAIL PROTECTED] ** delete all the parts with no in them to reveal my real address. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to replace a installed mysql version?
Hi there, I am a bit confused. It is not clear to me that the version I am currently running is the one I compiled last. How can I check the date of compiling? In order to get more performance I did try to install mysql 4.0.18 with different configure commands, now I am not sure if the make install did really override the already installed version of 4.0.18. Is there a possibility to find this out? Maybe with a variable called build time or similar? Thanx for any hint, Andy _ Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P! Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL function
Has anyone out there written a Credit Card Validation routine as a user-definable function (UDF)? We now have a requirement to collect credit card data through our Web Site. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Arguments to fight against Ms SQL Server and Oracle
Hi folks! I am sorry to bother but I need your help. At work I need to upgrade the data management the place is using (very old FOXPRO DOS) with something more modern. The use is only to store data and run multiple querys in a post mortem fashion. The data source is a Ms SQL server so management is thinking on upgrading directly to redmond's soft. The staff on the other hand, is thinking in using ORACLE, and I need very solid arguments to beet them. Any ideas I can borrow? Thanks Leo. MYSQL - QUERY - SQL - MYSQL - QUERY - SQL - MYSQL - QUERY - SQL - MYSQL - QUERY - SQL - MYSQL - QUERY - SQL - MYSQL - QUERY - SQL - MYSQL - QUERY - SQL
Re: Arguments to fight against Ms SQL Server and Oracle
Leonardo Javier Belén wrote: The staff on the other hand, is thinking in using ORACLE, and I need very solid arguments to beet them. Any ideas I can borrow? If *you* don't know any arguments, then maybe Oracle is the best solution. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect the end of a replication cycle?
On Tue, 2004-04-13 at 11:21, [EMAIL PROTECTED] wrote: Hello List: [snip] Here is where things get sticky. I would like to be able to merge the replicated reports into the warehouse database with the smallest practical delay (I have some very process-driven branch mangers and they feel they must have this data sooner than later). I think that I will need to hold off merging records from any particular branch until replication completes with that branch (to maintain relational integrity, transaction boundaries, etc. Nobody said the reports were simple.). Somehow, I need to detect the end of my central slaves' replication cycle so that I can trigger the merge processing. Its not clear what you mean by 'replication cycle'. I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW PROCESS LIST and watch for their status to change. I could start each slave with --log-slave-updates and watch the slaves' binlogs for changes. I could watch for the creation/deletion of the slave's relay logs. This seems to indicate that you are afraid of selecting rows on the slave that are in the middle of being updated from the master. A single update statement is still atomic, so you don't need to poll log files to determine if an update statement has finished. On the other hand, if there is some set of multiple updates and inserts that constitute a collection of data that you want to merge only when this collection is complete, you're better off finding a way to signal this through the database. You could have the master lock the tables in question until its finished and then the program quering the slave knows that when it gets a read lock, its will see the full set of data. You could also have a status column or a status table that has a flag letting the program on the slave side know when the data is ready. If this is off the mark, maybe some example statements would help... Basically I wind up using a timer to check the status of (something) and I am afraid that I will miss a status flip between timer ticks. Setting my timer too short will just consume excessive CPU cycles and also be counter productive. The target platform for my central server is tentatively Redhat 9.x with MySQL 4.1.xxx (the stable version when we go live). Can Linux help me here to hook into one of those events? If another OS can provide better hooks into this I can work that into the plan. Can anyone tell me why these ideas would or would not work? Is there a better way to synchronize an external process with the end of a replication event? Are there other options I didn't think of? (I even thought of modifying the MySQL source to produce a special merge slave but that will take too many people, too much time, be too task-specific, and may not be compatible with future versions of MySQL to be a good choice.) Please help! Thanks in advance! Shawn [EMAIL PROTECTED] ** delete all the parts with no in them to reveal my real address. ** -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: Arguments to fight against Ms SQL Server and Oracle
Leonardo : La verdad es que debes analizar lo que yo llamo ¿donde te aprieta el zapato? , esto quiere decir , que si requieres procedimientos almacenados y triggers y vistas , quizas oracle haga el trabajo. Si por otro lado la logica del negocio esta en tus aplicacciones y no requieres de estas caracteristicas que te nombre anteriormente Mysql es una buena opcion . la verdad es que debes leer respecto de las caracteristicas que hoy en dia tiene Mysql y comparar con los otros motores ya que como dicen por alli /la ignorancia es insolente/. Saludos, Alvaro Avello. Jochem van Dieten wrote: Leonardo Javier Belén wrote: The staff on the other hand, is thinking in using ORACLE, and I need very solid arguments to beet them. Any ideas I can borrow? If *you* don't know any arguments, then maybe Oracle is the best solution. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What distribution should I use for Mac OS X 10.3.x (Panther?
Which distribution should I install on Mac OS X Panther? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Index Usage: select ... where foo = 90 on a varchar
Lucy, you've got some EXPLAINing to do... (sorry, couldn't resist) A) select fileName from outDocInterchange where interchangeStatus = 91; B) select fileName from outDocInterchange where interchangeStatus = '91'; (A) Runs unindexed, (B) runs with the istat_date index. Can anyone explain why? My table (other columns/keys removed): Create Table: CREATE TABLE `outDocInterchange` ( `dateReceived` datetime default '-00-00 00:00:00', `interchangeStatus` varchar(20) default NULL, KEY `istat_date` (`interchangeStatus`,`dateReceived`), ) TYPE=MyISAM Obviously I need to change interchangeStatus to an int, but I was still suprised to see the results: mysql explain select fileName from outDocInterchange where interchangeStatus = 91; +---+--+---+--+-+-- +---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+-- +---+-+ | outDocInterchange | ALL | istat_date| NULL |NULL | NULL | 37223 | Using where | +---+--+---+--+-+-- +---+-+ 1 row in set (0.08 sec) mysql explain select fileName from outDocInterchange where interchangeStatus = '91'; +---+--+---++- +---+--+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+---++- +---+--+-+ | outDocInterchange | ref | istat_date| istat_date | 21 | const |1 | Using where | +---+--+---++- +---+--+-+ 1 row in set (0.08 sec) I'm using MySQL 4.0.18 for Solaris 8. Can anyone explain this? Or is this a bug (or missing optimization)? Thanks. - Max -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect the end of a replication cycle?
On Tue, 2004-04-13 at 15:11, [EMAIL PROTECTED] wrote: On Tue, 2004-04-13 at 11:21, [EMAIL PROTECTED] wrote: [more snip] Somehow, I need to detect the end of my central slaves' replication cycle so that I can trigger the merge processing. Its not clear what you mean by 'replication cycle'. A replication cycle starts when the slave is notified by the master that there are binlog entries it needs to process and ends when the slave has finished processing those entries and returns to waiting for more updates from the master. I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW PROCESS LIST and watch for their status to change. I could start each slave with --log-slave-updates and watch the slaves' binlogs for changes. I could watch for the creation/deletion of the slave's relay logs. This seems to indicate that you are afraid of selecting rows on the slave that are in the middle of being updated from the master. A single update statement is still atomic, so you don't need to poll log files to determine if an update statement has finished. On the other hand, if there is some set of multiple updates and inserts that constitute a collection of data that you want to merge only when this collection is complete, you're better off finding a way to signal this through the database. You could have the master lock the tables in question until its finished and then the program quering the slave knows that when it gets a read lock, its will see the full set of data. You could also have a status column or a status table that has a flag letting the program on the slave side know when the data is ready. If this is off the mark, maybe some example statements would help... [more snipping] Yes, I AM concerned about getting a partial update to the warehouse. I know that transactions aren't logged until after they commit. If I use transactional boundaries to post multitable reports (for instance: an invoice takes two tables, one for the base information and one for the line items) into the branch masters then they will arrive intact and I won't corrupt the central slaves. (That's NOT the issue I am worried about!) I am worried that if I start processing those new records from the slave database to the Warehouse before all of the records have been processed from the Relay Logs (lets say I started trying to merge records when I see the relay log being created), I could miss some data (like the last few items on the invoice). That's why I am so worried about not merging until the END of the cycle. I need to be sure that everything has been committed to my central slave BEFORE I can merge the latest updates with the warehouse database. I can lock a slave database so that it won't replicate in the middle of my merging so I know that if I can catch a slave when it goes back to sleep (Waiting for master to send event), I would have a complete set of data. I could use --log-slave-updates to copy the updates to the slave's binlog and check that to see if I need to merge records( if slave status is waiting and the binlog is not empty then merge). Each merge could flush the binlog. However, there was a post from someone using binlogs for similar purpose that said that for 4.1.x+ the binlogs vary in size after flushing so I don't know how reliable that would be as a check. How could I tell when a binlog is empty? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Index Usage: select ... where foo = 90 on a varchar
You shouldn't be surprised. This is normal behavior. interchangeStatus is a varchar, so select fileName from outDocInterchange where interchangeStatus = 91; requires that interchangeStatus be converted to an int for each row so it can be compared to 91, rendering the index useless. On the other hand, select fileName from outDocInterchange where interchangeStatus = '91'; compares interchangeStatus to a string, which the index is designed to do. In general, an index on a column won't help if the column is input to a function. Michael Max Campos wrote: Lucy, you've got some EXPLAINing to do... (sorry, couldn't resist) A) select fileName from outDocInterchange where interchangeStatus = 91; B) select fileName from outDocInterchange where interchangeStatus = '91'; (A) Runs unindexed, (B) runs with the istat_date index. Can anyone explain why? My table (other columns/keys removed): Create Table: CREATE TABLE `outDocInterchange` ( `dateReceived` datetime default '-00-00 00:00:00', `interchangeStatus` varchar(20) default NULL, KEY `istat_date` (`interchangeStatus`,`dateReceived`), ) TYPE=MyISAM Obviously I need to change interchangeStatus to an int, but I was still suprised to see the results: mysql explain select fileName from outDocInterchange where interchangeStatus = 91; +---+--+---+--+-+-- +---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+-- +---+-+ | outDocInterchange | ALL | istat_date| NULL |NULL | NULL | 37223 | Using where | +---+--+---+--+-+-- +---+-+ 1 row in set (0.08 sec) mysql explain select fileName from outDocInterchange where interchangeStatus = '91'; +---+--+---++- +---+--+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+---++- +---+--+-+ | outDocInterchange | ref | istat_date| istat_date | 21 | const |1 | Using where | +---+--+---++- +---+--+-+ 1 row in set (0.08 sec) I'm using MySQL 4.0.18 for Solaris 8. Can anyone explain this? Or is this a bug (or missing optimization)? Thanks. - Max -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY alias
As far as I know, you can't use an alias in a calculation outside of a HAVING clause. You could work around this by adding the calculation to your SELECT clause: SELECT kills.PlayerID, player.DeathsPerMinute, SUM(kills.Kills) AS Total, SUM(kills.Kills) * (1-player.DeathsPerMinute) AS rank FROM playerweaponkills AS kills, ETPlayerSummary AS player WHERE kills.PlayerID=player.PlayerID AND kills.WeaponID=17 GROUP BY kills.PlayerID ORDER BY rank DESC LIMIT 5 Michael Danielb wrote: I'm trying to order by an alias in a multi table SELECT statement(Note I've cut the statement down a bit to make it more readable): SELECT kills.PlayerID, player.DeathsPerMinute, SUM(kills.Kills) AS Total FROM playerweaponkills AS kills, ETPlayerSummary AS player WHERE kills.PlayerID=player.PlayerID AND kills.WeaponID=17 GROUP BY kills.PlayerID ORDER BY (Total*(1-player.DeathsPerMinute)) DESC LIMIT 5 When I run this I get the error: #1054 - Unknown column 'Total' in 'order clause' I take it the problem is that MySQL is unable to resolve the alias Total when its used in this way with player.DeathsPerMinute? Is there any way I can prefix Total to help it be resolved? The statement works fine with ordering by either Total or (1-player.DeathsPerMinute) its when you try and combine them in the above statement it freaks out. Any ideas? I ideal want to order by: (Total*(1-player.DeathsPerMinute)) Cheers, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reinstall Windows.
Backup the data folder under the mysql folder, that's it -Original Message- From: Alejandro C. Garrammone [mailto:[EMAIL PROTECTED] Sent: Monday, April 12, 2004 10:11 AM To: MySQL Mailing List Subject: Reinstall Windows. I need to re-install windows, so I need to re-install mysql. How can I backup my databases so when I reinstall mysql put them to work again?, Thx in advance, Alex -- 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]
Replication and Error 1200
Hi, I'm trying to get replication set up on a slave, and getting the error: Error 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO. The master machine is set up already, and there is already one database replicating off of it (a second instance of mysql on the same machine as the master). In master, FILE, SUPER, RELOAD, and SELECT have all been GRANTed to the slave user, and that user can log in using the command-line client. The GRANTs look like grant select on *.* to '[EMAIL PROTECTED]' identified by 'foobar'; Here is the relevant part of the my.cnf: [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K myisam_sort_buffer_size = 8M set-variable= default-character-set=cp1251 log-bin log-warnings log-slow-queries = /usr/local/mysql/data/slowqueries.log server-id = 75 replicate-do-table=master.data replicate-wild-do-table=search.% replicate-do-table=profile.digest replicate-do-table=profile.digestdata replicate-do-table=profile.user master-host=192.168.2.2 master-user=root master-password=foobar master-connect-retry=10 This is the SQL that should start the replication on the slave: mysql CHANGE MASTER TO MASTER_HOST='dbhost', - MASTER_USER='root', MASTER_PASSWORD='foobar', - MASTER_LOG_FILE='dbhost-bin.045', - MASTER_LOG_POS=4606; Query OK, 0 rows affected (0.00 sec) This runs ok, but slave start; gives the error. This is in mySQL 4.0.12, on Linux. Sorry if that's too much information. Thanks to any and all for comments or help. Regards, Jim N. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL function
From: Gordon [EMAIL PROTECTED] Date: 2004/04/13 Tue PM 06:45:17 GMT To: [EMAIL PROTECTED] Subject: MySQL function Has anyone out there written a Credit Card Validation routine as a user-definable function (UDF)? We now have a requirement to collect credit card data through our Web Site. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This sounds like it should be handled more by your server-side software (PHP, ASP, JSP, etc.) rather than MySQL. I'm no web guy, and don't claim to be, but to me it would make more sense to have your server side programming language handle that, rather than MySQL. I believe you can find some pre-written code if you hit Google. Something tells me I've seen it out there, just don't remember where. James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Altering MAX_DATA_LENGTH
Hi Dan, (Sending to the General list too, since this isn't a Windows-specific thing.) SHOW TABLE STATUS LIKE 'tbl_name' will show you the current Avg_row_length. But the specific values for MAX_ROWS and AVG_ROW_LENGTH when you CREATE or ALTER the table don't matter (except for looking correct :-)) as long as their product is greater than 4GB. BTW, you can't have the limit be 8GB -- when you go greater than 4GB, the Max_data_length will be 1TB. Hope that helps. Matt - Original Message - From: Dan Sent: Tuesday, April 13, 2004 3:58 PM Subject: Altering MAX_DATA_LENGTH If I have a table that has two fields: Char(100), Blob How do I determine the avg_row_length value if I want to increase the size limit to 8GB? Thanks Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple SELECTs in one query
Hey gang, many thanks to all for pointing me in the right direction for my previous multiple selects question. I moved to 4.1.1 and implemented Udikarni's use of multiple sum()s instead of multiple selects() and that stuff is all groovy now! Of course, I'm beating my head on *another* wall now...wouldn't ya just know it? My client code checks the main table for a few different criteria, and I used an additional hard select for a sorting method. Basically, each client looks for jobs to process, starting with jobs under its default project and default jobtype, and then by its default project and all other jobtypes, and finally everything else. Within each of these sets, jobs are sorted by a Priority field. My previous query looked like this (butchered pseudocode follows): SELECT A AS SortCode, * FROM Jobs WHERE Jobs.Project = MyProject AND Jobs.JobType = MyJobType UNION ALL SELECT B AS SortCode, * FROM Jobs WHERE Jobs.Project = MyProject AND Jobs.JobType MyJobType UNION ALL SELECT C AS SortCode, * FROM Jobs WHERE Jobs.Project MyProject AND Jobs.JobType = MyJobType UNION ALL SELECT D AS SortCode, * FROM Jobs WHERE Jobs.Project MyProject AND Jobs.JobType MyJobType ORDER BY SortCode ASC, Jobs.Priority ASC Now, in MySQL 4.1.1, I can't even get the first line to work - I suspect that I'm doing something wrong with that pesky asterisk, because the following works: SELECT A AS SortCode, JobName FROM Jobs But the following does not: SELECT A AS SortCode, * FROM Jobs From what I can see in the MySQL.org docs, this should work...any ideas? If I can get around that, I suspect that my UNIONS will work OK and all will be well in the worldone can hope?!? As before, many thanks for any insight that y'all can provide!! Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables + SUM + GROUP BY = strange behavior
Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++---+-+-++- -+ | CallDate | @a := SUM(Charge) | @b := SUM(Cost) | @a - @b | @a | @b | ++---+-+-++- -+ . | 2004-03-01 | 621.059 | 249.310 | 30.882 | 39.512 | 8.63 | | 2004-02-29 |54.620 | 17.660 | 30.882 | 39.512 | 8.63 | | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | | 2004-02-27 | 622.282 | 248.920 | 30.882 | 39.512 | 8.63 | | 2004-02-26 | 607.274 | 277.100 | 30.882 | 39.512 | 8.63 | | 2004-02-25 | 709.698 | 308.580 | 30.882 | 39.512 | 8.63 | | 2004-02-24 | 783.210 | 298.560 | 30.882 | 39.512 | 8.63 | | 2004-02-23 | 799.764 | 252.890 | 30.882 | 39.512 | 8.63 | .
User variables + SUM + GROUP BY = strange behavior
Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suse 9, chroot mysql [long]
OS: suse 9
Mysql: 4.0.15
Googled: yep, but didn't find anything conclusive
I'm attempting to chroot mysql, and I'm failing [miserabaly]. I realize
that this is a mysql list, but I currently believe it's either a dependency
of mysql I forgot or something something I'm not aware of w/ mysql. I also
figured I'd get a better response from people who also have chroot'd mysql
rather than asking the chroot folks.
Excuting mysql non-chroot'd works fine. I'm using the stock mysql from
Suse, and therefore it's dynam linked. I've 'ldd /usr/sbin/myslqd' as well
as 'ldd /usr/bin/mysql'
It [chroot] fails stating permision denied w/o giving anymore info [is
there some hidden switch for chroot?]
When I strace it [strace -o fail.log chroot /chroot/mysql/ mysql
/usr/sbin/mysqld ] I get this in the output log:::
execve(/usr/bin/chroot, [chroot, /chroot/mysql/, mysql,
/usr/sbin/mysqld], [/* 41 vars */]) = 0
uname({sys=Linux, node=template, ...}) = 0
brk(0) = 0x804b9ac
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0)
= 0x40019000
open(/etc/ld.so.preload, O_RDONLY)= -1 ENOENT (No such file or
directory)
open(/etc/ld.so.cache, O_RDONLY) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=12190, ...}) = 0
old_mmap(NULL, 12190, PROT_READ, MAP_PRIVATE, 3, 0) = 0x4001a000
close(3)= 0
open(/lib/i686/libc.so.6, O_RDONLY) = 3
read(3, \177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\320]\1..., 512) =
512
fstat64(3, {st_mode=S_IFREG|0755, st_size=1461208, ...}) = 0
old_mmap(NULL, 1256644, PROT_READ|PROT_EXEC, MAP_PRIVATE, 3, 0) = 0x4001d000
old_mmap(0x40149000, 20480, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 3,
0x12c000) = 0x40149000
old_mmap(0x4014e000, 7364, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x4014e000
close(3)= 0
munmap(0x4001a000, 12190) = 0
open(/usr/lib/locale/locale-archive, O_RDONLY|O_LARGEFILE) = -1 ENOENT (No
such file or directory)
brk(0) = 0x804b9ac
brk(0x806c9ac) = 0x806c9ac
brk(0) = 0x806c9ac
brk(0x806d000) = 0x806d000
open(/usr/share/locale/locale.alias, O_RDONLY) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=2601, ...}) = 0
mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x4001a000
read(3, # Locale name alias data base.\n#..., 4096) = 2601
read(3, , 4096) = 0
close(3)= 0
munmap(0x4001a000, 4096)= 0
open(/usr/lib/locale/en_US/LC_CTYPE, O_RDONLY) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=178468, ...}) = 0
mmap2(NULL, 178468, PROT_READ, MAP_PRIVATE, 3, 0) = 0x4015
close(3)= 0
chroot(/chroot/mysql/)= 0
chdir(/) = 0
execve(/sbin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars */]) = -1
ENOENT (No such file or directory)
execve(/usr/sbin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars */]) =
-1 ENOENT (No such file or directory)
execve(/usr/local/sbin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars
*/]) = -1 ENOENT (No such file or directory)
execve(/root/bin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars */]) =
-1 ENOENT (No such file or directory)
execve(/usr/local/bin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars
*/]) = -1 ENOENT (No such file or directory)
execve(/usr/bin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars */]) =
-1 EACCES (Permission denied)
execve(/usr/X11R6/bin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars
*/]) = -1 ENOENT (No such file or directory)
execve(/bin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars */]) = -1
ENOENT (No such file or directory)
execve(/usr/games/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars */]) =
-1 ENOENT (No such file or directory)
execve(/opt/gnome/bin/mysql, [mysql, /usr/sbin/mysqld], [/* 41 vars
*/]) = -1 ENOENT (No such file or directory)
write(2, chroot: , 8) = 8
write(2, mysql, 5)= 5
write(2, : Permission denied, 19) = 19
write(2, \n, 1) = 1
exit_group(126) = ?
Now I'm definitely not a 'strace guru' but the last set of::: execve
/sbin/mysql seems to be chroot looking for 'mysqld' and not finding
it...But then it finds it [in the jail [/chroot/mysql/usr/bin/mysql] ] but
doesn't like the permissions. :( Which follows the 'error message' that I
get when just using chroot w/o strace.
Here is a listing of my /chroot/mysql/* w/ permissions. Am I missing a
dependency? Any thoughts?
/chroot/mysql/dev:
total 8
drwxr-xr-x2 mysqlmysql4096 Apr 13 13:14 .
drwxr-xr-x9 mysqlmysql4096 Apr 13 14:49 ..
crw-rw-rw-1 mysqlmysql 1, 3 Apr 13 13:14 null
/chroot/mysql/etc:
total 36
drwxr-xr-x2 mysqlmysql4096 Apr 13 13:12 .
drwxr-xr-x9 mysqlmysql4096 Apr 13
Re: Multiple SELECTs in one query
I am not sure about MySQL but in Oracle this will NOT work: SELECT A AS SortCode, * FROM Jobs However, this WILL: SELECT A AS SortCode, Jobs.* FROM Jobs Try adding the table or alias in front of the *. In general, however, I will repeat my suggestion from before - try to do everything in one pass - it's much more efficient. Something like this: SELECT (CASE WHEN Jobs.Project = MyProject AND Jobs.JobType = MyJobType then A WHEN Jobs.Project = MyProject AND Jobs.JobType MyJobType then B WHEN Jobs.Project MyProject AND Jobs.JobType = MyJobType then C WHEN Jobs.Project MyProject AND Jobs.JobType MyJobType then D END CASE ) SortCode, Jobs.* ORDER BY SortCode ASC, Jobs.Priority ASC Instead of reading the table 4 times, each time taking a slice and labeling it A/B/C/D you read it once and during that one pass you attach to each row the code of A/B/C/D depending on its content and you're done. One pass instead of 4 and no UNIONs. You might have to tinker with the syntax if CASE is not available in MySQL to this extent but that's the general idea. In a message dated 4/13/2004 6:50:33 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: Hey gang, many thanks to all for pointing me in the right direction for my previous multiple selects question. I moved to 4.1.1 and implemented Udikarni's use of multiple sum()s instead of multiple selects() and that stuff is all groovy now! Of course, I'm beating my head on *another* wall now...wouldn't ya just know it? My client code checks the main table for a few different criteria, and I used an additional hard select for a sorting method. Basically, each client looks for jobs to process, starting with jobs under its default project and default jobtype, and then by its default project and all other jobtypes, and finally everything else. Within each of these sets, jobs are sorted by a Priority field. My previous query looked like this (butchered pseudocode follows): SELECT A AS SortCode, * FROM Jobs WHERE Jobs.Project = MyProject AND Jobs.JobType = MyJobType UNION ALL SELECT B AS SortCode, * FROM Jobs WHERE Jobs.Project = MyProject AND Jobs.JobType MyJobType UNION ALL SELECT C AS SortCode, * FROM Jobs WHERE Jobs.Project MyProject AND Jobs.JobType = MyJobType UNION ALL SELECT D AS SortCode, * FROM Jobs WHERE Jobs.Project MyProject AND Jobs.JobType MyJobType ORDER BY SortCode ASC, Jobs.Priority ASC Now, in MySQL 4.1.1, I can't even get the first line to work - I suspect that I'm doing something wrong with that pesky asterisk, because the following works: SELECT A AS SortCode, JobName FROM Jobs But the following does not: SELECT A AS SortCode, * FROM Jobs From what I can see in the MySQL.org docs, this should work...any ideas? If I can get around that, I suspect that my UNIONS will work OK and all will be well in the worldone can hope?!? As before, many thanks for any insight that y'all can provide!! Steve -- 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: User variables + SUM + GROUP BY = strange behavior
Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
First letter only of a column
Hello: Is it possible to use mysql to select only the first letter of a string in a column? IOWS select names from table - select first letter of names from table another way of asking my questions would be, Is it possible to truncate columns in selection set to a specific length (in the case: 1) Pointers to relevant documents are welcome. Thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REPLACE query
I have a table with four columns, the first three of which are combined
into a unique key:
create table Test {
cid int(9) NOT NULL default '0',
sid int(9) NOT NULL default '0',
uid int(9) NOT NULL default '0',
rating tinyint(1) NOT NULL default '0',
UNIQUE KEY csu1 (cid,sid,uid),
KEY cid1 (sid),
KEY sid1 (sid),
KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it doesn't exist and
replace an existing row if one does exist:
REPLACE into TEST (cid, sid, uid, rating) values (580, 0, 205, 1)
In the case of this particular row, a row already exists with the
concatenated key of 580-0-205 and I am getting a duplicate key error. I
thought REPLACE was supposed to actually replace the contents of the row
if one exists. Does anyone have any ideas as to why this would be
causing a duplicate key error?
Thanks,
Kevin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I detect the end of a replication cycle?
On Tue, 2004-04-13 at 13:13, [EMAIL PROTECTED] wrote: [snip] I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW PROCESS LIST and watch for their status to change. I could start each slave with --log-slave-updates and watch the slaves' binlogs for changes. I could watch for the creation/deletion of the slave's relay logs. This seems to indicate that you are afraid of selecting rows on the slave that are in the middle of being updated from the master. A single update statement is still atomic, so you don't need to poll log files to determine if an update statement has finished. On the other hand, if there is some set of multiple updates and inserts that constitute a collection of data that you want to merge only when this collection is complete, you're better off finding a way to signal this through the database. You could have the master lock the tables in question until its finished and then the program quering the slave knows that when it gets a read lock, its will see the full set of data. You could also have a status column or a status table that has a flag letting the program on the slave side know when the data is ready. If this is off the mark, maybe some example statements would help... [more snipping] Yes, I AM concerned about getting a partial update to the warehouse. I know that transactions aren't logged until after they commit. If I use transactional boundaries to post multitable reports (for instance: an invoice takes two tables, one for the base information and one for the line items) into the branch masters then they will arrive intact and I won't corrupt the central slaves. (That's NOT the issue I am worried about!) I am worried that if I start processing those new records from the slave database to the Warehouse before all of the records have been processed from the Relay Logs (lets say I started trying to merge records when I see the relay log being created), I could miss some data (like the last few items on the invoice). That's why I am so worried about not merging until the END of the cycle. I need to be sure that everything has been committed to my central slave BEFORE I can merge the latest updates with the warehouse database. I can lock a slave database so that it won't replicate in the middle of my merging so I know that if I can catch a slave when it goes back to sleep (Waiting for master to send event), I would have a complete set of data. I could use --log-slave-updates to copy the updates to the slave's binlog and check that to see if I need to merge records( if slave status is waiting and the binlog is not empty then merge). Each merge could flush the binlog. However, there was a post from someone using binlogs for similar purpose that said that for 4.1.x+ the binlogs vary in size after flushing so I don't know how reliable that would be as a check. How could I tell when a binlog is empty? Using the binlog in this way will lead to a race condition. What if another update comes in during the few milliseconds between your 'ready' check (an empty binlog and a 'waiting' status) and when you select those rows for processing? Plus if MySQL does any buffering of its output to the binlog, you could be basing your check on seconds old data, further aggravating the problem. The only solution I can think of that won't cause a race condition is to lock your tables, but your program running on the slave database would have to be able to connect to the master. Your slave program would lock the necessary tables on the master side, wait to receive the lock, wait until the slave had caught up, do your merge, then release the lock. Likewise, the code updating tables on the master would need to lock the tables while they write. You make the process a little friendlier by creating a separate control table that the slave and master alternately locked, rather than locking all the table you'll use. That way processes on the master that just want to read the data don't have to wait for a lock. -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: First letter only of a column
try this: select LEFT(names, 1) from table Tim Johnson wrote: Hello: Is it possible to use mysql to select only the first letter of a string in a column? IOWS select names from table - select first letter of names from table another way of asking my questions would be, Is it possible to truncate columns in selection set to a specific length (in the case: 1) Pointers to relevant documents are welcome. Thanks tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: First letter only of a column
* Peter Lovatt [EMAIL PROTECTED] [040413 16:27]: Hi select left(field, 1) from table where field = something http://dev.mysql.com/doc/mysql/en/String_functions.html * Kevin Carlson [EMAIL PROTECTED] [040413 16:27]: try this: select LEFT(names, 1) from table Thanks folks. I love it! tim .. another way of asking my questions would be, Is it possible to truncate columns in selection set to a specific length (in the case: 1) Pointers to relevant documents are welcome. -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Populating database...
Hi, i just created a db with around 30 tables and i need to populate it. Is there any software or special technic for doing that automatically (using random characters for example)? Thanks, ltcmelo __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about flush logs
In my box, mysql server version is 4.0.18. I tried flush logs. Nothing happened in the mysql data directory. All the log files were not replaced. Does flush logs rename the old log files and create new log files? Thanks. Grace Tang Software engineer Computer Associates (China) Co., Ltd. Beijing RD Department Units 7-10, 19/F, Tower E3, Oriental Plaza 1 East Chang An Ave. Dong Cheng District Beijing 100738, China Tel: (86 10) 8518 5358 Ext. 273 Fax: (86 10) 8518 8453 Mail: [EMAIL PROTECTED], [EMAIL PROTECTED]
Data typing calculation results in SELECT?
I'm doing a select where I lag across records, and would like to compute some differences. It seems to do the calcs right if I evaluate the result in an IF statement, but if I just want to get the calculation result stored, it seems to default to a data type that only stores on digit. Here's the offending code: create table rfdata SELECT t1.*, dayname( t1.tradedate ) AS tradedayofweek, dayofmonth( t1.tradedate ) AS trademonthday, dayofyear( t1.tradedate ) AS tradedoy, monthname( t1.tradedate ) AS trademonth, ((t2.currentPrice - t1.currentPrice) / t1.currentPrice) AS d1closechange, ((t2.dayMinPrice - t1.currentPrice) / t1.currentPrice) AS d1lowchange, ((t2.dayMaxPrice - t1.currentPrice) / t1.currentPrice) AS d1highchange, if(((t2.dayMaxPrice-t1.currentPrice)/t1.currentPrice).03,1,0) AS d1threepcthit, ((t3.open - t1.currentPrice) / t1.currentPrice) AS d2openchange, if(((t3.open-t1.currentPrice)/t1.currentPrice).02,1,0) AS d2twopct FROM sorted_data AS t1 LEFT JOIN sorted_data AS t2 ON t2.newid = t1.newid +1 AND t2.symbol = t1.symbol LEFT JOIN sorted_data AS t3 ON t3.newid = t1.newid +2 AND t3.symbol = t1.symbol; This give me a able with the following offending results: +--+---+ | d1highchange | d1threepcthit | +--+---+ | 0.0 | 0 | | 0.0 | 1 | | 0.0 | 0 | Where d1highchange should have been a decimal that was over .03, given the 1 in the second column. If I look at the 1st column in phpMyAdmin, it appears to be type Double, with 25,1 in the defaults/format display. Any way I can rewrite my table creation code to be sure that value gets stored out several decimal places? Thanks, I looked in the manual but didn't see anything about column typing in a CREATE ... SELECT query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fun Query with Question
Greetings! I have a query that I would like to refine a little more. I am using this to send email to a directory with so many users in it that it causes my SMTP server to clog up. I want to break up the database send module so that it sends to only last names with the letters A - K first, do a pause, and then send the email to letters L - Z. Any help would be appreciated! Here's the original query (please ignore the session(MemberType), as it is a session variable chosen from a screen prior): select last, email from tblMembers where email is not null and MemberDesc session(MemberType) Thank you for your help in advance! J.R.
Problem with 2GB limit.
Hi, I'm working with Solaris 8, and MySQL 4.0.17-standard. I was trying to upload data into a single table database and when it reached 2GB it stopped uploading sending the error message: ERROR 1030 at line 2450: Got error 27 from table handler After more than 4 hours digging into the FAQs and some of the digests of the mailing list, and a real headacke, I come to ask for help. I can say that this is not a problem of OS limits, since the file containing the SQL commands is more than 3GB. When I get into the folder of my database (named superfamily), I see that the file *.MYD is exactly 2GB (results in bytes): -rw-rw 1 mysqlmysql2147483647 Apr 13 22:26 align.MYD -rw-rw 1 mysqlmysql 1024 Apr 13 22:26 align.MYI -rw-rw 1 mysqlmysql 8616 Apr 13 22:19 align.frm Then after all what I read, I think this is useful to know that my ibdata1 file is not too big (results in bytes): -rw-rw 1 mysqlmysql10485760 Apr 13 21:09 ibdata1 Also, that my innodb was created as default: innodb_data_file_path ibdata1:10M:autoextend And that none of the logfiles is greater than 6MB (results in bytes): -rw-rw 1 mysqlmysql 25088 Jan 29 21:33 ib_arch_log_00 -rw-rw 1 mysqlmysql5242880 Apr 13 21:25 ib_logfile0 -rw-rw 1 mysqlmysql5242880 Jan 29 21:33 ib_logfile1 And if you ask me to send you anything else that would help you to help me, I will send it of course. I am new to MySQL, so please if you ask me to run any command please give me a hint (or better the command itself) so I can run (the exact way) whatever you think would help. I will appreciate any help/advice, I'm kind of disappointed, I know there is someone that had the same problem or that know how to solve it. Regards, and thank you guys in advance. César Nitrogen Fixation Research Center. RegulonDB staff. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple SELECTs in one query
A * by itself must come first, so SELECT *, A AS SortCode FROM Jobs will work. [EMAIL PROTECTED] wrote: I am not sure about MySQL but in Oracle this will NOT work: SELECT A AS SortCode, * FROM Jobs However, this WILL: SELECT A AS SortCode, Jobs.* FROM Jobs Try adding the table or alias in front of the *. This works in MySQL, as well. In general, however, I will repeat my suggestion from before - try to do everything in one pass - it's much more efficient. Something like this: SELECT (CASE WHEN Jobs.Project = MyProject AND Jobs.JobType = MyJobType then A WHEN Jobs.Project = MyProject AND Jobs.JobType MyJobType then B WHEN Jobs.Project MyProject AND Jobs.JobType = MyJobType then C WHEN Jobs.Project MyProject AND Jobs.JobType MyJobType then D END CASE ) SortCode, Jobs.* ORDER BY SortCode ASC, Jobs.Priority ASC SNIP You might have to tinker with the syntax if CASE is not available in MySQL to this extent but that's the general idea. MySQL has CASE, with almost the same syntax you describe, except it ends with END rather than END CASE. See http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: 5 - Out of memory (Needed 2298807288 bytes)
Hi All, I am trying to perform an update: UPDATE helpdesk_tickets ht, helpdesk_status_master hsm SET ht.status_id = (SELECT status_id FROM helpdesk_status_master WHERE is_closed = 'y') WHERE ht.submit_date DATE_ADD(curdate(), INTERVAL -7 day) AND ht.status_id = hsm.status_id AND hsm.is_closed = 'y' AND hsm.final_closed = 'n' When I get the above error. Is the above query allowed? Has it perhaps been fixed? This is the sequence of events 1) Ran the above query 2) The server shutdown 3) Started the server again 4) I get the out of memory error Running Mysql 4.1 1Gig Ram (2 Gig SWAP) 2X 2.4GhZ Xeon RH9 Here's the log: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 sort_buffer_size=2097144 max_used_connections=72 max_connections=150 threads_connected=22 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1007014 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x5fbf7ea0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfabeed8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x807e89b 0x829e7f8 0x80a0d7c 0x80a111a 0x804c3bc 0x80a1565 0x80a547e 0x806c970 0x806abc8 0x80a1565 0x80bbadc 0x80a5914 0x80a7b62 0x80bb95f 0x808b3a2 0x808dd99 0x8088c91 0x808847d 0x8087c39 0x829bfac 0x82d187a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x88b9cc8 = UPDATE helpdesk_tickets ht, helpdesk_status_master hsm SET ht.status_id = (SELECT status_id FROM helpdesk_status_master WHERE is_closed = 'y') WHERE ht.submit_date DATE_ADD(curdate(), INTERVAL -7 day) AND ht.status_id = hsm.status_id AND hsm.is_closed = 'y' AND hsm.final_closed = 'n' thd-thread_id=237423 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 237423 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 040414 13:34:27 mysqld restarted 040414 13:34:27 Can't start server: Bind on TCP/IP port: Address already in use 040414 13:34:27 Do you already have another mysqld server running on port: 3306 ? 040414 13:34:27 Aborting 040414 13:34:27 /usr/local/mysql/bin/mysqld: Shutdown Complete 040414 13:34:27 mysqld ended 040414 13:35:13 mysqld started 040414 13:35:14 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 157493553 InnoDB: Doing recovery: scanned up to log sequence number 0 157493553 040414 13:35:14 InnoDB: Flushing modified pages from the buffer pool... 040414 13:35:14 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.1.0-alpha-standard-log' socket: '/tmp/mysql.sock' port: 040414 13:36:07 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space Any help is appreciated. Thanks Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
