Just how much is a query supposed to slow down the whole system?

Complicated joins can be a precursor to temp table usage. By default, tmp_table_size is set to whatever the value max_heap_table_size is (16M).
For any given query, once a tmp table reaches 16M in RAM, it performs the following migration

the query suspends operation
mysqld will migrate the entire 16M of tmp table data onto disk as a MyISAM table
the query continues until completion

You must also keep in mind where the disk-based tmp tables will land. By default, the folder where disk-based tmp tables will land in defined in tmpdir.

For Windows, this would be C:\TEMP or C:\TMP (environment variable TEMP or TMP)
For Linux, this would be /tmp

If the mysqld is competing for tmpdir usage with the OS, your should feel it rather quickly because the OS would start swapping to find the elbow room in needs in the system temp folder while the query in question might be trying to force a tmp table into the same OS tmp space.
You may want to look into one or more of the following

mounting /tmp to a RAM disk
mounting /tmp to another disk away fromthe root partition
setting tmp_table_size and max_heap_table_size to 2K (that's not a typo, I SAID 2K) to force tmp tables to goto tmpdir quickly, thus shortening tmp table memory-to-disk migration time
I wrote about this in StackOverflow

UPDATE 2012-03-13 15:11 EDT
Setting tmp_table_size and max_heap_table_size to 512M is dangerously high. In the event of bad queries (especially the Cross Join (aka Caretsian Join)), mysqld will spend time migrating a 512M MEMORY table to a disk-based MyISAM table before the query continues processing. You may need to lower that number significantly to force tmp tables into disk faster. This is imperative since tmp_table_size and max_heap_table_size are set per DB Connection.


Connect to Oracle DB 9i using SSH TUNNEL via putty

If the global IP is the NAT running the ssh server and the LAN IP is the one with the Oracle server on it, then you need to use the LAN IP as the destination in the tunnel.


Measure Agent Job failure and running jobs with 'execution_status'

If you're willing to use an undocumented and, therefore, unsupported extended stored procedure, you can get the current execution stats from msdb.dbo.xp_sqlagent_enum_jobs.

I use the following to get details about running jobs:

DECLARE @can_see_all_running_jobs int = 1;
DECLARE @job_owner sysname = 'sa';
DECLARE @job_id uniqueidentifier;

DECLARE @job_states TABLE
(
    job_state_id int NOT NULL
    , job_state_desc varchar(30) NOT NULL
);

INSERT INTO @job_states (job_state_id, job_state_desc)
VALUES (0, 'Not idle or suspended')
    , (1, 'Executing')
    , (2, 'Waiting for Thread')
    , (3, 'Between Retries')
    , (4, 'Idle')
    , (5, 'Suspended')
    , (6, 'Waiting for Step to Finish')
    , (7, 'Performing Completion Actions');

DECLARE @xp_results TABLE
(
      job_id                UNIQUEIDENTIFIER NOT NULL
    , last_run_date         INT              NOT NULL
    , last_run_time         INT              NOT NULL
    , next_run_date         INT              NOT NULL
    , next_run_time         INT              NOT NULL
    , next_run_schedule_id  INT              NOT NULL
    , requested_to_run      INT              NOT NULL
    , request_source        INT              NOT NULL
    , request_source_id     sysname          COLLATE database_default NULL
    , running               INT              NOT NULL
    , current_step          INT              NOT NULL
    , current_retry_attempt INT              NOT NULL
    , job_state             INT              NOT NULL
);
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs
    @can_see_all_running_jobs
    , @job_owner
    , @job_id;

SELECT
    JobName = j.name
    , states.job_state_desc
    , LastRunDateTime = CASE
        WHEN COALESCE(xr.last_run_date, 0) > 0
            THEN msdb.dbo.agent_datetime(xr.last_run_date, xr.last_run_time)
        ELSE NULL
        END
    , xr.current_step
    , step_name = (
        SELECT '' + sjs.step_name
        FROM dbo.sysjobsteps sjs
        WHERE sjs.job_id = j.job_id
            AND sjs.step_id = xr.current_step
        FOR XML PATH ('')
        )
FROM msdb.dbo.sysjobs j
    INNER JOIN @xp_results xr ON j.job_id = xr.job_id
    LEFT JOIN @job_states states on xr.job_state = states.job_state_id
WHERE j.enabled = 1 /* enabled jobs only */
    AND xr.running = 1 /* running jobs only */
ORDER BY j.name;

This shows results like:

╔══════════╦════════════════╦═════════════════════════╦══════════════╦═══════════╗
║ JobName  ║ job_state_desc ║     LastRunDateTime     ║ current_step ║ step_name ║
╠══════════╬════════════════╬═════════════════════════╬══════════════╬═══════════╣
║ Test Job ║ Executing      ║ 2018-04-10 12:40:09.000 ║            1 ║ Step 1    ║
╚══════════╩════════════════╩═════════════════════════╩══════════════╩═══════════╝

Items of note:

The "last run date" columns only reflect the time the job was last started.  i.e. this does NOT indicate the start of the job if the job is currently running.  There appears to be no way to get that data from objects contained in msdb.  I do have a way to get those details from sys.dm_exec_sessions, etc, as in:

/*
      Shows the progress of any running SQL Agent jobs
*/
DECLARE @Actions TABLE
(
      ActionID INT
      , ActionText VARCHAR(50)
);
INSERT INTO @Actions (ActionID, ActionText)
VALUES ( 1,'Quit with success')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (2,'Quit with failure')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (3,'Go to next step')

INSERT INTO @Actions (ActionID, ActionText)
VALUES (4, 'Go to step on_success_step_id');

SELECT [Job Name] = sj.name
      , [Step Name] = sjs.step_name
      , SQLStatement = SUBSTRING(t.text, ISNULL(r.statement_start_offset / 2 + 1,0), CASE WHEN ISNULL(r.statement_end_offset, 0) = -1 THEN LEN(t.text) ELSE ISNULL(r.statement_end_offset / 2, 0) END - ISNULL(r.statement_start_offset / 2, 0))
      , [On Success Action] = ASuccess.ActionText
      , [On Fail Action] = AFail.ActionText
      , r.session_id
      , r.blocking_session_id
      , [Estimated Completion] = CASE WHEN r.estimated_completion_time = 0 THEN 'UNKNOWN' ELSE CONVERT(VARCHAR(50), DATEADD(MILLISECOND, r.estimated_completion_time, GETDATE()), 120) END
      , Duration = DATEDIFF(MINUTE, r.start_time, GETDATE())
      , r.last_wait_type
      , r.start_time
      , s.host_name
      , SQLText = t.text
      , s.program_name
FROM sys.dm_exec_sessions s WITH (NOLOCK)
      LEFT JOIN sys.dm_exec_requests r WITH (NOLOCK)on s.session_id = r.session_id
      CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
      LEFT JOIN msdb.dbo.sysjobsteps sjs WITH (NOLOCK) ON SUBSTRING(s.[program_name],30,34) = master.dbo.fn_varbintohexstr(sjs.job_id) AND SUBSTRING(s.[program_name], 72, LEN(s.[program_name]) - 72) = SJS.step_id
      LEFT JOIN msdb.dbo.sysjobs sj (NOLOCK) ON sjs.job_id = sj.job_id
      LEFT JOIN @Actions AFail ON sjs.on_fail_action = AFail.ActionID
      LEFT JOIN @Actions ASuccess ON sjs.on_success_action = ASuccess.ActionID
WHERE s.program_name like 'SQLAgent - TSQL%';

Limiting selected jobs with msdb.dbo.agent_datetime(run_date, run_time)  > DATEADD(DAY,-90, GETDATE()) in the WHERE clause means currently running jobs that have never been ran before will not show up, and will return an error for jobs that have never been ran.

I've tied all the above details into a single package, and made it available on my blog.


How to replicate MongoDB to a remote location?

Normally slow initial sync is because of slow network connection or VERY slow data disks. Have you measured what is transfer speed during initial sync.

Reason, why all data is copied with rsync, is that, that rsync thinks that too much has changed for delta copy.

And, yes option 3 is best solution, but only if you do that copy from snapshot or some other freezed data. Don't do it from live data files what are changing during copy process.


I could not get the syntax you used working. We typically provide a WHERE clause in QUERY. Example:

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_users;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
     23687

SQL> select count(*) from t1 where owner in (select username from t2 where username like 'SYS%');

  COUNT(*)
----------
     15775

Then export:

[oracle@o71 ~]$ expdp bp/bp tables=t1 query=t1:\"WHERE OWNER IN \(SELECT USERNAME FROM T2 WHERE USERNAME LIKE \'SYS%\'\)\"

Export: Release 19.0.0.0.0 - Production on Tue Mar 26 15:26:30 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "BP"."SYS_EXPORT_TABLE_01":  bp/******** tables=t1 query=t1:"WHERE OWNER IN (SELECT USERNAME FROM T2 WHERE USERNAME LIKE 'SYS%')"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BP"."T1"                                   1.839 MB   15775 rows
Master table "BP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BP.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/expdat.dmp
Job "BP"."SYS_EXPORT_TABLE_01" successfully completed at Tue Mar 26 15:26:54 2019 elapsed 0 00:00:21

[oracle@o71 ~]$

If IAO.ELIJ really exists, you could use something like this:

query=IAO.VERIFIC:"WHERE RACE_ID IN (SELECT B.RACE_ID FROM IAO.ELIJ B WHERE B.CREATE_DT > SYSDATE - 30)"


SQL Server full database backup without a job

A backup command can be run from just about anywhere and scheduled using any kind of scheduling tool. So, while SQL Agent is the most common method, it's not the only one. To determine what's happening when this backup runs, I'd suggest creating an Extended Events session to capture the queries. You'll be able to see the login that is being used. You may, depending on the connection settings, also be able to see the application making the call. At the very least, you can see the machine within your network that the call is coming from.
Because Extended Events can return a lot of data, I'd suggest turning it on only for the time that the calls are being made based on the log information you have. To search through the data, you can use the Live Data Explorer window within SSMS. I have a blog post on getting started with that tool that might help.


select *
from the_table
where userid in (select userid
                 from the_table
                 group by userid
                 having count(*) = 1)

Alternatively:

select *
from the_table
   inner join (select userid
               from the_table
               group by userid
               having count(*) = 1) t on t.userid = the_table.userid;

or (possibly more efficient because only a single scan is needed):

select id, data, userid
from (
  select id,
         data,
         userid,
         count(*) over (partition by userid) as cnt
  from the_table
) t
where cnt = 1;


Nothing broke, it's just part of the process of rebuilding indexes on a table. This can take quite a while on very large tables.


Unexpected Second Instance of SQL Started

It turns out - when reviewing all the logging that I could, that the Windows Event Log has a crash of SQL recorded - however, SQL itself has no crash in its logs, and the log wasn't closed and a new one created as you'd expect when SQL restarts.

In our monitoring, the second instance never grows beyond 2gb of RAM - and the RAM usage itself never grows, but the pagefile grew at 11gb per day.

I can't see the cause of the crash, and I'd probably have to open a Microsoft support ticket to see why it crashed, created a new instance, but never closed the existing instance - never released it from being the instance as the service, and never died or took over that I can see.

We can see some timeouts for a minute from an API that talks to the server, but no "unavailable" messages.

It seems like the crash is the underlying cause - but that's as far as I can determine for now, and it seems unusual to me.

None of our monitoring, or our consultants monitoring picked up the crash.


Get Stored Procedure Result Column Types

You should be able to use the new system stored procedure sp_describe_first_result_set for that - see the MSDN docs for details:

EXEC sp_describe_first_result_set N'YourStoredProcedureNameHere'


3 seconds is kind of a long time for mysql. I bet you have a lot of full-table scans going on that are using up CPU. I've gotten good mileage in decreasing CPU with just a little indexing.

This is not going to be a short answer, but try this:

Enable the slow query log (set global slow_query_log=on and set global slow_query_log_file='/someplace/you/can/find/it'), and set it to log anything above 1 second (set global long_query_time=1).
Let it log a little bit. Then look at the output. You can just look at the slow log or use a tool like pt-query-digest (http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html) to find your long-running queries. Look especially for queries that scan a lot of rows to only get a few rows of results.
Pick a query that gets run a lot. Look at the involved tables (show create table my-table-in-this-query) and see what the indexes are. Also see the explain plan to show what indexes mysql plans to use (explain query select blah blah put your query here).
Add an index. See CPU decrease.

You might not be able to index everything; the queries may have to be rewritten as well. But I bet you can get a lot of CPU back by doing this for your top queries.


Is there any time where we should avoid cascading delete?

I am generally wary of cascaded deletes (and other automatic actions that could drop/damage data), either via triggers or ON <something> CASCADE. Such facilities are very powerful, but also potentially dangerous.

  So, is cascading delete a correct choice here?

It would certainly do what you are looking for it to do: remove related records when a parent record is removed, without you needing to implement any other logic to ensure that children get removed first therefore making your code more concise. All the actions will be wrapped in an implicit transaction so if something blocks the child deletes the whole operation is blocked, maintaining referential integrity with little or no extra coding effort.

Make sure that your use of cascaded deletes and other "behind the scenes" actions are well documented so future maintainers of the system are fully aware of it.

  When cascading detele should not be used?

It should not be used if you are paranoid like me! One key point to consider is the other developers who currently, or may in future, work on your code/database (hence the comment above about documenting any "hidden" behaviours).

It is quite common in my experience for inexperienced people to use DELETE then re-INSERT in order to update rows, especially when what they really want is a MERGE/UPSERT operation (update existing rows and create new ones where a row with a given key does not exist) and the DBMS doesn't support merge/upsert (or they are unaware of its support). Without cascaded actions this is perfectly safe (or will error when it threatens data integrity) but if someone does this for rows in a parent table where referring FKs have ON DELETE CASCADE set then related data will be deleted as a result of the initial delete and not replaced - so data is lost (not that even if the delete and subsequent insert are wrapped in explicit transactions, the cascade happens with the delete operation - it won't wait to see if the transaction replaces rows in the parent table in subsequent statements) and the cascade could continue through other relation ships (for instance: delete a senior supervisor, his team get deleted by cascade, his teams' teams get deleted by cascade, all the tracked records for all those people get deleted by cascade, ...). Without cascading enabled, you would just get an error here instead of the data being silently lost.


CmdExec Powershell Job Step Escape Double Quotes

I believe the problem you're having has to do with SQL Server Agent Tokens as referred to by this Stack Exchange post SQL Agent - PowerShell step “syntax error”

Excerpting from that post

  This is a not very intuitive and I was never able to find anything
  concrete on the explanation [e.g. no exact BOL or white paper was
  found]. The syntax error in the SQL Agent job is a T-SQL syntax error
  based on User Tokens. So that basically means that a PowerShell
  Sub-Expression Operator is treated as a Token to SQL Server Agent. So
  in PowerShell this $( ) appears to be treated as reserved character
  sequence for SQL Server Agent.

Referencing your attempts,

$(get-date -f yyyy-MM-dd).zip

notice the $( where you're trying to get the current date for the zip file.  That syntax is a Powershell sub-expression. This part is attempting to be interpreted by the SQL Server Agent as a token.

My suggestion would be to separate out the sub-expression into another Powershell variable ($ZipDate) first and then use that to finish the Powershell command.

Try this:

powershell.exe "$ZipDate = (get-date -f yyyy-MM-dd); Compress-Archive -Path "C:\TestCompress" -DestinationPath "c:\TestCompressOut\$ZipDate.zip" -CompressionLevel Optimal"


Can a High number of Worktables/sec and/or workfiles/sec lead to tempdb Contention?

Short answer: yes.

Longer answer: Anything which creates and drops objects from tempdb will cause GAM/SGAM/IAM/PFS/etc. contention and needs to be addressed if possible.  Workspaces are creating tables under the covers so they'll cause a lot of the same issues.


Why use custom CLR aggregate functions for string concatenation and geometry unions in SQL Server?

TSQL is very inefficient for string manipulation compared to .NET (where you can use a StringBuilder for example).

The concatenation approach will allocate a whole load of new strings. It might not be so bad if you could just create a big string up front and use STUFF to replace parts of it but I believe from trying this approach previously that this too just creates a new string behind the scenes.

Additionally the behaviour you are relying on for both cases is undocumented and not guaranteed. "The correct behavior for an aggregate concatenation query is undefined."


Mysql Create table shows ERROR 1005 errno 150

tagName column in table cardTags  is not defined as primary key and you are declaring foreign key FOREIGN KEY (tagName) REFERENCES cardTags(tagName).


In the tree view on the left-hand-side, expand the tree for your database connection. Expand  "Other Users", then "XYZ", then "Views". Double-click on the view name "ABC_CHAR" - this will open the column list. Click "SQL" above the column list to see the query that created the view.

The other way to achieve this is to use SQL:

select text
from all_views
where owner='XYZ'
and view_name = 'ABC_CHAR';

This all assumes you have the correct grants to see the view text.


Just uninstall client tools completely, using Control Panel -> Programs and Features -> SQL Server 2012 -> Change. This should launch the setup program and allow you to uninstall the management tools. If the Control Panel method doesn't work, I have a more brute force way of doing it, just make sure you only remove those CLSIDs that relate to SSMS.

Then grab the Express edition of 2012 SP2 (x64 | x86), which has the same functionality as the version you had installed, but without the licensing and the expiration. For more details (or links to more modern versions of SSMS), see this answer.

There are more cumbersome workarounds listed here and here (yes, they say 2008, but they still apply). I think uninstall/reinstall is cleaner.


Migrate azure sql database to Azure SQL Database Managed Instance

Since the Managed Instance was in preview, the first option we have is to export your Azure SQL Database as a bacpac then import that bacpac to your Managed Instance.

A better option we have now is using the Azure Data Migration service. It is not only faster than the method I described above, but it gives you the option to perform a data validation at end and it rebuilds all indexes on the database. Here you have a step-by-step tutorial.


Unicode error with \u0000 on COPY of large JSON file into Postgres

In a (perhaps) similar case I had been confronted with \u0000 in a string I needed to process as JSON. The follow replace worked for me:

regexp_replace(stringWithNull, '\\u0000', '', 'g')

Hope this helps.


No, there isn't a way to separate the two.

You'll have to either hang on to the large file, or take a new full backup and start moving forwards from there.


Oracle Standby Database - Archivelog GAP

Assuming you mean that the database is "missing" logs from P->R, and that your primary database no longer has those logs either, then the answer is that you'll have a functional database (upon standby activation as a primary) but you will be missing data from P-> the Activation point.

The thing to remember here is that your standby is only as good as your last archive log that was fully applied. Without intervention on your part, your standby database is essentially frozen in time, and will never advance.

If you're missing archive logs, and they are truly unrecoverable (did you check storage backups? Backups to tape?), then your standby, IMO, is worthless as a production level standby. You, and your business, may have a different opinion based on your usage of the database in question.

But if this was truly a production level standby database that is meant to take over for production in a disaster scenario, personally, I would rebuild the standby immediately.


Is there any way to tell the number of matches found in a database value matched with LIKE?

You compare the length of the string with and without the text you're searching for:

  select
        ObjectID
        ,value
        ,(len(value) - len(replace(value,'love',''))) / 4 as occurs
        from exampleTable
        where value like '%love%'


I guess you are comparing the estimated costs for the queries. Those are just estimates based on (among other things) the estimated number of rows returned by the query. Not the actual number of rows.

Your first query estimated that it would return 30 rows and your second query estimated 1000 rows. That is where your difference in query cost comes from.

If you change the queries to fetch only 30 rows you will see that the estimated rows is the same for the queries and the first query actually is costed a bit higher, at least for me in SQL Server 2014.

Don't use the estimates when comparing performance of queries. Use things like duration, number of reads and size of memory grants instead.


Normally what you would have is something like this:

CREATE TABLE Product (
ProductID,
Name,
Description
) ;

CREATE TABLE Order ( /* Shipped products */
OrderID,
ProductID,
ShippedDate
);

