By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,493 Members | 3,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,493 IT Pros & Developers. It's quick & easy.

text table column would take at most 236 characters

P: n/a
I am trying to insert some textual data belonging to an HTML page into
a table column with 'TEXT' as data type

mysql's maual _/manual.html#String_types tell you, you may insert up
to (2^16 - 1), that is 65535 characters, but I am getting errors when
I try to insert a column larger than 236 characters.

Initially I thought it might be because I had to escape some
characters, but after playing a some scenarios and just inserting a
bunch of 'a's I have come to the conclusion that there are some length
limitations that is being somehow imposed on.

I have read the manual looking for a solution to my problem to no
avail.

my 'max_allowed_packet' is set to 1MB, 1048576, which is way larger
than 236

What is it I am not seeing?

How do I solve my problem?

Here is the result I get when I run "mysqld --verbose --help" to get
my current system conf. options

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
help TRUE
abort-slave-event-count 0
basedir D:/mysql-4.1.7-win/
bdb TRUE
bind-address (No default value)
character-set-server latin1
character-sets-dir D:\mysql-4.1.7-win\share\charsets/
chroot (No default value)
collation-server latin1_swedish_ci
concurrent-insert TRUE
console FALSE
datadir D:\mysql-4.1.7-win\data\
default-character-set latin1
default-collation latin1_swedish_ci
default-time-zone (No default value)
disconnect-slave-event-count 0
enable-locking FALSE
enable-pstack FALSE
external-locking FALSE
gdb FALSE
init-connect (No default value)
init-file (No default value)
init-slave (No default value)
innodb TRUE
innodb_data_home_dir (No default value)
innodb_fast_shutdown TRUE
innodb_file_per_table FALSE
innodb_flush_log_at_trx_commit 1
innodb_flush_method (No default value)
innodb_locks_unsafe_for_binlog FALSE
innodb_log_arch_dir (No default value)
innodb_log_group_home_dir (No default value)
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_status_file FALSE
innodb_table_locks TRUE
isam TRUE
language D:\mysql-4.1.7-win\share\english\
local-infile TRUE
log (No default value)
log-bin (No default value)
log-bin-index (No default value)
log-error
log-isam myisam.log
log-queries-not-using-indexes FALSE
log-short-format FALSE
log-slave-updates FALSE
log-slow-queries (No default value)
log-update (No default value)
log-warnings 1
low-priority-updates FALSE
master-connect-retry 60
master-host (No default value)
master-info-file master.info
master-password (No default value)
master-port 3306
master-retry-count 86400
master-ssl FALSE
master-ssl-ca (No default value)
master-ssl-capath (No default value)
master-ssl-cert (No default value)
master-ssl-cipher (No default value)
master-ssl-key (No default value)
master-user test
max-binlog-dump-events 0
memlock FALSE
myisam-recover OFF
ndbcluster TRUE
new FALSE
old-passwords FALSE
pid-file
D:\mysql-4.1.7-win\data\cande-34lijrpys.pid
port 3306
relay-log (No default value)
relay-log-index (No default value)
relay-log-info-file relay-log.info
replicate-same-server-id FALSE
report-host (No default value)
report-password (No default value)
report-port 3306
report-user (No default value)
rpl-recovery-rank 0
safe-user-create FALSE
secure-auth FALSE
server-id 0
shared-memory FALSE
shared-memory-base-name MYSQL
show-slave-auth-info FALSE
skip-grant-tables FALSE
skip-slave-start FALSE
slave-load-tmpdir
C:\DOCUME~1\CAMILO~1.CAN\LOCALS~1\Temp\
socket MySQL
sporadic-binlog-dump-fail FALSE
sql-bin-update-same FALSE
sql-mode OFF
symbolic-links TRUE
temp-pool TRUE
tmpdir (No default value)
use-symbolic-links TRUE
verbose TRUE
warnings 1
back_log 50
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
connect_timeout 5
crash_binlog_innodb 0
date_format (No default value)
datetime_format (No default value)
default_week_format 0
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
expire_logs_days 0
flush_time 1800
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (No default value)
group_concat_max_len 1024
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_safe_binlog FALSE
innodb_thread_concurrency 8
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
long_query_time 10
lower_case_table_names 1
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 100
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_join_size 4294967295
max_length_for_sort_data 1024
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
myisam_block_size 1024
myisam_data_pointer_size 4
myisam_max_extra_sort_file_size 2147483648
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
open_files_limit 0
preload_buffer_size 32768
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type 1
query_cache_wlock_invalidate FALSE
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only FALSE
read_rnd_buffer_size 262144
record_buffer 131072
relay_log_purge TRUE
relay_log_space_limit 0
slave_compressed_protocol FALSE
slave_net_timeout 3600
slow_launch_time 2
sort_buffer_size 2097144
sync-binlog 0
sync-frm TRUE
table_cache 64
thread_cache_size 0
thread_concurrency 10
thread_stack 196608
time_format (No default value)
tmp_table_size 33554432
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
wait_timeout 28800
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Albretch wrote:
I am trying to insert some textual data belonging to an HTML page into
a table column with 'TEXT' as data type

