Re: C api - mysql_list_fields
Hi Mike, all, Mike Aubury wrote: I'm probably being a bit stupid - but I'm trying to determine (in code) the length of the string in the schema for a given table. So - for example : create table a ( blah char(20) ) I want to return '20', but I'm getting '60' when I use mysql_list_fields.. (Always seems to be 3x longer that I'm expecting)... Am I missing something ? (or should I just divide by 3!!) You are missing the distinction between character and byte, which is brought to you by the ISO character sets which go far beyond ASCII. The moment you allow international characters (US-ASCII + German Umlauts + French accented vowels + Spanish cedilla + ... + Chinese + Korean + ...) in your data, storing one character may need more than one byte. The current encoding (versions 5.0 and 5.1) uses up to 3 bytes per character, that is the factor 3 you notice. With 6.0, a different encoding may be used, which uses up to 4 bytes per character. If you know you won't need arbitrary characters, you can use the charset (or character set) option in your create statements. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
Basically - so I can display it in the same form as the orginal table.. Or - if you want the longer version I work with an Opensource project called 'Aubit4GL' (its a clone of Informix4GL - which allows you to write really nice screen based database oriented programs + reports), see http://sourceforge.net/projects/aubit4gl Anyway - part of that project is a tool called 'asql' (which is a replacement for the Informix 'isql' tool..), this tool is itself written using Aubit4GL. You can think of asql as an easy to use screen based (ncurses) equivilent of the mysql tool.. One of the options is a 'Table Info' - where you get a list of the columns and the datatypes etc. When you create a table with a char(20) - you dont want to see it appear as a char(60)! I have a workaround atm - but its really clunky - I do a separate select for each column in the form : SHOW COLUMNS FROM table LIKE 'column' for each column returned from mysql_list_fields BTW - I'll need some help with some of the other displays (indexes, status etc) - if anyone fancies lending a hand - we always welcome new volunteers! (The 'mysql' driver for Aubit4GL could probably do with some attention from someone who knows their way around) On Tuesday 07 October 2008 13:10:18 walter harms wrote: Mike Aubury schrieb: Excellent - this seems to be the issue - the show create table shows : mysql show create table a\g +---+ + | Table | Create Table | +---+ + | a | CREATE TABLE `a` ( `blah` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +---+--- So - its utf8 (which I understand enough about to understand why its doing what its doing!) So - the next question is... Is there anyway in code I can find the 'fiddle' factor (1,3,or now possibly 4) that I need to use to divide by to get back to the character width specified in the CREATE TABLE ? why do you want to do that ? i would expect that mysql uses wchar_t for char() if utf8 is selected. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
Mike, all, Mike Aubury wrote: [[...]] So - the next question is... Is there anyway in code I can find the 'fiddle' factor (1,3,or now possibly 4) that I need to use to divide by to get back to the character width specified in the CREATE TABLE ? In the information_schema database, which you can use to access schema information, MySQL differs between CHARACTER_MAXIMUM_LENGTH (the number of characters) and CHARACTER_OCTET_LENGTH (number of bytes). But this need not be the same factor for all columns: If you specify one with charset latin1, it is one octet (byte) per character. So I don't see any use in such a constant factor. And sorry, no, off-hand I don't know a way to programmatically ask the server for this factor. If you need to know the number of characters specified for some column, you should access the information_schema database and get all those details, including character set and collation. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Any easier way to compare mysql schema of 50 databases?!
I just used mysqldiff for a project, and was successful. -Original Message- From: Uma Bhat [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 7:51 PM To: Andy Shellam; mysql@lists.mysql.com Subject: Re: Any easier way to compare mysql schema of 50 databases?! Thank you all, guys!! i shall try these and respond on which worked best for me. Regards, Uma On 10/6/08, Andy Shellam [EMAIL PROTECTED] wrote: Hi, I was also going to go down this route some time back, but then when I looked at it, it's pretty simple how it works and you can do the same thing yourself for free. Take a dump of both servers (mysqldump or via MySQL Administrator) of the databases in question, then use WinMerge (for free) to compare the dump files and (optionally) create a 3rd file which is a result of merging the 2 by choosing the changes you want to keep. I've used this method plenty of times to synchronise changes between servers, some that concern 000s of rows of data. Granted you cannot do this with PostgreSQL as those dumps tend to be in binary format, but it works well for MySQL backups. WinMerge: http://www.winmerge.org/ Andy D. Dante Lorenso wrote: Uma Bhat wrote: We are in progress of *optimizing* and designing the existing mysql database enviromnent on *linux*. And need help in comaparing schema of 50 databases from the same mysql instance. If you can afford to spend a few dollars to get the right tool, you want to get DB Comparer for MySQL from the folks at EMS: http://www.sqlmanager.net/en/products/mysql/dbcomparer This tool will compare the schemas of 2 MySQL Databases and allow you to selectively choose which changes to make in order to synch to the master or the target DB. I've been using the PostgreSQL version of this tool for many years and just recently started using their MySQL one. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
It works for me, I used your code:
Field =mycol Type=254 Length=20
so at least your code is fine, and the problem must be somewhere
else. I am using RH EL3.
cheers,
Doug
On 6 Oct 2008, at 19:52, Mike Aubury wrote:
I'm probably being a bit stupid - but I'm trying to determine (in
code) the
length of the string in the schema for a given table.
So - for example :
create table a (
blah char(20)
)
I want to return '20', but I'm getting '60' when I use
mysql_list_fields..
(Always seems to be 3x longer that I'm expecting)...
Am I missing something ? (or should I just divide by 3!!)
Heres an example :
#include stdio.h
#include stdlib.h
#include mysql.h
MYSQL conn;
int main(int argc,char *argv[]) {
// run with username port as arguments
char *tabname=a;
char *db=test1;
char *u;
char *p;
MYSQL_RES *result;
MYSQL_FIELD *field;
if (argc!=3) {
printf(usage : %s username password\n, argv[0]);exit(2);
}
u=argv[1]; p=argv[2];
mysql_init(conn);
if (!mysql_real_connect(conn, NULL,u,p,db,0,NULL,0) ) {
fprintf(stderr,
Failed to connect to database: Error: %s\n,
mysql_error(conn));
exit(2);
}
result = mysql_list_fields (conn, tabname, NULL);
field = mysql_fetch_field (result);
printf(Field =%s Type=%d Length=%d\n, field-name,
field-type, field-length);
}
Thanks in advance...
--
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: C api - mysql_list_fields
Mike Aubury schrieb: Excellent - this seems to be the issue - the show create table shows : mysql show create table a\g +---++ | Table | Create Table | +---++ | a | CREATE TABLE `a` ( `blah` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +---+--- So - its utf8 (which I understand enough about to understand why its doing what its doing!) So - the next question is... Is there anyway in code I can find the 'fiddle' factor (1,3,or now possibly 4) that I need to use to divide by to get back to the character width specified in the CREATE TABLE ? why do you want to do that ? i would expect that mysql uses wchar_t for char() if utf8 is selected. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
Excellent - this seems to be the issue - the show create table shows : mysql show create table a\g +---++ | Table | Create Table | +---++ | a | CREATE TABLE `a` ( `blah` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +---+--- So - its utf8 (which I understand enough about to understand why its doing what its doing!) So - the next question is... Is there anyway in code I can find the 'fiddle' factor (1,3,or now possibly 4) that I need to use to divide by to get back to the character width specified in the CREATE TABLE ? On Tuesday 07 October 2008 12:07:28 Joerg Bruehe wrote: Hi Mike, all, Mike Aubury wrote: I'm probably being a bit stupid - but I'm trying to determine (in code) the length of the string in the schema for a given table. So - for example : create table a ( blah char(20) ) I want to return '20', but I'm getting '60' when I use mysql_list_fields.. (Always seems to be 3x longer that I'm expecting)... Am I missing something ? (or should I just divide by 3!!) You are missing the distinction between character and byte, which is brought to you by the ISO character sets which go far beyond ASCII. The moment you allow international characters (US-ASCII + German Umlauts + French accented vowels + Spanish cedilla + ... + Chinese + Korean + ...) in your data, storing one character may need more than one byte. The current encoding (versions 5.0 and 5.1) uses up to 3 bytes per character, that is the factor 3 you notice. With 6.0, a different encoding may be used, which uses up to 4 bytes per character. If you know you won't need arbitrary characters, you can use the charset (or character set) option in your create statements. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
[EMAIL PROTECTED] (Mike Aubury) writes: I'm probably being a bit stupid - but I'm trying to determine (in code) the length of the string in the schema for a given table. So - for example : create table a ( blah char(20) ) I want to return '20', but I'm getting '60' when I use mysql_list_fields.. (Always seems to be 3x longer that I'm expecting)... Am I missing something ? (or should I just divide by 3!!) Is the table or database using UTF-8? I think that if it is MySQL will allocate space for each character and is forced to allocate 3x20 bytes as a UTF-8 character can be up to 3-bytes in length. SHOW CREATE TABLE a\G should show if this is the case. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sending a message to the GMane mirror for this mailing list fails
I signed up for the Server | General Discussion mailing list using an e-mail address I created at my web host, and through which I have received numerous e-mails without problem. The subscription went correctly. I then attempted to send a message to the GMane newsgroups which mirrors the list. The message was sent without error. I then received a Gmane authorization to have the message sent to the newsgroup, then replied to that and received a further message from Gmane saying that I was now authorized and my original message would appear on the list within 10 minutes. Finally I received a message from Gmane: 'This message was created automatically by mail delivery software. A message that you sent could not be delivered to one or more of its recipients. This is a permanent error. The following address(es) failed: mysql@lists.mysql.com mailto:mysql@lists.mysql.com SMTP error from remote mailer after end of data: host lists.mysql.com [213.136.52.31]: 552 spam score exceeded threshold (#5.6.1)' What followed was a copy of the original message. When I queried GMane about this they told me the problem was from the MySql list, not them. I then unsubscribed to the mailing list and subscribed using a completely different e-mail address from my ISP provider, through which I have received numerous e-mails without problem. Once again the exact same error occurred. I am not a spammer and have never sent spam of any kind. Here is the full header of the returned e-mail: 'From - Mon Oct 06 17:33:13 2008 X-Account-Key: account2 X-UIDL: 20081006212921M1000852qfe1h X-Mozilla-Status: 0001 X-Mozilla-Status2: X-Mozilla-Keys: Received: from lo.gmane.org ([80.91.229.12]) by isp.att.net (frfwmxc10) with ESMTP id 20081006212920M1000ihkoee; Mon, 6 Oct 2008 21:29:21 + X-Originating-IP: [80.91.229.12] Received: from list by lo.gmane.org with local (Exim 4.50) id 1Kmxea-0002m0-95 for [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]; Mon, 06 Oct 2008 23:30:08 +0200 Received: from main.gmane.org ([80.91.229.2] helo=ciao.gmane.org) by lo.gmane.org with esmtp (Exim 4.50) id 1KmxeQ-0002hc-Gz for [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]; Mon, 06 Oct 2008 23:29:58 +0200 Received: from Debian-exim by ciao.gmane.org with local (Exim 4.43) id 1KmxdK-0003ZS-4K for [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]; Mon, 06 Oct 2008 21:28:50 + X-Failed-Recipients: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Auto-Submitted: auto-generated From: Mail Delivery System [EMAIL PROTECTED] To: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Subject: Mail delivery failed: returning message to sender Message-Id: [EMAIL PROTECTED] Date: Mon, 06 Oct 2008 21:28:50 + X-Spam-Report: 30.0 points; * 4.0 BOUNCE_MAIL_DELIVERY_FAILED Subject: 'Mail delivery failed', bounce message * 5.0 BOUNCE_EMPTY_RETURN_PATH Return-Path: * 0.0 IS_A_BOUNCE IS_A_BOUNCE * 10 BOUNCE_FROM_MAILER_DAEMON From: [EMAIL PROTECTED], likely bounce * 2.0 BOUNCE_TRANSCRIPT_RECEIVED BODY: Looks like message transcript with 'Received:' header * 2.5 BOUNCE_COULD_NOT_BE_DELIVERED BODY: Message says 'could not be delivered', so probably a bounce * 2.5 BOUNCE_SMTP_ERROR BODY: Looks like SMTP transcript with 'SMTP error' * 2.0 BOUNCE_CREATED_AUTOMATICALLY BODY: Message says created by mail delivery software * 2.0 BOUNCE_COPY_OF_MESSAGE BODY: 'a copy of the message', likely a failure report * 0.0 BAYES_50 BODY: Bayesian spam probability is 40 to 60% * [score: 0.5026] Why is my message failing when sent to the Gmane mirror for this list but succeeding when sent directly to this list as I am doing so now ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