CREATE TABLE User (
UserName
) ;

And here's the essential table you need. This establishes a many-to-many relationship (commonly known as an intersection or bridge table) between Orders and Users:

CREATE TABLE User_Order (
OrderID,
UserName
) ;

For every order you would insert one record into the User_Order table for every user that can track that order. So you would have something like this:

OrderID         UserName
-----------------------------
1               joe
1               jane
1               john
2               joe
3               bill
3               jane


To trigger or not to trigger- That is the question

I don't think it's critical to have the old rows at the very moment they turn 30 days old - a few seconds/minutes/hours delay will probably not hurt you at all. Therefore I'd remove old rows with some scheduled task/query run at some fixed interval.

Otherwise you'll end up running additional query for every page view that you get.


What is this type of query called, and how can I formulate it?

I'm not sure if there's a name for this, but you really just want to traverse your relationships with JOINs:

SELECT DISTINCT
    U.*
FROM
    Users U
INNER JOIN
    Group_user GU
        ON GU.user_id=u.User_id
INNER JOIN
    Event_Group eg
        ON eg.group_id = gu.group_id
WHERE eg.event_id = 1234

I added a DISTINCT in case a user was in more  than one group that was in the event.

You can also add a JOIN to the events table if you need another filter (like events taking place on a certain date or whatever).

