mysqladmin user account not secure? I still playing with my own DB trying to learn and saw this:

I could change the root password without any problem at all... If I'm in the server I can create an algorithm to start testing password and and someday I will find it, I mean:

Web-Services-iMac-2:~ jbolivar$ mysqladmin -utest1 -p**SOME_THING_HERE** password test1.
is it ok to change password using this???:

update table mysql.user set password=PASSWORD('test')  where user='test1';

beside that if I create a dictionary table (a table with all possible words) and apply PASSWORD("word") I can make a join and find the value of any pass, right?.
Can you give me your opinion about my analysis?

Before you continue playing with mysqladmin, you need to make sure your installation is not intentionally giving away access.

For starters, can you login to mysql like this?

# mysql <hit enter>

If you can get just like that, run this command:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

If CURRENT_USER() return a user and host where the user is blank, then you were allowed in as an anonymous user. At that point, you can remove anonymou users with this:

DELETE FROM mysql.user WHERE user='';
FLUSH PRIVILEGES;

Now, locate all users with no password with this:

SELECT user,host,password FROM mysql.user;

If any users have no password, you can issue new passwords for user using mysqladmin or you could just assign them as follows:

UPDATE mysql.user SET password=PASSWORD('whateverpassword') WHERE user='...' AND host='...';
FLUSH PRIVILEGES;

Now, check for remote users

SELECT user,host FROM mysql.user WHERE host='%';

If you see any, run this:

DELETE FROM mysql.user WHERE host='%';
FLUSH PRIVILEGES;

Make sure when all is said and done that at least root@localhost and/or root@127.0.0.1 exist and have a password

SELECT user,host,password FROM mysql.user WHERE user='root';

I could probably go on. Here are other posts I have about stuff like this:

Cannot drop anonymous user from mysql.user
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Secure method beside phpMyAdmin for remote mysql
MySQL : Why are there "test" entries in mysql.db?


What is the optimal number of worker tasks to specify in MySQL Workbench for a speedy migration? What is the optimal number of worker tasks to specify in MySQL Workbench for a speedy migration?

The default is 2.

The MySQL server is running on the same Windows machine, installed with defaults.  It has 8 vCPUs and 61GB memory.

There is a one worker per table limitation, so the maximum number depends on the number of tables you are migrating.

Notes:

A decent maximum is the number of cores on your system
If you specify a large number but have fewer tables, then only the needed threads will be launched
If the network saturates, like maybe 20 tables with 20 workers, then the copy may actually be slower

So, there isn't a simple answer but hopefully this helps. For additional details, see the related wbcopytables documentation.


How to design a database table in a context where the columns can potentially change with each new row Lets consider the following task:  For a NLP task I am collecting news title in a database. I need to use this data for word frequency analysis, so I tokenize the title and store the words in the following way:
titles table:

titleID    word1    word2    word3    ....
1          0        0        1
2          1        1        0
3          0        0        0
...

Every column is a different word, and i mark it 0 or 1 according to whether or not it is present in the title.
The issue is of course that each time i add a new title, there might be a word that is new and therefore I would need to add a new column for this word.
In SQL world that means a potential migration at each insert of a new row, which is not tenable.
I could use nosql but I would still like that table to have relations with other tables in my database.
What would be the alternatives for my case? What is the common way to store NLP data in a database?

What you probably want to do is normalize your data. Please read up on it in this post: What is Database Normalization in SQL?
To summarize, you should actually restructure your table design by removing all the word columns and storing the words themselves into their own table that is referenced by your Titles table.
Your Words table would have a unique identifying column like WordId which would be referenced by your Titles table which itself would have a TitleId column.
You can then have a third table to relate the many-to-many relationship between the two tables (named something like TitleWords) and it would have TitleId and WordId as columns so you could link between the Titles and Words tables.
Implementing your database design this way not only helps your maintainability of the relational data structure, but it also reduces redundancy and saves space in your database leading to efficiencies as well.


SQL Server Not Consuming all Memory - Insufficient System Memory in Resource Pool 'internal' I am running SQL Server Standard 2014 with SP3. I am noticing a lot of activity on the log viewer around insufficient system memory in resource pool and memory Broker.

I have searched on this error and it was pointing back to SQL 2008 with a KB as a fix.
Am I looking at this right that SQL is only using less than 4GB of the memory?   We are Running Dynamics AX 2012, so the queries can be quite intense.

My physical server has 56Gb of memory and SQL has 16384 min and 36864 as max memory.
When querying SQL is shows that only 3.51 is being consumed.
I am using the following query to determine that which lines up with the Windows Task manager.

SELECT CONVERT(DEC(8,2),OSPM.physical_memory_in_use_kb/1024.0/1024,0) AS SQL_GB FROM sys.dm_os_process_memory OSPM
SELECT CONVERT(DEC(8,2),OSM.available_physical_memory_kb/1024.0/1024,0) AS AvailGB FROM sys.dm_os_sys_memory OSM

The memory seems available but SQL is only using a tiny portion, if I am looking at this right. My other test systems do not experience this type behavior.

Essentially, the error is saying that there is not enough memory available to Sql Server for it to grow its internal buffers - this generally happens if Windows is having to page a lot or other processes are consuming the memory.

You should also remember that just because you tell Sql Server to use a minimum amount of RAM, it can use less if the host OS needs it more - the host always takes priority and the buffer pool will get trimmed if the host starts to detect memory pressure. For instance, in the documentation for Server Memory Configuration Options it says:

  SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.

I would check to see what other processes are running (for instance Service Broker), and how much strain they are putting on the server. If possible, increase the size of the VM or move some of the other processes onto other boxes.

Lock Pages In Memory Option

It is a common misconception that when you use Lock Pages In Memory that it can never be released. Using the LPIM setting prevents Windows moving pages in memory to virtual memory on disk (the page file). It does not however, prevent Sql Server from reducing its memory footprint if it detects that the host OS is low on memory. The snippet below is taken from the same page the previous snippet is taken from:

  Setting this option does not affect SQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks.

Sql Server uses the Memory Notification API's within Microsoft Windows to detect when the host is running low on system resources. If the host is running low, Sql Server will begin to trim the buffer pool in response. This is an important fact - it is Sql Server that is releasing the memory in response to an external event - Windows is not taking it from Sql Server. The below is taken from the Memory Management Architecture Guide:

  When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS.


Range comparison between tables I have two tables like this:

Runners:
Name|Date    |FromDist|ToDist|RaceN
Al  |1/1/2017|1       |9     |axe
CC  |1/1/2017|11      |12    |axe
Bm  |2/1/2017|6       |2     |Charity

RaceStandards:
RaceN  |Date    |FromDist|ToDist|Direction|Rank
axe    |1/1/2017|1       |10    |+        |1
Charity|2/1/2017|10      |4     |-        |2

The race standards table gives the direction and distance ranges for each race. The runners table gives the distances ran by each runner.
Question/Issue/What I want to do:
1. Get the direction
2. If its +, range should be >=FromDist and <=ToDist; If its -, range should be <=FromDist and >=ToDist
3. Compare the distance range of Runners FromDist and ToDist based on steps 1 and 2. Assign the Rank number of that race if they fall in range, otherwise assign 0
4. If Runner is out of Range the value can be zero

So the end result would be something like this:

Runners:
    Name|Date    |FromDist|ToDist|RaceN   |Rank
    Al  |1/1/2017|1       |9     |axe     |1
    CC  |1/1/2017|11      |12    |axe     |0
    Bm  |2/1/2017|6       |2     |Charity |2

There would be hundreds of rows in each table so I would want it dynamic. There could be overlaps and stuff but I just need a headstart for now, once I get a base code I can try to expand from there. I would appreciate any help. I tried to give as much detail, if more is required please ask.

If I understand correctly, this should give you what you're looking for.

SELECT R.*, COALESCE(RS.Rank, 0) AS Rank
FROM Runners AS R
  LEFT OUTER JOIN RaceStandards AS RS
    ON R.RaceN = RS.RaceN
    AND (
      (RS.Direction = '+' AND R.FromDist >= RS.FromDist AND R.ToDist <= RS.ToDist)
      OR (RS.Direction = '-' AND R.FromDist <= RS.FromDist AND R.ToDist >= RS.ToDist)
    )


Want to write a trigger after UPDATE I have writen a trigger which will log every data updation and will log previous_value, new_value, field_name and many more. But the problem is i have 77 fields in my table. So, it's difficult to write IF ENDIF for each field so i want to know is it possible to write using loop?

I have tried so far::

BEGIN

IF(OLD.company_name != NEW.company_name) THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_name", OLD.company_name, NEW.company_name, CURRENT_TIMESTAMP(), NEW.company_id);
END IF;
IF(OLD.company_first_name != NEW.company_first_name) THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_first_name", OLD.company_first_name, NEW.company_first_name, CURRENT_TIMESTAMP(), NEW.company_id);
END IF;

END

Please help, or any suggestion will be very helpfull.

Thanks in advance.

Since dynamic sql is not allowed in the trigger body,you can use a statement which runs through all the columns and builds the IF statements

SELECT CONCAT('IF(OLD.',COLUMN_NAME, '=NEW.',COLUMN_NAME,' THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_name", OLD.' ,COLUMN_NAME,', NEW.',COLUMN_NAME,', CURRENT_TIMESTAMP(), NEW.company_id; END IF;')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='yourTable' AND column_key!='PRI';

You can narrow the results even more, take a look at COLUMNS table if you want to filter based on column type or remove columns with keys and so on.I removed the primary key columns in the last condition for example.


Cumulative array aggregation in Postgres Does anybody know if it is possible perform cumulative array aggregation in Postgres? For example, to transform this:

create table records
(
  data text,
  path text[]
);

insert into records values
('John', '{"AAA", "BBB", "CCC"}'),
('Paul', '{"AAA", "BBB"}'),
('George', '{"AAA", "BBB", "CCC", "DDD"}'),
('Ringo', '{"EEE"}');

into this:

John    {AAA}
John    {AAA,BBB}
John    {AAA,BBB,CCC}
Paul    {AAA}
Paul    {AAA,BBB}
George  {AAA}
George  {AAA,BBB}
George  {AAA,BBB,CCC}
George  {AAA,BBB,CCC,DDD}
Ringo   {EEE}
Ringo   {EEE,FFF}

