cast( value as UNSIGNED)

stops processing

value

as soon as it encounters something that does not represent a number, iirc. So here it would stop at the comma. What you could try is to build the join on a LIKE clause:

ON x.opt_code LIKE concat( '%', y.opt_code_id, '%')

Definitely not the fastest join, but, it should work

There is totally a way of doing this faster in tsql, string_split can convert a column containing CSV data into a joinable table which makes dealing with very silly old data much easier. Have heard people using JSON data types to do this also but not sure how off top of head. Hopefully there may be an equivalent in mysql?

Hi. I'm replicating a question I've asked on the Working Code Podcast discord. Interested in hearing from anyone with MySQL DBA experience.

I am going to ask a contextless question because any context will influence the point of the question, and accordingly the answer.

THIS IS IMPORTANT: Any answers given should be from existing knowledge, and not from googling. I'm not testing ppl's ability to google stuff. I am trying to establish what "common knowledge" on the topic is.

MySQL "CREATE TABLE ... SELECT Statements". Any observations?

Cheers. Sorry it's vague.

Any SQLS dbas here that can tell me how to lock down user access to

selects

only?

i am running this query

select individual_Ratings.*,count(rating) as tRating from individual_Ratings where productID = 99 group by rating

and my query is giving me an error on this

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shopzone1.individual_Ratings.individualid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

its weird how do i do sql_mode=only_full_group_by or is there a universal way to make the query work

if i do in mysql console i am getting this

mysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    49
Current database: *** NONE ***

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
mysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
mysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

the error states the problem, you are including columns by way of the * that are not part of the group by statement

you should brush up a bit on basic sql

I'm cleaning up some legacy code to move to a new server, including adding cfqueryparam tags where they don't exist. I just into an instance where they are passing the entire query as a URL parameter. Is there any way to secure this and is there any better open door for SQL injection that this?

This the database cfconfig I've been using for a while now. Does ACF/Lucee encrypt the connection? Does it use the SSL certificate that the server/website uses? And is it risky if my web server is in Amazon making direct sql connections to a database in Azure?

"mydatasourcename": {
     ...
     "class": "macromedia.jdbc.MacromediaDriver",
     "database": "${db_database}",
     "dsn": "jdbc:macromedia:sqlserver://${db_host}:1433;databaseName=${db_database};SelectMethod=direct;sendStringParametersAsUnicode=false;querytimeout=0;MaxPooledStatements=100;jdbcbehavior=0",
     "host": "${db_host}",
     "password": "${db_password}",
     "port": "1433",
     "username": "${db_login}"
}

I am not sure how db drivers work underneath the hood. I would assume it uses the configured certificate on the server. Anyone?

My apologies if this has been addressed before, but I have a question about the use of views within views. I have seen some uses where there are views that use views that use views (3, 4, 5 levels deep). Any thoughts or concerns on performance on this?

Not really. The SQL query optimizer will still create the best execution plan

The only thing that bothers me is when it gets so deep it's a pain to manage and debug

Also, I have used some diagramming and dictionary tools in the past, but it's been a while. Favorites, anyone?

main issue i find with views in views in sql server anywho is when you alter one in the chain you need to call refresh view to make the next one in the chain pick up the changes. and when they are 5 views deep you have to do that in the correct order and its annoying

depends on the DB engine, i know oracle handles views very differently, so you may see multiple views chained for performance reasons or to leverage the oracle specific view behavior, which i think is pretty neat

Has anyone ever seen a case where a computed column in MS Sql looses it's value when cfoutput. It should be a 2, but the dump and output is showing as 0.

anyone tried this out? https://twitter.com/hnasr/status/1657569218609684480 https://github.com/0xShamil/ulid4j

Can someone tell me how to create a datasource to connect to an MSSQL instance that is not the default?

dbaseServer\DEMO

, for example? When using this in Lucee, the instance name is ignored.

how can i format this date in sql to show it properly including if there is any timezone 2023-06-19T151016.381+00:00 using sql server 2014

Has anyone upgraded an AWS RDS mySQL database from v5.7 to v8? Or even just switched from 5.7 to 8. Any pain points I should be aware of? I need to move some legacy applications as 5.7 is reaching end of support.

anyone know offhand if there is a TSQL function similar to COALESCE but instead of it returning the first non-null value it returns the largest value in the series?

sql server, is it possible to determine how much storage a particular column in a table is consuming (rather than the table as a whole)?

i am trying to check against the datetime of my table

select * from schedule where TRIM(gameTime)  = "2023-11-14 00:00:00"

and my column gameTime is

gameTime	datetime	YES

i am using Mysql 5

it has one row but it is retuning me no rows

You don't need to trim a datetime type. What is returned when you leave out the WHERE?

select * from schedule;

And as a test you can use BETWEEN

select * from schedule where gameTime between 'xxx' AND 'yyy';

Actually I might have just worked it out... What results do you get if you use single-quotes instead of double-quotes? Double quotes is used a column name in mySQL

select * from schedule where TRIM(gameTime)  = '2023-11-14 00:00:00'

You can avoid that problem by using

cfqueryparam

for all items that appear in a WHERE / GROUP By etc.