This is SQL Server syntax but I think it should be basically the same for MySQL, JOIN syntax may vary.


How to get the result of MySQL explain query into a file?

Using the syntax of EXPLAIN, no.

You can run the explain from the command line however:

mysql -e 'EXPLAIN ... ' > /tmp/myexplain.json

SQL isn't responsible for the pretty presentation of data, Workbench is one option.


How to monitor a file share witness for accessibility

The file share witness does not need to be accessible by the SQL Server service account. The FSW is used by the Windows Server Failover Clustering cluster to achieve a quorum and avoid split-brain scenarios. The cluster machine account is the AD principal that needs access to the FSW and if it did not have access you wouldn't be able to set it up as a witness in your cluster.

From an Availability Group perspective, it does not care about access to the FSW. The cluster cares about this and when it fails (and the quorum is lost) it signals the AG to shut down to avoid split-brain. The AG only cares about the SQL Server specific health checks (latency, connectivity to replicas etc) and the signals received from the WSFC cluster regarding cluster health.

If the cluster CNO loses rights to the FSW share then that resource would go offline in the cluster. This won't necessarily trigger any action in the cluster but should log alerts and events in the Windows Event Log for which you can monitor.


How to structure an append-only database in PostgreSQL?

This sounds like a use-case for temporal tables. AFAIK PostgreSQL does not implement this natively. There are extensions for it. It can be written in user-code, usually with triggers.

The basic idea is that the data table has two date/time columns. These define the interval during which the value in a row is applicable - it is "the" value for that key during that interval. By key I mean natural key, not a surrogate row-labeling key i.e. the id column, not the key column in your example. It is best to make this a "closed-open" interval so the start time is included but the end time excluded. The resolution of the interval start and end columns has to be appropriate for the update frequency.

When a new value for that key arrives, the existing row is updated to set its end and a new row is written. The end date/time for the old row and the start date/time for the new row are equal, hence the closed-open formulation.

To use your example: the first row arrives

║ key ║ id ║ field1 ║ field2 ║ from                ║ to
║ 0   ║ 0  ║ a      ║ b      ║ 1970-01-01 00:00:01 ║ 9999-12-31 23:59:59 ║

Some time later the second row for this key arrives. The first row is updated, the second row is written

║ key ║ id ║ field1 ║ field2 ║ from                ║ to
║ 0   ║ 0  ║ a      ║ b      ║ 1970-01-01 00:00:01 ║ 1970-01-01 00:05:01 ║
║ 2   ║ 0  ║ a      ║ c      ║ 1970-01-01 00:05:01 ║ 9999-12-31 23:59:59 ║

To read a value

select <columns>
from <table>
where id = 0
and from <= <right now>
and to > <right now>

The place holder <right now> represents the current instant on the server. It can be local, UTC or whatever. It just has to be consistent with what was used when the row was written. Using UTC uniformly makes this easier.

A consistent set of values can be read from the database from any point in history by using that historical time in the predicate instead of <right now>.

To delete a value set to to the current time.

There are, of course, downsides. Each table must have extra columns. These must be indexed. Ensuring intervals for each key do not overlap using only constraints is non-trivial. Every query must have temporal predicates and they must all use exactly the same time value.

These complexities can be mitigated by adding an archive table. When an existing row's to values is set, that row is also deleted from the data table inserted into the corresponding archive table. So each id value will only have one row in the data table, and many retired rows in the archive table. Queries no longer need the additional temporal predicates. Indexes (on the data table) need not include the interval columns. Historical queries become more complex, and every new value stored will incur the cost of an additional delete and insert.


create table tableA (EX char(1), STATUS char(3), DLV varchar(10), Actual_FEE decimal(18,2));
insert into tableA values
('N', 'ACT', 'MAIL',   1.25),
('N', 'ACT', 'EMAIL',  1.25),
('N', 'ACT', 'PICKUP', 0),
('N', 'NON', 'MAIL',   0),
('N', 'NON', 'EMAIL',  1.25),
('N', 'NON', 'PICKUP', 0),
('Y', null,  null,     0);
GO

7 rows affected

create table tableB (EX char(1), STATUS char(3), DLV varchar(10), Expected_FEE decimal(18,2));
insert into tableB values
('N', 'ACT', 'MAIL',   1.25),
('N', 'ACT', 'EMAIL',  0),
('N', 'ACT', 'PICKUP', 0),
('N', 'NON', 'MAIL',   1.25),
('N', 'NON', 'EMAIL',  0),
('N', 'NON', 'PICKUP', 0),
('Y', null,  null,     0);
GO

7 rows affected

According to your expected result it could be:

select    a.EX, a.STATUS, a.DLV, a.Actual_FEE, b.Expected_FEE
from      tableA a
left join (select distinct EX, STATUS, DLV, Expected_Fee
           from   tableB) b
on        a.EX = b.EX
and       (a.STATUS = b.STATUS OR b.STATUS IS NULL)
and       (a.DLV = b.DLV or b.DLV IS NULL)
GO

EX | STATUS | DLV    | Actual_FEE | Expected_FEE
:- | :----- | :----- | :--------- | :-----------
N  | ACT    | MAIL   | 1.25       | 1.25
N  | ACT    | EMAIL  | 1.25       | 0.00
N  | ACT    | PICKUP | 0.00       | 0.00
N  | NON    | MAIL   | 0.00       | 1.25
N  | NON    | EMAIL  | 1.25       | 0.00
N  | NON    | PICKUP | 0.00       | 0.00
Y  | null   | null   | 0.00       | 0.00

dbfiddle here


What can I do with VIEW DEFINITION permissions that I can't do with ALTER permissions?

VIEW DEFINITION is a way to explicitly GRANT or DENY metadata visibility, but it's not the only way a user gets metadata visibility.
Users implicitly get metadata visibility without the VIEW DEFINITION permission on objects they own or have some other permission on:

The visibility of metadata is limited to securables that a user either
owns or on which the user has been granted some permission. For
example, the following query returns a row if the user has been
granted a permission such as SELECT or INSERT on the table myTable .

Metadata Visibility Configuration
And by default the public role has VIEW DEFINITION.  So while your user does not have the VIEW DEFINITION permission, ALTER on the database implies ALTER on all the objects, which allows the user to view the metadata of the objects.


Trying to limit stored procedure results based on date

Here is one method for doing that.  But it only gets records that are for the current day.  If you needed for the last 24 hours, it would be slightly different.

Per suggestions in the comments, changed the query to better use any potential indexes on the base table.  However, it does assume that ModifiedDate does not contain future dates.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TW_spRPTAllReceiverInventory]
@SiteId varchar(50),
@ReceiverId int,
@UserName varchar(50)

AS

BEGIN

DECLARE @SQL varchar(8000), @Index int, @Value varchar(500), @Source varchar(100), @tmp DATETIME

SELECT @Index = CHARINDEX('Initial Catalog=', ConnectionString) + 16, @Value = ConnectionString
FROM CustomerSetting
WHERE SiteId = @SiteId

SET @Source = '[' + SUBSTRING(@Value , @Index, CHARINDEX(';', @Value, @Index) - @Index) + ']'

SET @SQL = 'SELECT R.SiteId, L.*
        FROM ' + @Source + '.[dbo].[vwReceiver] R JOIN ' +
            @Source + '.[dbo].[vwInventory] L ON R.ReceiverId = L.ReceiverId
        WHERE R.ReceiverId = ' + CAST(@ReceiverId as varchar) + ' AND L.ModifiedUserName = '
            + Cast(@UserName as varchar) + ' AND ModifiedDate >= CAST(CAST(GETDATE() AS DATE) AS DATETIME)'

EXEC(@SQL)

END


Trigger to update a column in a new record to CURRENT_TIMESTAMP as it is inserted

Your trigger needs to leverage the 'virtual inserted' table as in the following example:

CREATE TRIGGER ModDate
    ON TableX
    AFTER INSERT
AS
BEGIN
    UPDATE X
    SET ModifiedDate = CURRENT_TIMESTAMP
    FROM TableX X
    JOIN inserted i ON X.key = i.key -- change to whatever key identifies
                                     -- the tuples
END

AS an alternative to a trigger, why not simply assign a default value to that column.  Example:

ALTER TABLE myTable
  ADD CONSTRAINT CONSTRAINT_NAME
    DEFAULT CURRENT_TIMESTAMP FOR myColumn

example:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp

CREATE TABLE #temp (
    col1 VARCHAR(10)
    ,col2 DATETIME
    )

ALTER TABLE #temp ADD CONSTRAINT ConstraintForTemp DEFAULT CURRENT_TIMESTAMP
FOR col2

INSERT INTO #temp (col1)
VALUES ('abc')

SELECT *
FROM #temp


TSQL performance - JOIN on value BETWEEN min and max

You need an additional index.  In your Fiddle example I added:

CREATE UNIQUE INDEX ix_IP ON Request(CategoryID, IP)

Which covers you for the request table and gets an index seek instead of a clustered index scan.

See how that improves it and let me know.  I'm guessing it'll help quite a bit since the scan on that index is I'm sure not cheap.


Why does a query on a sparse index perform much faster than a query on a partial index in MongoDB?

Turns out I created the partial index incorrectly.

If you want to speed up an $exists query, you need to add the partialFilterExpression option like so:

db.detection_events.createIndex({"thingToQueryOn": 1}, {partialFilterExpression: {thingToQueryOn: {$exists: true}}})

Note the lack of quotation marks in the second document


How does a transaction log backup deal with active log?

Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY option works.
I think it's also important to understand how database restore operations and options work with SQL Server and that too will help you get a better understanding of some of this.

Quoted explanations and some key points to help clarify further

1. Transaction Log Backup
A transaction log backup allows you to backup the active part of the
transaction log.  So after you issue a "Full" or "Differential" backup
the transaction log backup will have any transactions that were
created after those other backups completed.  After the transaction
log backup is issued, the space within the transaction log can be
reused for other processes.  If a transaction log backup is not taken,
the transaction log will continue to grow.

2. Log Backup Chain
A log backup chain is an unbroken series of log backups that contain
all the transaction log records necessary to recover a database to a
point in time. A chain starts with a full database backup, and
continues until something breaks the chain, thus preventing more log
backups being taken until another full (or differential) backup is
taken.

3. Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it
automatically truncates the inactive portion of the transaction log.
This inactive portion contains completed transactions and so is no
longer used during the recovery process. Conversely, the active
portion of the transaction log contains transactions that are still
running and have not yet completed. SQL Server reuses this truncated,
inactive space in the transaction log instead of allowing the
transaction log to continue to grow and use more space.

Additional Reading

Understanding How Restore and Recovery of Backups Work in SQL
Server


Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

As Erwin Brandstetter pointed out, cross post from stackoverflow.com/q/12379598/939860. Solved.


What would be the best way to store one valuer per hour per day per customer?

If the values don't change too often, I would use and Slowly Changing Dimension (Type 2) approach. In this case, you will store a new row only when your value changes. This is mostly common used on Data Warehouses to track historical data.

In a nutshell:

1) Change your table to something like this:

CREATE TABLE dbo.CustomerHistory (
CustomerHistoryKey int identity (1,1),  -- PK and surrogate key
CustomerId int NOT NULL,
Value int,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL
)

ALTER TABLE dbo.CustomerHistory ADD  CONSTRAINT PK_CustomerHistory  PRIMARY KEY CLUSTERED
(CustomerHistoryKey)

CREATE UNIQUE NONCLUSTERED INDEX [BK_CustomerHistory] ON dbo.CustomerHistory
(CustomerId, StartDate)

CREATE UNIQUE NONCLUSTERED INDEX [IX_CustomerHistory_currentRow] ON dbo.CustomerHistory
(CustomerId, EndDate)
WHERE EndDate = CAST('99990101' AS datetime)

Avoid using nulls on StartDate and EndDate. Instead, choose a lower and higher date value, such as 19000101 and 99990101

2) Continue running your routine every hour to check if Value was changed, but only add a new row for those customers with changes. To add a new row, do:

DECLARE @ChangeDate datetime = GETDATE()
Set the current row EndDate equals to @ChangeDate
Add a new row with the updated Value with StartDate equals to @ChangeDate and EndDate equals to '99990101'

How it works:

You will store much less data if your values doesn't change to often
If you need to create a relationship pointing to a specific point in time, use CustomerHistoryKey
If you need to check one value in time, you can search by CustomerId and time >= StartDate and time < EndDate

Check the following link for an quick overview: https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row


Best way to put commas into large numbers

TSQL's FORMAT function can do that.  EG

select format(1232131312342132123.2222,'0,###.##')

outputs

1,232,131,312,342,132,123.22

If you want to use a different separator, you still use , which has a special meaning in .Net format string, and then replace the , with something else.  EG:

select replace(format(123445678.2222,'#,###.##'),',','_')

outputs

123_445_678.22


You already are not using the "same license" for both servers. SSRS is part of the SQL Server license (Standard Edition and above), but it's only covered by your SQL Server license on the same server. IOW, if your report server is on Server A and SQL Server is on Server B, you need to license them both.
So yes, if you have acquired a license for Server B to upgrade SQL Server, you can do that without touching Server A (report server). Server A will continue to be covered by its previous license.
Verify with whomever handles your SQL Server license purchases.


Why can't relational databases meet the scales of Big Data?

MS just had a tech talk in the Netherlands where they discussed some of this stuff.  It starts off slowly, but gets into the meat of Hadoop around the 20 minute mark.

The gist of it is that "it depends".  If you have a sensibly arranged, (at least somewhat) easy to partition set of data that (at least somewhat) is homogeneous, it should be fairly easy to scale to those high data volumes with an RDBMS, depending upon what you're doing.

Hadoop and MR seem to be more geared to situations where you are forced to to large distributed scans of data, especially when those data aren't necessarily as homogeneous or as structured as what we find in the RDBMS world.

What limitations are Big Data solutions not bound to?  To me, the biggest limitation they're not bound to is having to make a rigid schema ahead of time.  With Big Data solutions, you shove massive amounts of data into the "box" now, and add logic to your queries later to deal with the lack of homogeneity of the data.  From a developer's perspective the tradeoff is ease of implementation and flexibility on the front end of the project, versus complexity
 in querying and less immediate data consistency.


Does the order of columns in a PK index matter?

Yes it does.

By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table. There may be a number of extra index pages added to represent the upper levels of the b-tree index, but the lowest (leaf) level of a clustered index is simply the logical order of the data itself.

To be clear about it, rows on a page are not necessarily physically stored in clustered index key order. There is a separate indirection structure within the page that stores a pointer to each row. This structure is sorted by the clustered index keys. Also, each page has a pointer to the previous and next page at the same level in clustered index key order.

With a clustered primary key of (RowNumber, DataDate), the rows are logically sorted first by RowNumber and then by DataDate - so all rows where RowNumber = 1 are logically grouped together, then rows where RowNumber = 2 and so on.

When you add new data (with RowNumbers from 1 to n) the new rows logically belong inside the existing pages, so SQL Server will likely have to do a lot of work splitting pages to make room. All this activity generates a lot of extra work (including logging the changes) for no gain.

Split pages also start off about 50% empty, so excessive splitting can result in low page density (fewer rows than optimal per page) as well. Not only is this bad news for reading from disk (lower density = more pages to read), the lower-density pages also take up more room in memory when cached.

Changing the clustered index to (DataDate, RowNumber) means that new data (with, presumably, higher DataDates than currently stored) is appended to the logical end of the clustered index on fresh pages. This will remove the unnecessary overheads of splitting pages and result in faster load times. Less fragmented data also means that read-ahead activity (reading pages from disk just before they are needed for an in-progress query) can be more efficient.

If nothing else, your queries are much more likely to search on DataDate than RowNumber. A clustered index on (DataDate, RowNumber) supports index seeks on DataDate (and then RowNumber). The existing arrangement only supports seeks on RowNumber (and only then, perhaps, on DataDate). You might well be able to drop the existing nonclustered index on DataDate once the primary key is changed. The clustered index will be wider than the nonclustered index it replaces, so you should test to ensure that performance remains acceptable.

When importing new data with bcp, you may get higher performance if the data within the import file is sorted by the clustered index keys (ideally (DataDate, RowNumber)) and you specify the bcp option:

-h "ORDER(DataDate,RowNumber), TABLOCK"

For best data loading performance, you might try to achieve minimally-logged inserts. For more information, see:

SQL Server Index Basics by Robert Sheldon
Effective Clustered Indexes by Michelle Ufford
Bulk Inserts Via TSQL by Robert Sheldon
Minimal Logging with INSERT…SELECT into Empty Clustered Tables by me
Minimal Logging with INSERT…SELECT and Fast Load Context by me


SQL Formatting: Add Space within query result

You can do this several ways. I'm assuming this is stored as a number and not a string, and I'm using RTRIM to prevent invalid datatype errors.

DECLARE @i BIGINT = 1234567891234567891

SELECT LEFT(@i, 4) + ' ' + RIGHT(@i, LEN(@i) -4) AS lefty_righty

SELECT SUBSTRING(RTRIM(@i), 0, 5) + ' ' + SUBSTRING(RTRIM(@i), 5, LEN(@i)) AS substrings

SELECT STUFF(RTRIM(@i), 5, 0, ' ') AS stuffy


You say the code is an example of how to use ADO, but it references DAO. DAO and ADO are two completely different data access libraries from Microsoft. ADO is the newer one. Please don't mix them up.

Try the following:

In your Access Visual Basic IDE, click on Tools | References. Make sure that Microsoft ActiveX Data Objects 2.8 is selected. Unselect any other references mentioning DAO, Data Access Objects, ADO or ActiveX Data Objects. If you don't have version 2.8 installed, try the latest version you've got.

Now, modify your sub to look like this:

Sub TestMyADODB()
    Dim myConnection As ADODB.Connection
    Set myConnection = CurrentProject.Connection

    Dim mySQL As String
    mySQL = mySQL & "UPDATE Amplifier "
    mySQL = mySQL & "SET Amplifier.lngCategory = 405, Amplifier.lngSubType = 3 "
    mySQL = mySQL & "WHERE Amplifier.lngPartIndex=9"

    myConnection.Execute mySQL

    Debug.Print "DONE"
End Sub

I changed the connection type to ADODB.Connection to match the selected library. I also removed the recordset object since that's only used for SELECT queries. Queries that don't return rows can be executed directly on the connection.

This works on my machine at least.


Where are database specific settings stored?

Database-level settings are not stored in any configuration file, but the pg_db_role_setting catalog, the only question is how to get them out there.

As often in similar cases, psql can be of tremendous help here.  The key is \set ECHO_HIDDEN on, which then shows the queries behind the different meta-commands.

In one of my sandbox DBs I see the following:

test=# \drds
                       List of settings
    Role     │ Database │              Settings
─────────────┼──────────┼─────────────────────────────────────
 alice       │          │ log_statement=none                 ↵
             │          │ work_mem=12MB
 bob         │          │ log_statement=all                  ↵
             │          │ work_mem=64MB
 luser       │          │ log_statement=all
 tom         │          │ log_statement=all
             │ test     │ search_path=test, public
             │ wacky    │ default_transaction_read_only=true

We are interested in the rows where there is no user specified - these are exactly the settings you were asking about.  With ECHO_HIDDEN on, you also get the following query (the formatting is mine):

SELECT rolname AS "Role",
       datname AS "Database",
       pg_catalog.array_to_string(setconfig, E'\n') AS "Settings"
  FROM pg_catalog.pg_db_role_setting s
  LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
  LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole
 ORDER BY 1, 2;

One can simplify this to get only the DB-specific settings:

SELECT datname AS database,
pg_catalog.array_to_string(setconfig, E'\n') AS settings
FROM pg_catalog.pg_db_role_setting s
JOIN pg_catalog.pg_database d ON d.oid = setdatabase ORDER BY 1, 2;

 database │              settings
──────────┼─────────────────────────────────────
 test     │ search_path=test, public
 wacky    │ default_transaction_read_only=true


How to continuously keep two databases in sync

This is a textbook use case for standard MySQL Replication.

You will need to decide if one DB will be the master and the other the slave in which case only the master replicates to the slave. If you want clients to be able to connect to either machine and issue writes and then have writes be propagated to both machines than you will need to look into Multi-Master replication:

http://mysql-mmm.org/

If you will be doing just standard master -> slave than MySQL has native tools as thats the default stack.


Do you still use MyISAM or prefer Aria storage engine?

