3

I have a MySQL query generated by PHP and it is stored in a string.

The amount of data to be inserted is quite large. When I run the query to insert the data into the database nothing happens. When I copy the string into a instance of vim and save the file as *.php, only the first ~2995 characters are highlighted in the string.

Even if I delete lines from the first 2995 characters, the next lines in the string are highlighted so that the first ~2995 characters are always highlighted.

I thought at first that there was a maximum string length, but found it was 2GB in this thread which my string is definitely not.

I don’t know if it's helpful, but the string follows the format

INSERT INTO Table_Name(Field1, Field2) VALUES('value1', 'value2'),......

NOTE: All quotes in the insertion values have been escaped or removed so that should not cause a problem.

Community
  • 1
  • 1
user3247608
  • 583
  • 3
  • 9
  • 17
  • What is the size of the column you are trying to insert into in your database? – Fluffeh Jun 01 '14 at 06:46
  • @Fluffeh Sorry I don't understand what you mean by size of column – user3247608 Jun 01 '14 at 06:48
  • Is the column you are trying to insert into a text, a varchar, a tinytext data type? It might be that the database is simply putting in what it can - then ignoring whatever else there is in your variable. – Fluffeh Jun 01 '14 at 06:51
  • @user3247608 Are you sure the DB is not corrupt? If it is InnoDB there could be issues. – Giacomo1968 Jun 01 '14 at 06:53
  • its varchar(20), but I have checked all the lengths are less than 20 before creating the string. – user3247608 Jun 01 '14 at 07:03
  • Do you have any control codes in the string you're trying to insert? I'm thinking you have something like an embedded CR/LF/EOL/SOMETHING char in there that's prematurely ending the string. – FreudianSlip Jun 01 '14 at 07:30

1 Answers1

3

This sounds like an issue with max_allowed_packet size in your my.cnf. More details here.

The details below mention using the mysqld from the command line to adjust this. But if possible it is best to just go straight into the my.cnf file and adjust max_allowed_packet in there. For example on a few servers I manage that use InnoDB, I set that value to max_allowed_packet=64M.

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

shell> mysqld --max_allowed_packet=16M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]
max_allowed_packet=16M

It is safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you do not run out of memory by using large packets accidentally.

EDIT: I also found another setting that might help, net_buffer_length:

Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement.

This variable should not normally be changed, but if you have very little memory, you can set it to the expected length of statements sent by clients. If statements exceed this length, the connection buffer is automatically enlarged. The maximum value to which net_buffer_length can be set is 1MB.

The default for net_buffer_length is 16384 bytes which is 0.015625MB which then is 16KB. Try adjusting that value & try again. Maybe something like 524288 bytes which is 512 KB or 131072 bytes which is 128 KB. What you need to know is net_buffer_length is connected directly to mysqldump line lengths. So this could be it.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
  • The size of the string is 1/10 of the size of the max allowed packet value. – user3247608 Jun 01 '14 at 06:49
  • @user3247608 Are you sure you know what the `max_allowed_packet` size is on your server? The default might be `1MB` but you really never know what your specific server is setup for. – Giacomo1968 Jun 01 '14 at 06:51
  • @user3247608 Check my latest edit for `net_buffer_length`: “This variable should not normally be changed, **but if you have very little memory, you can set it to the expected length of statements sent by clients.**” This has taught me something new as well! – Giacomo1968 Jun 01 '14 at 06:59
  • the default is ~1MB, but my string is not 1MB long. – user3247608 Jun 01 '14 at 07:02
  • @user3247608 “the default is ~1MB” How do you know your MySQL server is set to default values? Have you logged into the server and run a simple `SHOW VARIABLES;` to confirm? – Giacomo1968 Jun 01 '14 at 07:03
  • yes, I did SHOW VARIABLES LIKE 'max_allowed_packet'. – user3247608 Jun 01 '14 at 07:05
  • @user3247608 What about `SHOW VARIABLES LIKE net_buffer_length`? – Giacomo1968 Jun 01 '14 at 07:06
  • 1
    I get 16384, is this too small? – user3247608 Jun 01 '14 at 07:07
  • @user3247608 1048576 bytes is 1MB. 16384 bytes which is 0.015625MB which then is 16KB. Try adjusting that value & try again. Maybe something like 524288 bytes which is 512 KB or 131072 bytes which is 128 KB. What you need to know is `net_buffer_length` is connected directly to `mysqldump` line lengths. So this could be it. – Giacomo1968 Jun 01 '14 at 07:12
  • tried that with set global net_buffer_length=524288, and ran the code again with no change – user3247608 Jun 01 '14 at 07:16
  • @user3247608 Sorry. I am all tapped out. Good luck! – Giacomo1968 Jun 01 '14 at 07:17
  • Ok, Tnx for your help – user3247608 Jun 01 '14 at 07:17