No Data on table
Hi All, Hope you can help me with this problem: I had an error that my database is already full, when i do a select there is always no result even select * from tablename. but when i look at it on /var/lib/mysql i can see the size of the file is still huge, -rw-rw 1 mysql mysql 9174 Feb 18 11:46 tablename.frm -rw-rw 1 mysql mysql 4294967092 Feb 18 11:49 tablename.MYD -rw-rw 1 mysql mysql 782402560 Feb 18 11:52 tablename.MYI which might mean that the data should still be in there somewhere, but how can i retrieve it? TIA Regards nhadie Message sent using UebiMiau 2.7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT using SELECT results
Hi,
I have a table LOCAIS with:
id typedesc
1 t1 blah blah
2 t1 uihuih
3 t2 pokpokp
I want to list only the distinct types and create a table with those
results.
I know how to list the distinct and i thought i got the insert
right, but it doesnt work:
INSERT INTO ztipos('type') SELECT DISTINCT type FROM locais
Whats wrong with that command?
I want the result to be:
results table ZTIPOS:
id type
1 t1
2 t2
Thanks
Miguel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Data on table
Hello On 2007-02-18 Nhadie wrote: I had an error that my database is already full, when i do a select there is always no result even select * from tablename. but when i look at it on /var/lib/mysql i can see the size of the file is still huge, -rw-rw 1 mysql mysql 9174 Feb 18 11:46 tablename.frm -rw-rw 1 mysql mysql 4294967092 Feb 18 11:49 tablename.MYD -rw-rw 1 mysql mysql 782402560 Feb 18 11:52 tablename.MYI which might mean that the data should still be in there somewhere, but how can i retrieve it? It seems that you are using a combination of MySQL + Kernel + Filesystem + Unix Distribution that is not capable of writing files larger than 4GB. That was a known limitations until some years ago. If you can, then copy those tables to a recent computer and execute a REPAIR TABLE on them. If they end up with 4GB then delete as much as necessary and they will be usable on your old computer again. HTH, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT using SELECT results
This may help you:
mysql create table locais(
- id int,
- type varchar(2),
- `desc` varchar(10));
Query OK, 0 rows affected (0.12 sec)
mysql select * from locais;
+--+--+---+
| id | type | desc |
+--+--+---+
|1 | t1 | sk|
|2 | t2 | dsk |
|3 | t1 | ddfsk |
+--+--+---+
3 rows in set (0.00 sec)
mysql select distinct type from locais;
+--+
| type |
+--+
| t1 |
| t2 |
+--+
2 rows in set (0.00 sec)
mysql create table ztipos(id int auto_increment primary key, type
varchar(2));
Query OK, 0 rows affected (0.14 sec)
mysql insert into ztipos(type) select distinct type from locais;
Query OK, 2 rows affected (0.18 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select * from ztipos;
++--+
| id | type |
++--+
| 1 | t1 |
| 2 | t2 |
++--+
2 rows in set (0.00 sec)
mysql
I think the ztipos table definition has a problem. If you post that we may
be able to help better
Anoop
(anokun7)
On 2/18/07, Miguel Vaz [EMAIL PROTECTED] wrote:
Hi,
I have a table LOCAIS with:
id typedesc
1 t1 blah blah
2 t1 uihuih
3 t2 pokpokp
I want to list only the distinct types and create a table with
those results.
I know how to list the distinct and i thought i got the insert
right, but it doesnt work:
INSERT INTO ztipos('type') SELECT DISTINCT type FROM locais
Whats wrong with that command?
I want the result to be:
results table ZTIPOS:
id type
1 t1
2 t2
Thanks
Miguel
--
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
Re: INSERT using SELECT results
INSERT INTO ztipos (type) VALUES (SELECT DISTINCT type FROM locais)
?
-afan
Miguel Vaz wrote:
Hi,
I have a table LOCAIS with:
idtypedesc
1t1blah blah
2t1uihuih
3t2pokpokp
I want to list only the distinct types and create a table with
those results.
I know how to list the distinct and i thought i got the insert
right, but it doesnt work:
INSERT INTO ztipos('type') SELECT DISTINCT type FROM locais
Whats wrong with that command?
I want the result to be:
results table ZTIPOS:
idtype
1t1
2t2
Thanks
Miguel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport problem with , inside fields
I've been trying to import fields that contains the comma character ',' inside double quotes '', with the results following: code mysqlimport --fields-optionally-enclosed-by= --fields-terminated-by=, --lines-terminated-by=\r\n --ignore-lines=1 --user=root --password shark c:\documents and settings\shamm\desktop\result.csv Enter password: ** mysqlimport: Error: Row 48 was truncated; it contained more data than there were input columns, when using table: result Line 48: 48, 14.729606, 10.1.1.22, 10.182.167.209, TCP, pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 mysql desc result; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | No | int(10) unsigned | NO | | | | | Time| text | NO | | | | | Source | text | NO | | | | | Destination | text | NO | | | | | Protocol| text | NO | | | | | Info| text | NO | | | | +-+--+--+-+-+---+ 6 rows in set (0.03 sec) C:\Documents and Settings\shammmysql --version mysql Ver 14.12 Distrib 5.0.26, for Win32 (ia32) /code -- `Twas brillig, and the slithy toves Did gyre and gimble in the wabe: All mimsy were the borogoves, And the mome raths outgrabe.
Re: Borland C++ Builder 2006 DLL Woes
We have settled on the ado.net connector www.mysql.com after dbExpress pains. Although apparently dbExpress does work. What BDS 2006 service pack are you on? Keep us all posted if you solve it. Mark [EMAIL PROTECTED] wrote: We just purchased The Borland Developer Studio 2006 IDE and are having significant problems using dbExpress objects to communicate with MySQL servers (both 4 and 5). Curiously, we can perform inserts but not selects, even though identical code in C++ Builder 6 worked just fine. The DLL in C++ Builder 2006 is dbxmys30.dll. Anybody having similar issues? Thanks, David David P. Giragosian, Psy.D. Database and Software Developer MD Anderson Cancer Center Houston, TX 713-792-7898 -- Mark Carson 2Lend Business Unit Manager 2Cana Solutions (Pty) Ltd EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED] snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Cell : +27 83 260 8515 Tel : +27 12 665 3901 Fax : +27 12 665 2113 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data back up for innodb tables - Copy paste
Hi, I want to copy paste the data files of Innodb database, is it possible, i mean can i just copy the data files like that we do for myisam tables, Thanks, Abhishek jain
Re: Data back up for innodb tables - Copy paste
On 2007-02-19 abhishek jain wrote: I want to copy paste the data files of Innodb database, is it possible, i mean can i just copy the data files like that we do for myisam tables If you mean for a daily backup while the server is running: No! You often end up with corrupted tables doing that with MyISAM, too. Use mysqlhotcopy or mysqldump for that. If you stop the server, then copy the files and make sure that you have the same innodb_data_file_path statements at the target host, it maybe works. bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport problem with , inside fields
Hey Scott - I dont think you can use , with mysqlimport as a field separator if it is part of the data. use something else - I used the pipe | character... This is what worked for me: C:\mysqlimport --fields-enclosed-by= --fields-terminated-by=| --lines-terminated-by=\r\n --ignore-lines=1 --user=root --password test c:\result.csv --verbose=TRUE --ignore=tr ue Enter password: Connecting to localhost Selecting database test Loading data from SERVER file: c:/result.csv into result test.result: Records: 7 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost C:\type result.csv 48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 48| 14.729606| 10.1.1.22| 10.182.167.209| TCP| pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 mysql select info from result; ++ | info | ++ | pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 | | pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 | | pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 | | pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 | | pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 | | pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 | | pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 | ++ 7 rows in set (0.00 sec) Let me know how it goes.. Anoop (anokun7) On 2/18/07, Scott Hamm [EMAIL PROTECTED] wrote: I've been trying to import fields that contains the comma character ',' inside double quotes '', with the results following: code mysqlimport --fields-optionally-enclosed-by= --fields-terminated-by=, --lines-terminated-by=\r\n --ignore-lines=1 --user=root --password shark c:\documents and settings\shamm\desktop\result.csv Enter password: ** mysqlimport: Error: Row 48 was truncated; it contained more data than there were input columns, when using table: result Line 48: 48, 14.729606, 10.1.1.22, 10.182.167.209, TCP, pop3 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 mysql desc result; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | No | int(10) unsigned | NO | | | | | Time| text | NO | | | | | Source | text | NO | | | | | Destination | text | NO | | | | | Protocol| text | NO | | | | | Info| text | NO | | | | +-+--+--+-+-+---+ 6 rows in set (0.03 sec) C:\Documents and Settings\shammmysql --version mysql Ver 14.12 Distrib 5.0.26, for Win32 (ia32) /code -- `Twas brillig, and the slithy toves Did gyre and gimble in the wabe: All mimsy were the borogoves, And the mome raths outgrabe. -- Thanks and best regards, Anoop
Re: Real BITs which use 1 bit in 5.1?
Kevin Burton wrote: A little birdie: http://forge.mysql.com/wiki/Top10SQLPerformanceTips notes.. In 5.1 BOOL/BIT NOT NULL type is 1 bit, in previous versions it's 1 byte. Is this true? Hmm, I had wondered about that when someone yelled it out at MySQL Camp... No, it's not true. The BIT data type is not the same as BOOL. BIT is used for easier bitmask/bitfield type columns, and BOOL is used for boolean values, but it is currently aliased to TINYINT(1). So, this is patently false. True boolean data type support is, however, planned for a future release...not sure when though. I'll remove that from the wiki page. Cheers, Jay I didn't see a note in the manual.. I assume it would be here http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Data on table
Hi Christian, Thanks for the reply. I got the table repaired and deleted some data on it. What combination can i use to overcome the 4GB limit? TIA Regards, Nhadie Christian Hammers wrote: Hello On 2007-02-18 Nhadie wrote: I had an error that my database is already full, when i do a select there is always no result even select * from tablename. but when i look at it on /var/lib/mysql i can see the size of the file is still huge, -rw-rw 1 mysql mysql 9174 Feb 18 11:46 tablename.frm -rw-rw 1 mysql mysql 4294967092 Feb 18 11:49 tablename.MYD -rw-rw 1 mysql mysql 782402560 Feb 18 11:52 tablename.MYI which might mean that the data should still be in there somewhere, but how can i retrieve it? It seems that you are using a combination of MySQL + Kernel + Filesystem + Unix Distribution that is not capable of writing files larger than 4GB. That was a known limitations until some years ago. If you can, then copy those tables to a recent computer and execute a REPAIR TABLE on them. If they end up with 4GB then delete as much as necessary and they will be usable on your old computer again. HTH, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