It is very interesting this question would come up because a similar question was asked back in January 2011 ( When is the right time to use MariaDB instead of MySQL, and Why? ). Giving thought to that 16-month-old question and how I answered it in April 2011, here are my answers to your immediate questions:

  Why still using MyISAM ?

MyISAM still has features that are unique and useful for small datasets and read slaves in HA setups

Deadlocking is impossible in low-write, heavy-read environments

MySQL MyISAM Locking question
Benefits of table level locking

Converting to Fixed Row Format creates bigger tables but increases query performance in terms of

character string comparison/compression : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
read/write speed : Which is faster, InnoDB or MyISAM?

Full Faith and Confidence

InnoDB has become the de facto standard of Transactional Storage Engines for MySQL. XtraDB is being accepted and used as well among the Percona Server community. Full faith and confidence seems to rest with InnoDB over XtraDB as present. This could easily change over time as more recognize XtraDB's superior features.
The same equally applies to MyISAM vs Aria. Full faith and confidence currently rests with MyISAM. An example of those who do not have Full faith and confidence are Drizzle users (Drizzle have abandoned MyISAM as an Optional Storage Engine and relegated it to temp table use only)

  To changes storage engine from MyISAM to Maria should it be a problem? (lose index or something)

If you mysqldump the data and the schema into two separate files, you can always import data into table with either the MyISAM or Aria storage engines. Just keep around the data only mysqldump to be sure.


Store two metric sets long or wide if frequently joined?

If that's the complete situation I would merge metrics_a and metrics_b for considerably smaller storage and considerably faster and simpler queries.

Your example query could then look like this:

SELECT point_id
     , count(*) AS tot_ct
     , :lim AS lim
     , count(a00 <= :lim OR NULL) AS a_ct00
     , count(a01 <= :lim OR NULL) AS a_ct01
     , count(a02 <= :lim OR NULL) AS a_ct02
     -- ,  ... more
     , count((a00 - b00) >= 4.25 AND b00 <= :lim OR NULL) w_ct00
     , count((a01 - b01) >= 4.25 AND b01 <= :lim OR NULL) w_ct01
     , count((a02 - b02) >= 4.25 AND b02 <= :lim OR NULL) w_ct02
     -- ,  ... more
     , avg(a00) AS av_a00, avg(a01) AS av_a01, avg(a02) AS av_a02  -- , ... more
FROM   metrics
WHERE  logdate BETWEEN '2017-06-01' AND '2017-07-31';

Renamed m00 to a00 and b00 etc. respectively.
Saves at least 28 bytes (tuple header + item identifier) + 8 - 16 bytes for redundant columns per row. Smaller is faster, too. See:

Configuring PostgreSQL for read performance

No join, faster query.
Shorter, simpler code.

Asides:

I shortened the count syntax. Should be about as fast. See:

For absolute performance, is SUM faster or COUNT?

Don't omit the AS keyword for column aliases. See:

Aliases with correlated subqueries

Always use standard ISO 8601 date format. No exceptions. Related:

Can't execute dynamic DDL, argument is NULL


Is pg_trigger_depth() bad to use for preventing trigger cascading (recursion)?

It is not bad to use per se (IMHO). You just need to be aware of implications.

I'd rather make it pg_trigger_depth() < 1 instead of pg_trigger_depth() = 0, on principal, but that's not important here. We are talking about triggers like:

CREATE TRIGGER my_trigger
AFTER INSERT ON my_tbl
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE my_trigger_func();

If you later add triggers that would in turn fire your trigger, nothing will happen instead. Your trigger only reacts to non-trigger events. Depending on your setup this may be exactly what you want - or kind of a trap, if you forget about this and later add more triggers that should in turn (also) fire this trigger. You are changing default behavior. Be sure to document this clearly.

Be aware that pg_trigger_depth() counts any trigger, not only the same trigger to prevent recursion, so the condition prevents the trigger from firing if called from any other trigger, too.
The manual:

  current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)

Related:

How to prevent a PostgreSQL trigger from being fired by another trigger?


NHibernate Parameter Sniffing, SQL Server 2005 vs SQL Server 2008

I don't know which version of NH you're using, but I'm using 3.1 with SQL 2008 and it does not have any special features to handle Parameter Sniffing. I know this because I've suffered quite a bit trying to overcome this issue.

I don't know if you use HQL/Criteria/QueryOver/LINQ, but I found two ways to (kind of) solve this:

Appending "option(recompile)" on the query -> with an hook on the query execution if you're using the likes of QueryOver/LINQ.

Although this works, it no longer fetches existing query plans from the cache, and every query has to build it ground-up.

Conditionally, add a dummy predicate to the query

Or something that would force a different query plan to be fetched. For example, if the date interval is less than a month add a .Where(x => true). It would then have a different plan for large intervals and a plan for small intervals.


What happens when checkpoint_segments reached?

Combing through the docs, I see the following (accidental incorrect details will hopefully (or surely) be pointed out by others):

  There is a separate server process called the background writer, whose
  function is to issue writes of "dirty" (new or modified) shared
  buffers.

BW does its work according to its config parameters.  It writes at most bgwriter_lru_maxpages dirty shared buffers, then has a bgwriter_delay millisecond rest.  Now, when it comes to checkpoint_segments log segments (or checkpoint_timeout seconds, if it comes first), it flushes all (remaining) dirty segments to data files.  Now the database data has a consistent state on disk, with a corresponding entry in the log as well.  It is a good starting point for a subsequent REDO if necessary.

And who does make the checkpoints?

  The server's background writer process automatically performs a
  checkpoint [...]

As for your 2nd point, WAL is WAL (stored in log segment files) and data files are data files.  The two will be consistent with each other but not merged.


You can use ORDER BY inside GROUP_CONCAT() function. You can also change the separator, if you don't want to use comma. Further details in MySQL documentation: GROUP_CONCAT()

Example:

SELECT
    product_id,
    GROUP_CONCAT(name
                 ORDER BY language_id
                 SEPARATOR ','
                ) AS product_names
FROM
    products_languages
GROUP BY
    product_id ;


Access Linked Server error "The provider did not give any information about the error."

I found the issue but I'm not sure why its happening.

To query the linked server I have to right click SSMS and select Run as Admin.  Then I can query the access db.

I dont know why this is the case as I'm remoted into the machine.  I'm an administrator on the machine.  I created the Linked Server to the Access Database which resides on the C:\Drive of the server.

But this will at least allow me to hand run my code.  Since this is my development server I think that will suffice at least for now.


MySQL Storage Engine Changed from InnoDB to MyISAM on restoring backup in lower version

If the backup was restored from a .sql dump which didn't include the ENGINE on the CREATE TABLE statements, MySQL would fall back to the "default_storage_engine" (5.7 manual version), which before 5.5.5 was "MyISAM"


SELECT id, name, SUM(result)
FROM (SELECT cat_id as id, name, result  FROM test
UNION ALL
SELECT t1.parent_id as id, t2.name, t1.result
FROM test as t1
LEFT JOIN test as t2 ON t2.cat_id = t1.parent_id
) as a
GROUP BY id,name ORDER BY id


You can't do this on RDS. The rds_superuser isn't a true superuser and cannot load any C-language extensions.


How to group records by a two-value column ignoring small gaps?


Can changes to cardinality optimization be made without taking users offline?

Yes the changes can be done without taking users offline. But please note that this is ALTER DATABASE command which will lock the database so if any schema changes are happening it would be blocked. It would be better to run this query when load is relatively very less, just so that it finishes very quickly.

Also note that when you enable legacy cardinality estimation the procedure and plan cache will be flushed and new queries would be recompiled using old CE. This will force SQl Server to do some extra work and you might see slowness for a very small period.


Adding column from SSMS wizard in SQL Server

It only does that if you try and insert the new column between already existing ones.

If you add it to the end of the table it will just do a simple

 ALTER TABLE T ADD some_column INT

The reason SSMS does this is because there is no TSQL syntax to reorder the columns in a table. It is only possible by creating a new table.

I agree that this would be a useful addition. Sometimes it would make sense for the new column to be shown next to an existing column when viewing the table definition (e.g. to have a created column next to a last_modified column or telephone_number grouped with address and email columns).

But a related Connect Item ALTER TABLE syntax for changing column order is closed as "won't fix". The only workaround apart from rebuilding the table would be to create a view with your desired column order.


What's the maximum number of tables that can be referenced in a query in MySQL?

Limitations of Joins

In MySQL (4.0, 5.0, 5.5), the maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.


After hours of trying numerous things I've found this as to answer my question. On a tip of an Oracle Community discussion, I was told to run this below.

select comp_id, version, status, comp_name
from dba_registry
where comp_id='SDO';

This would tell me if I am or not running Oracle Spatial on my existing database that I ran the export from. Now, why I bring this up is I've asked numerous people in my organization if we're using spatial and numerous people have swore up and down we don't. Come to find out, the database is installed with spatial and that above sql script showed me that yes, it is running spatial.

RDS won't accept spatial data, even if there is no spatial data in it. So I'm in the process of installing an EC2 instance with 11gR2 and actually importing the data that way. This is why the stored procedures failed as above. I just don't know why our AWS team didn't catch this when I submitted my trouble ticket. Oh well.

Thanks for all of the help.


Does the Mysql install location affect write speeds?

As a comparison, I uninstalled the Mysql 8.0 server, and replaced it with a 5.7 64-bit version, with the same setup: Server binaries on the regular hard drive, data files on the SSD. The speed went back to 8-10 minutes to import the SQL files.

I can only assume that the issue is with the v8.0 server, but I cannot speculate as to why. Since I have no particular reason to need the v8.0, I will stay with the v5.7 for now.


How to use LOW_PRIORITY for heavy queries?

LOW_PRIORITY doesn't work for two reasons.

It works only with MyISAM engine.
It would just cause to wait until there are no more normal or HIGH_PRIORITY read requests. If your server happens to be under constant load, it can even happen, that the update doesn't get executed at all.

What you can do, is, to check with a SELECT first (I mean rewriting your UPDATE to a SELECT), that your query is performant, that it uses indexes to find the rows to update (check with EXPLAIN) and add appropriate indexes if necessary.

