When creating a table in Maria DB 'Truncated incorrect INTEGER value' error is generated even though there are no °c symbols in the cells

I am creating several tables for one big join, everything works except for one table which I cannot create. There is a column in one initial table that contained Integers of temperature in °C in each cell (e.g. 4 °C, 6°C etc. ). So what i did was that I got rid of °C symbol so that only Integers are left in this columns since I needed to avg the numbers etc. When I am creating a Select or a View everything works fine. But when I try to create a Table I am receiving following message:" SQL Error [1292] [22007]: (conn=41) Truncated incorrect INTEGER value: ‘4 °c’ ". And this is strange because I do not have any °C symbol in the cells next to the Integers. Therefore, I can create a view but I cannot create a table. Anyone can think of any reason? Please take a look of a View for that some guys helped me with on this forum here.

  CREATE OR REPLACE VIEW v_weather_data AS 
  	    CONVERT (REPLACE(w.temp, ' °c', ''), INT) AS 'daily_avg_temp_°c',
	    CONVERT (REPLACE(w.rain, ' mm', ''), DECIMAL) AS 'rainy_hours',
	    CONVERT (REPLACE(w.temp, ' km/h', ''), INT) AS 'max_wind_gust_km_h'
	   FROM countries c
	  	 	weather w 
	  	 	c.capital_city = w.city 
	  	 	w.city IS NOT NULL 
	  	 ORDER BY w.date DESC

The one thing that stands out as a possible error is this part.
Should it really be w.temp?


Of course that was the error! Thank you, I can’t believe I didn’t see it :slight_smile:
Now I can create a table as well!

1 Like

It happens when you are trying to debug.
You look at it so long you can’t see the wood for the trees. It just needs fresh eyes.


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.