Re: Problem restarting server
Mike, - Original Message - From: Mike Blezien [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 22, 2006 3:42 AM Subject: Problem restarting server Hello, we had to reboot our server and now we can't get MySQL started, in the error log it states: == 060321 19:34:13 mysqld started 060321 19:34:13 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: Error: log file ./ib_logfile0 is of different size 0 67108864 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes! 060321 19:34:13 [ERROR] Can't init databases 060321 19:34:13 [ERROR] Aborting 060321 19:34:13 [Note] /usr/sbin/mysqld: Shutdown complete 060321 19:34:13 mysqld ended = How can this problem be fixed so we can restart MySQL server, kind of in a bind here now... mysqld is probably reading a different my.cnf file now than it did when you started mysqld last time. Have you edited my.cnf or replaced it while mysqld was running? Or could mysqld fail to find a my.cnf file altogether? You should determine where your my.cnf is, and where your ibdata files and ib_logfiles are, and specify in my.cnf appropriately: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html innodb_data_file_path innodb_log_group_home_dir innodb_log_files_in_group innodb_log_file_size TIA, Mike Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query and version problem .... Help!
It's working ok .. But I have one problem .. I'm getting the following error Table 'max_bids3' already exists; but I get the following ... mysql DROP TABLE max_bids3; ERROR 1051 (42S02): Unknown table 'max_bids3' What do I do to fix this, and how can I see the temp tables ? On 3/21/06, Addison, Mark [EMAIL PROTECTED] wrote: -Original Message- From: Gregory Machin [mailto:[EMAIL PROTECTED] Sent: 21 March 2006 11:28 To: mysql@lists.mysql.com Subject: mysql query and version problem Help! Hi. I have just found out that my hosting provider is using mysql 4 and I'm using mysql 5 the one query I need wont work and is a key feature in the application .. here is the query i'm using SELECT dealer_id, auto_id, bid_amount FROM bids b1 WHERE bid_amount=(SELECT MAX(b2.bid_amount) FROM bids b2 WHERE b1.auto_id = b2.auto_id) AND auto_dealer_id = '3' AND Bid_Status = '1'; How do I get this to work on version 4 ? You could create a tmp table with the max bids and then join on that. Something like: CREATE TEMPORARY TABLE max_bids SELECT auto_id, MAX(bid_amount) AS max_bid_amount FROM bids GROUP BY auto_id; SELECT dealer_id, b1.auto_id, bid_amount FROM bids b1, max_bids b2 WHERE b1.auto_id = b2.auto_id AND bid_amount=max_bid_amount AND auto_dealer_id = '3' AND Bid_Status = '1'; mark -- Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096
symbolic-links mysql 4 error on ubuntu 5.10
hi all, I read http://dev.mysql.com/doc/refman/5.0/en/symbolic-links-to-databases.html but on MySQL 4.0.24_Debian-10ubuntu2 server , in my my.conf file: datadir = /var/lib/mysql ls show as： lrwxrwxrwx 1 mysql mysql 67 2006-03-12 22:42 mnogosearch3235 - /home/username/mnt/cobd/temp_mysql_datadir/datadir/mnogosearch3235/ after reboot mysql server,i cannot find the DB:mnogosearch3235. and if i change mysql my.conf: datadir = /home/username/mnt/cobd/temp_mysql_datadir/datadir/ then it runs work! but if chang as this : -rw-r- 1 mysql mysql 8998 2006-03-21 17:00 url.frm lrwxrwxrwx 1 mysql mysql 76 2006-03-21 16:41 url.MYD - /home/username/mnt/cobd/temp_mysql_tabledir/tabledir/mnogosearch3235/url.MYD -rw-r- 1 mysql mysql 47276032 2006-03-21 17:08 url.MYI show me following error: Error: 'DB err: MySQL driver: #1105: File '/home/username/mnt/cobd/temp_mysql_tabledir/tabledir/mnogosearch3235/url.MYD' not found (Errcode: 13) [EMAIL PROTECTED]:~/var/temp_mysql_datadir$ sudo ls -l /home/username/mnt/cobd/temp_mysql_tabledir/tabledir/mnogosearch3235/url.MYD -rw-rw 1 mysql mysql 47498160 2006-03-21 15:18 /home/username/mnt/cobd/temp_mysql_tabledir/tabledir/mnogosearch3235/url.MYD what`s wrong?anybody can help me? -- View this message in context: http://www.nabble.com/symbolic-links-mysql-4-error-on-ubuntu-5.10-t1322625.html#a3528642 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb in 4.1.18
Bill Adams wrote: InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate. well, thats what I found before. But it doesn't explain why InnoDB does need a logfile even when all transactions are committed or rolled back and mysql is shut down. More is stored in the innodb log files besides a strict log of events. It does explain, that I can use innodb_file_per_table which gives the impression that with that option a shared ibdata isn't required. Or maybe it is required during runtime, but not for a backup. You might be able to set e.g. innodb_data_file_path = ibdata:0M but in any event the innodb log files track the existence of the different ibdata files (size, if they have been formatted, etc). You always have to have the central tablespace, whether you are using file per table or not. First and foremost, for your backup purposes, *it holds data dictionary information* (as well as within the frm files within each database), plus undo/rollback segment space and the adaptive hash index - even when using file per table. So with InnoDB it is not possible to shutdown the database server, backup the files and maybe use them on another server if the other server already has an ibdata and ib_logfile? Do I always have to do a mysqldump then? That takes much longer. You can. But direct file system operations on MySQL (really any database) are dangerous. Same thing with why IMHO you should use PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); rather than just deleting your binary logs directly (if you are using replication and all that). If you are going to copy the InnoDB files you probably need to have an exact setup. And as I am sure you have figured out you have to back up the .idb files and the innodb log files at the same time. AFAIK, You cannot take the .idb file from one server and copy it to another server and have it just work (like you can with the MyISAM files). Correct, to move any InnoDB tables between machines you need: o All ib data files o All ib logfiles o All related database directories and frm files o Your my.cnf/ini http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html Regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query and version problem .... Help!
You don't need to drop a TEMPORARY table... it is dropped at connection-close ! You don't need to wory about different names for TEMPORARY tables... Manual says: A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. Good luck !
Re: Problem restarting server
Yes, that was the problem, the my.cnf file was missing, replace it, restarted and all is well :) thx's Mike - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 22, 2006 2:10 AM Subject: Re: Problem restarting server Mike, - Original Message - From: Mike Blezien [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 22, 2006 3:42 AM Subject: Problem restarting server Hello, we had to reboot our server and now we can't get MySQL started, in the error log it states: == 060321 19:34:13 mysqld started 060321 19:34:13 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: Error: log file ./ib_logfile0 is of different size 0 67108864 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes! 060321 19:34:13 [ERROR] Can't init databases 060321 19:34:13 [ERROR] Aborting 060321 19:34:13 [Note] /usr/sbin/mysqld: Shutdown complete 060321 19:34:13 mysqld ended = How can this problem be fixed so we can restart MySQL server, kind of in a bind here now... mysqld is probably reading a different my.cnf file now than it did when you started mysqld last time. Have you edited my.cnf or replaced it while mysqld was running? Or could mysqld fail to find a my.cnf file altogether? You should determine where your my.cnf is, and where your ibdata files and ib_logfiles are, and specify in my.cnf appropriately: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html innodb_data_file_path innodb_log_group_home_dir innodb_log_files_in_group innodb_log_file_size TIA, Mike Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- 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: ~BCP for mysql~
Try Using Select into outfile More Info : http://dev.mysql.com/doc/refman/5.0/en/select.html --Praj On Wed, 22 Mar 2006 12:59:44 +0530 Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, Can we any use bcp to perform bulk copy for MYSQL as in SYBASE ? If not, is there any other tool which can do the same operation. I need to perform a bcp out from Mysql and then bcp in to Sybase. Please help me out regarding the same. Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OFAC SDN lists
repost your question, this time giving an explanation (complete with MySQL version, platform, queries and results) as to how it doesn't work. -Sheeri On 3/15/06, Ing. Edwin Cruz [EMAIL PROTECTED] wrote: Hi folks! I'm asking in a web form for clients, those clients I have to search them into OFAC SDN lists( http://www.ustreas.gov/offices/enforcement/ofac/sdn/delimit/index.shtml), if they are into that list they wont be able to do transactions in my system. My problem is to perform a good search of the names into that list, I had thought in fulltext, but it appears to not work. Fulltext works with short names. does anyone have any suggestion for this? Regards! Edwin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching of german umlauts with LIKE
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, what is the best way to match german umlauts like 'ä' also their alternative writing 'ae'? For example I'm searching for übersee and I also want to find the word uebersee in the database. The words are actually names of persons. One possibility is to dynamically expand the SQL statement if such special characters are found. So the search term übersee will be expanded to SELECT * FROM person WHERE name LIKE 'übersee%' AND name LIKE 'uebersee%' but this is getting dirty and very very long if multiple umlauts are used to cover all cases ... So the other idea is to have the name twice in the database for every person and the second version of the name is a normalized for where all special characters are replaced with their alternative writing. E.g. I store the field name übersee and also name2 uebersee and when matching I match against name2. If the field would container more special characters it still would work without much more work, e.g. name is überseemöbel then name2 would be ueberseemoebel and when the term überseemö is entered it's also normalized to ueberseemoe and the LIKE statement will still match. Basically this is some kind of primitive stemming like lucene does it. Is there maybe some built-in support from MySQL for such special cases? thanks for any pointers, - - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEIWDH1nS0RcInK9ARAkzyAKCyoPPVd1YRfhs1p/p8kY465/QPVQCfa5uj r2ZarPZvsJp5FPNDsdhAN7E= =5ADZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query and version problem .... Help!
Gregory I thought about that but I'm worried about the users getting the rite data if multiple users make the same requests at the same time ... i supose the easiest would be to name the temp tables after the user making the request ?? A temp table is visible only in the thread where it's been created, and vanishes when the connection closes. PB - Gregory Machin wrote: On 3/21/06, Addison, Mark [EMAIL PROTECTED] wrote: -Original Message- From: Gregory Machin [mailto:[EMAIL PROTECTED] Sent: 21 March 2006 11:28 To: mysql@lists.mysql.com Subject: mysql query and version problem Help! Hi. I have just found out that my hosting provider is using mysql 4 and I'm using mysql 5 the one query I need wont work and is a key feature in the application .. here is the query i'm using SELECT dealer_id, auto_id, bid_amount FROM bids b1 WHERE bid_amount=(SELECT MAX(b2.bid_amount) FROM bids b2 WHERE b1.auto_id = b2.auto_id) AND auto_dealer_id = '3' AND Bid_Status = '1'; How do I get this to work on version 4 ? You could create a tmp table with the max bids and then join on that. Something like: CREATE TEMPORARY TABLE max_bids SELECT auto_id, MAX(bid_amount) AS max_bid_amount FROM bids GROUP BY auto_id; SELECT dealer_id, b1.auto_id, bid_amount FROM bids b1, max_bids b2 WHERE b1.auto_id = b2.auto_id AND bid_amount=max_bid_amount AND auto_dealer_id = '3' AND Bid_Status = '1'; mark -- I thought about that but I'm worried about the users getting the rite data if multiple users make the same requests at the same time ... i supose the easiest would be to name the temp tables after the user making the request ?? No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/287 - Release Date: 3/21/2006
INSERT...SELECT Query Help Request.
Dear All, First of all, I would like to thank Shawn Green, Peter Brawley and Josh for their kind help in my previous issue. I have a table named master_list with two field customer_id and list_code. I need to insert only non existent records in master_list from a child_list. Both master_list and child_list table structure are identical but data in child_list may contain records from master_list and new records. I have constructed query using INSERT SELECT but I am unable to check and filter out records that already exist in master_list. INSERT INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list Is there anyway to check and insert records in master_list without creating dups? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
Re: INSERT...SELECT Query Help Request.
Yesmin Patwary [EMAIL PROTECTED] wrote on 03/22/2006 09:56:20 AM: Dear All, First of all, I would like to thank Shawn Green, Peter Brawley and Josh for their kind help in my previous issue. I have a table named master_list with two field customer_id and list_code. I need to insert only non existent records in master_list from a child_list. Both master_list and child_list table structure are identical but data in child_list may contain records from master_list and new records. I have constructed query using INSERT…SELECT but I am unable to check and filter out records that already exist in master_list. INSERT INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list Is there anyway to check and insert records in master_list without creating dups? Thanks in advance for any help. If you have a unique key or primary key set up on (master_list.customer_id, master_list.list_code) it's more simple that you think. Just add the word IGNORE to your INSERT statement like this :-) INSERT IGNORE INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list usage details are here: http://dev.mysql.com/doc/refman/5.0/en/insert.html The IGNORE will tell the engine to disregard all duplicate key errors and continue processing rows. If you don't have such a key, I suggest you add one or let us know why you can't create it. Which workaround we can use for the lack of the key will depend on the version you are using. You are most welcome! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: OFAC SDN lists
Ok, and sorry for my poor explanation and my spanglish
The ofac list is a database with thousands of names of persons who are
forbidden to do transactions like change of dollars to mexican pesos, they
are trying to avoid money laundering, so if somebody try to do a
transaction he have to be searched into ofac list, but the problem is that
the name can be formatted in diferents ways like 'Edwin Cruz' 'Cruz Edwin'
'Edwin E. Cruz' and so on
I've tryed this:
Select * from OFACSDN where match(name) against ('edwin cruz');
And it returns more than 20 names that contain one or both words and i want
to improve my search...
An example:
SELECT NameSDN FROM tofacsdn t where nombre like '%cruz%';
+--+
| NameSDN |
+--+
| CRUZ REYES, Antonio Pedro|
| CRUZ, Juan M. de la |
| PEREZ CRUZ, Osvaldo |
| SANTACRUZ LONDONO, Jose |
| CAVIEDES CRUZ, Leonardo |
| SANTACRUZ CASTRO, Ana Milena |
| CASTRO DE SANTACRUZ, Amparo |
| CASTRILLON CRUZ, Maria Leonor|
| RUELAS MARTINEZ, Jose de la Cruz |
| SANTA CRUZ IMPERIAL AIRLINES |
+--+
10 rows in set (0.02 sec)
How do I have to perform a search in that list with my name 'Edwin Cruz', if
I try with full text I get this:
SELECT NameSDN from tofacsdn WHERE MATCH(nombre) AGAINST ('Edwin cruz')
+--+
| NameSDN |
+--+
| MUGUTI, Edwin|
| PARRA VELASCO, Edwin Hiulder |
| MUTASA, Didymus Noel Edwin |
| CRUZ, Juan M. de la |
| CAVIEDES CRUZ, Leonardo |
| PEREZ CRUZ, Osvaldo |
| CASTRILLON CRUZ, Maria Leonor|
| RUELAS MARTINEZ, Jose de la Cruz |
| SANTA CRUZ IMPERIAL AIRLINES |
| CRUZ REYES, Antonio Pedro|
+--+
10 rows in set (0.01 sec)
The closest result that I want is with this query:
SELECT NameSDN from tofacsdn WHERE NameSDN like '%Edwin%' and NameSDN like
'%Cruz%'
But it isn't healthy because what abaut if I provide my name like 'Edwin
C.', I dont know what I am going to do with this... In the worst case I'll
program a script to build a query like above.
I'm using MySQL 5.0.18, php 5.0.5
-Mensaje original-
De: sheeri kritzer [mailto:[EMAIL PROTECTED]
Enviado el: Miércoles, 22 de Marzo de 2006 08:31 a.m.
Para: Ing. Edwin Cruz
CC: Mysql
Asunto: Re: OFAC SDN lists
repost your question, this time giving an explanation (complete with MySQL
version, platform, queries and results) as to how it doesn't work.
-Sheeri
On 3/15/06, Ing. Edwin Cruz [EMAIL PROTECTED] wrote:
Hi folks! I'm asking in a web form for clients, those clients I have
to search them into OFAC SDN lists(
http://www.ustreas.gov/offices/enforcement/ofac/sdn/delimit/index.shtm
l), if they are into that list they wont be able to do transactions in
my system.
My problem is to perform a good search of the names into that list, I
had thought in fulltext, but it appears to not work. Fulltext works
with short names.
does anyone have any suggestion for this?
Regards!
Edwin.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: OFAC SDN lists
Ing. Edwin Cruz [EMAIL PROTECTED] wrote on 03/22/2006 11:38:53 AM:
Ok, and sorry for my poor explanation and my spanglish
The ofac list is a database with thousands of names of persons who are
forbidden to do transactions like change of dollars to mexican pesos,
they
are trying to avoid money laundering, so if somebody try to do a
transaction he have to be searched into ofac list, but the problem is
that
the name can be formatted in diferents ways like 'Edwin Cruz' 'Cruz
Edwin'
'Edwin E. Cruz' and so on
I've tryed this:
Select * from OFACSDN where match(name) against ('edwin cruz');
And it returns more than 20 names that contain one or both words and i
want
to improve my search...
An example:
SELECT NameSDN FROM tofacsdn t where nombre like '%cruz%';
+--+
| NameSDN |
+--+
| CRUZ REYES, Antonio Pedro|
| CRUZ, Juan M. de la |
| PEREZ CRUZ, Osvaldo |
| SANTACRUZ LONDONO, Jose |
| CAVIEDES CRUZ, Leonardo |
| SANTACRUZ CASTRO, Ana Milena |
| CASTRO DE SANTACRUZ, Amparo |
| CASTRILLON CRUZ, Maria Leonor|
| RUELAS MARTINEZ, Jose de la Cruz |
| SANTA CRUZ IMPERIAL AIRLINES |
+--+
10 rows in set (0.02 sec)
How do I have to perform a search in that list with my name 'Edwin
Cruz', if
I try with full text I get this:
SELECT NameSDN from tofacsdn WHERE MATCH(nombre) AGAINST ('Edwin cruz')
+--+
| NameSDN |
+--+
| MUGUTI, Edwin|
| PARRA VELASCO, Edwin Hiulder |
| MUTASA, Didymus Noel Edwin |
| CRUZ, Juan M. de la |
| CAVIEDES CRUZ, Leonardo |
| PEREZ CRUZ, Osvaldo |
| CASTRILLON CRUZ, Maria Leonor|
| RUELAS MARTINEZ, Jose de la Cruz |
| SANTA CRUZ IMPERIAL AIRLINES |
| CRUZ REYES, Antonio Pedro|
+--+
10 rows in set (0.01 sec)
The closest result that I want is with this query:
SELECT NameSDN from tofacsdn WHERE NameSDN like '%Edwin%' and NameSDN
like
'%Cruz%'
But it isn't healthy because what abaut if I provide my name like 'Edwin
C.', I dont know what I am going to do with this... In the worst case
I'll
program a script to build a query like above.
I'm using MySQL 5.0.18, php 5.0.5
8 snip 8
Fulltext searches are what you need but you will need to change both the
stop word list and the minimum length parameter. That way you don't filter
out the shorter name parts like de and la. In this list almost nothing
you have is noise so you really need to turn off that part of the full
text indexing engine.
Another thing to try is the BOOLEAN operators that you can use when you do
a search IN BOOLEAN MODE...
I think you are on the right track. However, If for some reason the built
in fulltext indexing doesn't work for you or can't be configured to work
for you, you may need to create your own FT index. It's not as hard as it
may sound with data like what you have. Here are the basic components:
a) a table of all of the words appearing in any indexed column -- in your
situation, all you have are names. This table would hold (as single words)
each part of every name (without any punctuation). Most languages have a
function to split a string into an array based on some delimiter (like a
space) so this should be easy to do.
b) a table linking each of the words in the table from a) with a record in
your source table along with the position of the word in the source table.
It's a simple two-column table and should be very fast to search. If a
word appears more than once in the source record, it gets more than one
record in this table. You typically build this as you build the first
table.
c) an optional stemming table - this is where you can create a table to
expand or contract a name or a name part into other recognizable forms
(like misspelled names into their proper spelling)
d) a searching routine that queries the table from a) for matches to your
base terms and your stemmed terms then uses that list to make a list of
all of the records in the source table (by matching the first list to the
table in b) ). This will give you a list of how many matches occurred for
each source record. Run a count() query on this list to see how many
matches were returned per source row. Order the results by # of matches in
descending order. You could even throw out all single hits as noise
matches.
Anyway, that means that you are now in charge of your own FT index but
this one will be tuned to your particular searching needs. The one built
into MySQL is tuned better for matching words in lots of longer text
fields (like newspaper articles) than it is for searching lists of names.
The little bit of effort you put into building the
UDF help, convert BLOB to BIGINT
I'm in the process of writing my first UDF and would appreciate some help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF help, convert BLOB to BIGINT
David Godsey [EMAIL PROTECTED] wrote on 03/22/2006 01:21:07 PM:
I'm in the process of writing my first UDF and would appreciate some
help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message
)
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null,
char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I
want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
Accomplishing the impossible means only that the boss will add it to
your
regular duties.
David Godsey
C is not my strongest language but aren't you getting a null-terminated
string as args[0] ? What if you allocated a longlong and byte-copied the
bytes from args[0] into your longlong? Maybe something like...
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
char idx, *cArg
longlong tmplong, *plonglong ;
plonglong = tmplong;
cArg = (args-args[0]);
for(idx=0;idx8;idx++) {
plonglong[idx]=cArg[idx];
}
return tmplong;
}
Again, I strongly stress that C/C++ is not my best language (I don't use
it nearly enough) but I think you can see what I was trying to do. Other
options: memcpy(), strcpy(), strncpy() etc
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Limit characters in a long text
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello all,
Have a question:
I would like to limit the amount of data viewed. I searched but did not
find anything.
Here is my code:
$get_c = select * from $table ORDER BY news_id DESC LIMIT 5;
$get_c_res = mysql_query($get_c) or die(mysql_error());
while ($c = mysql_fetch_array($get_c_res)) {
$news_id = $c['news_id'];
$news_posted = $c['news_posted'];
$news_subject = $c['news_subject'];
$NEWS_D = nl2br(stripslashes($c['news_desc']));
}
I would like to limit $NEWS_D to say 100 characters.
- --
Best regards,
~WILL~
Key: http://code-heads.com/keys/ch1.asc
Key: http://code-heads.com/keys/ch2.asc
Linux Commands: http://code-heads.com/commands
Linux Registered User: 406084 (http://counter.li.org/)
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iD8DBQFEIaDzfw3TK8jhZrsRAuZsAJ9S6QCvo6Pyru45qh1dzzANyD/OUgCgpuTG
RqcZaNUSgSA4TvHRusMzn18=
=P+Is
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection Buffer
Is there anything I can put into the my.cnf file to buffer connection (leave it open) to give a similar effect as connection pooling? Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF help, convert BLOB to BIGINT
Just figured it out without a UDF(not documented anywhere that I found).
SELECT conv(hex(fdata),16,10) INTO fdata_bigint;
So a double conversion seems to work for me.
You solution looks like it will work, but since I was able to get it to
work without a UDF, I'm not going to test it out. Thanks.
David Godsey
David Godsey [EMAIL PROTECTED] wrote on 03/22/2006 01:21:07 PM:
I'm in the process of writing my first UDF and would appreciate some
help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message
)
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null,
char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I
want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
Accomplishing the impossible means only that the boss will add it to
your
regular duties.
David Godsey
C is not my strongest language but aren't you getting a null-terminated
string as args[0] ? What if you allocated a longlong and byte-copied the
bytes from args[0] into your longlong? Maybe something like...
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
char idx, *cArg
longlong tmplong, *plonglong ;
plonglong = tmplong;
cArg = (args-args[0]);
for(idx=0;idx8;idx++) {
plonglong[idx]=cArg[idx];
}
return tmplong;
}
Again, I strongly stress that C/C++ is not my best language (I don't use
it nearly enough) but I think you can see what I was trying to do. Other
options: memcpy(), strcpy(), strncpy() etc
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Connection Buffer
I think you are asking for mysql_pconnect(localhost,root) -Original Message- From: Cory at SkyVantage [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 22, 2006 2:20 PM To: MySQL-List Subject: Connection Buffer Is there anything I can put into the my.cnf file to buffer connection (leave it open) to give a similar effect as connection pooling? Thanks in advance! -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit characters in a long text
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
I recommend LEFT( ).
If you are looking to do it in PHP then this is the wrong email list.
Good luck.
b.
CodeHeads wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello all,
Have a question:
I would like to limit the amount of data viewed. I searched but did not
find anything.
Here is my code:
$get_c = select * from $table ORDER BY news_id DESC LIMIT 5;
$get_c_res = mysql_query($get_c) or die(mysql_error());
while ($c = mysql_fetch_array($get_c_res)) {
$news_id = $c['news_id'];
$news_posted = $c['news_posted'];
$news_subject = $c['news_subject'];
$NEWS_D = nl2br(stripslashes($c['news_desc']));
}
I would like to limit $NEWS_D to say 100 characters.
- --
Best regards,
~WILL~
Key: http://code-heads.com/keys/ch1.asc
Key: http://code-heads.com/keys/ch2.asc
Linux Commands: http://code-heads.com/commands
Linux Registered User: 406084 (http://counter.li.org/)
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iD8DBQFEIaDzfw3TK8jhZrsRAuZsAJ9S6QCvo6Pyru45qh1dzzANyD/OUgCgpuTG
RqcZaNUSgSA4TvHRusMzn18=
=P+Is
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection Buffer
wait_timeout setting would leave the connection/thread open for the defined time even after the query has been executed.. Kishore Jalleda On 3/22/06, Cory at SkyVantage [EMAIL PROTECTED] wrote: Is there anything I can put into the my.cnf file to buffer connection (leave it open) to give a similar effect as connection pooling? Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit characters in a long text
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bill Adams wrote: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html I recommend LEFT( ). If you are looking to do it in PHP then this is the wrong email list. Good luck. Sorry about that I realized I picked the wrong mailing list after I sent it. Sorry again. - -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFEIfUIfw3TK8jhZrsRAgBJAKCu9eiZbHWAY2VSYfqrwuGuoT/9ewCgj5to U26Z/uD9hPZs9ukwREkMq64= =gi7p -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.18 restore dump file 'max_allowed_packet' error
Hi. I've got mysql 4.0.18 installed on a sun X4100 running solaris. This is just a 32 bit version of mysql. I've reverted back to this version after trying mysql 5, 4.0.26 and 4.0.18 64bit. Those versions were all unstable on 64bit, that is, the server would just go away for no apparent reason. This would ofter rear it's head when importing a dump file, the dump file I need to import is around 10 GIG, but also at other unpredictable times. I have this same database running on another machine running solaris, with no problems, except speed/performance. This other machine is the one that produces the dump file I'm trying to import. It does that with the following command: /usr/local/bin/mysqldump --opt --complete-insert --max_allowed_packet=32M rt3 | bzip2 -9 rt3.out-`date +\%Y\%m\%d-\%H`.bz2 I'm trying to restore the file on the new machine and I'm getting a 'max_allowed_packet' error: ERROR 1153 at line 162: Got a packet bigger than 'max_allowed_packet I've tried different settings for this in my.cnf, from 32 up to 1024M, and I still get the error. Is there anything I can do to remedy this? Kind regards. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Authenticating MySQL off of an LDAP
Hey everybody, I'm new to the lists, but I had a question about authenticating a MySQL database; I wondered if anybody knew how to authenticate off of an LDAP instead of the default mysql table? Or if it's even possible? So far we have had no success browsing the docs or anywhere else online. I know it's possible to go the other way, authenticate services off of a MySQL backend, so I guess I'm looking to go the opposite direction. Any thoughts anybody has would be much appreciated. Thanks. -Kyle
Re: Authenticating MySQL off of an LDAP
In the last episode (Mar 22), Kyle Johnson said: I'm new to the lists, but I had a question about authenticating a MySQL database; I wondered if anybody knew how to authenticate off of an LDAP instead of the default mysql table? Or if it's even possible? So far we have had no success browsing the docs or anywhere else online. I know it's possible to go the other way, authenticate services off of a MySQL backend, so I guess I'm looking to go the opposite direction. Any thoughts anybody has would be much appreciated. Thanks. This is bug 4703: http://bugs.mysql.com/bug.php?id=4703 , apparently on their TODO list somewhere. PAM authentication would be the best solution, which would let the administrator use whatever method they want. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database compatibility
Does anyone know of a resource ( on the web perhaps ) that discusses the core differences between the different database's sql. I'm trying to write code that produces the correct sql for a variety of databases. Such things as Oracle's SEQUENCES versus mysql's AUTO_INCREMENT are of interest. Maybe I'm asking too much to find a summary of such differences. But I'm only interested in using mainstream sql functinality, nothing complicated.
Re: database compatibility
That's a pretty difficult request: 1) What versions? MySQL 4.0 has no stored procedures, functions, views, subselects. 4.1 added subselects. 5.0 added the rest. Oracle 8i doesn't support ANSI-style outer joins (they use a proprietary format). 2) Different engines; MySQL supports federated, archive, MyISAM, InnoDB in 5.0 (there are probably a few others). Each engine has different DML (Data Manipulation Language - select, insert, update, delete) and DDL options (ALTER TABLE, CREATE TABLE, etc). 3) Built-in funtions vary widely (though there are some common ones, the format and structure can differ). That's just touching the surface (I have 5 minutes while a database machine reboots, so I thought I'd post a reply). I am not sure what you are after, but you might want to consider an existing ORM (Object-Relational) tool that does the SQL for you. Hibernate for Java is amazing, and NHibernate is now out for .NET (not sure if it's alpha, beta or production). If you are coding to experiment, I'd suggest you limit yourself to a few (MySQL-InnoDB is very popular, and Postgres). Both free, with lots of good online-documentation available. Check out this article: http://www.devx.com/dbzone/Article/20743 David ChadDavis wrote: Does anyone know of a resource ( on the web perhaps ) that discusses the core differences between the different database's sql. I'm trying to write code that produces the correct sql for a variety of databases. Such things as Oracle's SEQUENCES versus mysql's AUTO_INCREMENT are of interest. Maybe I'm asking too much to find a summary of such differences. But I'm only interested in using mainstream sql functinality, nothing complicated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql for freebsd 6.0
hi all... i can't see the mysql 5 version for freebsd 6.0 on the mysql developer site? am i blind or it's on purpose?!?! curious... and actually need it... thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database compatibility
I think it's better to pick one database and try to design and code to its strengths and weaknesses. I've worked on several projects in which the overall architectural plan involved writing a database abstraction layer to keep options open as to the specific relational db - MySQL, Oracle, MS SQL, etc. None of these projects has gone particularly well. I've worked on other projects where the focus was on making things work well with whatever database was chosen and not worrying so much about keeping the options open, and these projects have gone much better. And in neither case has the project actually ever switched databases, so the effort spent in trying to preserve compatibility was wasted. If you stick to the lowest common denominator in your SQL you won't be taking advantage of any of the unique benefits which the different servers bring. MySQL is my favorite database. For web work it has several significant advantages over other databases - the LIMIT clause alone is unique to MySQL and it's worth using MySQL just for that. For example, if you use MS SQL and .Net, the entire results of queries are sent from the DB server to the web server and pagination is done on the web server. This moves a lot more data over network connections, ties up more memory in the db and web server, and slows things down. With the LIMIT clause, only the rows to be displayed on the current page are sent over the wire. If you try to use only the ANSI standard features of MySQL you will miss out on this advantage. Here is an interesting article about Google's switch from MySQL to Oracle for AdWords: http://xooglers.blogspot.com/2005/12/lets-get- real-database.html I believe (can anyone from the big G confirm or correct?) that AdWords has been moved back to MySQL. Oracle has some neat features for handling trees and hierarchical data (CONNECT BY etc) which won't work in any other version of SQL. If you're going to use Oracle and you have data which is best represented in trees, it would be a mistake to not use Oracle's built- in tree features. Oracle is very nice, but it doesn't give you any speed over MySQL and you can put a man on the moon for less money. Microsoft SQL Server is very nice if you like that sort of thing and Sybase is pretty much the same thing only the port numbers have been changed to protect the innocent. I'm sure DB2 and Postgres and Informix and all the others are very nice too. Stored procedure syntax is significantly different among the major dbs, so if you're going to limit yourself to standard sql there's no point in using stored procedures. Which isn't really a bad thing on the whole. Good luck! On Mar 22, 2006, at 11:30 PM, David Griffiths wrote: That's a pretty difficult request: 1) What versions? MySQL 4.0 has no stored procedures, functions, views, subselects. 4.1 added subselects. 5.0 added the rest. Oracle 8i doesn't support ANSI-style outer joins (they use a proprietary format). 2) Different engines; MySQL supports federated, archive, MyISAM, InnoDB in 5.0 (there are probably a few others). Each engine has different DML (Data Manipulation Language - select, insert, update, delete) and DDL options (ALTER TABLE, CREATE TABLE, etc). 3) Built-in funtions vary widely (though there are some common ones, the format and structure can differ). That's just touching the surface (I have 5 minutes while a database machine reboots, so I thought I'd post a reply). I am not sure what you are after, but you might want to consider an existing ORM (Object-Relational) tool that does the SQL for you. Hibernate for Java is amazing, and NHibernate is now out for .NET (not sure if it's alpha, beta or production). If you are coding to experiment, I'd suggest you limit yourself to a few (MySQL-InnoDB is very popular, and Postgres). Both free, with lots of good online-documentation available. Check out this article: http://www.devx.com/dbzone/Article/20743 David ChadDavis wrote: Does anyone know of a resource ( on the web perhaps ) that discusses the core differences between the different database's sql. I'm trying to write code that produces the correct sql for a variety of databases. Such things as Oracle's SEQUENCES versus mysql's AUTO_INCREMENT are of interest. Maybe I'm asking too much to find a summary of such differences. But I'm only interested in using mainstream sql functinality, nothing complicated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Douglas Sims [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