You want to use window functions.

select data, array_agg(unnest) over (partition by data order by ordinality)
 from records, unnest(path) with ordinality

In this case, the default "framing" does exactly what you want: from the beginning to the current row; so you don't have to include a framing clause.


MySQL UDF - wrong ELF class: ELFCLASS32 I'm having a problem creating a MySQL udf on my server (I was able to do it locally on OS X). I am compiling it like this on the server:

gcc $(mysql_config --cflags) -I /usr/include/mysql -I. -shared udf_median.cc -o udf_median.so

Here is the output of mysql_config --cflags

mysql_config --cflags
-I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -fasynchronous-unwind-tables  -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DMY_PTHREAD_FASTMUTEX=1

So from what I can tell, MySQL is compiled for 32bit (it was done via x86_64 RPM). I had to install a bunch of different packages to get it to compile (32 bit packages, I think).

When I try to do:

CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';

I get:

Can't open shared library 'udf_median.so' (errno: 11 /usr/lib64/mysql/plugin/udf_median.so: wrong ELF class: ELFCLASS32)

From what I can tell this is a conflict between 32 and 64 bit but I'm not sure on how to fix it. I tried to put the .so in /usr/lib/mysql/plugin/ however MySQL doesn't seem to be recognizing that directory.

Anyone have any recommendations?

Edit

So if I try to compile it without the mysql_config like this:

gcc -shared -fPIC -I /usr/include/mysql -o udf_median.so udf_median.cc

I get these errors:

In file included from udf_median.cc:36:
/usr/include/mysql/m_string.h: In function ‘const uchar* skip_trailing_space(const uchar*, size_t)’:
/usr/include/mysql/m_string.h:261: error: cast from ‘const uchar*’ to ‘intptr’ loses precision
/usr/include/mysql/m_string.h:263: error: cast from ‘const uchar*’ to ‘intptr’ loses precision
/usr/include/mysql/m_string.h:265: error: cast from ‘const uchar*’ to ‘intptr’ loses precision

Unfortunately, mysql_config contains quite a bit of black magic and quite a bit of blatant sketchiness.  It's notoriously untrustworthy if you have more than one copy of it or the version doesn't match your server build.

Unfortunately, too, if you're using one from a distro (not official binaries) then that utility may not even be in the same package as the server... it's bundled with the client, which could easily be 32 bit and work just fine with a 64-bit server.

$ mysql_config
The program 'mysql_config' can be found in the following packages:
 * libmysqlclient-dev
 * libmariadbclient-dev
Try: sudo apt-get install <selected package>

Not confidence-inspiring.

$ mysql_config --version
5.5.40

Neat, I don't even have server on this particular system, and never have.

So, that gives you a sense of how much hocus pocus this little utility can sometimes offer up.

Fundamentally, though, there's this: wrong ELF class: ELFCLASS32 means you compiled the plugin for 32 bit, but your server is indeed 64 bit.

The missing piece from your compiler flags is -m64.  If you add that and remove the -m32 (I'm not sure where that's coming from... mysql_config doesn't include either one on my test system) then you should be able to get that plugin to compile and install.

You have the plugin in the correct directory, or the system wouldn't have found the file to complain about the architecture it was compiled for.


How can I restore the sql_variant_property of baseType back to a table variable in SQL Server Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008

I have a table that I am trying to use as a storage container. It has 3 columns:

reportID integer,
dataLabel varchar(50),
dataValue sql_variant

The way I want to use this is to pass a reportID to a function and have the function return a table variable that is accurately typed. I have the process down EXCEPT for the accurately typed part.

Below is a script that will build a temp table, insert in some values and the pivot the values to the table variable that I would want to use. The problem is that the values type returned for each column is sql_variant and I cannot figure out how to convert them to their baseType. I have tried (unsuccessfully) to use the sql_variant_property but can't figure it out.

For example, in the final table I would like the myDate column to be a dateTime type and not a sql_variant type.

Are there any SQL gurus who can push me in the right direction?

create table #rStorage
    (
      reportID  bigint not null
    , dataLabel varchar (50) null
    , dataValue sql_variant null
    )
go

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'myInt', convert(int,5621))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'mydate', convert(smalldatetime,getdate()))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'myvarchar', convert(varchar(200),'testing Varchar'))

insert into #rStorage (reportID, datalabel, datavalue)
values (1, 'mydateNoTconverted', getDate())

select reportID
, dataLabel
, dataValue
into #tmp
from #rStorage

declare @QuestionList nvarchar(max)
, @qry nvarchar(max)

SELECT @QuestionList = STUFF(
 (SELECT ', ' + quotename(dataLabel)
 FROM #tmp
 GROUP BY dataLabel
 ORDER BY dataLabel
 FOR XML PATH(''))
 , 1, 2, '');

select @qry = '
 select *
 from #tmp

 pivot
 (
 max(dataValue)
 for dataLabel in ('+@QuestionList+')
 ) as q
 '

print @qry

exec sp_executesql @qry;

drop table #tmp
drop table #rStorage

The eventual goal is a udf since a stored proc can't return a table variable. So I tried this with a cursor and some dynamic sql. I think I am close but with the below function I get the error

  Only functions and some extended stored procedures can be executed
  from within a function. Severity 16 State 2

Code:

if exists (select * from sysobjects where  name = N'fn_pivotReportStorage')
   drop function fn_pivotReportStorage
go

create function fn_pivotReportStorage
 (@reportID bigint)
 returns @result table (reportID bigint)