Then when you execute your UPDATE statements, pack them in a transaction.
The InnoDB engine logs information about currernt transactions in a memory buffer. When a transaction commits or rolls back, the log buffer is flushed to disk. If the log buffer is small, it might fill up before the end of the transaction, requiring a flush to the log file before the outcome of the transaction is known. For a committed transaction, this results in multiple disk operations rather than one. For a rolled-back transaction it results in writes that, with a larger buffer, would not need to have been made at all. To set the size of the log buffer, adjust the value (or add it, if it's not set yet) in your my.cnf (Linux) or my.ini (Windows)

[mysqld]
innodb_log_buffer_size = 8M

The default value is 1MB. Typical values range from 1MB to 8MB. Values larger than 8MB are of no benefit.

If this isn't enough, you can update rows in batches using LIMIT and spread the update statements over time, so that the row-locks aren't held for too long.

Good luck.


how to upgrade postgresql 9.0.4 to latest with minimum downtime

The fastest way will be pg_upgrade with the -k option.

This way. you can get away with a few minutes down time, which is required to move the database metadata.

If you cannot afford a few minutes down time, you will have to resort to a trigger based replication method like Slony. This way, you can replicate the database to a current version and switch the application over once replication has caught up.


It looks like you have mostly answered your other questions in your monologue, and the things that made a difference are the standard list of things to do to force a recompile after being afflicted by parameter sniffing.

To answer your last question, the one marked my question is this:

  What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

The answer is that the plan cache is very specific about SET settings that are in effect on the session that is presenting the query. A different SET setting could need a very different plan, such as the setting SET ANSI_NULLS.  Very likely, your connection from SSMS has different settings from what your .Net application.  This is easily verifiable by using SQL Server Profiler to trace for Existing Connections and Login Audits, which show you the SET settings.

The effect is that the seemingly same query will result in a Cache Miss and a new one has to be created, which will be somewhat based on the parameters being presented to SQL Server for this instance of the query.


Causes for pg_class locks except DROP TABLE

The situation is caused by a long-running transaction which does a SELECT on pg_class first. This causes the ACCESS SHARE lock.

Typically, DROP TABLE does not cause an ACCESS EXCLUSIVE lock, but in that case there is a PG extension loaded which uses more restrictive locking.

Solution for the current situation is to move the SELECT on pg_class out of the long-running transaction.


where clause: if this value from col x make sure col y is this value

Like this:

select * from myTable
where colY = 'that' or colX <> 'this'

The standard implementation of any criteria specified as A => B is as B or not A

Update:

Using this CTE for sample data:

with myTable as (
    select N,colX,colY from ( values
         (1,'this','hey')
        ,(2,'this','that')
        ,(3,'yup', 'yerp')
        ,(4,'yup', 'that')
    ) myTable(N,colX,colY)
)

and this query from above:

select * from myTable
where colY = 'that' or colX <> 'this'

yields (as required):

N           colX colY
----------- ---- ----
2           this that
3           yup  yerp
4           yup  that

(3 row(s) affected)


Possible to do INSERT with some columns from SELECT query and some columns with specific values?

Literal must be a column in output list simply:
INSERT into ORDERS (cust_id,   rep_id , sku_ordered)
SELECT                id   , sales_rep,   'ABC123'
from CUSTOMERS
where cust_name = 'ABC Corp';


The easiest way would be to change the global setting for long_query_time to a ridiculously high number, run the mysqldump, and change long_query_time back to its original value.
Perhaps something like this
MYSQL_USER=backupuser
MYSQL_PASS=backupuserpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Record the long_query_time value
#
SQL="SELECT variable_value FROM information_schema.global_variables"
SQL="${SQL} WHERE variable_name='long_query_time'"
ORIGINAL_LONG_QUERY_TIME=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Change long_query_time to 1 year
#
SQL="SET GLOBAL long_query_time = 31536000"
mysql ${MYSQL_CONN} -ANe"${SQL}
#
# Perform mysqldump
#
mysqldump ${MYSQL_CONN} ...
#
# Change long_query_time to original value
#
SQL="SET GLOBAL long_query_time = ${ORIGINAL_LONG_QUERY_TIME}"
mysql ${MYSQL_CONN} -ANe"${SQL}

or disable the slow query log
MYSQL_USER=backupuser
MYSQL_PASS=backupuserpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Disble Slow Query Log
#
SQL="SET GLOBAL slow_query_log = 'OFF'"
mysql ${MYSQL_CONN} -ANe"${SQL}"
#
# Perform mysqldump
#
mysqldump ${MYSQL_CONN} ...
#
# Enable Slow Query Log
#
SQL="SET GLOBAL slow_query_log = 'ON'"
mysql ${MYSQL_CONN} -ANe"${SQL}"

Give it a Try !!!
CAVEAT #1
Your DBA must give the SUPER privilege to backupuser so that backupuser can globally change the long_query_time setting. Perhaps a DBA can have a cronjob give away SUPER privilege to backupuser just before the mysqldump and revoke SUPER privilege when the backup is done.
CAVEAT #2
Unfortunately, any long running queries that occur during the mysqldump might not be recorded


See whether a groupped item have more than one value in a database column

You can either use an IN clause with an intersect

select *
from product_lines
where sku in (select sku
              from product_lines
              where discount > 0
              intersect
              select sku
              from product_lines
              where discount = 0);

Using exists might be faster with SQL Server:

select *
from product_lines p1
where exists (select *
              from product_lines p2
              where p2.discount > 0
               and p2.sku = p1.sku)
  and exists (select *
              from product_lines p3
              where p3.sku = p1.sku
                and p3.discount = 0);


How can I use value of the id column as default value of another non-nullable column when using postgresql-rails?

You will need to manually create your own sequence instead of letting Postgres/Rails do it for you. Then, because you know the name of the sequence you can use currval() to grab the current value of id as the DEFAULT value for other rows.

In a Rails migration, you can drop down to raw SQL for this:

class CreateSampleTable < ActiveRecord::Migration

  def up

    execute "CREATE SEQUENCE sample_id_seq"

    execute %{
      CREATE TABLE sample (
        id   bigint UNIQUE NOT NULL DEFAULT nextval('sample_id_seq'),
        col1 bigint        NOT NULL DEFAULT currval('sample_id_seq')
        -- Add other columns here
      )
    }

    execute "ALTER SEQUENCE sample_id_seq OWNED BY sample.id"

  end

  def down
    drop_table :sample
  end

end

Now whenever you insert a row into this table, id will auto-increment and col1 will have the same value as id by default.


How to define and query in case of table with Mutually exclusive one-to-many relationships

It will be something like:

SELECT e.index,
       e.entityname,
       COALESCE(a1.AggregateName, a2.AggregateName, a3.AggregateName) AggregateName,
       COALESCE(c2.CountryName, c3.CountryName) CountryName,
       r3.Region,
       e.Value
FROM Entity e
-- when idAggregate_fkey is specified
LEFT JOIN Aggregate a1 ON a1.idAggregate = e.idAggregate_fkey
-- when idCountry_fkey is specified
LEFT JOIN Country c2 ON c2.idCountry = e.idCountry_fkey
LEFT JOIN Aggregate_to_Country ac2 ON ac2.idCountry_fkey = e.idCountry_fkey
LEFT JOIN Aggregate a2 ON a2.idAggregate = ac2.idAggregate_fkey
-- when idRegion_fkey is specified
LEFT JOIN Region r3 ON r3.idRegion = e.idRegion_fkey
LEFT JOIN Country c3 ON c3.idCountry = r3.idCountry_fkey
LEFT JOIN Aggregate_to_Country ac3 ON ac3.idCountry_fkey = c3.idCountry_fkey
LEFT JOIN Aggregate a3 ON a3.idAggregate = ac3.idAggregate_fkey

Check join order and conditions for each branch - I may make a mistake.
Check if some joins may be INNER.


From the mysqladmin documentation:

  In MySQL 5.6, the new password can be omitted following the password
  command. In this case, mysqladmin prompts for the password value,
  which enables you to avoid specifying the password on the command
  line. Omitting the password value should be done only if password is the final
  command on the mysqladmin command line. Otherwise, the next argument
  is taken as the password.

In earlier versions you can't do this as those will complain for 'Too few arguments to change password' (thanks Kaarel for reporting this).  In theory you can issue a command like

mysqladmin -u kaarel password newpass

and then it does not appear in the database logs - but may still be stored elsewhere, like .bash_history.


Is it impossible to force MariaDB users to SSL?

REQUIRE SSL grant option on users is what you need. It works for me as intended:

MariaDB [(none)]> CREATE USER testssl@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT USAGE ON *.* TO testssl@localhost REQUIRE SSL;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> Bye

$ sudo mysql -u testssl --ssl
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+---------------------------------------------------------+
| Grants for testssl@localhost                            |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testssl'@'localhost' REQUIRE SSL |
+---------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> Bye

$ sudo mysql -u testssl --skip-ssl
ERROR 1045 (28000): Access denied for user 'testssl'@'localhost' (using password: NO)

$ grep ssl /etc/my.cnf
ssl-ca = /etc/mysql/ca.pem # [client]
ssl-cert = /etc/mysql/client-cert.pem
ssl-key = /etc/mysql/client-key.pem
ssl-ca = /etc/mysql/ca.pem # [server]
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem

You can see how workbench works with SSL, but fails to connect if I force it to not use SSL:

require_secure_transport is MySQL 5.7+ only, and while it has something to do with forcing TLS, it also considers secure local unencrypted connections.


PostgreSQL postmaster will not start after configuring for SymmetricDS

I must be doing something wrong, so what is it?

Per the 9.2 release notes, custom_variable_classes was removed. It is no longer required or supported. You can just leave it out without problems.


The short answer is yes. You can read more about the Merge command syntax at the Microsoft MSDN site.


Yes, that's perfectly possible.
The main benefit of a Recovery Catalog is when restoring / recovering a database in place.
All the information needed to do the recovery is mastered in the Control Files so if you recover a database "over" itself, then the information needed to do that recovery is also overwritten.  You get "one go" at doing the recovery and that's it.
So, if the Users look at the recovered data and say ...

Oh dear.  Can we go back another hour?

... then the answer is, very simply:

No.

A Recovery Catalog duplicates the information from the Control files, so you can have as many "goes" at an "in-place" recovery as you like.
When duplicating the database onto a new machine, there is no "overwriting" issue so yes, you can do this and, as long as you're prepared to trash the database and its files in between attempts, you can do so as often as you like.


Once you've installed the Database Instance onto your other PC:

Use SQL Developer's "Database Copy" to copy straight from one to the other, or "Database Export" to simply export the source database into file(s).
They're available in the "Tools" menu.

Both instances should be running if you choose "Database Copy."

If you choose "Database Export," you can choose to have it all go to one or the other.

Beware of disk space and wallet issues, and ensure your other instance's environment is ready to take the new structure/files.


Like I commented, this would be more efficient with a normalized DB layout, with a table like this

CREATE TABLE task_packets (
  task_id int PRIMARY KEY
, state text NOT NULL
-- or: state_id int NOT NULL REFERENCES state(state_id) ...
);

Among other things, we can have a PK constraint enforcing unique task_id numbers. And the UPDATE you want is trivial.

But to answer the question asked:

To SELECT:

SELECT *
FROM   json_test jt
     , LATERAL (
   SELECT jsonb_set(filter_data
                  , '{task_packets}'
                  , jsonb_agg(CASE WHEN elem->>'task_id' = '1001'
                               THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                               ELSE elem
                             END)) AS filter_data_new
   FROM   jsonb_array_elements(filter_data->'task_packets') elem
   ) tp
WHERE  jt.filter_data @> '{"task_packets": [{"task_id": 1001}]}';

I suggest a LATERAL join, among other things to exclude the possibility of multiple matching rows that might be lumped together incorrectly in a plain join.

To UPDATE:

UPDATE json_test
SET    filter_data =
   (
   SELECT jsonb_set(filter_data
                  , '{task_packets}'
                  , jsonb_agg(CASE WHEN elem->>'task_id' = '1001'
                                THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                                ELSE elem
                              END))
   FROM   jsonb_array_elements(filter_data->'task_packets') elem
   )
WHERE  filter_data @> '{"task_packets": [{"task_id": 1001}]}';

The same can be implemented with a correlated subquery in the UPDATE (or in the SELECT as well).

To make this fast for big tables, be sure to have an appropriate index, ideally a jsonb_path_ops index:

What's the proper index for querying structures in arrays in Postgres jsonb?


How to use insert and select simultaneously?

So is it possible to do insert and select simultaneously?

Yes, you can use the output clause of the insert statement.

insert into dbo.Test(age)
output inserted.ID
values(13);


Inconsistent behavior of aggregate functions about whether to return NULL on empty input?

I can't speak from any experience but SQL Server, but I'll assume that this applies to most/all SQL language implementations:

COUNT(*) is counting rows. If there are zero rows to count, zero is the response.

Most other aggregate functions depend upon values contained in fields (MIN, MAX, SUM, etc). If there are no values to aggregate (either because no records are in the dataset or only NULL values in the field(s) involved), NULL is the response.


It must be as quick if not quicker to do:

ALTER TABLE mydb.datafulltxt DISABLE KEYS;
Do many INSERTs into mydb.datafulltxt
ALTER TABLE mydb.datafulltxt ENABLE KEYS;
Repeat as required.


T-SQL: Convert XML to string without self-closing tags

A workaround would be to add an unlikely value to your empty nodes using insert (XML DML) and then replace the value on your string representation.

declare @S nvarchar(max)

declare @XML xml = '
<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE />
</BOOKING>'

-- Add _ as value to all empty nodes
while @XML.exist('//*[.="" and count(*)=0]') = 1
  set @XML.modify('insert text{"_"} into (//*[.="" and count(*)= 0])[1]')

-- Cast to string and remove _ as value
set @S = replace(cast(@XML as nvarchar(max)), '>_<', '><')

select @S


dbcc failing with Cannot continue the execution because... alwayson having a change of state event for each db

I have experienced this behavior before, where the AGs "blip:"

from PRIMARY to RESOLVING and back to PRIMARY
from SECONDARY to SECONDARY

It's my understanding that, on a system with a small number of logical processors (1-4), and with multiple AGs (or AGs with many databases), some of these "blips" are unavoidable.  Especially if the server is a VM with even slightly noisy neighbors.

Availability Groups use a lot of worker threads baseline.  You can read about the thread requirements for AGs in detail here: Thread Usage by Availability Groups

I'd suggest that you avoid having other CPU-heavy scheduled tasks run at the same time, in order to reduce the risk of these events occurring.  In this case, it looks like index maintenance was running at the same time as CHECKDB.  If you have a big enough window, try to spread these out so they don't overlap.


Setting password for 9.3 PostgreSQL server

If your pg_hba.conf is set correctly and you're sure about your password, and you're still getting issues related to "peer authentication" failed, you probably need to reload your postgresql instance

/etc/init.d/postgresql reload 9.3

should reload your settings and allow you to use the password. Hope that helps. =)


