Re: InnoDB: Problem with innobackup
James,
page number 22357 is corrupt. InnoDB Hot Backup notices these checksum
errors, and refuses to do the backup. Sometimes it happens that an unused
(i.e., freed) page in an ibdata file becomes corrupt. Then it would be nice
to have some tool to reset the checksums on that page, so that mysqld or
ibbackup would not complain of the page.
I wrote now a little C program innodb_page_checksum_reset.c that can be used
for this :). The program code is below. It will reset page 22357 in ibdata1.
I can send a Linux binary, if you do not have a working C compiler.
To compile in Linux:
gcc -o reset innodb_page_checksum_reset.c
Regards,
Heikki
/* This program is for resetting the lsn and checksum fields of an InnoDB
page, so that ibbackup and mysqld will not complain of corruption. NOTE that
this program does NOT fix the corruption, though! Read the instructions
below
VERY carefully.
Copyright 2005 Innobase Oy. This program is released under the GNU GPL
license version 2. */
#include stdio.h
#include sys/types.h
#include sys/stat.h
#include fcntl.h
int
main(void)
{
off_t page_number;
int file;
off_t offs;
off_t ret_offset;
ssize_t ret;
char* file_name;
charbuf[8];
/* page_number is the number of the page in the ibdata file that you
want to reset. Note that if you have several ibdata files, you have
to
calculate the page number in the particular ibdata file, and NOT use
the global tablespace page number. InnoDB page size is 16 kB. */
file_name = ibdata1;
page_number = 22357;
offs = page_number * 16 * 1024;
memset(buf, '\0', 8);
file = open(file_name, O_RDWR);
if (file == -1) {
printf(Cannot open %s\n, file_name);
exit(1);
}
/* Reset FIL_PAGE_SPACE_OR_CHKSUM */
ret_offset = lseek(file, offs, SEEK_SET);
if (ret_offset 0) {
printf(Error in lseek 1\n);
exit(1);
}
ret = write(file, buf, (ssize_t)4);
if (ret != 4) {
printf(Error in write 1\n);
exit(1);
}
/* Read FIL_PAGE_LSN */
ret_offset = lseek(file, offs + 16, SEEK_SET);
if (ret_offset 0) {
printf(Error in lseek 2\n);
exit(1);
}
ret = read(file, buf, (ssize_t)8);
if (ret != 8) {
printf(Error in read\n);
exit(1);
}
/* Reset FIL_PAGE_END_LSN_OLD_CHKSUM */
ret_offset = lseek(file, offs + 16 * 1024 - 8, SEEK_SET);
if (ret_offset 0) {
printf(Error in lseek 3\n);
exit(1);
}
ret = write(file, buf, (ssize_t)8);
if (ret != 8) {
printf(Error in write 2\n);
exit(1);
}
close(file);
printf(lsn and checksum fields of page %lu in file %s reset\n,
(ulong)page_number, file_name);
return(0);
}
- Original Message -
From: James Green [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, February 18, 2005 5:53 PM
Subject: InnoDB: Problem with innobackup
Hi,
On running the hot backup tool we receive:
ibbackup: Re-reading page at offset 0 366297088 in
/var/lib/mysql/data/ibdata1
ibbackup: Re-reading page at offset 0 366297088 in
/var/lib/mysql/data/ibdata1
050218 15:18:01 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
eeaefd1a57557b35693200017183e16e45bf000[garbage
continues]
(.(.u:.%.1./.7u.E.e8.'%.e.c9]q...;InnoDB: End of page dump
050218 15:18:01 InnoDB: Page checksum 4004445466,
prior-to-4.0.14-form checksum 3154721000
InnoDB: stored checksum 4004445466, prior-to-4.0.14-form stored
checksum 2825075037
InnoDB: Page lsn 1 1904468334, low 4 bytes of lsn at page end 1904466222
InnoDB: Page number (if stored to page already) 22357,
InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 162
ibbackup: Error: page at offset 0 366297088 in
/var/lib/mysql/data/ibdata1 seems corrupt!
innobackup: Error: ibbackup child process has died at innobackup.pl line
332.
We have gone through (via a script) and every table in every database
(all by 'mysql' is InnoDB) returns 'OK' using 'check table'.
We did suffer a hardware failure which required a table to be dropped
and rebuilt, however that was resolved and everything appears to be
operating fine now. Except we want the hot backup to work and it
clearly doesn't.
Looking for options. We have mysqldumps but clearly restoration will
be very slow. The server is Debian Linux (stable) with MySQL-4.1.9
from the mysql.com binary tarball.
Help! Many thanks!
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
how to write this query?
I have two dates (start_date, end_date). Datediff() function returns difference in days. I need the difference but not including Satuday and Sunday. Any ideas? TH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to write this query?
On Sat, 19 Feb 2005 14:01:05 +, Jerry Swanson [EMAIL PROTECTED] wrote:
I have two dates (start_date, end_date). Datediff() function returns
difference in days.
I need the difference but not including Satuday and Sunday.
Any ideas?
C:\Program Files\MySQL\MySQL Server 4.1\binmysql -utest -ptest test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 4.1.8-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql CREATE TABLE `DateDiffs` (
- start_date DATETIME,
- end_date DATETIME
- );
Query OK, 0 rows affected (0.15 sec)
mysql INSERT INTO DateDiffs
- (start_date, end_date)
- VALUES
- ('2005-02-14 00:00:00', '2005-02-18 00:00:00'),
- ('2005-02-07 00:00:00', '2005-02-18 00:00:00'),
- ('2005-02-04 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-31 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-24 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-21 00:00:00', '2005-02-18 00:00:00'),
- ('2005-01-17 00:00:00', '2005-02-18 00:00:00');
Query OK, 9 rows affected (0.06 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql SELECT
-start_date,
-end_date,
-datediff(end_date, start_date)
-AS dd1,
-datediff(end_date, start_date) -
floor(datediff(end_date, start_date) / 7) * 2
-AS dd2
- FROM DateDiffs
- ORDER BY start_date;
+-+-+--+--+
| start_date | end_date| dd1 | dd2 |
+-+-+--+--+
| 2005-01-17 00:00:00 | 2005-02-18 00:00:00 | 32 | 24 |
| 2005-01-21 00:00:00 | 2005-02-18 00:00:00 | 28 | 20 |
| 2005-01-24 00:00:00 | 2005-02-18 00:00:00 | 25 | 19 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-31 00:00:00 | 2005-02-18 00:00:00 | 18 | 14 |
| 2005-02-04 00:00:00 | 2005-02-18 00:00:00 | 14 | 10 |
| 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 11 |9 |
| 2005-02-14 00:00:00 | 2005-02-18 00:00:00 |4 |4 |
+-+-+--+--+
9 rows in set (0.00 sec)
mysql DROP TABLE DateDiffs;
Query OK, 0 rows affected (0.19 sec)
mysql exit
The column dd1 contains the unaltered DATEDIFF() which includes the
Saturdays and Sundays, while the dd2 column contains the number of
business days omitting the weekend days.
--
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.7 Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.1.7, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. Version 3.1.7 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.7 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, 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: 02-18-05 - Version 3.1.7-stable - Fixed BUG#7686, Timestamp key column data needed _binary' stripped for UpdatableResultSet.refreshRow(). - Fixed BUG#7715 - Timestamps converted incorrectly to strings with Server-side prepared statements and updatable result sets. - Detect new sql_mode variable in string form (it used to be integer) and adjust quoting method for strings appropriately. - Added 'holdResultsOpenOverStatementClose' property (default is false), that keeps result sets open over statement.close() or new execution on same statement (suggested by Kevin Burton). - Fixed BUG#7952 -- Infinite recursion when 'falling back' to master in failover configuration. - Disable multi-statements (if enabled) for MySQL-4.1 versions prior to version 4.1.10 if the query cache is enabled, as the server returns wrong results in this configuration. - Fixed duplicated code in configureClientCharset() that prevented useOldUTF8Behavior=true from working properly. - Removed 'dontUnpackBinaryResults' functionality, the driver now always stores results from server-side prepared statements as-is from the server and unpacks them on demand. - Fixed BUG#8096 where emulated locators corrupt binary data when using server-side prepared statements. - Fixed synchronization issue with ServerPreparedStatement.serverPrepare() that could cause deadlocks/crashes if connection was shared between threads. - By default, the driver now scans SQL you are preparing via all variants of Connection.prepareStatement() to determine if it is a supported type of statement to prepare on the server side, and if it is not supported by the server, it instead prepares it as a client-side emulated prepared statement (BUG#4718). You can disable this by passing 'emulateUnsupportedPstmts=false' in your JDBC URL. - Remove _binary introducer from parameters used as in/out parameters in CallableStatement. - Always return byte[]s for output parameters registered as *BINARY. - Send correct value for 'boolean' true to server for PreparedStatement.setObject(n, true, Types.BIT). - Fixed bug with Connection not caching statements from prepareStatement() when the statement wasn't a server-side prepared statement. - Choose correct 'direction' to apply time adjustments when both client and server are in GMT timezone when using ResultSet.get(..., cal) and PreparedStatement.set(, cal). - Added 'dontTrackOpenResources' option (default is false, to be JDBC compliant), which helps with memory use for non-well-behaved apps (i.e applications which don't close Statements when they should). - Fixed BUG#8428 - ResultSet.getString() doesn't maintain format stored on server, bug fix only enabled when 'noDatetimeStringSync' property is set to 'true' (the default is 'false'). - Fixed NPE in ResultSet.realClose() when using usage advisor and result set was already closed. - Fixed BUG#8487 - PreparedStatements not creating streaming result sets. - Don't pass NULL to String.valueOf() in ResultSet.getNativeConvertToString(), as it stringifies it (i.e. returns null), which is not correct for the method in question. - Fixed BUG#8484 - ResultSet.getBigDecimal() throws exception when rounding would need to occur to set scale. The driver now chooses a rounding mode of 'half up' if non-rounding BigDecimal.setScale() fails. - Added 'useLocalSessionState' configuration property, when set to 'true' the JDBC driver trusts that the application is well-behaved and only sets autocommit and transaction isolation levels using the methods
Query caused different errors on master and slave.
Anyone know what to do when SHOW SLAVE STATUS says: Query caused different errors on master and slave. Master: MySQL 4.1.9-Debian_2-log Slave: MySQL 4.1.5-gamma-log Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question!
Dear Sir/Madam, I am in third year college in Trinity. I came across your e-mail after much research on the pipeten.com site and was hoping you mite be able to help me with a software labs report. If not it would be great if you could indicate a person who mite. SCENARIO: Over the last few weeks, I have been using a three-tier approach to allow web browser clients communicate with a back end database. To date, I have just been inserting data into a table. The middleware scripting language I have used is PHP. OBJECTIVES: I was wondering if you could tell me of three or four alternative software options that can be used for this middle layer. What are the costs involved with any of these. Also if you could supply me with a sample script for inserting data using these alternative scripting languages, as I have been implementing with PHP. If you can indicate any possible acedemic resources or books that might help me that would be great. Thank you Yours Faithfully, Lisa Mannion, JS MSISS Student, Trinity College Dublin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question!
I was wondering if you could tell me of three or four alternative software options that can be used for this middle layer. What are the costs involved with any of these. Also if you could supply me with a sample script for inserting data using these alternative scripting languages, as I have been implementing with PHP. Google for: PHP ASP JSP CGI ColdFusion I'm sure you'll find more than enough information there. PHP is free. ASP uses VBScript (usually), comes from Microsoft and is currently free too, as is ASP.NET. JSP uses Java, comes from Sun Microsystems and is probably free as well. CGI is free, written in C or Perl. ColdFusion is not free, is from Macromedia (the same company that does MM Flash/Fireworks/Freehand/UltraDev) and, like PHP, uses its own language. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL_RES ......
Dears,I was attempping with your code.But it could't do my job.
My job:
I have list of my tables in my DB,i did it with mysql_list_tables() func.
It returns MYSQL_RES type.But i don't know how to split it to an array.
Please guide me..
Hi,
I think you can find all the information you are looking for there:
http://dev.mysql.com/doc/mysql/en/c.html
You have a description of all the functions :
http://dev.mysql.com/doc/mysql/en/c-api-functions.html
For example:
http://dev.mysql.com/doc/mysql/en/mysql-fetch-row.html
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(result);
for(i = 0; i num_fields; i++)
{
printf([%.*s] , (int) lengths[i], row[i] ? row[i] : NULL);
}
printf(\n);
}
Maybe this is a good start point for you!
From: Mohsen Pahlevanzadeh [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: MYSQL_RES ..
Date: Fri, 18 Feb 2005 16:44:06 -0800 (PST)
Dears,mysql_list_tables returns name of tables.(tables of a DB)
It returns MYSQL_RES type.
I want to split it to an array.
I'm newbie in SQL,Please guide me.
I'm working C.
yours,Mohsen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!
http://toolbar.msn.co.uk/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question!
Quick definitions (Mite) a.. noun: any of numerous very small to minute arachnids often infesting animals or plants or stored foods a.. noun: a slight but appreciable addition I think you meant to say might, not mite ;-) Rhino - Original Message - From: Lisa Mannion [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, February 19, 2005 12:33 PM Subject: Question! Dear Sir/Madam, I am in third year college in Trinity. I came across your e-mail after much research on the pipeten.com site and was hoping you mite be able to help me with a software labs report. If not it would be great if you could indicate a person who mite. SCENARIO: Over the last few weeks, I have been using a three-tier approach to allow web browser clients communicate with a back end database. To date, I have just been inserting data into a table. The middleware scripting language I have used is PHP. OBJECTIVES: I was wondering if you could tell me of three or four alternative software options that can be used for this middle layer. What are the costs involved with any of these. Also if you could supply me with a sample script for inserting data using these alternative scripting languages, as I have been implementing with PHP. If you can indicate any possible acedemic resources or books that might help me that would be great. Thank you Yours Faithfully, Lisa Mannion, JS MSISS Student, Trinity College Dublin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 18/02/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 18/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL_RES ......
Mohsen,
The data structure you seem to be looking for is MYSQL_RES, the C API
resultset, which is an array which is described in the manual page you
were pointed to. It's pretty straightforward to use, eg
...
MYSQL_RES *rset;
...
if( mysql_real_query( conn, qry, strlen( qry ) == MYSQL_TRUE ) {
if(( rset = mysql_store_result( conn )) != NULL ) {
cols = mysql_num_fields( rset );
rows = (long) mysql_num_rows( rset );
...etc...
PB
-
Mohsen Pahlevanzadeh wrote:
Dears,I was attempping with your code.But it could't do my job.
My job:
I have list of my tables in my DB,i did it with mysql_list_tables() func.
It returns MYSQL_RES type.But i don't know how to split it to an array.
Please guide me..
Hi,
I think you can find all the information you are looking for there:
http://dev.mysql.com/doc/mysql/en/c.html
You have a description of all the functions :
http://dev.mysql.com/doc/mysql/en/c-api-functions.html
For example:
http://dev.mysql.com/doc/mysql/en/mysql-fetch-row.html
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(result);
for(i = 0; i num_fields; i++)
{
printf([%.*s] , (int) lengths[i], row[i] ? row[i] : NULL);
}
printf(\n);
}
Maybe this is a good start point for you!
From: Mohsen Pahlevanzadeh [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: MYSQL_RES ..
Date: Fri, 18 Feb 2005 16:44:06 -0800 (PST)
Dears,mysql_list_tables returns name of tables.(tables of a DB)
It returns MYSQL_RES type.
I want to split it to an array.
I'm newbie in SQL,Please guide me.
I'm working C.
yours,Mohsen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!
http://toolbar.msn.co.uk/
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 2/18/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dual Xeon EM64T fedora3 x86_64
I use: # ps -axH | grep mysql and now I can see all theads. I was worry about mysql is not using both CPU, but it is. I made a test running stress queries: with 1 coneccion I got 75% idle processor, 2 coneccion I got 50% idle processor, 3 coneccion I got 25% idle processor Thanks Paul Gleb Paharenko wrote: Hello. Try -m command line option to see all threads. Paul Chinen - NB [EMAIL PROTECTED] wrote: Hello, I just installed MySQL-server-4.0.23-0.x86_64.rpm on Dual Xeon 3.2 64 bit 8G of RAM with Fedora3 x86_64. I noticed (doing a ps ax|grep mysql) that there is only one mysqld process running. Is this normal? BR Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Approximately when is a beta with useable stored procedures released?
I have downloaded both 5.0.0 and latest 5.0.1 binary snapshot but I found that the stored procedures are very unstable. E.g. when I fetch empty string into variables, the server crashes. It seems like 5.x development has been ongoing for more than a year. Can somebody give me a rough estimate about when a beta is available with useable stored procedures? If not what other options do I have for fast cursor processing? (I dont want to code in C.) Thanks, -- Bereczki Gabor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Non root user MySQL install possible?
I had no problem installing mysql as a simple user in my home directory
on a linux x86_64 machine either from source or binary.
I did this:
1.Download binary or source from MySQL mirror
2.Untar into a directory database_dir or make install using ./configure
-prefix=database_dir
3.Softlink database_dir as mysql
4.Add to path database_dir/bin and database_dir/scripts, change to mysql
dir
5.Create database tables
./scripts/mysql_install_db --ldata=./data
or if installed from source
./bin/mysql_install_db --ldata=./data --basedir=${PWD}
6.Start mysql daemon:
./bin/safe_mysqld --basedir=. --datadir=./data --err-log=./mysql.log --
pid-file=mysql.pid socket=./mysql.sock
or if installed from source:
./bin/mysqld_safe --basedir=. --datadir=./data --pid-file=mysql.pid --
socket=./mysql.sock
7. Start mysql client
mysql socket=./data/mysql.sock
--
Bereczki Gabor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Translating Data Into NULL
I'm having a terrible time with MySQL's new collation feature. It frequently fills cells that should be NULL with 0's and pushes data from one column to another. I can usually import csv files into my tables using either Navicat or phpMyAdmin, but I have to experiment with a lot of settings, and sometimes it doesn't work at all. Publishing tables online is yet another game, as my webhost hasn't upgraded to the latest MySQL. So I THINK I may have a good idea for a workaround. I simply change all my fields to NOT NULL, then fill every cell that would ordinarily be NULL with a unique constant, like 606060. Then I find some sort of PHP code that translates 606060 into NULL, ignoring it when both displaying data and sorting table cells. Does this sound like a workable solution, and does anyone know how to actually implement it? Thanks. __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Translating Data Into NULL
--- Alex S Moore [EMAIL PROTECTED] wrote: David Blomstrom wrote: So I THINK I may have a good idea for a workaround. I simply change all my fields to NOT NULL, then fill every cell that would ordinarily be NULL with a unique constant, like 606060. Then I find some sort of PHP code that translates 606060 into NULL, ignoring it when both displaying data and sorting table cells. Why not set columns to NOT NULL and default to space for character and 0 for numeric? OK, you're suggesting I... 1. Set all the columns to NOT NULL 2. Set 0 as the default for numeric fields 3. Set space for the default in the other fields? I don't understand what you mean by space. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Translating Data Into NULL
On Sat, 2005-02-19 at 23:55, David Blomstrom wrote: Why not set columns to NOT NULL and default to space for character and 0 for numeric? OK, you're suggesting I... 1. Set all the columns to NOT NULL 2. Set 0 as the default for numeric fields 3. Set space for the default in the other fields? Using a single space as the default for a column won't work because mysql trims (removes trailing whitespace) from inserted values. Doing something like this sounds like a really bad work around. The data you want should be stored and appear in the right columns. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Translating Data Into NULL
--- Andy Bakun [EMAIL PROTECTED] wrote: On Sat, 2005-02-19 at 23:55, David Blomstrom wrote: Why not set columns to NOT NULL and default to space for character and 0 for numeric? OK, you're suggesting I... 1. Set all the columns to NOT NULL 2. Set 0 as the default for numeric fields 3. Set space for the default in the other fields? Using a single space as the default for a column won't work because mysql trims (removes trailing whitespace) from inserted values. Doing something like this sounds like a really bad work around. The data you want should be stored and appear in the right columns. OK, thanks. __ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Help Importing CSV Data
I used to use Navicat to import CSV files into MySQL. Now I find that I can use either Navicat or the latest phpMyAdmin upgrade, and one or the other usually works, even when I'm battling collation errors. But I'm stumped on a relatively large but simple table. I wondered if I might make it work if I set my my MySQL table a certain way. I put a picture of my basic table structure online at http://www.geobop.org/dat1.gif The first six fields were originally int(3). However, I made them int(3) so I could insert 937937 in all the NULL cells. When that didn't work, I tried another experiment, changing them to VARCHAR and adding an x to the last cells in the 1st and 4th-6th fields. http://www.geobop.org/dat3.gif is a picture of my csv file, focusing on the bottom row. Adding an X to the numerals in the bottom row yielded quotes around those numerals, which I thought might help. http://www.geobop.org/dat2.gif shows what I see after I import the data nd browse my table. The 6th column, RemoteStatesReg, is almost always messed up, displaying NULL and apparently pushing the data on the right into the wrong field. But if I import the data another way, data from several fields are jumbled together in the last field. When I import csv with phpMyAdmin, I first truncate the table, then choose the following settings: Fields Terminated - , Fields Enclosed - (Optionally) I leave Fields Escaped By and Lines Terminated By blank. Would it help if I created an extra column, inserted some unique symbol, like !!!, then chose Lines Terminated By !!!? Actually, I'll just give it a try. I'm just fishing for any tips anyone can offer for getting this data onboard. Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