mysql's maual _/manual.html#String_types tell you, you may insert up
to (2^16 - 1), that is 65535 characters, but I am getting errors when
I try to insert a column larger than 236 characters.

Initially I thought it might be because I had to escape some
characters, but after playing a some scenarios and just inserting a
bunch of 'a's I have come to the conclusion that there are some length
limitations that is being somehow imposed on.


There is no such limit in MySQL, I think your problem is somewhere else.

You can verify this by using the command line tool (mysql) and try
inserting 255 a-characters to the column by using sql INSERT command.
Jul 20 '05 #2

P: n/a
Yeah exactly, this is what I was using in the first place.

I think it might be somehow related to the windows command promt
interpreter and/or mysql's relationship with it for the windows fork.

Do the following exp.

mysql> CREATE TABLE texttest (ix INT, txt TEXT);
Query OK, 0 rows affected (0.08 sec)

then try to inser a relatively long text value

mysql> INSERT INTO texttest (ix, txt) VALUES
(711,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaa');

In my case mysql does not insert it and actually trims the input

mysql> INSERT INTO texttest (ix, txt) VALUES
(711,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaa

and I think mysql is part of the bug because you can:

1. simply paste the whole string onto a command window
2. try running it, and
3. get back the long string from the command prompt cache by clicking
the up arrow key

Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:\>INSERT INTO texttest (ix, txt) VALUES
(711,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
'INSERT' is not recognized as an internal or external command,
operable program or batch file.

C:\>

Aggro <sp**********@yahoo.com> wrote in message news:<lo***************@read3.inet.fi>...
Albretch wrote:
I am trying to insert some textual data belonging to an HTML page into
a table column with 'TEXT' as data type

mysql's maual _/manual.html#String_types tell you, you may insert up
to (2^16 - 1), that is 65535 characters, but I am getting errors when
I try to insert a column larger than 236 characters.

Initially I thought it might be because I had to escape some
characters, but after playing a some scenarios and just inserting a
bunch of 'a's I have come to the conclusion that there are some length
limitations that is being somehow imposed on.


There is no such limit in MySQL, I think your problem is somewhere else.

You can verify this by using the command line tool (mysql) and try
inserting 255 a-characters to the column by using sql INSERT command.

Jul 20 '05 #3

P: n/a
Albretch wrote:
then try to inser a relatively long text value

mysql> INSERT INTO texttest (ix, txt) VALUES
(711,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaa...
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaa...


This might not solve your problem, but do this:

mysql> INSERT INTO texttest (ix, txt) VALUES( 711, 'aaaaaa [press enter]
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaa [press enter]
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaa [press enter]
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaa [press enter]

<repeat the above line as many lines as you like to make sure that you
have at least 500 characters or something>

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaa' );

And it should insert it fine, except if will insert the line changes
also. This is just to show that the limit is not the problem. Also, if
you really need to insert a long line of text, use file instead. Create
file query.sql for example and insert in there:

use databasename;
insert into texttest(ix,txt) values( '500 characters here' );

Then from command promt (not mysql command line)
c:\mysql\bin\mysql -u username -p < query.sql

And it should insert the long text line correctly.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.