What kind of checks should I perform while dealing with stored procedures?

You should perform checks to guarantee code correctness, follow the coding standards of your organization, and to make your code understandable to other programmers. I will give you my initial thoughts on your code as an outsider who doesn't know anything about your application. Starting with this one:

CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin

IF (SELECT is_activated from USERS where mat_emp = @mat_emp) = 1
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end
else
print 1
end

What happens if the value of is_activated changes after your SELECT query is run but before your UPDATE query runs? The code pattern that you have here isn't safe under concurrency. It's also not clear what print 1 means here and if it's okay to mix that information with information about row counts (SET NOCOUNT ON isn't present). Perhaps you're looking for an output parameter?

For this code:

CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end

In most code around locking I see some kind of error handling or information returned to the caller if a lock can't be taken. For example, if you're taking a lock in order to perform some type of operation that requires exclusive access to the user then allowing many concurrent processes to take locks could cause data integrity issues. Does the caller need to know if a row exists for the @mat_emp input parameter? Should the stored procedure time out if it can't get a lock quickly enough on the USERS table?

It's true that updating a column in a row to the same value does have some subtle effects, but I think that's the wrong way to look at it except for extremely high performance applications. If you want to develop a habit I think that it's best to avoid non-updating updates (when practical) just to avoid surprises. However, the code (including comments) of the stored procedure needs to make the expected outcome of the operation clear. As someone without any knowledge of your application, I can't say if either approach is safe.


'Invalid utf8 character string' warning when importing into AWS RDS MySQL

Judging by your initial post, where you said you used default options for mysqldump, it means you didn't use the --hex-blob option. I've played around with osTicket some time ago, and you should definitely used --hex-blob option to create your dump. It will give you much bigger .sql file (and of course, the import to RDS will take more time) but in that way you won't get the UTF8 warnings you're getting now, and your attachments will be available once you deploy osTicket to new host.

One more advice, update to newer versions (1.9 or 1.10) and move those files out of DB, you can use S3 or even EFS now, or just store it under your EBS volume


Problem with new MySQL Workbench connection

Since you get an "access denied" the actual connection to the server worked. You just have used a user that is not allowed to connect either because it connects from a machine that is not enabled in the user settings and/or the user does not exist or the password is wrong.

Is it the correct server you connected to?
Are you 100% sure to have used the right user/pw?
Is the IP address of the user allowed to connect at all?


SQL 2008: Why is *any* user, with minimal permissions able to restart/stop the SQL instance through Management Studio

Correct Answer:
It looks like SSMS looks at the context of the user who opens up SSMS itself, and not the user who connects to the DB engine.

I was able to log in to MS SQL Server as a SQL Server user and I was still able to restart the service under these 2 conditions:

Logged in directly into the server via RDP as a user with admin or start/stop services rights, opened up SSMS, and was able to restart it
Opened up SSMS across a different domain impersonating a domain user which has admin rights on that box using /runas netuse.

Original Answer (Partially Correct):
It seems you might be confusing "MS SQL Server" permissions and "Windows" permissions. The MS SQL Server user permissions don't control access to Windows Services, a MS Windows domain or local user server controls who can or cannot restart the services.  You can modify these settings through secpol.msc.

If you log in to MS SQL Server as a SQL Server user (even sa or a sa equivalent user) the 'restart' option is greyed out.  If you log in to it as a "Windows User" who also has admin or restart services to the Windows machine, then you can restart it.


The unique identifier for Manager would be the personnel id.  That identifier would migrate to Team as a column and thus refer back to Manager.  This will ensure each Team can be managed by one and only one Manager.  This constraint would be a Foreign Key constraint.  The constraint on Manager, which specifies the personnel id as the key, would be called a Primary Key constraint.


Secondary nodes in mysql 8.0.15 innodb cluster are not reading group_replication_member_expel_timeout variable from my.cnf

When using the MySQL Shell and the AdminAPI to set up and manage MySQL InnoDB clusters there's no need, and it's not recommended, to manually change MySQL Server system variables and other configurations. The AdminAPI provides the functionalities to do any required configuration changes.

Regarding your need to change the value of group_replication_member_expel_timeout, what you need to do is as simple as:

cluster.setOption("expelTimeout", 60)

This command ensures the option is set on all the cluster members.

To verify the options being used by your cluster, you can use the following function:

cluster.options()

For more information please consult the InnoDB cluster userguide:

https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-working-with-cluster.html#mysql-innodb-cluster-setting-options

And for some examples and further details you can check the blog posts series published during the 8.0.14 release:

https://mysqlserverteam.com/mysql-innodb-cluster-whats-new-in-the-8-0-14-ga-release/

The series includes posts explaining the usage of the 'expelTimeout' option and also how to change cluster options 'live'.

Cheers,

Miguel


how to select top ID from join operation and staging tables

Window Functions, or the OVER() Clause

It appears that what you are looking for is a window function or an OVER() clause.

In your original example, you are trying to use two max() conditions, which doesn't work, because when you try to then apply GROUP BY, you can have a condition where the max of the first column ID and the second column startdate aren't in the same row, and so then GROUP BY simply can't be understood.

However, if you are looking to extract the max over a grouping, and you can define that grouping, and you want to obtain the 'column-wise' maximum for more than one column for that grouping (as you seem to want to do), then here's the solution.

CREATE TABLE test (
  jobcode integer,
  id integer,
  a_startdate datetime,
  a_enddate datetime,
  b_startdate datetime,
  b_enddate datetime);

  INSERT INTO test VALUES (513801, 7136, '11-01-2011', '12-31-9998', '11-01-2011', '12-31-9998');
  INSERT INTO test VALUES (513801, 7137,'04-26-2014', '12-31-9998','04-26-2014', '12-31-9998');

I first made this table to recreate your input data in the picture.  I then applied this query.

SELECT jobcode, max(id) OVER (PARTITION BY jobcode),
max(a_startdate) OVER(PARTITION BY jobcode),
a_enddate,  b_startdate, b_enddate FROM test;

Try out this SQL Fiddle, and see if it gives the results you're looking for.  I did the best I could with the description I had.


Dealing with different types in a fact table

I would steer clear of the 3 option. It's called a snowflake instead of a star schema. It's a rather advanced solution which can be used when necessary but has it's own drawbacks. As usual refer to Kimball when looking for datawarehouse design tips.

