I have tables where I am running views off of. Anything with over varchar(255) get automatically cut off in a view.
I even created a query to create a table using AS SELECT (that was a view) with the right thresholds set for each field... and it still gets cut off at the same spot.
What setting is this and why when I create a table with varchar(1000) - and this is verified - does it only show 255 characters???
Steps for view. I created a test table to reproduce it.
- Table testlimit
- test1 varchar (255)
- test2 varchar (1000)
Populated a couple rows. One of the rows have longer than 255 characters for test2 field.
Creating view... select * from testlimit.
Boom the longer data is trimmed for field test2.
Also Example #2....
Building a query to reproduce it as a table.
CREATE TABLE testlimit2 AS SELECT * FROM testlimit.
Open up new testlimit2 table and shows test2 as varchar(1000).
Data for test2 is still trimmed.
Also Example #3...
CREATE TABLE testlimit_commaexplode
SELECT
test1
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(test2, ',', n.digit+1), ',', -1)) test_sep,
FROM
testlimit
INNER JOIN
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) n
ON LENGTH(REPLACE(test2, ',' , '')) <= LENGTH(test2)-n.digit
- This example pulls comma separated field so that each item has its own row. This fails too and the data is trimmed at the exact same spot as the view and create table - in some examples it trims off 3+ rows.
I use navicat to build thing but not sure how the software would effect that. CharacterSet - utf8, collation utf8_general_ci.
New Update
The server seemed to be acting wonky all the way around. Many weird issues so I decided to reboot. After rebooting I did some tests after and wow was I surprised...
In our main tables - that haven't changed in years - and that have varchar(1000) set for some larger rows... they are still cut off. Not surprised there. I tried to load the import file again... Again tells me there are no changes to be made (even though for several rows the import file has the extra characters for thousands of rows).
So I decide to create new tables and import the data. I make it really easy and import just our primary key and one of the long columns. I choose varchar(1000)... Fails to import. Even though I just imported this same file above (with more rows/fields). The data being imported for sure does not have 1000 characters.
I then create a new table and import the same small test file, this time choose text as my column type. Boom everything works - no trimming off the data. I verified in creating a new view and all of the data is still there.
So I will update the question to be more accurate. How/What broke my varchar fields?? These things have worked for YEARS. Was there some sort of update that snuck in that changed this in the past few weeks?? Navicat I haven't changed in years and server doesn't even have access for this. Windows is company controlled so yes it could at anytime. I don't believe MySQL could update but I guess it is possible.
Here is an example of the truncation - and note that I tried many times to just type in random letters (to rule out "/" and "&")and got the same limit:
In Database after "Bug" Commercial & Retail Bank, Investment Bank / M&A Advisory, Broker-Dealer, Inter-Dealer Broker, Exchange, Independent Research, Clearing & Securities Services, Asset Management & Investment Advisory, Hedge Fund, Insurance, Private Equity / Venture Capital,
In Database Before "Bug" Commercial & Retail Bank, Investment Bank / M&A Advisory, Broker-Dealer, Inter-Dealer Broker, Exchange, Independent Research, Clearing & Securities Services, Asset Management & Investment Advisory, Hedge Fund, Insurance, Private Equity / Venture Capital, Custody & Fund Accounting, Corporate Energy & Basic Materials, Corporate Consulting & Advisory, Corporate Retail, Corporate Technology, Corporate Other, Government, Academia, Media, Individual Investor
The example above is kind of crazy because every way I massage the table, it creates the bug. So before "bug" is just the same table from last week. If I import data to this, if I create a view, even if I try to switch the longer columns to text.... boom bug hits and data is chopped off. I have data backups and I think I can start fresh and create the tables using text fields but this is crazy that it has worked as is for year and BOOM stopped working.