as
begin

    declare @dataLabel sql_variant
    ,   @dataValue sql_variant
    ,   @dataType sql_variant

    ,   @alterTableStr nvarchar(max)

    declare thisCursor Cursor fast_forward for
       select   dataLabel
            ,   dataValue
            ,   dataType = case SQL_VARIANT_PROPERTY(dataValue,'BaseType')
                        when 'varchar' then convert(varchar(50),SQL_VARIANT_PROPERTY(dataValue,'BaseType'))+'('+convert(varchar(50),SQL_VARIANT_PROPERTY(dataValue,'maxLength'))+')'
                        else SQL_VARIANT_PROPERTY(dataValue,'BaseType') end
        FROM reportStorage
        where reportID = @reportID

    insert into @result (reportID) values (@reportID)

    open thisCursor

    fetch next from thisCursor into @dataLabel,@dataValue,@dataType

    while @@fetch_status = 0
    begin
            select @alterTableStr = 'alter table @result add '+ convert(varchar(2000),@dataLabel)+' '+ convert(varchar(2000),@dataType)
            exec sp_executesql @alterTableStr;
            select @alterTableStr = 'update @result set '+ convert(varchar(2000),@dataLabel)+' = '''  +convert(varchar(2000),@dataValue)+''''
            exec sp_executesql @alterTableStr;
     fetch next from thisCursor into @dataLabel,@dataValue,@dataType
    end
    close thisCursor
    deallocate thisCursor

 return
end
go

A lot of problems with what you are trying that I just don't think this particular approach is going to work (I think the goal is interesting):

returns @result table (reportID bigint) - this is basically schema-bound.  You can't alter the schema of the returned table - I see later you are going to attempt to "ALTER" this variable - that's just not going to happen.

Even if you could, this part:

select @alterTableStr = 'alter table @result add '+ convert(varchar(2000),@dataLabel)+' '+ convert(varchar(2000),@dataType) ;

exec sp_executesql @alterTableStr;

isn't going to affect the @result in the outer part - these dynamic SQL parts have their own scope and can't get to the variables in the calling part this way

And then it really comes down to the error you are currently getting - which is sp_executesql can't be run inside a function anyway.

Anyway, to aid you a little in making progress, may I suggest you read the following two articles which are are tangentially related (and I have used recently) regarding parsing CSV and JSON data.  Both use his hierarchy idea (an unpivoted name/value thing) and repivoting and you might find some useful techniques there:

http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-csv-comma-delimited-of-errors/

http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

If you can use the CLR, this article which was published might also be useful:

http://www.sqlservercentral.com/articles/.Net/94922/


Setting MySQLDump to keep running in the background after session times out I'm tryng to set up a new Slave DB by copying another Slave DB. Normally I would simply use MySQLDump to dump the Slave and create a .sql file, then import that into the new database.

However, as our new server is a Linux (Debian 7) server (the slave I'm copying is on a Windows server), I figured I would try to pipe the dump straight into the new database. This seemed to go well, until my putty session expired and it all stopped.

I tried it again, this time using nohup .... &. But still the dump stopped as soon as my session expired.

Is there a way to make this work? My full dump command is:

nohup mysqldump -hxxx -P3306 -uxxx -pxxx --dump-slave --databases a b c d --ignore-table=a.xxx -F | mysql -h127.0.0.1 -P3306 -uxxx -pxxx &

All DB's are MySQL 5.5.

I think I found the problem. Apparently the nohup command ended at the | symbol, so when the session expired the second part of the command stopped with the session.

To get round this I ran:

nohup sh -c 'mysqldump -hxxx -P3306 -uxxx -pxxx --dump-slave --databases a b c d --ignore-table=a.xxx -F | mysql -h127.0.0.1 -P3306 -uxxx -pxxx' &

I checked it was running, and then EXITed the shell, and so far, it is still running.


Table partitioning in MYSQL Master Slave I have to Partition a MYSQL table using Range partitioning. Table is 200M rows big and I have Master/Slave replication.

What I want to do is to stop replication on slave, partition the table on slave and start replication. My question for you guys is would this work despite the fact Master would have no partitioning on that table but slave would have roughly 50 partitions of that table?

Would replication still work ?

The manual does not list a problem, but...

Statement Based Replication should not have any problem since the Slave simply re-executes the DML statement without knowing or caring (up front) about whether the table is partitioned.

Some rare statements are very likely to break replication:

SELECT ... PARTITION pnnn -- 5.6.2
ALTER TABLE -- to change PARTITIONs

Please comment on bug report if you find more issues.


SQL Server 2017 on RHEL Linux with IDM for AD authentication We are using Red Hat IDM to authenticate AD users to SQL Server. Users can login successfully to SQL Server with SSMS and SQL CMD with AD Authentication. But running some elevated commands from SSMS or SQL CMD can run only once in session and it kick out AD authenticated users from SQL Server with the following error:

Error 15404 ‘Could not obtain information about Windows NT group/user '%ls', error code 0x80090304'

I had similar experience. I believe this was due to a limitation in IDM. There is currently a bugzilla filed for this issue. I believe Red Hat is working on addressing this issue.


SQL Server uses too much CPU I have a VM 2012 R2 server with SQL Server 2014 Express SP2 installed. There are some applications and databases, but nothing too big. Databases are small, about 100MB size combined.

At some moment (not from the beginning) the SQL Server process started to show constant high CPU usage. Usually the CPU load is about 15-20%. It shows a periodic picture with a minute of 15-20% load, and a couple of seconds of 0% usage between the periods. Sometimes SQL Server stops to do that (whatever he does...) for like 2 minutes, then starts again with 10-20 seconds of >95% load and "usual" 15-20% after...

Of course it affects performance for users.

I have investigated all I could (session reports, query reports, profiler, memory allocation SP...), but I haven't found anything that could cause this. Or, exactly, I've found there are no sessions, no queries and nothing in the actual applications installed that could load the CPU so much. I tried to shut down all applications using SQL Server and detach their DBs and then restart the server. No use.

I even uninstalled and reinstalled SQL Server, but it started to consume CPU short after startup, while being "clean".

Also, there was no "unusual" configuration, it was just a clean VM with a straightforward SQL Server installation. I have installed it, created the DBs, logins and that's it.

From the Troubleshooting SQL Server high CPU usage by Karthick P.K, I've checked the possible issues with timer, that can be caused by power plan or other settings. Using -T8038 at startup did not affect the CPU load. Power plan is set to high performance.

Why is SQL Server using so much CPU, even if there are no queries and attached DBs? How can I at least learn what's happening?

Issue is the following.

Let a Hyper-V VM is configured with high startup memory (like 10240MB), and dynamic memory limits active and are like 512-10240 range.

VM starts up with full amount of RAM available. Short after load the host reclaims excess memory from the VM. At the same moment SQL Server process starts to create that unexplainable CPU load.

It doesn't happens though, if startup memory set to 512MB.


How to dump only the MySQL database structure? I am trying to take dump of MySQL database structure using the following commands:

mysqldump -d -h localhost -u root -p databasename > dumpfile.sql
mysqldump -d -h localhost -u root -p database databasename > dumpfile.sql
mysqldump -d -h localhost -u root -P 3306 -p databasename > c:\dumpfile.sql

What is wrong with them? Where should I get this file?

if you are using MySQLdump and specify  location will be inside the directory you specified for example:

mysqldump  -u root -d  -P port -p password databasename > c:\dumpfile.sql

the dump location will be inside your machine under C drive

but if you did not specify  the directory the dump location will by inside MySQL\bin

mysqldump -u root -d -P port -p password databasename > dumpfile.sql


Is it normal for MySQL to gradually increase memory usage over time? Is it normal for MySQL to increase memory usage over time? See image below of my server's memory usage over the last two weeks. After "service mysql restart" it drops to 40%. The database is used by a node.js app.

Server has 4GB of RAM with below additions to mysqld.cnf:
innodb_ft_min_token_size = 1
ft_min_word_len = 1
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 5
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
max_connections = 300
long_query_time = 1
innodb_ft_enable_stopword = 0
Thank you!

I can't address the MySQL specifics, but in general, yes, memory usage of database engines will tend to increase over time. As queries are processed, if the data isn't in memory, the engine will load the data from disk into memory. Then, as long as there are no other pressing needs to use that memory, the data will be left there in case it is queried again.
So as a database engine starts up, it'll use a moderate amount of memory. (Some will allocate a "minimum memory" just to have it ready for when they need it.) Then, as it runs, the memory usage will gradually increase, until it reaches either:

an amount configured as the "maximum memory" within the DBMS, or
the maximum amount the operating system will allow it to take

Only then will it start ejecting data from memory to make room for more data coming in from disk.
The specifics vary from DBMS to DBMS, and I've used some terms here that are inspired by SQL Server's terminology. But the general concepts should hold for most engines.
I suspect you'll get other answers with better MySQL details, or a better explanation of memory usage and caching and pools. But I hope this summary is still useful.


Need help trying to run a batch file from a SQL Agent job I am trying to run a batch file from a SQL Agent job. This batch file is a connection command and script file that will run. I can run the the actions straight from Command Prompt, but I need it to be fired from a SQL Agent job for the automation of it.

Every time I try to fire it off I get these errors:

"The system cannot find the file specified"

When I set the command up, I am using the GUI in the Jobs area and using the 'Open' button and then navigating to the proper file. I have also tried changing the Services from the NT user to a Local Admin user, but still had the same end result.

The 'Open' button is for the convenience of importing a large script into the command window, which is not helpful with this type of job step as I explain below.  If you want it to run a batch file, you simply enter the path and filename, such as:

D:\scripts\DoSomething.bat

or

D:\executables\DoSomething.exe

And be sure to specify the type of command as "Operating System (CmdExec)"

Whatever account the step is set to run as will need permission to execute the batch file or process as well as access to any resources that are used in the script or process.

Also, a quirk of this type of job step is that it will only run one command.  So if you put the following in the command input box:

rem This script does something
D:\scripts\DoSomething.bat

It will run the first command and then stop, so the batch file will not be run.  So that is why the 'Open' button is not really helpful for CmdExec job steps.


Moving database file locations in SQL Server 2008 I have SQL Server 2008 Express edition installed on my machine. I had changed the location of some folders from one drive to another (from F: to E:).

Now when I connect to SQL Server using SSMS, it does not show any database. If I try to attach a database by selecting the file it show me the error that

  Cannot attach a database with the same name as an existing database

How can I clean up the no-existing (for SQL Server) databases and re-attach the moved files?

Edit:- In Event Log it is showing errors like following for moved databases:

FCB::Open failed: Could not open file F:\Code\EFTest\App_Data\eftest.mdf for
file number 0.  OS error: 2(failed to retrieve text for this error.
Reason: 15105).

master, model,msdb,tempdb databases are available as these are in default location. The unavailable databases were in custom locations (in moved folder).

It's likely that all you need to do is issue the following command for each user database that was moved:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

I grabbed that line of code from these instructions published by Microsoft. So read those too.


Restore specific MSSQL backup from media set using AWS rds_restore_database I have backed up several Microsoft SQL Server databases to a single media set (.bak file). I have uploaded the database to S3 and am now trying to restore the database using the following script where the database is named MyDB and the backup media set file is MyBackup.bak.
EXECUTE msdb.dbo.rds_drop_database N'MyDB'
EXECUTE msdb.dbo.rds_restore_database @restore_db_name = 'MyDB',@s3_arn_to_restore_from='arn:aws:s3:::dev-web/DbBackup/MyBackup.bak';

Unfortunately this just seem so to restore the first backup from the media set which is from another database. Is there a way to specify which backup I want to restore from the .bak file?

Seems from the documentation that the procedure doesn't support specifying which file within a .bak you're actually trying to restore. The native RESTORE DATABASE command allows you to specify this using FILE = .
I think the solution in your case is to always back up any database to a single .bak file (name the backup file uniquely, and always use WITH INIT when backing up). Combining multiple backups into a single file doesn't have a single benefit that I can think of, especially combining multiple backups of different databases.
To get to the backup you want, you may have to download the multi-file backup file, restore it on your own instance using RESTORE DATABASE ... WITH FILE =, back that up to a new, single-backup .bak file, and upload it back to AWS. But I would consult with AWS support in case they have any other (easier) options.


Simple Query optimization using ORDER BY The following is a simple query that is taking more than 10 seconds to execute:

select * from inventory.asset ass  where ass.product_id=83  and ass.expiry_date > now()  and ass.status=1  order by ass.expiry_date ASC, ass.serial_number ASC limit 10 for update

create table asset (
        id  serial,
        action_date timestamp not null default current_timestamp on update current_timestamp,
        curramt int check (curramt >=0),
        features text,
        initamt int check (initamt >=0),
        opfield1_value varchar(24),
        product_id bigint unsigned not null,
        serial_number varchar(255) not null,
        status tinyint unsigned not null,
        version int,
        export_id bigint unsigned,
        import_id bigint unsigned,
        expiry_date date not null,
        primary key (id),
        unique (serial_number, product_id)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;

create index ix_asset_import_id on asset (import_id);

create index ix_asset_expiry_date on asset (expiry_date);

create index ix_asset_export_id on asset (export_id);

create index ix_asset_serial_number on asset (serial_number);

create index ix_asset_product_id on asset (product_id);

alter table asset
    add constraint fk_asset_import
    foreign key (import_id)
    references shipment(id);

alter table asset
    add constraint fk_asset_export
    foreign key (export_id)
    references shipment(id);

EXPLAIN results:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

'1', 'SIMPLE', 'ass', 'ref', 'fk_asset_status,ix_asset_expiry_date,ix_asset_product_id', 'fk_asset_status', '1', 'const', '2001344', 'Using where; Using filesort'

EXPLAIN EXTENDED results:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra

'1', 'SIMPLE', 'ass', 'ref', 'fk_asset_status,ix_asset_expiry_date,ix_asset_product_id', 'fk_asset_status', '1', 'const', '2001344', '100.00', 'Using where; Using filesort'

You should add one of these two indexes:

ALTER TABLE asset
ADD INDEX product_id_status_expiry_date(product_id, status, expiry_date);

This will help with the filtering.

ALTER TABLE asset
ADD INDEX expiry_date_serial_number(expiry_date, serial_number);

This will help with the filesort.

Only one of them can be used at a time, so add only the most advantageous.


SQL geography::point null parameter error when parameter is not null? I was getting this error:

'geography::Point' failed because parameter 1 is not allowed to be null.

On this sql:

SELECT [ID], geography::Point([lat], [long], 4326) AS [loc]
FROM (
  SELECT [ID], CONVERT(float, [lat]) AS [lat], CONVERT(float, [long]) AS [long]
  FROM (
    SELECT [ID], [lat],
    [long], ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
    FROM [GEO]
  ) AS temp1
  WHERE [sequence] = 1
  AND [lat] IS NOT NULL
  AND [long] IS NOT NULL
) AS temp2
ORDER BY [ID]

But there where no null values, and I was only getting the error on our production machine (Production 13.0.4422.0) and not on our development machine (Dev 13.0.1728.2). After hours of searching and retrying I found that by reordering some stuff this would work:

SELECT [ID], [loc]
FROM (
  SELECT [ID],
    geography::Point([lat], [long], 4326) AS [loc],
    ,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [EFFDT] desc) AS [sequence]
  FROM [GEO]
  WHERE [lat] IS NOT NULL
  AND [long] IS NOT NULL
) AS temp
WHERE [sequence] = 1

I would really like to understand what I was doing wrong in the first query and why this worked in the second query?

The first query I tried to get only the rows that have the most recent records based on a sequence number calculated using an effective date column, and then convert lat and long data to a geography type.

In the second query I convert the lat and long data and then filter.

I thought I was being more efficient with the first query.

I have struck similar issues with Geometry and Geography data types before.  I think the issue is to do with where (and how) the optimizer decides to build the geometry in the plan.

I was having an issue with insert statement that was basically

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable
WHERE X is not NULL and Y is not NULL

After playing around and getting a little confused found that

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable;

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(ISNULL(X,0),ISNULL(Y,0),0)
FROM HeapTable
WHERE ISNULL(X,0) != 0;

both work but the following doesn't

INSERT INTO (ID,GEOM)
SELECT ID, Geometry::Point(X,Y,0)
FROM HeapTable
WHERE ISNULL(X,0) != 0;

For the first query the plan shows that the compute scalar is done before the filter

And the expression being computed has had the ISNULL function stripped from it.

<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="[Geometry]::Point(CONVERT_IMPLICIT(float(53),[sandbox].[dbo].[TestNullGeomIssueSource].[X],0),CONVERT_IMPLICIT(float(53),[sandbox].[dbo].[TestNullGeomIssueSource].[Y],0),(0))">

I think the Point on the Geometry and Geography is flawed in not allowing NULLs to be input.  Simply return a NULL as a result would be more correct in my opinion.

If you wish I can put up my entire test script for this.


Postgres function/procedure won't return value I'm new to PostgreSQL, coming from SQL server and I'm having trouble creating a function and/or procedure. First of all, may I just clarify that the only difference between a function and a procedure is indeed the support for transactions?
Now going back to my problem, I want a simple func/proc that returns the current connected user. Not using it anyhow, so any other example would work, it's only for learing the syntax as pgsql is different from t-sql. Here is my code, which I took from a tutorials website:
CREATE OR REPLACE FUNCTION dev.get_user()
RETURNS VARCHAR AS $user$
DECLARE
    $user$ VARCHAR;
BEGIN
    SELECT current_user AS user;
    RETURN user;
END;
$user$ LANGUAGE plpgsql;

I'm getting this and I don't know why:
ERROR:  syntax error at or near "VARCHAR"
LINE 4:  $user$ VARCHAR;

If I remove line 4, it creates it successfully, but when I run it, it returns this error:
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

I'm on pgsql 12.3, if it makes any difference. Thank you.

may I just clarify that the only difference between a function and a procedure is indeed the support for transactions?

No, the biggest difference is that functions are intended to return something while procedures are not.
In PL/pgSQL, the result of a SELECT statement needs to be stored somewhere (which is what the error message is telling you).
Variables are not defined with a $ so the second $user$ ends the string started with the first $user$ which is why you get the first error.
So the correct way to write this would be:
CREATE OR REPLACE FUNCTION dev.get_user()
  RETURNS VARCHAR AS $user$
declare
  l_user varchar
begin
  select current_user
     into l_user;
  return l_user;
end;
$user$
language plpgsql;

The SELECT isn't really necessary, you can assign a variable directly if you call a function (which current_user is):
begin
   l_user := current_user
   return l_user;
end;

The assignment using := is faster then a SELECT .. INTO.

Note that the whole function can be simplified to:
CREATE OR REPLACE FUNCTION dev.get_user()
  RETURNS VARCHAR
AS
$user$
  select current_user;
$user$
language sql;

For more details see the PL/pgSQL reference


How can I provision more CPU from VMware when MAXDOP is set to 1? We have a BizTalk Server group hosted over seven servers, all hosted in VMware.  This group comprises four BizTalk application servers and three SQL Server instances.
As per Microsoft's out of the box configuration of BizTalk, one of the SQL Server instances in the group has its MAXDOP intentionally set to 1.  According to Microsoft, this setting must not be changed.
We believe that some of the queries running on this SQL instance may be CPU-bound, and would like to consider whether we can boost the CPU resources of this particular virtual machine.
My understanding is that adding additional vCPU cores would be pointless: MAXDOP is set to 1, so any queries constrained by CPU would not be able to make use of any additional CPU cores because they may not run parallel operations.
Is it possible to do anything in this situation?  My assumption is that the clock speed of the vCPU in the virtual machine is the same as the clock speed of the physical CPU in the VMware host, so there is no way of increasing that.

Are all of my assumptions above correct?
If so, does that mean there is no way to boost the CPU available to these queries?
Is there anything else we can do?

Are all of my assumptions above correct?

Yeah, you're correct on all counts as far as I can tell.

If so, does that mean there is no way to boost the CPU available to these queries?

Given the constraints you've described, the only way to "boost the CPU" for those queries would be to move the VM to a host with a CPU that has a faster clock speed.

Is there anything else we can do?

Your best bet would probably be to identify the problematic queries and set up plan guides to add MAXDOP hints to those specific queries.  This will override the server level setting, so those specific queries would go parallel, while the rest would still be at DOP 1.
Some other options, that are less ideal / more likely to cause problems:

Increase server level MAXDOP from 1 - this goes against the config guidance of course, but it is an available option to test and see if it benefits these problematic queries without causing much harm to other queries
Add more CPUs - this doesn't help individual queries that are CPU-bound, but it will allow more queries to run at once potentially.  This could help or hurt depending on the details of the workload.  For example, running more queries at once might just lead to contention (locks or tempdb or something else) rather than increased throughput


MySQL [Bad handshake] error after update I have MASTER-SLAVE configuration on 2 servers. The MySQL replication is set over SSL (I use self-signed certificates).

Everything had been working well. But today some packages (mysql also) were updated via apt update, upgrade. Current version is: mysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64)

Both server and client have the same mysql version now, but slave can't connect to the master. In master's log I see the following error repeating many times:

2019-11-19T16:49:55.109509Z 1130 [Note] Bad handshake
2019-11-19T16:50:55.296184Z 1432 [Note] Bad handshake

What should I check in order to fix this problem? I've already tried to reissue certificates, but it didn't help.

Thanks.

MySQL 5.7.28 dropped support for yaSSL. On some platforms, such as RHEL and presumably Ubuntu, that resulted in a change from yaSSL to OpenSSL. There are a variety of things that would have worked under yaSSL but no longer work under OpenSSL. For example, yaSSL could not handle certificate chains, but OpenSSL both sends and verifies full chains.
Among other troubleshooting techniques, try taking a packet capture of the TLS handshake with tcpdump and decoding it in Wireshark.


MySQL query returns unexpected results inside a stored procedure This is my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `facebookId` VARCHAR(20), IN `userAccessToken` TEXT)
    NO SQL
    COMMENT 'check if user is valid. return true or false'
Begin
    /* declare the select resluts variables*/
    Declare accessToken TEXT;
    Declare expires datetime;

    /* fill the variables with saved user's access token an expires time*/
    Select Access_Token, Expires
    Into accessToken, expires
    From Users Where Facebook_Id = facebookId;

    select expires,accessToken;
    /* If the saved access token and expires are valid return true */
    If (BINARY accessToken = userAccessToken And expires > now()) Then
        select true As isUserValid;
    Else
        select false As isUserValid;
    End If;
End

For some reason the query:

/* fill the variables with saved user's access token an expires time*/
Select Access_Token, Expires
Into accessToken, expires
From Users Where Facebook_Id = facebookId;

returns the Expires as null all the time.

When I'm running the same query outside of the procedure, it returns a valid value for Expires.

more correct change variable names:

CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `p_facebookId` VARCHAR(20), IN `p_userAccessToken` TEXT)
    NO SQL
    COMMENT 'check if user is valid. return true or false'
Begin
    /* declare the select resluts variables*/
    Declare v_accessToken TEXT;
    Declare v_expires datetime;

    /* fill the variables with saved user's access token an expires time*/
    Select U.Access_Token, U.Expires
    Into v_accessToken, v_expires
    From Users U Where U.Facebook_Id = p_facebookId;

    select v_expires, v_accessToken;
    /* If the saved access token and expires are valid return true */
    If (BINARY v_accessToken = p_userAccessToken And v_expires > now()) Then
        select true As isUserValid;
    Else
        select false As isUserValid;
    End If;
End

it help read code more easy for You and for MySQL parser as well


Oracle 12c - Insufficient Privileges When Trying To Create A Trigger With SYS User I want to create a trigger for CRE schema. I'm trying to create this trigger with SYS user but I have insufficient privileges error.
My trigger:
CREATE OR REPLACE TRIGGER CRE.TRIGGER_NAME
AFTER LOGON ON DATABASE
DECLARE
asd1 NUMBER;
asd2 NUMBER;
asd3 NUMBER;
asd4 NUMBER;
asd NUMBER;
qwe VARCHAR2(200);
qaz VARCHAR2(200);
ewq NUMBER;
zaq VARCHAR2(50);
xsw VARCHAR2(50);
BEGIN
CRE.SOME_PROCEDURE_NAME('S', SYSDATE);
END;
/

How can it possible with SYS user?
How can I fix it?
Best regards.

Even if you create the trigger as SYS, the owner must have the CREATE TRIGGER privilege:
GRANT CREATE TRIGGER TO CRE;
In addition to the above, to create an AFTER LOGON ON DATABASE trigger, the ADMINISTER DATABASE TRIGGER privilege is also required:
GRANT ADMINISTER DATABASE TRIGGER TO CRE;


Amazon RDS t2.small as a production server I currently have a m3.medium as production PostgreSQL server but I want to move it to RDS with the Multi-AZ feature.

Since the CPU usage of our current server is never higher than 5% and the RAM usage is 500MB I was considering using a cheaper instance like the t2.small on RDS.
On a few online forums and on Amazon help pages they don't recommend the small instances for a production database.
Is this just marketing or are there some real technical issues that may come up?

The only difference I found between the t2 and m3 instance types is the burstable CPU but since our DB server doesn't use any CPU I wonder what the other dangers are.

T2 instances are given cpu time based on a credit system, and t2.micros are have the least overall cpu capability in the instance class. These credits are accumulated at a rate of 6 per hour on a t2.micro, which gives you the ability to burst to 100% of the cpu core for 6 minutes. All other times you are only granted 10% of the core. Furthermore, you will only accumulate credits if your instance is completely idle.

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/t2-instances.html

Depending on the purpose on your production server, and how much downtime is acceptable, ... you can always start with a t2.micro, and upgrade the instance if you see that there is need for more resources. Mostly you can upgrade the instance type in 3-5 minutes. Just power off, upgrade type and power on. I think that's more than a reasonable 'maintenance downtime'.


TokuDB optimize table does not reclaim disk space Was running an optimize table command against one of my tables about 11Gig in size, surprisingly used disk space grew by 2Gig immediately after optimization process finished.
I would expect the optimization to reclaim a space.

What's happening?

After the optimization finished MySQL CPU utilization 2-3 times higher than it used to be with load average 18-20 on 24 core machine. CPU and load average went down slightly after few hours but still higher than usual. Any had similar experience?
Thanks.

I've had the same experience. The solution is to rebuild the table via this command:

ALTER TABLE my_table ENGINE=TokuDB ROW_FORMAT=TOKUDB_SMALL;

Replace TOKUDB_SMALL with your favourite compression algorithm.

With InnoDB an OPTIMIZE TABLE command simply does a trivial ALTER. But in TokuDB it does not, and tablespace is not reclaimed. By forcing an ALTER with ENGINE=TOKUDB you're taking this outside TokuDB's hands, and let the MySQL server tell TokuDB to create a (really) new table.


Find when Oracle 10g table was last populated I am working on an Oracle 10g database which is being populated periodically with data from network nodes.
What Oracle table can I query to to get the time at which myTableOfInterest was last populated?

I have already searched and tried:
select max(ORA_ROWSCN), scn_to_timestamp(max(ORA_ROWSCN)) from myTableName;
Unfortunately this select results in no rows being found.

Have a look at the TIMESTAMP column of the ALL_TAB_MODIFICATIONS. http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2097.htm

Also, keep in mind this part:

  Note: These views are populated only for tables with the MONITORING
  attribute. They are intended for statistics collection over a long
  period of time. For performance reasons, the Oracle Database does not
  populate these views immediately when the actual modifications occur.
  Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS
  PL/SQL package to populate these views with the latest information.
  The ANALYZE_ANY system privilege is required to run this procedure.


MySQL query groupBy and having too slow I need to select the oldest(min) due datetime for each InternalOrder and then filter these against some datetime for example NOW().
As you can see in schema image, I designed the schema as one InternalOrder has multiple Deliveries and each Delivery has multiple DeliveryRev(revisions), as I need to have whole trace of inputed data. I always look at the latest revision, if that one is deleted the whole delivery should not be considered.

Table rows:

InternalOrder: 2970
Delivery: 3258
DeliveryRev: 12272

I select the newest DeliveryRev via joins and filter these, which have due datetime field filled and also are not deleted.

I have working query (first one), but it is not exactly, what I need, since I can get the result with InternalOrder, which have oldest(min) due under specified threshold(not selected) and another one, which is over (in the result). In this case I do not want that InternalOrder in the result, which I solved using having instead of where, but this query is taking ages.

Am I doing something wrong with HAVING, or how can I optimize/change to get right result?

This query is fast (40ms), but not getting right result.

SELECT
    s9_.internal_order_id,
    MIN(s10_.due) AS minDue
FROM sales_internal_delivery s9_
    LEFT JOIN sales_internal_deliveryrev s10_
        ON s9_.id = s10_.delivery_id
           AND (s10_.due IS NOT NULL AND s10_.deleted = 0)
    LEFT JOIN sales_internal_deliveryrev s11_
        ON s10_.delivery_id = s11_.delivery_id
           AND s10_.timestamp < s11_.timestamp
WHERE
    s11_.delivery_id IS NULL
    AND s10_.due > NOW()
GROUP BY s9_.internal_order_id
ORDER BY minDue ASC

This query is slow (25s), but the result is alright.

SELECT
    s9_.internal_order_id,
    MIN(s10_.due) AS minDue
FROM sales_internal_delivery s9_
    LEFT JOIN sales_internal_deliveryrev s10_
        ON s9_.id = s10_.delivery_id
           AND (s10_.due IS NOT NULL AND s10_.deleted = 0)
    LEFT JOIN sales_internal_deliveryrev s11_
        ON s10_.delivery_id = s11_.delivery_id
           AND s10_.timestamp < s11_.timestamp
WHERE
    s11_.delivery_id IS NULL
GROUP BY s9_.internal_order_id
HAVING minDue > NOW()
ORDER BY minDue ASC

I don't know that the query could be refactored much and still be logically correct. And since I don't have your dataset it is difficult to test possible solutions for performance.

This query should still be logically the same, but may be executed in a more efficient way.

Select sid.internal_order_id,
    Min(sidr.due) As minDue
  From (
    Select delivery_id,
        Max(timestamp) As MaxTimestamp
      From sales_internal_deliveryrev
      Group By delivery_id) As lr
  Join sales_internal_deliveryrev As sidr
    On lr.delivery_id = sidr.delivery_id
    And lr.MaxTimestamp = sidr.timestamp
  Join sales_internal_delivery As sid
    On sidr.delivery_id = sid.id
  Where sidr.deleted = 0
    And sidr.due Is Not Null
  Group By sid.internal_order_id
  Having minDue > Now()
  Order By minDue Asc;

Edit

As per discussion about the proper way to handle revisions with deleted = 0 this is an alternate version which does not consider a revision with deleted = 1 when looking for the latest revision.

The WHERE deleted = 0 has been moved into the inner subquery.

Select sid.internal_order_id,
    Min(sidr.due) As minDue
  From (
    Select delivery_id,
        Max(timestamp) As MaxTimestamp
      From sales_internal_deliveryrev
      Where deleted = 0
      Group By delivery_id) As lr
  Join sales_internal_deliveryrev As sidr
    On lr.delivery_id = sidr.delivery_id
    And lr.MaxTimestamp = sidr.timestamp
  Join sales_internal_delivery As sid
    On sidr.delivery_id = sid.id
  Where sidr.due Is Not Null
  Group By sid.internal_order_id
  Having minDue > '2016-12-07'
  Order By minDue Asc;


How to determine if Oracle Java classes are being used Oracle EE 12.1.0.2

As part of a general clean up of a legacy application prior to a migration I'm trying to determine what objects are actually in use.

There are some 230 JAVA class objects in my database.  I can look at DBA_DEPENDENCIES to see where the dependencies between the java classes exist.  I can look at DBA_SOURCE to find what packages  include the phrase 'AS LANGUAGE JAVA' to find packages that wrap JAVA classes. I am auditing the execution of all the packages owned by our application schema.  But as far as I know you can't audit what procedures are called inside of a package.

Is there a way to audit the execution of the java class itself?  Can JAVA classes be called without being wrapped in a PL/SQL procedure?

I'm not a JAVA programmer (if that isn't obvious).

Doc https://docs.oracle.com/database/121/SQLRF/statements_4007.htm#SQLRF01107 for AUDIT statement says "Java schema objects (sources, classes, and resources) are considered the same as procedures for purposes of auditing SQL statements." So I assume that you can audit these Java objects with AUDIT statement (but I have not tested this).


Relating 2 tables with possible wildcards? I am having trouble getting the desired results when trying to relate the following tables.

NULL in the following table essentially means that if it is not an exact match with another row then choose this one.  A wildcard if you will.  I have 100% control over this table so if this is a bad design for what I am trying to accomplish please advise.

tblRateCategory

RateCategoryID Managed Customer    Category
10             0       NULL        NULL
20             0       NULL        A
30             0       CustC       A
40             1       CustC       A
50             1       NULL        A
60             0       NULL        B

tblJob

JobNumber Managed Customer     Category
1         0       CustA        C
2         0       CustB        D
3         0       CustC        A
4         1       CustC        A
5         0       CustD        A
6         0       CustE        B
7         1       CustF        A

Desired results

JobNumber RateCategoryID
1         10
2         10
3         30
4         40
5         20
6         60
7         50

I have tried this which gave duplicate job numbers and incorrect RateCategoryID if Category and/or Customer has match.  So I know I need to somehow look for match first and then settle for NULL or Any if no match found.

Select JobNumber, RateCategoryID
From tblJob J
Inner Join tblRateCategory RC
ON isnull(RC.Category,J.Category)=J.Category and
RC.Managed=J.Managed and isnull(RC.Customer,J.Customer)

Results from above query:

JobNumber RateCategoryID
1         10
2         10
3         10
3         20
3         30
4         10
4         20
4         40
5         10
5         20
6         10
6         60
7         10
7         50

Tables

DECLARE @RateCategory AS table
(
    RateCategoryID integer PRIMARY KEY NONCLUSTERED,
    Managed bit NOT NULL,
    Customer varchar(10) NULL,
    Category char(1) NULL,

    UNIQUE CLUSTERED (Managed, Category, Customer)
);

DECLARE @Job AS table
(
    JobNumber integer PRIMARY KEY,
    Managed bit NOT NULL,
    Customer varchar(10) NOT NULL,
    Category char(1) NOT NULL
);

Sample Data

INSERT @RateCategory
    (RateCategoryID, Managed, Customer, Category)
VALUES
    (10, 0, NULL, NULL),
    (20, 0, NULL, 'A'),
    (30, 0, 'CustC', 'A'),
    (40, 1, 'CustC', 'A'),
    (50, 1, NULL, 'A'),
    (60, 0, NULL, 'B');

INSERT @Job
    (JobNumber, Managed, Customer, Category)
VALUES
    (1, 0, 'CustA', 'C'),
    (2, 0, 'CustB', 'D'),
    (3, 0, 'CustC', 'A'),
    (4, 1, 'CustC', 'A'),
    (5, 0, 'CustD', 'A'),
    (6, 0, 'CustE', 'B'),
    (7, 1, 'CustF', 'A');

Solution

The logic here is to find a match on:

Managed, Category, and Customer
Managed and Category
Managed alone

Each step only occurs per job if the previous step did not find a match. The initial CROSS APPLY ensures a row is only returned if one of the steps found a match.

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY
(
    SELECT
        RateCategoryID =
            COALESCE
            (
                -- Preferred order of matches
                MatchAll.RateCategoryID,
                Match2.RateCategoryID,
                Match1.RateCategoryID
            )
    FROM
    (
        SELECT NULL
    ) AS Dummy (NotUsed)
    OUTER APPLY
    (
        -- Complete match
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- Conditions for this step
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer
    ) AS MatchAll
    OUTER APPLY
    (
        -- Match Managed and Category
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- No previous match
            MatchAll.RateCategoryID IS NULL
            -- Conditions for this step
            AND RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL
    ) AS Match2
    OUTER APPLY
    (
        -- Match Managed only
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- No previous match
            MatchAll.RateCategoryID IS NULL
            AND Match2.RateCategoryID IS NULL
            -- Conditions for this step
            AND RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match1
) AS RC;

Results

Demo

Stack Exchange Data Explorer

Execution Plan

Note the Filter operators have startup predicates. This means a seek is only performed if the previous seek did not find a row.

Alternative

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY
(
    -- Just the highest priority match per job
    SELECT TOP (1)
        Match.RateCategoryID
    FROM
    (
        -- Priority 1: Match Managed, Category, and Customer
        SELECT
            RC.RateCategoryID,
            [Priority] = 1
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer

        UNION ALL

        -- Priority 2: Match Managed and Category
        SELECT
            RC.RateCategoryID,
            [Priority] = 2
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL

        UNION ALL

        -- Priority 3: Match Managed
        SELECT
            RC.RateCategoryID,
            [Priority] = 3
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match
    ORDER BY
        Match.[Priority] ASC
) AS RC;


Need to include FKs in SQL Indexed View clustered index? I'm using a SQL Server Indexed view to improve performance of a join that filters a (very) large table by a category on a small table. eg:

CREATE VIEW BigTableSubset WITH SCHEMABINDING
AS
SELECT b.ID, b.SomeValue from BigTable b
INNER JOIN SmallTable s on (b.CategoryFK = s.CategoryPK)
where s.Type = 'Blah'

CREATE UNIQUE CLUSTERED INDEX PK_BigTableSubset ON BigTableSubset
(ID)
INCLUDE (SomeValue)

(BigTable.ID is the PK on BigTable)

Query performance is substantially increased (since the subset of bigtable required is already stored), but I'm concerned to ensure that SQL can update the index itself in the most optimal way when either of the source tables is updated.

So the question is: do I need to include SmallTable.CategoryPK in the view's clustered index to ensure this, or will SQL do this automagically as part of the index structure? Or to put it another way, how does SQL identify what pages in the index need updating every time a 'left hand' (SmallTable) table updates, and do I need to do anything to facilitate that?

SQL Server will always be able to automatically update the view's index(es) as the source table(s) change; there's nothing extra you need to do.

You can inspect a query plan that modifies one of the source tables, you'll see there are extra operators to update the view's index(es), the same way it has to update all the applicable nonclustered indexes on the source tables when data changes.


What's the structure of a matchmaking server? This is my current structure of my matches table:

id, title, description, max_players, host_username, host_ip

and there's another table called matches_players:

match_id, player_username

This requires two queries to get the matches list and the player count of each one, so is there a better way?

I'm using:

SELECT COUNT(*) FROM `matches_players` WHERE `match_id` = ?

to get the number of players in a match.

Server inserts one row to the matches table, and then another one to add the host to the matches_players table.

It is working, but it makes the code more complex, saving the matches list, then doing lot of queries to get the players number. I was wondering if there is a better way?

Try this:-

SELECT id
      ,title
      ,DESCRIPTION
      ,max_players
      ,host_username
      ,host_ip
      ,(
           SELECT COUNT(*)
           FROM   matches_players
           WHERE  match_id = id
       ) AS player_count
FROM   matches


Sql Server 2008 database is suspected My database in sql server 2008 has marked suspect.

How can I fix this problem?

At first

DO NOT detach your database
DO NOT restart your server or services
DO NOT use DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS parameter.

Second, you need to know the cause of suspection. There is the chance that there is no more space on the database disk. In this case you need to free some space and then try to return the database to normal state using sp_resetstatus.

But if database seems to be corrupted, you need to try to fix the database using DBCC CHECKDB with appropriate parameters. But the database needs to be set in EMERGENCY mode first:

ALTER DATABASE [yourdatabase] SET EMERGENCY;

Now, you can execute the DBCC CHECKDB command.
Be careful with result of DBCC execution: if you see the errors on the pages with data, you need to restore your database from backup, because you can lost the integrity of your data; But obviously (in my expirience) DBCC returns the errors on the pages with indexes, and in most cases you can rebuild your indexes.

Finally, you need to fix the cause of the problem. If your problem concerned with disk space, move the data to larger disks. If your database is corrupted because windows is unexpectelly shutted down - move your database to another server, and so on.

I think you need to read this article and the forum discussion below to get some basic information to start the recovery.


How to find rows that do not match between two tables P and N Scenario:
I created and updated a table so that the new table (N) has some rows that are different from the previous table (P). Now I want to find the rows that have been updated.

I'm running the following now:

SELECT N.*
FROM previous_table P
JOIN new_table N
  ON P.item_id = N.item_id
WHERE (P.column_1 != N.column_1)
   OR (P.column_2 != N.column_2)
   OR ... (P.column_n != N.column_N);

Right now I only have three columns, so it's working for me but I imagine this method is rather untenable if the number of columns is very large.

Is there a way to simplify the WHERE bit?

SELECT N.*
FROM new_table N
LEFT JOIN previous_table P
     ON P.item_id = N.item_id
    AND P.column_1 = N.column_1
    AND P.column_2 = N.column_2
    AND /* ... */ P.column_n = N.column_N
WHERE P.item_id IS NULL;

or

SELECT N.*
FROM new_table N
LEFT JOIN previous_table P
     ON (P.item_id, P.column_1, P.column_2, /* ... , */ P.column_N)
       =
        (N.item_id, N.column_1, N.column_2, /* ... , */ N.column_N)
WHERE P.item_id IS NULL;


Perl vs Ksh for Unix Database Administration I realize this is a somewhat more subjective question, but I am looking to the community for guidance.

Our company is fairly new to having DBAs. We used to use DB2 for i on the IBM's iSeries servers, so there really wasn't a need for a DBA (at least operationally) as the OS pretty much managed everything for us. Within the last few years we started to move to DB2 LUW on AIX. Thus a need for DBAs was born. Three of us were interested in making the career change from developers to DBAs (of which I am one of them).

Because we are on AIX, we defaulted to using ksh as our scripting language for database management functions (creation, deployment, operations, etc.). I have found ksh to be quite powerful, yet some of the functions I need to perform get rather complex. An example of this is my question over on Unix and Linux StackExchange.

Based on that question, as well as reading blogs by other DB2 administrators, I see a general suggestion that Perl is better suited for administration scripting than ksh is.

I realize that the Unix/Linux forum would have their own thoughts on such a thing, but I wish to approach this from a database administration viewpoint.

Regardless of DBMS, which scripting language seems better suited for administration on the Unix/Linux platform? Perl? Ksh? (or perhaps other?). What strengths and weaknesses in your use of scripting languages did you encounter as a DBA, and why did you pick the language you did?

What I don't want is a highly subjective flame war on "My language is better than your language!" kind of stuff, like I used to see in the development community regarding say Java vs Ruby, etc. I am looking for honest, as objective-as-possible, information regarding what best suits the needs of database administrators.

The end of your post is really the important bit:

  information regarding what best suits the needs of database administrators.

So let's start there.

What are your needs as a DBA? You generally have two realms of operation: system level maintenance, and database level maintenance (whereby all actions are merely maintaining the system for better performance ... look, don't get pedantic, go with me here). So you have two realms to consider.

ksh - The Korn Shell is one of the older Unix operational shells, used to control the server. The goal of ksh is to manage a server, and allow one to do all the things that the traditional Bourne shell (not the Bourne Again SHell that most people are familiar with, this is older vintage) allowed, with a few differences (this shell had better support for things like arithmetic and arrays, making it easier to program in some regards). It is the default shell on AIX.

perl - Perl is a high-level general purpose programming language. It is designed to be run on top of a shell, and was designed primarily with reporting in mind. It combined features of shell scripting and C, as well as regex technologies to attempt to make it better than all of the previous. A lot of admins around the planet like perl because it makes system management easier, because of the combination of shell capabilities and report generation speed up (it being a programming language). Perl scripts can be run as simply as shell scripts, and there is even a convention in the Unix world of having the first line indicate what will run the script (the familiar hashbang line). So running perl scripts is as easy as running a shell script.

The difference, then, is do you need a full-fledged programming language, or do you need a basic shell script? The answer then becomes use the one that gives what you need, as both are highly useful to you. If you want stats, that's impossible to give, we each have our own style. Just agree on something internally and call it that (if you're going to set a standard, make it perl as that has more features, and you never want to get your hand caught because you set an unrealistic standard. Perl is also more extensible in the future). Better to restrict yourself to two or three languages in this case.

Back at the beginning I mentioned that you have two options: system level and database level.

If your purpose is primarily system level, then we've answered all the questions here (use the one that suits your needs). If you also need to program into the database then you really need to just standardize on perl or get really used to command line syntaxes, because Perl has modules for every conceivable database on the planet somewhere, and if one doesn't exist, it can be written (example: DBI -> DBD:DB2 -> DB2 client).

As an aside, I do Microsoft SQL Server and C#, so I live in the Windows world, and I use PowerShell over DOS Batch files. PowerShell is in many ways similar to perl, and gives me a lot more flexibility than DOS batch programming (but there are plenty of admins in the world still using old DOS scripts to manage their systems). While the DOS Batch system allows for arrays, loops, variables and all the rest, it's much easier to program in a programming language instead of a shell script, so I'm able to be much more productive.

At the end of the day, the real question is what features do you need in your scripting environment, and we can't help you with that. But I will always opt for more clarity, maintainability and extensibility, over "this is what shipped in the box".


Find closed sub directory with full text search I have a simple table with registered directory names inside a full text indexed varchar column.

For example:

'c:\Dir1\'
'c:\Dir1\Sub1\'
'c:\Dir1\Sub2\'
'c:\Dir2\'
'c:\Dir3\'

I now need to find the closest subdirectory to file paths like:

'c:\Dir1\File.txt' -> 'c:\Dir1\'

'c:\Dir1\Sub2\File.txt' -> 'c:\Dir1\Sub2\'

'c:\Dir2\Sub1\SubSub1\File.txt' -> 'c:\Dir2\'

All my efforts with CONTAINS and NEAR failed.
Can somebody help me how I can solve this with text search?

If you take the path of interest and parse it into it's sub part, and use those to  build a search string, you should then be able to use that search string to find matches of all lengths and then pick the best fit.  There is an assumption that the folder structure is embedded in other data within the field (otherwise, why use Full Text Indexing), so the manner of finding the best match will depending on your specific context.

Here is some code the demonstrates the suggested path, include a sample way of selecting the best fit.

DECLARE @PathOfInterest NVARCHAR(MAX) = 'c:\Dir1\Sub2\File.txt';

DECLARE @SearchString NVARCHAR(MAX);

DROP TABLE IF EXISTS #Search;

WITH Builder AS (
    SELECT
        0 AS Score -- How close to the initla path it is
        , @PathOfInterest AS InitalPath -- Path
        , CAST(NULL AS NVARCHAR(MAX) ) AS SubPath -- SubPath
        , CAST(0 AS BIGINT)  AS DelimPos -- End of last Search
        , CAST(NULL AS NVARCHAR(MAX) ) AS SearchValue -- Cumulative Search Value
    UNION ALL
    SELECT
        Score + 1
        , InitalPath -- Pass through path
        , CAST(SUBSTRING(InitalPath, 1, CHARINDEX('\\', InitalPath, DelimPos + 1) - 1)AS NVARCHAR(MAX) ) -- Extract the string to the folder delimi
        , CHARINDEX('\\', InitalPath, DelimPos + 1) -- Find the next delim
        , CAST(
            CONCAT(
                SearchValue + ' OR ' -- When V is null, no leading OR
                , '("'
                , SUBSTRING(InitalPath, 1, CHARINDEX('\\', InitalPath, DelimPos + 1))
                , '")'
            ) AS NVARCHAR(MAX) -- Make sure the type matches the anchor of the recusive CTE
        )
    FROM Builder
    WHERE
        CHARINDEX('\\',InitalPath, DelimPos + 1) > 0
)
SELECT
    Score, SubPath, SearchValue
--INTO #Search
FROM Builder

SELECT @SearchString = SearchValue FROM #Search

SELECT * FROM #Search s

DROP TABLE IF EXISTS #Search;
SELECT
    FolderInfo
INTO
    #FirstPass
FROM FullTextIndexTables
WHERE CONTAINS(FolderInfo, @SearchString)

---- Select best fit from matching results
SELECT TOP 1 *
FROM
    #Search
CROSS APPLY (
    SELECT * FROM #FirstPass
    WHERE FolderInfo LIKE CONCAT('%', SubPath, '[^\]%' )
)
ORDER BY Score DESC

Notes:
 1. To preserve formatting in SO, I changed '\' to '\'.
 2. I did not have an environment to test the Full Index Search, so some
    tweaking may be needed.
 3. The selection criteria in the 'best fit' may produce inconsistent
    results if there are multiple matches with the same score (wobble).
    If this is of concern, additional ORDER BY criteria should be
    specified.


DB2 drop trigger if exists? In MySQL, if I have schema myschema and a trigger named mytriggername I can drop a trigger like follows

DROP TRIGGER IF EXISTS myschema.mytriggername

Anyway, I can't find on DB2 docs for the DROP statement any counterpart for the DROP TRIGGER IF EXISTS statement?
Any suggestion?

Another way of doing this is a FOR loop only executing DROP statements for objects that exist. It's frankly overkill for a single object but it's useful when dealing with schema loads of things. For example:

BEGIN ATOMIC
    FOR D AS
        SELECT 'DROP TRIGGER ' || TRIGNAME AS DDL
        FROM SYSCAT.TRIGGERS
        WHERE TRIGSCHEMA = 'SOMESCHEMA'
        AND TRIGNAME = 'SOMETRIGGER'
    DO
        EXECUTE IMMEDIATE D.DDL;
    END FOR;
END@

I used similar tricks in a DROP_SCHEMA routine ages ago (source link at the bottom of the page) although it's redundant now.

The above trick can also be used for conditional upgrades of "only those objects which exist", and/or "only those objects which don't exist".

For actual schema creation I generally preferred keeping a "clean" creation script (no conditional stuff) and a separate "drop" script to clean things up. If that sounds like making redundant work for yourself, there are certain tricks one can employ to generate the drop script from the creation script (use of tac to reverse the install script, and a little regex magic to convert CREATE statements into DROP; it does mean being vaguely disciplined in how you format your SQL though unless you want the regexes to get really messy).


MongoDB Shard Zone Range Overlapping I am configuring a test mongo setup with 2 shards, and I'm trying to add zone ranges to each shard:

sh.updateZoneKeyRange('mydb.test', {id:MinKey,ts:MinKey}, {id:MaxKey,ts:1548787704000}, 'cold')

sh.updateZoneKeyRange('mydb.test', {id:MinKey,ts:1548787704000}, {id:MaxKey,ts:MaxKey}, 'hot')

First command runs fine, but second tells me:

Zone range: { id: MinKey, ts: 1548787704000.0 } -->> { id: MaxKey, ts: MaxKey }  on  hot is overlapping with existing: { id: MinKey, ts: MinKey } -->> { id: MaxKey, ts: 1548787704000.0 }  on  cold

I thought the maximum bounds were exclusive and minimum bounds inclusive?

From what I can gather after some experiments, it seems that the prefix range is the one that must be split.


SQL Server User mapping vanished SQL Server 2008 web edition x64
I'm opening: (connection)/Security/Logins/John.
Select "User Mapping'. On right pane is marked checkbox at DB1 line.
I turn on checkbox at DB2 line, correct username and schema appear.
Press 'ok'.
Opening (connection)/Security/Logins/John once more and there is mo mark at DB2 checkbox, it's clear. No username/schema at that line, of course. Mark checkbox again, and so on until become mad.

No error messages, no errors in log.

I can't turn off DB1 checkbox because of 'Canno drop the user dbo'

DB1 really doesen't importanf for me, but I need DB2 mapping. How can I fix this?

I'm not sure if the issue is difficulty connecting a user to the login on database(db2) or if you are having difficulty unassociating the user with the schema dbo on database(db1).

It sound to me like the larger issue is connecting the user to db2.  If the user already exists on db2 you can associate them with the login using the command

EXEC sp_change_users_login ‘UPDATE_ONE’ , ‘sqlusername’, ‘sqlloginname’;

If the user doesnt exist in db2 you can create them using

CREATE USER 'username' FOR LOGIN 'loginname'
WITH DEFAULT_SCHEMA = 'default schema name';


How to create an index to speed up an aggregate LIKE query on an expression? I may be asking the wrong question in the title. Here are the facts:

My customer service folk have been complaining about slow response times when doing customer lookups on the administration interface of our Django-based site.

We're using Postgres 8.4.6. I started logging slow queries, and discovered this culprit:

SELECT COUNT(*) FROM "auth_user" WHERE UPPER("auth_user"."email"::text) LIKE UPPER(E'%deyk%')

This query is taking upwards of 32 seconds to run. Here's the query plan provided by EXPLAIN:

QUERY PLAN
Aggregate  (cost=205171.71..205171.72 rows=1 width=0)
  ->  Seq Scan on auth_user  (cost=0.00..205166.46 rows=2096 width=0)
        Filter: (upper((email)::text) ~~ '%DEYK%'::text)

Because this is a query generated by the Django ORM from a Django QuerySet generated by the Django Admin application, I don't have any control over the query itself. An index seems like the logical solution. I tried creating an index to speed this up, but it hasn't made a difference:

CREATE INDEX auth_user_email_upper ON auth_user USING btree (upper(email::text))

What am I doing wrong? How can I speed up this query?

There is no index support for LIKE / ILIKE in PostgreSQL 8.4 - except for left anchored search terms.

Since PostgreSQL 9.1 the additional module pg_trgm provides operator classes for GIN and GiST trigram indices supporting LIKE / ILIKE or regular expressions (operators ~ and friends). Install once per database:

CREATE EXTENSION pg_trgm;

Example GIN index:

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

Related:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) I need to dump a database from a server that uses the old password hash. It's a shared server and I am unable to change the user's password to the new hash.

Attempts with MySQLWorkbench fail with the error:

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

Does anyone have a solution?

PS, in the end I did get SSH access to the server, so I was able to run mysqldump there. But in absence of that I don't know another way.

Thanks for insight.

If you don't use a newer or equal version as 5.7.5 as client, you can add the parameter --skip-secure-auth to mysqldump. But since this is deprecated and not supported in newer version, it's best to migrate away from Pre-4.1 Password Hashing and the mysql_old_password plugin


How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to? When I query sys.dm_tran_locks, a couple of questions arise:

I can see there are some locks that are placed at KEY, PAGE, EXTENT, RID level (resource_type column). How to find out to which table or object these particular keys, pages, extents, rids belong to?
For the OBJECT level locks (resource_type = 'OBJECT') - what is the proper way to define object name? Is the resource_associated_entity_id column the object_id of the object? Or is there some caveat?
Some OBJECTS have resource_associated_entity_id = -1261057897 (negative number) - how to find out the object name for these?

sys.dm_db_database_page_allocations() was introduced specifically to avoid having to do any of this spelunking yourself, using unsupported and undocumented commands like DBCC IND and DBCC PAGE. I talk about an even better replacement, coming in SQL Server 2019, in this tip. With the caveat that this is a pretty expensive query, and you may want to filter it down further to limit the output to just the specific rows in sys.dm_tran_locks you are investigating:

SELECT OBJECT_SCHEMA_NAME(p.object_id), OBJECT_NAME(p.object_id), *
FROM sys.dm_tran_locks AS t
INNER JOIN sys.dm_db_database_page_allocations(DB_ID(),NULL,NULL,NULL,'LIMITED') AS p
ON t.resource_associated_entity_id = CASE
  WHEN resource_type = N'OBJECT' THEN p.object_id
  WHEN resource_type IN (N'KEY',N'PAGE',N'RID') THEN p.rowset_id
  -- not quite sure about EXTENT
END
WHERE OBJECTPROPERTY(p.object_id, 'IsMsShipped') = 0;

Yes, resource_associated_entity_id is the object_id, when the resource_type is OBJECT. For other resource types, it is going to reference other types of identifiers, like rowset_id or allocation_unit_id. This is why the column isn't simply named object_id.
SELECT OBJECT_NAME(object_id); will return the object name, regardless of whether the object_id is negative or positive. #temp tables have negative object_id values, for example, in SQL Server 2012 and up, and really there is no guarantee that any object_id will be positive.


Assuming all columns NOT NULL. And 'left' is never earlier than the associated 'joined'.
Simple case
If users can join teams only once (which would be enforced by a UNIQUE constraint on ("user", team), ideally), then the solution is a simple GROUP BY and works in Redshift as well as in most any RDBMS:
SELECT "user", team
     , min(CASE WHEN event = 'joined' THEN timestamp END) AS joined
     , max(CASE WHEN event = 'left'   THEN timestamp END) AS "left"
FROM   event
GROUP  BY "user", team
ORDER  BY "user", joined NULLS FIRST;

Note the NULLS FIRST clause. Seems like you want to sort an open start with joined IS NULL first. Redshift supports that, too.
Apart from that it's the most basic form of a crosstab / pivot query.
Not so simple
Judging from your column names and example data it might not be so simple. If users can join teams more than once (non-overlapping), you have to do more. You wouldn't want to merge multiple team memberships into one row like in this related answer:

Postgres: Use result of group by as column names

Instead you have to pair up adjacent 'joined' and 'left' rows somehow. There are many ways ...
Postgres 9.4+
For modern Postgres I like this best:
SELECT "user", team
     , min(timestamp) FILTER (WHERE event = 'joined') AS joined
     , max(timestamp) FILTER (WHERE event = 'left'  ) AS "left"
FROM  (
   SELECT *, count(*) FILTER (WHERE event = 'joined')
                      OVER (PARTITION BY "user", team ORDER BY timestamp) AS ct
   FROM   event
   ) sub
GROUP  BY "user", team, ct
ORDER  BY "user", joined NULLS FIRST;
Using the aggregate FILTER clause in a window function as well as in an aggregate function. Related (with links to alternatives):

Return counts for multiple ranges in a single SELECT statement

This way we count how many times the same user joined the same team, so we can group adjacent rows. Works for a missing 'joined' at the begin or a missing 'left' at the end as well.
Redshift
... does not support the new FILTER clause. We can substitute with a plain old CASE:
SELECT "user", team
     , min(CASE WHEN event = 'joined' THEN timestamp END) AS joined
     , max(CASE WHEN event = 'left'   THEN timestamp END) AS "left"
FROM  (
   SELECT *, count(CASE WHEN event = 'joined' THEN 1 END)
                      OVER (PARTITION BY "user", team ORDER BY timestamp, event) AS ct
   FROM   event
   ) sub
GROUP  BY "user", team, ct
ORDER  BY "user", joined NULLS FIRST;

SQL Fiddle.

Aside: you shouldn't use reserved words as identifiers, even if it's permitted by Redshift (or Postgres).


Why RECOMPILE doesn't fix parameter sniffing, but local variables do? A developer was asking me about why his procedure wasn't running, but the query was ok.

When I run the query, it was fast.But running the procedure, it was executing forever.

What I did was to add at the end of the query:

OPTION(RECOMPILE)

It doesn't help.

The procedure was like this (the slow one):

CREATE procedure [dbo].[pr_ProcedureName]
    @CodOnda int
AS
BEGIN
  set nocount on
  ...

Then I did this:

CREATE procedure [dbo].[pr_ProcedureName]
    @CodOnda int
AS
BEGIN
   set nocount on

   declare @localCodOnda int
   set @localCodOnda = @CodOnda
   ...

And BAM! Local variables. The procedure was faster than ever. I know this is something about parameter sniffing. But why the recompile didn't help?

I really would like to have the old query plan, but I don't have.

And the problem wasn't about locking or blocking. I was tracking with sp_WhoIsActive and some queries that I found in some answers here (thanks to Aaron Bertrand and Brent Ozar for them). I am using SQL Server 2008 R2.

But why the recompile didn't help?

Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits.

Determining the specific reasons that OPTION (RECOMPILE) did not help in your particular case would require details you are unable to provide.

  Local variables. The procedure was faster than ever.

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available.

For more details please see:

Parameter Sniffing, Embedding and the RECOMPILE Options


MySQL behaviour on connection failure My MySQL database is hosted on a different machine from my application system, which issues queries to the MySQL database remotely.

If for some reason the network connection between the two machines break, while a query was being processed, what will happen?

I suspect that MySQL will reject any garbage data but I don't want it to save that garbage data in my rows and I don't want MySQL to get stuck.

You have to understand that the underlying protocol that you're using to send dta(http://en.wikipedia.org/wiki/Transmission_Control_Protocol). The TC-Protocol is designed to deal with network breaks. Ultimately if you send a transmission to the remote server saying, for instance "insert into table (id, name) values (1, 'test');" then the TC-Protocol will handle not only delivering it, but also making sure it arrives in tact. In fact, it will even retry... but if there's a persistent network break it'll tell your application that the connection to the remote server was lost.

Thus, the answer is simply you do not need to do anything about this. TCP will make sure you don't get garbage data. TCP is the reason that you do not see garbage web-pages when you are on a shoddy connection. It also makes sure that you don't deliver garbage to your DB's.

We run about 500 million transactions a month into the DB and not one are garbage - because of TCP.


Group by without temporary table and filesort My aim is to make the following query faster.

SELECT SQL_NO_CACHE Links.id,Links.link,sum(ID_Links_Words.count) AS wordCount
 FROM Links LEFT JOIN ID_Links_Words ON Links.id = ID_Links_Words.link
 WHERE ID_Links_Words.word IN ('1025','1056','2353') GROUP BY Links.id

My table Links has the columns

id,link,title,description and timestamp

and the keys id (Primary),link (index), timestamp (index), (link,title,description) (fulltext)

The table ID_Links_Words has the columns

link, word, count

and the keys (link,word) (UNIQUE), link (index), word (index)

 |id | select_type | table         | type | possible_keys     | key | key_len | ref                | rows | Extra
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|
 | 1     SIMPLE     ID_Links_Words   range link,link_2,word    word     4       NULL                 6        Using where; Using  temporary; Using filesort|
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|
 | 1     SIMPLE     Links           eq_ref   PRIMARY           PRIMARY   4     ID_Links_Words.link   1
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|

Sorry for this bad explain table :/ Hope you can read it.
I would like to get rid of the using temporary and I'm not sure if it's possible. Are there any options to improve this sql statement? I might have to deal with about 100,000 words and 1,000 links.

Is ID_Links_Words a many-to-many table for connecting Words and Links?  Then this is the optimal schema:

CREATE TABLE ID_Links_Words(
    word ... NOT NULL,
    link ... NOT NULL,
    count ... NOT NULL,
    PRIMARY KEY(word, link), -- for going one direction
    INDEX(link, word)        -- and the other
) ENGINE = InnoDB;           -- Important because of the clustering of the PK.

The two indexes can be swapped, depending on the various queries you do; this arrangement -- with word first in the PRIMARY KEY -- is best for this SELECT.

Remove LEFT unless there is a compelling reason for it.

What percentage of the table has those 3 'words'?  If it is a non-trivial percentage, you are stuck with a table scan.

Check the settings of innodb_buffer_pool_size and key_buffer_size.

What version of MySQL are you using?  There were improvements in the handling of IN(...) in 5.6, perhaps before.  If you are in an older version, recommend upgrading.

If the my other hints don't do enough, try this:

(   SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '1025'
        GROUP BY  W.link
)
UNION  ALL (
    SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '1056'
        GROUP BY  W.link
           )
UNION  ALL (
    SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '2353'
        GROUP BY  W.link
           );

And, assuming that the LEFT is not necessary, test this

SELECT  W.link as id,
        ( SELECT  link
            FROM  Links
            WHERE  id = W.link
        ) AS link,
        wordCount
    FROM
      ( SELECT  SUM(count) AS wordCount
            FROM  ID_Links_Words
            WHERE  word IN ('1025','1056','2353')
            GROUP BY  link
      ) AS W ;

This, if I wrote it right, has the following potential benefits:

The GROUP BY is done without being bothered by the other table.
The other table is done after shrinking the number of rows by the GROUP BY.

Again, the IN could be turned into a UNION.

Edit

There is a world of difference between INDEX(word, link) and INDEX(link, word).  INDEXes are ordered.  If you have a word and need to find the links associated with it, the former is much better.  If you have a link and want to find the words the latter is much better.  Imagine looking in a list of names ordered by (last_name, first_name), and looking for all the last names, given a first name.

Index cookbook


How to test a function that selects from real tables? I've got a mega function that everybody is afraid to modify. So each fix adds a dozen of new IF lines. To me it feels that this function screams for proper testing.
Instead of the real function I'm including a really simplified example, because I believe that the crux of the challenge is that it's not only a function of its explicit parameters, but also of the state of some of the actual tables in the database.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [statistic].[daysByProject](
    @DateFrom DATE,
    @DateTo DATE,
)
returns
@ret table(ProjectID INT, IsFine TINYINT, Days smallint)
BEGIN

insert into @ret(ProjectID, IsFine, Days)
select ProjectID, IsFine, Days
from dbo.SomeRealTable

RETURN
END

Is it possible to add tests to such a function? I've even heard of including tests in a transaction, apparently something akin to this
/* alter function */

/**
  * Set up test case
  * Run
  * Raise error if test fails
  * Repeat with all test cases
  */

/* Roll back on error */

Could something like that be possible so that (unless explicitly working around this) developers could only modify the function in a way that satisfies the requirements of test cases?

This is really difficult to answer. What works for others might not work for you and vice versa.
Personally i would set up a battery of repeatable test scripts. Start with a clean set of data, run a standard set of executions and then check the results are valid. Personally i would not put tests inside the code itself.
However, i would put validation checks inside your code so that it exits with sensible warnings, rather than corrupt data.
Im not a programmer, but i have learned that when a function or proc gets so complicated no-one understands it, it may be worth breaking it down into smaller more manageable functions.