This is what they say in Snowflakes, Outriggers, and Bridges:

  We generally encourage you to handle many-to-one hierarchical
  relationships in a single dimension table rather than snowflaking.
  Snowflakes may appear optimal to an experienced OLTP data modeler, but
  they’re suboptimal for DW/BI query performance. The linked snowflaked
  tables create complexity and confusion for users directly exposed to
  the table structures; even if users are buffered from the tables,
  snowflaking increases complexity

When it comes to 1 or 2, I would say it's up to the reporting requirements and how well the dimension members (the records in the dimension) should be seen as one "axis" to report against. There can be benefits to combining multiple types of records in a single dimension (such as defining hierarchies and attribute relationships to improve performance) but in this case I would lean towards seperate dimensions for different entities.
Again, Kimball explains this in the basics of dimensional modeling but they have more articles on the subject.

  Rule #1: Load detailed atomic data into dimensional structures.

  Rule #2: Structure dimensional models around business processes.

  Rule #8: Make certain that dimension tables use a surrogate key.

When it comes to NULL values in your fact table foreign keys, those should be solved by using surrogate keys which you should use anyway.

I answered a related question here but it basically comes down again to Selecting Default Values for Nulls which explains how you should handle missing data in your dimensions.

  The first scenario where nulls should be avoided is when we encounter
  a null value as a foreign key for a fact table row during the ETL
  process. We must do something in this case because an actual null
  value in a foreign key field of a fact table will violate referential
  integrity;

In some way you can interpret that as "NULLS are a no-no" but it actually means "insert a dummy record such as N/A in your dimension, and use the surrogate key of that record in your fact tables foreign key field"


How to execute SQL query from two database

As @Akina mentioned linked server might be a way to go. if you create linked server with name RemoteServer your query may look like:

  Select * from myLocalDatabase.dbo.myLocalTable  mlt
    join RemoteServer.RemoteDatabase.dbo.myRemoteTable mrt
    on mlt.col1 = mrt.col1

Be aware of performance penalty when running remote joins.
in certain cases you might be better importing data to local server and then joining it especially if this is going to repeat more often.

As for creation of linked server you could try this to add another SQL Server

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerDescription', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'RemoteServername_or_IP', @catalog=N'databasename'

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerDescription', @locallogin = NULL , @useself = N'True'
GO


Community Wiki answer originally left as question comments

You need:

EXEC sp_executesql
    @sql,
    N'@value1 varchar(100) OUTPUT',
    @value1=@value1 OUTPUT;

An online demonstration of the technique: http://rextester.com/GDL49591


I'm not sure of the limitations of transactional replication, but with BCP you can create a view on the table that contains only columns that exist in the BCP file, and load into that view rather than the table. How do you define the data for the publisher column at the moment?


Live by the cross apply, die by the cross apply

 CREATE TABLE #TableTest
 (
   dealerID INT,
   itemname VARCHAR(100),
   itemonhand INT,
   itemname2 VARCHAR(100),
   itemonhand2 INT,
   itemname3 VARCHAR(100),
   itemonhand3 INT
 );
 INSERT INTO #TableTest
        ( dealerID, itemname, itemonhand, itemname2, itemonhand2, itemname3, itemonhand3 )
 VALUES ( 1, 'ball', 0, 'hat', 1, 'sock', 0 )
  ,     ( 2, 'ball', 0, 'hat', 0, 'sock', 1 );

 SELECT tt.dealerID,
        ca.*
 FROM   #TableTest AS tt
 CROSS APPLY ( SELECT   *
               FROM     ( VALUES ( itemname, itemonhand), ( itemname2, itemonhand2), ( itemname3, itemonhand3) ) x ( item_name, item_count ) ) ca
 WHERE  ca.item_count = 1;


Storing math,physics equations in mysql Database

Do you have some way of entering utf16?  I don't know of such, except internally in Java.  The world is moving toward utf8, not utf16.

I recommend using utf8mb4 throughout.  The collation may not matter; the default is utf8mb4_general_ci, but _bin or _unicode_ci are also possibilities.

A little history lesson on why I suggest utf8mb4 instead of utf8.  More than a decade ago, MySQL added utf8, but coded it only to the extent of the 3-byte encodings.  Recently, a number of 'new' Chinese characters have come out that need a 4-byte encoding.  And there are other sets that need 4 bytes.  MySQL realized that expanding its utf8 to 4 bytes would be far too incompatible, so it added utf8mb4 (think "MultiByte4").  A lot more info on utf8, etc:  http://mysql.rjweb.org/doc.php/charcoll


It took me a while to figure this one out.

        SELECT  t1.id      + 1 AS `start`,
                MIN(t3.id) - 1 AS `stop`
          FROM (SELECT  table1.id
                  FROM (SELECT treeleft  AS id FROM structure
                        UNION ALL
                        SELECT treeright AS id FROM structure
                       )
                    AS  table1)
             AS t1
LEFT OUTER JOIN (SELECT  table2.id
                   FROM (SELECT treeleft  AS id FROM structure
                         UNION ALL
                         SELECT treeright AS id FROM structure
                        )
                     AS  table2)
             AS  t2
             ON  t1.id = t2.id - 1
LEFT OUTER JOIN (SELECT  table3.id
                   FROM (SELECT treeleft  AS id FROM structure
                         UNION ALL
                         SELECT treeright AS id FROM structure
                        )
                     AS  table3)
             AS  t3
             ON  t1.id < t3.id
          WHERE  t2.id IS NULL
            AND  t3.id IS NOT NULL
       GROUP BY  t1.id,
                 t2.id;


Will DB session terminate when client close DB connection

If the DB connection is closed the session will get an error the next time it attempts to query the database. But will work on subsequent tries as it establishes a new connection.
I believe this answer varies by RDBMS, but to answer you question, I believe the general answer is no. You can however manually kill the session, which should release the locks.
You should commit or rollback your transactions, you should not leave them open.


Invoices kept in document with user information in no sql database is recommended?

Almost always with databases, it depends on what you're going to do with the data after you're stored it.

How are you going to retrieve and process it?

If you only ever access this data by Tenant, then carrying a block of "stuff" around with each Tenant "document" is probably OK.
If you wanted to start querying and comparing bills across your tenants, though, this structure might be inefficient.

  ... won't do any computations on bills ...

Do you not have to summarise your rental income for your annual Tax Return?

That would mean pulling out any Current or Previous "rent" entries that fall within the last Tax Year - I suggest that might get just a bit fiddly with the data structure you have (and, although you've not shown any, the Date formats that you're using).


different log file for master and beta mysql

I see one solution to this situation is to have multiple commit of the same file.

let say 20140926.sql file  in this case, Now you have two changes one should go to Beta server and one to Master server.

You can have two seperate commit, changes that suppose to go to Beta in one commit and changes that is suppose to Master in other commit, with proper comment to indentify and versions incrementing count too can help.

Now while merge in Beta branch you can get the only version of commited for it and in Master the other corresponding version commited for it.


Using Two LEFT JOIN or use AND with single LEFT JOIN?

They are different. In the first option you get 2 times Table2 into your query. Once as t2 and once as t3. Both have a different content and you must put them somehow back together. To me this is more an OR instead of an AND. In the second option you get only the Table2 rows that meet both criteria.

Suppose you have Table2 with the following content:

| Color | Size |
|-------|------|
| Red   |  S   |
| Blue  |  S   |
| Blue  |  XS  |

Suppose you want to have the rows that are Blue and S. In your first option you get all rows (t2 for example with all Blue and t3 with all S) and in your second option you only get row 2.


How can I use a MySQL query in an openquery query on Microsoft SQL Server.?

According to the OPENQUERY syntax you'd have to rewrite the script in the following way:

DELETE OPENQUERY (MySQLServer, '
    SELECT sb.*
    FROM stockbatches sb
    INNER JOIN batchestodelete btd
    ON sb.batch_batch = btd.batchno
    AND sb.batch_part = btd.batchpart;
');


For MySQL, how to INSERT the result of a SELECT statement?

The basic syntax for this generally involves inserting into one table by selecting from another, which would look something like:

INSERT INTO t1 (a1, b1)
     SELECT a2, b2
       FROM t2
      WHERE a2 > 0
            AND
            b2 > 0
;

There is some flexibility in that, although perhaps not as much as you are looking for. It might be worth looking at the mysql docs for insert-select for more information.


SQL import data when both source and destination has data and identity column

Don't just use the whole table. Instead choose the option to write a query.

Then simply write a select statement that doesn't use that Identity Column.

Alternatively, when you map columns, if you click "Delete rows in destination table", it will truncate the target table before loading data. If the opportunity exists for data to be removed from the source system and it should have remained in the destination, then this step will not be right for you.

Your package would look something like this


mysqldump: Got error: 1066: Not unique table/alias

A common case for this error is when the same table appears more than one in the mysqldump list of tables:

mysqldump [options] TableFoo  ... TableFoo ...


It is unclear if the commercial version you mention is a trial or a purchased version of MySQL Enterprise. You should get Oracle credentials for download in the first case associated with your license.

In both cases, you have here a link to both the trial and licensed versions of MySQL Enterprise Edition (including Enterprise Backup):

https://www.mysql.com/trials/


Search for mongod.lock and remove it and restart the service.

Find out the Mongo data path cat /etc/mongod.conf | grep dbPath
Look for mongod.lock file in it.
If its there then delete the file and restart the server.


Is it possible to limit connecting users to a schema from specific terminal or program?

I am not aware of something like that native in the database. However, it should be doable using a logon trigger.

Regards,
Daniel


It's very easy to script out SQL Server Agent Jobs using Powershell (if that is an option).  The following is an example of how you can use Powershell to script the Agent Jobs and also delete previous scripted jobs that were created over a number of days ago.  This script generates a file per Agent Job

You can play around with the code to see if it meets your needs.

# Set sql server
$Instances = "localhost"

foreach($Instance in $Instances)
    {
    # Create sql server and db objects
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Instance;

    # Write out the job names
    #$srv.JobServer.jobs|foreach-Object {Write-Host "$_.name" }

    # Script out the Jobs
    $CurrentDate = get-date -f "yyyy-MM-dd"
    $Instance = $Instance.Replace("\","-") + "-" + $CurrentDate
    $srv.JobServer.jobs|foreach-Object {$_.script() > "C:\Temp\SqlJobs\$Instance-$_.txt"}
    }

# Purge files older than 2 weeks
$cutoff = (get-date).AddDays(-14)
dir "C:\Temp\SqlJobs\"|Get-ChildItem -incl '*.txt' -rec|Where{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer}|remove-item