Results 1 to 8 of 8

Thread: 2 MySQL questions

  1. #1
    Join Date
    Apr 2003
    Posts
    1,389
    Rep Power
    0

    Default 2 MySQL questions

    1 - can I use pipes ( | ) instead of commas ( , ) to separate fields?

    2 - my local server throws errors when trying to execute sql queries over 1MB large, but my webhost is able to process up to 5MB. How do I tweak my local server to handle large queries?

  2. #2
    Join Date
    Jul 2004
    Posts
    264
    Rep Power
    0

    Default Re:2 MySQL questions

    Number 1 - I suppose u r talking about loading from a text file ..... eg LOAD DATA INFILE 'file_name.txt' INTO TABLE table_name FIELDS ENCLOSED BY '|' ;

    or if u r creating an output file .... eg

    SELECT * INTO OUTFILE 'file_name' FIELDS ENCLOSED BY '|' FROM table_name;

  3. #3
    Join Date
    Apr 2003
    Posts
    1,389
    Rep Power
    0

    Default Re:2 MySQL questions

    more along the lines of INSERTS, but yeh..

  4. #4
    Join Date
    Feb 2003
    Posts
    3,184
    Rep Power
    0

    Default Re:2 MySQL questions

    mysql has a network transfer bandwidth setting somewhere in it's settings try increasing the value

  5. #5
    Join Date
    Apr 2003
    Posts
    1,389
    Rep Power
    0

    Default Re:2 MySQL questions

    tried all the settings on MySQL .. nada.. tried tweaking PHPMyAdmin and things got worse.. oh well..

    I have an over 60MB file that I need to run and I'm stuck

  6. #6
    Join Date
    Jul 2004
    Posts
    264
    Rep Power
    0

    Default Re:2 MySQL questions

    ok check this out ....

    max_allowed_packet
    The maximum size of one packet or any generated/intermediate string. The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns or long strings. It should be as big as the biggest BLOB you want to use. The protocol limit for max_allowed_packet is 16MB before MySQL 4.0 and 1GB thereafter.
    for your server prob .... u should be able to use mysqld to change that parameter.... if that doesn't work ... well ...


    and about the pipes .... if u mean usin pipes in an INSERT statement then i don't think that would be possible

  7. #7
    Join Date
    Apr 2003
    Posts
    1,389
    Rep Power
    0

    Default Re:2 MySQL questions

    thanks a1.. here are my findings:

    1 - the prob was with phpmyadmin.. not mysql.

    2 - setting the max script execution time to '0' (thereby disabling it) in phpmyadmin config solved the problem

    3 - I can output in pipes.. but NOT in SQL syntax.. therefore using it in inserts won't work

    4 - the 'LOAD DATA INFILE' command is excellent for large tables.. loads a 64MB text file (roughly 16000 rows and 11 fields = 176000 entries.. roughly) in less than 5 mins

    5 - the 'LOAD DATA INFILE' command has one fatal flaw for me.. you have to load table by table.. so because i Have 67 tables I use phpmyadmin for the smaller ones and the L.D.I. command for the bigger boys


    Thanks for the help A1.


    PS: If anyone is curious and wants to see what I'm working on hit http://shop.digisolvit.com

  8. #8
    Join Date
    Jul 2004
    Posts
    264
    Rep Power
    0

    Default Re:2 MySQL questions

    Yeh ... anytime man ....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •