Re: Intersting MySQL / Access Issue
Raj Goel wrote: Dan, Here's what I've narrowed it down to: We're updating FROM Access TO MySql. If the column in Access is blank/empty, then the error pops up. If the field has data, then the error does not pop up. So, somewhere, either Access, or MyODBC or MySql is interpreting empty columns incorrectly. :) Didn't take the original hint, eh? If you want meaningful tech support, you have to give *FAR* more information than that. Rest assured that we have used Access connected to MySQL for 8 years without issue. You're doing *something* wrong. But if you don't post *details* to the list, how are people supposed to know what you're doing? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Combining several sum queries
When i run the queries below they all work just fine SELECT sum(consultation)+ sum(laboratory) FROM nairobi,familymembers WHERE familymembers.dependantid = nairobi.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM riftvalley,familymembers WHERE familymembers.dependantid = riftvalley.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM coast,familymembers WHERE familymembers.dependantid = coast.memberid and familymembers.memberid = AKI1 But i would like to run the querys above as one query that will return a value that will be equivalent to the sum of the values of those queries. i hope its possible. is it? i am using MySQL version 4.1.7 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Shifting dates
From: Keith Ivey Jigal van Hemert wrote: Maybe because dates before Jan 1, 1970 have an undefined timestamp and dates beyond 2038 cannot be used with 32-bit integers? Quite a few people were born before 1970 and sometimes one needs to store their date of birth too? Yes, but birthdates are generally DATE, not DATETIME, unless you're doing astrology. Sebastian was talking about DATETIME versus Unix timestamp INT. You can find imperfections in all examples. Fact remains that the range of a Unix timestamp is way too limited for many purposes. OTOH Unix timestamps were designed for timestamps related to files and in that the case the range is not much of a limit. I find that for certain applications that DATE and DATETIME both lack a large enough range and sufficient resolution. Historical dates BC cannot be stored, nor can you use a resolution of less than a second. Also the problems with DATE(TIME) and daylight saving are not very amusing. Still the larger range and all the functions available for handling DATE(TIME) values make me use them more often than unix timestamps. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing Per-Table-Tablespaces
David, David Griffiths wrote: The manual is a little unclear on per-table-tablespaces in 4.1/5.0 (http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html) Using per-table-tablespaces ignores the innodb_data_file_path (yes, it uses it for the ibdata files, but not for the tablespace/data-files for the individual tables). It doesn't talk about the relationship between the per-tables-tablespaces and the innodb_data_file_path (or just as importantly the lack of relationship between the two). That would all be fine, except the same page also states, Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of the remaining InnoDB tables. How do you move a table (thus the tablespace) to a seperate disk? It implies that different tables and their related tablespaces can be put on different disks, but doesn't really get into the specifics. you have to symlink the innodb table file: move it to the disk you want, then create a symlink in the correct database directory that points towards the new location. See: http://www.mysqluc.com/pub/w/35/sessions.html New InnoDB Features David Regards, Frank. -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql overall stability
Stephane, I've was wondering if anyone is using MySql as their main ERP production database, if so how stable and reliable is it? SAP and MySQL have teamed up to certify (part of?) SAP's R/3 ERP system on MaxDB. Check: http://searchsap.techtarget.com/originalContent/0,289142,sid21_gci967139 ,00.html. The page is a year old, and I have no idea how far they have progressed by now. -- Martijn ASML ITMS Application Support / Webcenter -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql overall stability
Martijn van den Burg wrote: Stephane, I've was wondering if anyone is using MySql as their main ERP production database, if so how stable and reliable is it? SAP and MySQL have teamed up to certify (part of?) SAP's R/3 ERP system on MaxDB. Check: http://searchsap.techtarget.com/originalContent/0,289142,sid21_gci967139 ,00.html. The page is a year old, and I have no idea how far they have progressed by now. -- Martijn ASML ITMS Application Support / Webcenter What is the relationship between mysql and maxdb. (thats Mysql the software not the company). Are they basicly the same software or is Maxdb a totally different thing? --Zach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a small request
sir, i am a software employee working in a firm.. i am not able to create any tables in to my mysql server .. plz suggest me the possible reasons for it... thanking u , c.krishna chaitanya -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Decimal comma in input
Hallo, I get data with a format that I can't choose. The fields are enclosed in double quotes (), separated by commas (,). The main problem is that some fields contain amounts with a decimal comma. I have not found a way yet to load these data properly. Could somebody help me please? Groetjes, Hans. --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql overall stability
Zachary Kessin [EMAIL PROTECTED] wrote on 22/06/2005 09:12:22: Martijn van den Burg wrote: Stephane, I've was wondering if anyone is using MySql as their main ERP production database, if so how stable and reliable is it? SAP and MySQL have teamed up to certify (part of?) SAP's R/3 ERP system on MaxDB. Check: http://searchsap.techtarget.com/originalContent/0,289142,sid21_gci967139 ,00.html. The page is a year old, and I have no idea how far they have progressed by now. -- Martijn ASML ITMS Application Support / Webcenter What is the relationship between mysql and maxdb. (thats Mysql the software not the company). Are they basicly the same software or is Maxdb a totally different thing? MaxDB is a totally different thing to the main MySQL Database, though MySQL AB is atring to converge the SQL dialects to make them more interchangeable. See http://dev.mysql.com/doc/mysql/en/maxdb-history.html To respond to the original question, I would not know about ERP in particular, but a lot of people are using MySQL in demanding, mission critical systems. I think most users would classify the production versions of MySQL as very stable indeed. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Combining several sum queries
On 6/22/05, David Kagiri wrote: When i run the queries below they all work just fine SELECT sum(consultation)+ sum(laboratory) FROM nairobi,familymembers WHERE familymembers.dependantid = nairobi.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM riftvalley,familymembers WHERE familymembers.dependantid = riftvalley.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM coast,familymembers WHERE familymembers.dependantid = coast.memberid and familymembers.memberid = AKI1 But i would like to run the querys above as one query that will return a value that will be equivalent to the sum of the values of those queries. i hope its possible. is it? i am using MySQL version 4.1.7 SELECT sum(consultation)+ sum(laboratory) FROM familymembers fm INNER JOIN coast ON fm.dependantid = coast.memberid INNER JOIN riftvalley ON fm.dependantid = riftvalley.memberid INNER JOIN nairobi ON fm.dependantid = nairobi.memberid WHERE fm.memberid = AKI1 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
On 6/21/05, Sebastian wrote: i never understand why people use datetime anyway.. unix timestamp is so much easier to work with. Unix epoch is by definition UTC. Sometimes I want to work with dates in some local timezone. In other databases that have a more complete implementation of the SQL standard you can do really neat tricks with that. Just look at the following examples from PostgreSQL: jochemd= select '2005-06-15 00:00:00'; ?column? 2005-06-15 00:00:00 jochemd= select '2005-06-15 00:00:00' AT TIME ZONE 'PDT'; timezone - 2005-06-14 17:00:00 jochemd= set TimeZone = 'EST'; jochemd= select '2005-06-15 00:00:00'; ?column? - 2005-06-15 00:00:00 jochemd= select '2005-06-15 00:00:00' AT TIME ZONE 'PDT'; timezone - 2005-06-14 22:00:00 While I can't use this functionality in MySQL (yet?), I use it enough in other databases to always use a timestamp datatype instead of a epoch to keep code as uniform as possible. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Combining several sum queries
Jochem van Dieten [EMAIL PROTECTED] wrote on 06/22/2005 07:34:30 AM: On 6/22/05, David Kagiri wrote: When i run the queries below they all work just fine SELECT sum(consultation)+ sum(laboratory) FROM nairobi, familymembers WHERE familymembers.dependantid = nairobi.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM riftvalley, familymembers WHERE familymembers.dependantid = riftvalley.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM coast,familymembers WHERE familymembers.dependantid = coast.memberid and familymembers. memberid = AKI1 But i would like to run the querys above as one query that will return a value that will be equivalent to the sum of the values of those queries. i hope its possible. is it? i am using MySQL version 4.1.7 SELECT sum(consultation)+ sum(laboratory) FROM familymembers fm INNER JOIN coast ON fm.dependantid = coast.memberid INNER JOIN riftvalley ON fm.dependantid = riftvalley.memberid INNER JOIN nairobi ON fm.dependantid = nairobi.memberid WHERE fm.memberid = AKI1 Jochem I hate to be the bearer of bad news but unless there is a very special arrangement of the data in these tables, that query is doomed to failure. It will fail because of HOW joined tables are actually joined. I will try to be brief (you will need to un-wrap some of this to help it make sense) Here is some sample data and an example why this query probably won't work: familymembers (dependantid) AKI1 coast (memberid, consultation, laboratory) AKI1, 4, 4 AKI1, 4, 15 riftvalley (memberid, consultation, laboratory) AKI1, 2, 6 AKI1, 2, 9 SELECT * FROM familymembers fm INNER JOIN coast c ON fm.dependantid = coast.memberid INNER JOIN riftvalley r ON fm.dependantid = riftvalley.memberid WHERE fm.dependantid = 'AKI1'; -- (fm.dependantid, c.memberid, c.consultation, c.laboratory, r.memberid, r.consultation, r.laboratory) -- AKI1,AKI1, 4, 4, AKI1, 2, 6 AKI1,AKI1, 4, 4, AKI1, 2, 9 AKI1,AKI1, 4, 15, AKI1, 2, 6 AKI1,AKI1, 4, 15, AKI1, 2, 9 First, the column names in Jigal's query will be ambiguous as both `coast` and `riftvalley` provide columns called `consultation` and `laboratory` to the query. Second, because of the Cartesian product used to compute the JOINed dataset, you wind up with duplicates of the rows from both `coast` and `riftvalley`. The final SUM() could be off by some multiple based on the numbers of rows in each table. This is a tricky problem to solve because it requires a SUM() of a SUM(). The results of the first SUM() queries, I guess you could refer to them as subtotals, are computed on a per-table basis (each table representing a service area) and provide a single row of data as their result (you could expand the query to use a GROUP BY and process multiple `dependantid`s at once but that would just muddy this particular example). These subtotal rows need to be added together to produce a grand total. The store the subtotals in a temporary table method is the one most compatible with the most MySQL installations (especially with those that do not have subqueries and anonymous views) (I will revert to using the original queries AND I will rewrite them to NOT use the comma-separated, implicit INNER JOIN syntax) The first step is to collect all of your regional subtotals into a single list. I chose to use a temporary table: CREATE TEMPORARY TABLE tmpTotals SELECT nairobi.memberid, sum(consultation)+ sum(laboratory) as subtotal FROM nairobi INNER JOIN familymembers ON familymembers.dependantid = nairobi.memberid WHERE familymembers.memberid = AKI1; INSERT tmpTotals (memberid, subtotal) SELECT coast.memberid, sum(consultation)+ sum(laboratory) as subtotal FROM coast INNER JOIN familymembers ON familymembers.dependantid = coast.memberid WHERE familymembers.memberid = AKI1; INSERT tmpTotals (memberid, subtotal) SELECT riftvalley.memberid, sum(consultation)+ sum(laboratory) as subtotal FROM riftvalley INNER JOIN familymembers ON familymembers.dependantid = riftvalley.memberid WHERE familymembers.memberid = AKI1; (NOTE: it would have been possible to use a single UNION query and reduce this step to a single statement but I don't know if David has UNIONs available) Now it's academic to use the data in the temporary table to provide the grand total SELECT memberid, sum(subtotal) as grandtotal FROM tmpTotals GROUP BY memberid; Of course, when you are through with it, you should always destroy a temporary table (don't rely on your OS or MySQL to do it for you, it may never get around to it and you could run low on resources) DROP TABLE tmpTotals; I know David
Re: Combining several sum queries
On Wed, 22 Jun 2005 09:55:39 -0400, [EMAIL PROTECTED] said: Jochem van Dieten [EMAIL PROTECTED] wrote on 06/22/2005 07:34:30 AM: On 6/22/05, David Kagiri wrote: When i run the queries below they all work just fine SELECT sum(consultation)+ sum(laboratory) FROM nairobi, familymembers WHERE familymembers.dependantid = nairobi.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM riftvalley, familymembers WHERE familymembers.dependantid = riftvalley.memberid and familymembers.memberid = AKI1 SELECT sum(consultation)+ sum(laboratory) FROM coast,familymembers WHERE familymembers.dependantid = coast.memberid and familymembers. memberid = AKI1 But i would like to run the querys above as one query that will return a value that will be equivalent to the sum of the values of those queries. i hope its possible. is it? i am using MySQL version 4.1.7 SELECT sum(consultation)+ sum(laboratory) FROM familymembers fm INNER JOIN coast ON fm.dependantid = coast.memberid INNER JOIN riftvalley ON fm.dependantid = riftvalley.memberid INNER JOIN nairobi ON fm.dependantid = nairobi.memberid WHERE fm.memberid = AKI1 Jochem Excuse my mathematical impreciseness, but when you say sum - could you use UNION? I've patched together many queries using UNION with useable results - would that help? See: http://www.w3schools.com/sql/sql_union.asp Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a small request
Hello. There could be a lot of reasons. First, are you able to connect to MySQL using mysql command line client? If you are able then what error do you see? Krishna Chaitanya [EMAIL PROTECTED] wrote: sir, i am a software employee working in a firm.. i am not able to create any tables in to my mysql server .. plz suggest me the possible reasons for it... thanking u , c.krishna chaitanya -- 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: myisam insta corruption in 4.1.12
Hello.
Looks like a bug for me. I've reported it at:
http://bugs.mysql.com/bug.php?id=11494
David Sparks [EMAIL PROTECTED] wrote:
db1 corruption # cat my.sql
DROP TABLE IF EXISTS service_contacts;
CREATE TABLE service_contacts (
croeated datetime NOT NULL default '-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO service_contacts VALUES ('2006-06-14 10:27:40');
db1 corruption # mysqladmin -u root -p create test1
Enter password:
db1 corruption # mysql -u root -p test1 my.sql
Enter password:
db1 corruption # /etc/init.d/mysql stop
* Stopping mysqld (/etc/mysql/my.cnf) ...
[ ok ]
db1 corruption # myisamchk /var/lib/mysql/test1/*MYI
Checking MyISAM file: /var/lib/mysql/test1/service_contacts.MYI
Data records: 1 Deleted blocks: 0
- check file-size
myisamchk: warning: Datafile is almost full, 9 of 7 used
- check record delete-chain
- check key delete-chain
- check index reference
MyISAM-table '/var/lib/mysql/test1/service_contacts.MYI' is usable but
should be fixed
--%--
1 row in a 1 column table and it is already corrupt. Yikes! The
problem is related to this my.cnf setting:
myisam_data_pointer_size = 8
I seem to be having this problem on x86 (Gentoo + SuSE), x86_64 (Gentoo
+ Debian) and also using the Mysql binary build static glibc 2.2.
Does anyone understand what is going on under the hood here?
Thanks,
ds
--
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]
MySQL Connector/J 3.1.9 Has Been Released
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
MySQL Connector/J 3.1.9, a new version of the Type-IV all-Java JDBC
driver for MySQL has been released.
Version 3.1.9 is the latest production release of the 3.1 series that is
suitable for use with all versions of MySQL, including MySQL-4.1 or
MySQL-5.0. Connector/J 3.1.9 is required if you want to use server-side
prepared statements with MySQL-4.1.2 or newer, or CallableStatements
with MySQL-5.0.
Sources and binaries are now available from the Connector/J
download pages at http://dev.mysql.com/downloads/connector/j/3.1.html
as well as mirror sites (note that not all mirror sites may be up to
date at this point of time - if you can't find this version on some
mirror, please try again later or choose another download site.)
If you are upgrading from Connector/J 3.0, or are upgrading from
MySQL-4.0 to MySQL-4.1 or 5.0, please make sure to check out the
'Upgrades' section in the documentation that comes with the driver, or
available on the web site at
http://dev.mysql.com/doc/connector/j/en/cj-upgrading-3-0-to-3-1.html
-Mark
- From the changelog:
06-22-05 - Version 3.1.9-stable
- Overhaul of character set configuration, everything now
lives in a properties file.
- Driver now correctly uses CP932 if available on the server
for Windows-31J, CP932 and MS932 java encoding names,
otherwise it resorts to SJIS, which is only a close
approximation. Currently only MySQL-5.0.3 and newer (and
MySQL-4.1.12 or .13, depending on when the character set
gets backported) can reliably support any variant of CP932.
- Fixed BUG#9064 - com.mysql.jdbc.PreparedStatement.ParseInfo
does unnecessary call to toCharArray().
- Fixed Bug#10144 - Memory leak in ServerPreparedStatement if
serverPrepare() fails.
- Actually write manifest file to correct place so it ends up
in the binary jar file.
- Added createDatabaseIfNotExist property (default is false),
which will cause the driver to ask the server to create the
database specified in the URL if it doesn't exist. You must have
the appropriate privileges for database creation for this to
work.
- Fixed BUG#10156 - Unsigned SMALLINT treated as signed for
ResultSet.getInt(), fixed all cases for UNSIGNED integer values and
server-side prepared statements, as well as ResultSet.getObject() for
UNSIGNED TINYINT.
- Fixed BUG#10155, double quotes not recognized when parsing
client-side prepared statements.
- Made enableStreamingResults() visible on
com.mysql.jdbc.jdbc2.optional.StatementWrapper.
- Made ServerPreparedStatement.asSql() work correctly so auto-explain
functionality would work with server-side prepared statements.
- Made JDBC2-compliant wrappers public in order to allow access to
vendor extensions.
- Cleaned up logging of profiler events, moved code to dump a profiler
event as a string to com.mysql.jdbc.log.LogUtils so that third
parties can use it.
- DatabaseMetaData.supportsMultipleOpenResults() now returns true. The
driver has supported this for some time, DBMD just missed that fact.
- Fixed BUG#10310 - Driver doesn't support {?=CALL(...)} for calling
stored functions. This involved adding support for function retrieval to
DatabaseMetaData.getProcedures() and getProcedureColumns() as well.
- Fixed BUG#10485, SQLException thrown when retrieving YEAR(2)
with ResultSet.getString(). The driver will now always treat YEAR types
as java.sql.Dates and return the correct values for getString().
Alternatively, the yearIsDateType connection property can be set to
false and the values will be treated as SHORTs.
- The datatype returned for TINYINT(1) columns when
tinyInt1isBit=true (the default) can be switched between Types.BOOLEAN
and Types.BIT using the new configuration property
transformedBitIsBoolean, which defaults to false. If set to false
(the default), DatabaseMetaData.getColumns() and
ResultSetMetaData.getColumnType() will return Types.BOOLEAN for
TINYINT(1) columns. If true, Types.BOOLEAN will be returned instead.
Irregardless of this configuration property, if tinyInt1isBit is
enabled, columns with the type TINYINT(1) will be returned as
java.lang.Boolean instances from ResultSet.getObject(..), and
ResultSetMetaData.getColumnClassName() will return java.lang.Boolean.
- Fixed BUG#10496 - SQLException is thrown when using property
characterSetResults with cp932 or eucjpms.
- Reorganized directory layout, sources now in src folder,
don't pollute parent directory when building, now output goes to
./build, distribution goes to ./dist.
- Added
[Trying to install/setup mysql 4.1.12 on aix]
Description: When trying to install or setup mysql running the script/mysql_install_db --usr=mysql command I get errors and says that my hostname cannot be resoved even though hostname and uname shows the hostname just fine and i can ping and talk back and forth with no issues and yes the hostname is in the /etc/hosts file. How-To-Repeat: [/usr/local/mysql] $ scripts/mysql_install_db --user=mysql exec(): 0509-036 Cannot load program ./bin/my_print_defaults because of the following errors: 0509-130 Symbol resolution failed for my_print_defaults because: 0509-136 Symbol _isinf (number 54) is not exported from dependent module /usr/lib/libc.a(shr.o). 0509-192 Examine .loader section symbols with the 'dump -Tv' command. Neither host 'nmaeg2' nor 'localhost' could be looked up with ./bin/resolveip Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option mp2396_on_nmaeg2[/usr/local/mysql] $ hostname nmaeg2 Fix: Submitter-Id: submitter ID Originator:Mike Perugini/816-275-3626 Organization: MySQL support: [email support | extended email support ] Synopsis: cannot install mysql (one line) Severity: Priority: Category: mysql Class: Release: mysql-4.1.12-standard (MySQL Community Edition - Standard (GPL)) C compiler: C for AIX Compiler, Version 6 C++ compiler:C for AIX Compiler, Version 6 Environment: System: AIX nmaeg2 1 5 85404C00 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/cc Compilation info: CC='xlc_r' CFLAGS='-ma -O2 -qstrict -qoptimize=2 -qmaxmem=8192' CXX='xlC_r' CXXFLAGS='-ma -O2 -qstrict -qoptimize=2 -qmaxmem=8192' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 bin bin 19 Jun 09 14:20 /lib/libc.a - /usr/ccs/lib/libc.a lrwxrwxrwx 1 bin bin 19 Jun 09 14:20 /usr/lib/libc.a - /usr/ccs/lib/libc.a 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' '--with-named-z-libs=no' '--disable-shared' '--with-readline' '--with-embedded-server' '--with-archive-storage-engine' '--with-innodb' 'CC=xlc_r' 'CFLAGS=-ma -O2 -qstrict -qoptimize=2 -qmaxmem=8192' 'CXXFLAGS=-ma -O2 -qstrict -qoptimize=2 -qmaxmem=8192' 'CXX=xlC_r' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing Mysql beta on Debian
Hi, I've been successfully using mysql 5.0.x on my win32 development machine. I would like to install it on a server running stable Debian. Unfortunately, there are no .deb packages for the MySQL 5.0 series. I have tried to use the linux precompiled binaries but my debian complains about not being able to connect using /var/run/.../mysqld.sock and the /etc/init.d/mysql start script will tell me something went wrong. I have accidentally deleted /etc/mysql/debian-start and replaced it with an empty file :(. Now, what I would like to do is to either 1) use the precompiled binaries without breaking apt's database (ie overwriting the files that already exist) and using canonical debian path conventions. 2) compile by myself, but I *really* need some help with all the options ./configure provides, in order, again, to use the debian patch conventions. thanks in advance, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Intersting MySQL / Access Issue
When I have seen this error it was caused by a field defined in the MySQL database as NOT NULL in the Create table and the value in Access is NULL or usually for us an empty field in EXCEL which is appears to be intreped as NULL when you do a PASTE APPEND. Ours is often times a datetime field but I don't think its limited to data time fields defined as NOT NULL. I have not really found an easy wasy to find the offending field except by process of elimination. Hope this helps. -Original Message- From: Edward Maas [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 21, 2005 10:31 PM To: mysql@lists.mysql.com Subject: Intersting MySQL / Access Issue Dear Community, My team and I have been experiencing an interesting mysql error during the past few weeks of testing. Here is the scenario we are trying to accomplish. We are essentially working to use MsAccess as a windows client for a linux based mysql databases. We have installed myODBC 3.51 and are using that for communication. We seem to be able to create a linked table just find and select queries work great. The problem arises when we try to update or insert data. Updates yield the following error: You Tried to assign the Null value to a variable that is not a Variant data type. From my searching, I was unable to find how to set fields to variant data types. Secondly, I am not sure which field is causing the error. The second issue was with inserts. On insert of just one field (none are required other than the primary key), ALL of the fields of type text are set to NULL. This is particularly odd and occurs also in the mysql command line utilty. If anyone has any ideas or experience, please send emails to [EMAIL PROTECTED] We will definitely summarize the solution for the educaitonal purposes of the list. Again many thanks in advance. Sincerely,, Ed Maas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.0.17 Has Been Released
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
MySQL Connector/J 3.0.17, a new version of the Type-IV all-Java JDBC
driver for MySQL has been released.
Version 3.0.17is a bugfix release for the stable tree that is
suitable for use with any MySQL version including MySQL-4.1 or MySQL-5.0
(although it provides minimal 'new' functionality with MySQL-4.1 or
MySQL-5.0, users connecting to MySQL-4.1 or 5.0 should be using
Connector/J 3.1.x).
It is now available in source and binary form from the Connector/J
download pages at http://dev.mysql.com/downloads/connector/j/3.0.html
and mirror sites (note that not all mirror sites may be up to date at
this point of time - if you can't find this version on some mirror,
please try again later or choose another download site.)
-Mark
- From the changelog:
06-22-05 - Version 3.0.17-ga
- Fixed BUG#5874, Timestamp/Time conversion goes in the wrong
'direction' when useTimeZone='true' and server timezone differs from
client timezone.
- Fixed BUG#7081, DatabaseMetaData.getIndexInfo() ignoring 'unique'
parameter.
- Support new protocol type 'MYSQL_TYPE_VARCHAR'.
- Added 'useOldUTF8Behavoior' configuration property, which causes JDBC
driver to act like it did with MySQL-4.0.x and earlier when the
character encoding is 'utf-8' when connected to MySQL-4.1 or newer.
- Fixed BUG#7316 - Statements created from a pooled connection were
returning physical connection instead of logical connection when
getConnection() was called.
- Fixed BUG#7033 - PreparedStatements don't encode Big5 (and other
multibyte) character sets correctly in static SQL strings.
- Fixed BUG#6966, connections starting up failed-over (due to down
master) never retry master.
- Fixed BUG#7061, PreparedStatement.fixDecimalExponent() adding
extra '+', making number unparseable by MySQL server.
- Fixed BUG#7686, Timestamp key column data needed _binary'
stripped for UpdatableResultSet.refreshRow().
- Backported SQLState codes mapping from Connector/J 3.1, enable
with 'useSqlStateCodes=true' as a connection property, it defaults to
'false' in this release, so that we don't break legacy applications (it
defaults to 'true' starting with Connector/J 3.1).
- Fixed BUG#7601, PreparedStatement.fixDecimalExponent() adding
extra '+', making number unparseable by MySQL server.
- Escape sequence {fn convert(..., type)} now supports ODBC-style
types that are prepended by 'SQL_'.
- Fixed duplicated code in configureClientCharset() that prevented
useOldUTF8Behavior=true from working properly.
- Handle streaming result sets with 2 billion rows properly by
fixing wraparound of row number counter.
- Fixed BUG#7607 - MS932, SHIFT_JIS and Windows_31J not recog. as
aliases for sjis.
- Fixed BUG#6549 (while fixing #7607), adding 'CP943' to aliases for
sjis.
- Fixed BUG#8064, which requires hex escaping of binary data when
using multibyte charsets with prepared statements.
- Fixed BUG#8812, NON_UNIQUE column from DBMD.getIndexInfo()
returned inverted value.
- Workaround for server BUG#9098 - default values of CURRENT_* for
DATE/TIME/TIMESTAMP/TIMESTAMP columns can't be distinguished from
'string' values, so UpdatableResultSet.moveToInsertRow() generates bad
SQL for inserting default values.
- Fixed BUG#8629 - 'EUCKR' charset is sent as 'SET NAMES euc_kr'
which MySQL-4.1 and newer doesn't understand.
- DatabaseMetaData.supportsSelectForUpdate() returns correct value
based on server version.
- Use hex escapes for PreparedStatement.setBytes() for double-byte
charsets including 'aliases' Windows-31J, CP934, MS932.
- Added support for the EUC_JP_Solaris character encoding, which
maps to a MySQL encoding of eucjpms (backported from 3.1 branch). This
only works on servers that support eucjpms, namely 5.0.3 or later.
- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCuZe7tvXNTca6JD8RAjFpAJsE551UG0OGNftW78r0JZiPaIIQhwCeM4rA
xEl/cfq+xSkgmKugLlthaG0=
=L+Wi
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing Per-Table-Tablespaces
Frank, thanks for the reply. I'd thought of that, but I was worried about using HotBackup (we use this for all of our backups on our production machines) - the hot backup manual at http://www.innodb.com/manual.php doesn't mention if it can follow a symlink to the data file. Thanks for the link - some very interesting presentations there (wish I had gone to the conference - next year maybe). David Dr. Frank Ullrich wrote: David, David Griffiths wrote: The manual is a little unclear on per-table-tablespaces in 4.1/5.0 (http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html) Using per-table-tablespaces ignores the innodb_data_file_path (yes, it uses it for the ibdata files, but not for the tablespace/data-files for the individual tables). It doesn't talk about the relationship between the per-tables-tablespaces and the innodb_data_file_path (or just as importantly the lack of relationship between the two). That would all be fine, except the same page also states, Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of the remaining InnoDB tables. How do you move a table (thus the tablespace) to a seperate disk? It implies that different tables and their related tablespaces can be put on different disks, but doesn't really get into the specifics. you have to symlink the innodb table file: move it to the disk you want, then create a symlink in the correct database directory that points towards the new location. See: http://www.mysqluc.com/pub/w/35/sessions.html New InnoDB Features David Regards, Frank. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Choosing between FULLTEXT INDEX and regular INDEX
I have a table which includes the following columns in addition to lots of other ones name - populated with just one name city - populated with just one city keywords - lots of keywords I'm definitely going to use a FULLTEXT on the `keywords` column For `name` and `city` ...I will allow users to search on one name and one city. Should I just stick to regular indices for those two columns and use ...WHERE name LIKE '%bob%' OR city LIKE '%montreal%' Will FULLTEXT indices for these two columns give me any sort of advantage, such as performance boosts? I know that if I use FULLTEXT indices on `name` I would need it to index words that are 2 characters and above! The reason is that there are names such as `ed` or `bo` that will be lost if I don't. And I can't turn this on a column to column basis. If I set ft_min_word_length = 2, that will make ALL FULLTEXT indices handle 2 chars and above. This will make the index files huge no? -- -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Hello Anoop, On 22 Jun 05, Anoop kumar V wrote to All: AkV Are u saying that u have data in a text file and you need to parse AkV this and insert them into mysql? That's what I Ntried to say, yes :) The main problem is the decimal comma in the amounts. Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
From: [EMAIL PROTECTED] That's what I Ntried to say, yes :) The main problem is the decimal comma in the amounts. What about reading the data into the table and storing the 'amount' in varchar for now. Then you can run an update query in which you replace the decimal comma with a decimal point and store that in the float field (emptying the varchar in the process). UPDATE `table` SET `amount`= REPLACE( `v_amount`, ',' , '.'), `v_amount` = NULL WHERE `v_amount` IS NOT NULL; Works like a charm ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding row by value of a certain length
I've been cruising the docs for a while now and can't find what I'm looking for. I know it has soemthing to do with value or LEN or something easy like that but I just can't find the right command structure. I need to list the rows in a table where the length of a field, lets say field1 is a minimum of 60 characters or larger. The field type is varchar. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Possible to DoS a slave by using multiple connections on the master!.
Something doesn't sound like it's setup properly. There was a time that I had more then 100 slaves connected to a master. Many reads and writes occurred on the masters and all slaves kept up. I suspect that your running out of IO bandwidth on a slave from a table scan or huge and frequent sorts. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Kevin Burton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 21, 2005 6:01 PM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Possible to DoS a slave by using multiple connections on the master!. Not sure if this is a known issue or not.. but I haven't seen it documented anywhere. Anyway. My past thinking was that you should always use as many connections as you have tables (at least with myisam). This way in the worst case scenario you could have locks open on all tables instead of one lock on one table holding back all other tables. This is a BAD idea if you're trying to reach max qps with INSERTs. What happens is that the disk controller is able to command queue (and other opterations) to optimize IO on the master since technically you have multiple INSERTs happening at once (one for each table). Then on the slave since there's only one thread replaying the DML it will back up since the disk controller isn't able to optimize the IO. We were actually running a master with RAID5 and a slave with RAID0 and the slave still couldn't keep up. The problem was only fixed when we told our client to only use one connection. While this is a temporary fix this limits the scalability of MySQL as I could easily see a LOT more QPS going through these boxes. It might be possible place an intelligent scheduler to bulk up INSERTS and use FK relationships to allow non-dependent SQL to pass forward. You could also have one thread per slave per connection on the master. Then in the binary log you could flag the thread ID that performed the modification on the master and use the same thread on the slave. The downside being that you'd need more resources on SLAVE boxes. Seems like a wiki page in the making -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- 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: Installing Mysql beta on Debian
Hello. It is recommended to use precompiled binaries in most cases. MySQL has a very flexible system of parameters which you could change through different ways. If you want to use this server as dedicated database server, probably the solution exists (I mean integrating MySQL binaries without breaking apt's database). But if you want to use other programs which requires MySQL shared libraries the problem becomes harder. [EMAIL PROTECTED] wrote: Hi, I've been successfully using mysql 5.0.x on my win32 development machine. I would like to install it on a server running stable Debian. Unfortunately, there are no .deb packages for the MySQL 5.0 series. I have tried to use the linux precompiled binaries but my debian complains about not being able to connect using /var/run/.../mysqld.sock and the /etc/init.d/mysql start script will tell me something went wrong. I have accidentally deleted /etc/mysql/debian-start and replaced it with an empty file :(. Now, what I would like to do is to either 1) use the precompiled binaries without breaking apt's database (ie overwriting the files that already exist) and using canonical debian path conventions. 2) compile by myself, but I *really* need some help with all the options ./configure provides, in order, again, to use the debian patch conventions. thanks in advance, Phil -- 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: Finding row by value of a certain length
Ed Curtis [EMAIL PROTECTED] wrote on 06/22/2005 02:27:02 PM: I've been cruising the docs for a while now and can't find what I'm looking for. I know it has soemthing to do with value or LEN or something easy like that but I just can't find the right command structure. I need to list the rows in a table where the length of a field, lets say field1 is a minimum of 60 characters or larger. The field type is varchar. Thanks, Ed You were SO close!!! SELECT field list FROM table references WHERE CHAR_LENGTH(varcharfield) = 60; http://dev.mysql.com/doc/mysql/en/string-functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Finding row by value of a certain length
On Wed, 22 Jun 2005 [EMAIL PROTECTED] wrote: You were SO close!!! SELECT field list FROM table references WHERE CHAR_LENGTH(varcharfield) = 60; Thanks so much. I knew I was close but couldn't remember the exact command. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Alternatively, you can parse the text files using application logic (java, c++, etc) and then after extracting (and cleaning) your data insert them into mysql. matter of fact we do something very similar - read from a host of text files - massage the data and then send them to be inserted into the db. HTH, Anoop On 6/22/05, Jigal van Hemert [EMAIL PROTECTED] wrote: From: [EMAIL PROTECTED] That's what I Ntried to say, yes :) The main problem is the decimal comma in the amounts. What about reading the data into the table and storing the 'amount' in varchar for now. Then you can run an update query in which you replace the decimal comma with a decimal point and store that in the float field (emptying the varchar in the process). UPDATE `table` SET `amount`= REPLACE( `v_amount`, ',' , '.'), `v_amount` = NULL WHERE `v_amount` IS NOT NULL; Works like a charm ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlshow question
Hi everyone, My /usr/local/mysql/bin/mysqlshow --host=xxx--user=xxx --password= command hangs. I can ping host from local machine I can locally execute mysqlshow command on the host. I can locally execute mysqlshow command on remote server My environment is UNIX with MySQL 4.1.xx installed on both local server and host. Any help is greatly appreciated, Mikhail Berman
strange database grant to mysql database
Hallo, after creating a user via the grant command, I found that this new user can see by the 'show database' command the mysql database although the grant does not apply to it. This new user can run the 'use mysql' command, but has no access to the tables. The db table only show access grants to non mysql databases. What has went wrong here and how can I solve the problem? mysql version 4.0.16. regards Harald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange database grant to mysql database
Harald Falkenberg wrote: Hallo, after creating a user via the grant command, I found that this new user can see by the 'show database' command the mysql database although the grant does not apply to it. This new user can run the 'use mysql' command, but has no access to the tables. The db table only show access grants to non mysql databases. What has went wrong here and how can I solve the problem? mysql version 4.0.16. regards Harald Hi Harald, that is quite easy. Using the grant command sets the privileges in the user table (which is one of the security tables). read the info at: http://dev.mysql.com/doc/mysql/en/privilege-system.html to understand the privilege system in MySql. you have to revoke all privileges in the user table and insert a record into the db table where you can define the database you want the user to be granted on. Best Regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlshow question
Berman, Mikhail wrote: Hi everyone, My /usr/local/mysql/bin/mysqlshow --host=xxx--user=xxx --password= command hangs. I can ping host from local machine I can locally execute mysqlshow command on the host. I can locally execute mysqlshow command on remote server My environment is UNIX with MySQL 4.1.xx installed on both local server and host. Any help is greatly appreciated, Mikhail Berman Hi Berman, sorry if i might ask some simple questions ... but can you logon remotely at all(?), using the client tools like: - mysql -u username -ppassword -h host -D database - mysqladmin -u username -ppassword -h host flush-tables are you granted for a remote logon? does your mysql-client tools hang as well? the statement show a no-space between '--host=xxx--user=xxx' it should be '--host=xxx --user=xxx' or are you aware of that? Best Regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Choosing between FULLTEXT INDEX and regular INDEX
I did a little test by configure the `name` and `city` with FULLTEXT
INDEX and a regular INDEX.
mysql describe testing_text_performance;
+---+--+--+-+---++
| Field | Type | Null | Key | Default |
Extra |
+---+--+--+-+---++
| id| int(10) unsigned | | PRI | NULL |
auto_increment |
| name | varchar(100) | | MUL | |
|
| city | varchar(100) | | MUL | |
|
| age | tinyint(3) | | | 0 |
|
| timestamp | timestamp| YES | | CURRENT_TIMESTAMP |
|
+---+--+--+-+---++
5 rows in set (0.00 sec)
select count(*) from testing_text_performance where match(name)
against ('marge'); -- average of about 1.25 sec
select count(*) from testing_text_performance where name like
'%marge%'; -- average of about 0.35 sec
The FULLTEXT searches were about 3 times as slow. For 120,000 rows
Also I found that with the two fields the performance between a
regular INDEX and NO INDEX were pretty much the same...I measured the
times...with about 120,000 rows, they both took about .35 seconds.
mysql explain select count(*) from testing_text_perf_no_index where
name like '%marge%';
++-++--+---+--+-+--++-+
| id | select_type | table | type |
possible_keys | key | key_len | ref | rows | Extra |
++-++--+---+--+-+--++-+
| 1 | SIMPLE | testing_text_perf_no_index | ALL | NULL
| NULL |NULL | NULL | 120015 | Using where |
++-++--+---+--+-+--++-+
1 row in set (0.00 sec)
mysql explain select count(*) from testing_text_performance where
name like '%marge%';
++-+--+---+---+--+-+--++--+
| id | select_type | table| type | possible_keys
| key | key_len | ref | rows | Extra|
++-+--+---+---+--+-+--++--+
| 1 | SIMPLE | testing_text_performance | index | NULL
| name | 100 | NULL | 120015 | Using where; Using index |
++-+--+---+---+--+-+--++--+
1 row in set (0.00 sec)
So based on this test it seems that I should either use a regular
INDEX on `name` or NO INDEX at all. Does this sound right?
Shouldn't an INDEX help performance?
-James
At 1:06 PM -0400 6/22/05, James wrote:
I have a table which includes the following columns in addition to
lots of other ones
name - populated with just one name
city - populated with just one city
keywords - lots of keywords
I'm definitely going to use a FULLTEXT on the `keywords` column
For `name` and `city` ...I will allow users to search on one name
and one city.
Should I just stick to regular indices for those two columns and use
...WHERE
name LIKE '%bob%' OR
city LIKE '%montreal%'
Will FULLTEXT indices for these two columns give me any sort of
advantage, such as performance boosts?
I know that if I use FULLTEXT indices on `name` I would need it to
index words that are 2 characters and above! The reason is that
there are names such as `ed` or `bo` that will be lost if I don't.
And I can't turn this on a column to column basis. If I set
ft_min_word_length = 2, that will make ALL FULLTEXT indices handle 2
chars and above. This will make the index files huge no?
--
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
-James
Re: Sarge problems with MySQL and DBI / DBD::mysql
John Trammell wrote:
#!perl
use strict;
use warnings;
sub foo {
warn wantarray() in foo() is: , wantarray(), \n;
my @r = 3 .. 8;
return @r;
}
my @x = foo() or warn in ... or warn() #1\n;
warn [EMAIL PROTECTED]: @x\n;
my $x = foo() or warn in ... or warn() #2\n;
warn \$x: $x\n;
Perhaps I'm just being dense, but how is this relevant?
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Choosing between FULLTEXT INDEX and regular INDEX
Responses interspersed
James [EMAIL PROTECTED] wrote on 06/22/2005 04:48:59 PM:
I did a little test by configure the `name` and `city` with FULLTEXT
INDEX and a regular INDEX.
mysql describe testing_text_performance;
+---+--+--+-+---
++
| Field | Type | Null | Key | Default |
Extra |
+---+--+--+-+---
++
| id| int(10) unsigned | | PRI | NULL |
auto_increment |
| name | varchar(100) | | MUL | |
|
| city | varchar(100) | | MUL | |
|
| age | tinyint(3) | | | 0 |
|
| timestamp | timestamp| YES | | CURRENT_TIMESTAMP |
|
+---+--+--+-+---
++
5 rows in set (0.00 sec)
select count(*) from testing_text_performance where match(name)
against ('marge'); -- average of about 1.25 sec
select count(*) from testing_text_performance where name like
'%marge%'; -- average of about 0.35 sec
The FULLTEXT searches were about 3 times as slow. For 120,000 rows
You should have also tried:
select count(*) from testing_text_performance where name like
'marge%';
and
select count(*) from testing_text_performance where name =
'marge';
Also I found that with the two fields the performance between a
regular INDEX and NO INDEX were pretty much the same...I measured the
times...with about 120,000 rows, they both took about .35 seconds.
mysql explain select count(*) from testing_text_perf_no_index where
name like '%marge%';
++-++--
+---+--+-+--++-+
| id | select_type | table | type |
possible_keys | key | key_len | ref | rows | Extra |
++-++--
+---+--+-+--++-+
| 1 | SIMPLE | testing_text_perf_no_index | ALL | NULL
| NULL |NULL | NULL | 120015 | Using where |
++-++--
+---+--+-+--++-+
1 row in set (0.00 sec)
mysql explain select count(*) from testing_text_performance where
name like '%marge%';
++-+--+---
+---+--+-+--++--+
| id | select_type | table| type | possible_keys
| key | key_len | ref | rows | Extra|
++-+--+---
+---+--+-+--++--+
| 1 | SIMPLE | testing_text_performance | index | NULL
| name | 100 | NULL | 120015 | Using where; Using index |
++-+--+---
+---+--+-+--++--+
1 row in set (0.00 sec)
So based on this test it seems that I should either use a regular
INDEX on `name` or NO INDEX at all. Does this sound right?
Shouldn't an INDEX help performance?
Indexes work exactly like a telphone book (some people call them
directories). If want to find the entries for people whose last name is or
begins with the letters Jam (like James or Jameson) it's pretty easy
because the information is sorted that way. However, if you wanted to find
all of the names that contained or ended with the letters 'son' (like
Jameson, Carson, Deesonay,...) you would have to look at each and every
name in the book so that you can tease out those inner matches.
If you want to do exact matches or prefix matches (words that are or start
with...), INDEXES are the perfect tool for the job. However if you are
always doing substring matches, an INDEX cannot help. The engine would
have to search every record for a match, just as you would have to search
the entire phone book to find those names.
There is a technique for creating an index to handle searches of the ends
with type. Store the information reversed and index the column of
backwards text. Then when you want to look for things that end with son,
you search the backwards column for words that _start_ with nos. The
penalty you pay is the space it takes to store and maintain the reversed
string and extra index but it helps to optimize another type of substring
search.
Normal and reversed-text indexes cover 2 of the 3 types of substring
searches. The third kind looks for information anywhere else in the string
(in the middle). So far, there is not an indexing strategy to optimize
that kind of search available to use with MySQL (that I know of...) Does
anyone else out there know of one?
-James
At 1:06 PM -0400 6/22/05, James wrote:
I have a table which
Not sure about Error
Greetings! I run the following query against MySQL: SELECT demographic_no, first_name, last_name, chart_no, sex, year_of_birth, month_of_birth, date_of_birth, family_doctor, roster_status, patient_status, phone FROM demographic JOIN demographic ON allergies.demographic_no = demographic.demographic_no JOIN drugs ON demographic.demographic_no = drugs.demographic_no JOIN dxresearch ON drugs.demographic_no = dxresearch.demographic_no JOIN echart ON dxresearch.demographic_no = echart.demographicNo JOIN ichppccode ON dxresearch.dxresearch_no = ichppccode.ichppccode WHERE allergies.description LIKE '%CARBACHOL%' MySQL returns the following error: Not unique table/alias: 'demographic' What exactly is meant by this statement :( Thank you in advance! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select performance
Hi, I use MySQL for years with very much respect of it's stability and performance. But in these years one of my servers has grown to several hundreds of databases with approximately 50 tables in each of the databases. Some of these database have tables containing more than a million records, which will still grow. Actually I expect them to grow even to more than 10 million records. Storing these records is no problem, but querying them becomes more and more difficult within a reasonable time. The total size of all databases is about 40 GB and will grow constantly. So, the MySQL-cluster seems no solutions since all data must be kept into memory. I am looking for a scalable solution where I can put more hardware, if necessary, for more performance. Is there some way to loadbalance select-queries over multiple servers? When I look at the google-technology, they have a lot of machines, each of them storing some chunks of data. So each server handles just a little piece of the request. It would be great if there is such technique for MySQL, splitting up the databases in chunks over several machines. Firing a query should result in a query to all machines, which return their results. The master-process collects all chunks of data from the machines and returns the total results to my application. If I run into performance trouble in the future, it should be a matter of placing some more hardware to solve the problem. Any ideas, suggestions or solutions? Thanx. El.
Re: Not sure about Error
In the last episode (Jun 23), Schalk Neethling said: I run the following query against MySQL: SELECT demographic_no, first_name, last_name, chart_no, sex, year_of_birth, month_of_birth, date_of_birth, family_doctor, roster_status, patient_status, phone FROM demographic JOIN demographic ON allergies.demographic_no = demographic.demographic_no Do you maybe mean FROM allergies JOIN demographic ON allergies.demographic_no ... ? JOIN drugs ON demographic.demographic_no = drugs.demographic_no JOIN dxresearch ON drugs.demographic_no = dxresearch.demographic_no JOIN echart ON dxresearch.demographic_no = echart.demographicNo JOIN ichppccode ON dxresearch.dxresearch_no = ichppccode.ichppccode WHERE allergies.description LIKE '%CARBACHOL%' -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not sure about Error
Dan Yes, I completely overlooked that. Thanks! Dan Nelson wrote: In the last episode (Jun 23), Schalk Neethling said: I run the following query against MySQL: SELECT demographic_no, first_name, last_name, chart_no, sex, year_of_birth, month_of_birth, date_of_birth, family_doctor, roster_status, patient_status, phone FROM demographic JOIN demographic ON allergies.demographic_no = demographic.demographic_no Do you maybe mean FROM allergies JOIN demographic ON allergies.demographic_no ... ? JOIN drugs ON demographic.demographic_no = drugs.demographic_no JOIN dxresearch ON drugs.demographic_no = dxresearch.demographic_no JOIN echart ON dxresearch.demographic_no = echart.demographicNo JOIN ichppccode ON dxresearch.dxresearch_no = ichppccode.ichppccode WHERE allergies.description LIKE '%CARBACHOL%' -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select performance
At 05:32 PM 6/22/2005, you wrote: Hi, I use MySQL for years with very much respect of it's stability and performance. But in these years one of my servers has grown to several hundreds of databases with approximately 50 tables in each of the databases. Some of these database have tables containing more than a million records, which will still grow. Actually I expect them to grow even to more than 10 million records. Storing these records is no problem, but querying them becomes more and more difficult within a reasonable time. The total size of all databases is about 40 GB and will grow constantly. So, the MySQL-cluster seems no solutions since all data must be kept into memory. I am looking for a scalable solution where I can put more hardware, if necessary, for more performance. Is there some way to loadbalance select-queries over multiple servers? When I look at the google-technology, they have a lot of machines, each of them storing some chunks of data. So each server handles just a little piece of the request. It would be great if there is such technique for MySQL, splitting up the databases in chunks over several machines. Firing a query should result in a query to all machines, which return their results. The master-process collects all chunks of data from the machines and returns the total results to my application. If I run into performance trouble in the future, it should be a matter of placing some more hardware to solve the problem. Any ideas, suggestions or solutions? Thanx. El. El, Show us some of your queries and table structures. How many rows are returned? Are you trying to return all 10 million rows at once? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.1.10 Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.1.10, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. This has been released shortly after 3.1.9 to fix the following issue that we noticed after release: - - Fixed connecting without a database specified raised an exception in MysqlIO.changeDatabaseTo(). We apologize to those of you that make connections without specifying a database, as this would've caused problems for you :( -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCuicMtvXNTca6JD8RAuS2AKCMO+xk2tokuy6yPcLH0ZXXth85RACdGuHW S1A28upvYDFZ9yhjvMf7F9Q= =7Be1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting more sum()
Hi, I have the following tables: create table articles( id int unsigned not null primary key, title varchar(255) not null, body text not null ); create table newspapers( id int unsigned not null primary key, name varchar(255) not null ); create table visitors( id int unsigned not null primary key, id_articles int unsigned not null ); create table comments( id int unsigned not null primary key, id_articles int unsigned not null ); I would like to select: - the title from `articles` - the length of the body from `articles` - the name of the newspaper which correspond to the title of the article - the number of visitors each articles have (count(*) from visitors where articles.id=visitors.id_articles) - the number of comments each articles have (count(*) from comments where articles.id=comments.id_articles) I don't know how to select the last 2 elements (the number of visitors and the number of comments). I want to select all the articles from `articles` even if there are no visitors or no comments in the `visitors` and `comments` tables, so I might need using left join. I have tried a few ways of doing this, but without any result. Please help me if you can. (I have written the table definitions above right in the email client, so they are not tested, but I think they are correct) Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select performance
hi, you didn't speakabout your tuning work on the databases with only one machine. Have you done such work with the indexation part for best performance ? I can tell you that several databases with about 40 Go for all is not huge. but if you want look at some linux clustering solutions like openmosix. It's not a mysql load-balancing solutionn but a linux software one. personnaly, i advice you to begin by indexation and maybe denormalization, sumarry tables, ... tunig :o) Mathias Selon El Bunzo [EMAIL PROTECTED]: Hi, I use MySQL for years with very much respect of it's stability and performance. But in these years one of my servers has grown to several hundreds of databases with approximately 50 tables in each of the databases. Some of these database have tables containing more than a million records, which will still grow. Actually I expect them to grow even to more than 10 million records. Storing these records is no problem, but querying them becomes more and more difficult within a reasonable time. The total size of all databases is about 40 GB and will grow constantly. So, the MySQL-cluster seems no solutions since all data must be kept into memory. I am looking for a scalable solution where I can put more hardware, if necessary, for more performance. Is there some way to loadbalance select-queries over multiple servers? When I look at the google-technology, they have a lot of machines, each of them storing some chunks of data. So each server handles just a little piece of the request. It would be great if there is such technique for MySQL, splitting up the databases in chunks over several machines. Firing a query should result in a query to all machines, which return their results. The master-process collects all chunks of data from the machines and returns the total results to my application. If I run into performance trouble in the future, it should be a matter of placing some more hardware to solve the problem. Any ideas, suggestions or solutions? Thanx. El. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
