Oracle: summing column value with subquery? Are there some restrictions in Oracle that disallows to have a WHERE clause that compares value of column+(subquery count(*))? If the subquery table is empty, count(*) doesn't yield to zero in this scenario.

I expect this query

select * from Foo where (x+(select count(*) from SomeEmptyTable))>0

To be identical to

select * from Foo where x>0

But instead Oracle doesn't return any results with the first query. In MySql the both queries return the same result selt.

My test SQL snippets:

create table Foo ( x int );

insert into Foo values ( 1 );

create table SomeEmptyTable ( x int );

I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

UPDATE:

Or does the comparison to zero have some special meaning in this case?

Both

select * from Foo where (x+(select count(*) from SomeEmptyTable))>=0

and

select * from Foo where (x+(select count(*) from SomeEmptyTable))>-1

Returns 1 row as would be expected. Only when compared to >0 zero rows are returned.

You're unfortunately hitting bug 12999577 (Oracle support account required) or something close to it. And the fix for that bug introduced another bug 17564992.

The workaround indicated in the first bug report is to switch the conditions around, i.e. express your where clause as 0 < ( ... ).

The workaround indicated in the second is to set either of these:

alter session set "_fix_control" = '12999577:0';
-- or
alter session set "_optimizer_squ_bottomup"= FALSE;

I've tried your example on an 12.1.0.2 database that reproduces the behavior you are seeing (including returning the expected result for the >=0 and >-1 cases). The workarounds suggested for that second bug appear to work and give the expected result. But do verify the bugs yourself, and seriously consider applying the bugfix rather than these parameters. I'd suggest opening an SR with Oracle if you want confirmation for your particular version.

Interesting difference in the explain plans. With the _fix_control (correct result):

--------------------------------------------------------------------------------
| Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    13 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | FOO      |     1 |    13 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |          |     1 |       |        |          |
|   3 |    TABLE ACCESS FULL| EMPTYTAB |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("F"."X"+ (SELECT COUNT(*) FROM "EMPTYTAB" "EMPTYTAB")>0)

Without the _fix_control (no rows returned):

-------------------------------------------------------------------------------
| Id  | Operation      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    82 |  1066 |     4   (0)| 00:00:01 |
|*  1 |  FILTER        |          |       |       |        |          |
|   2 |   TABLE ACCESS FULL| FOO      |    82 |  1066 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPTYTAB |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "EMPTYTAB" "EMPTYTAB"))

Plan is completely different, the filtering clause is interesting.


Is filling up plan cache causes a decrease in space allocated for data cache? SQL Server uses allocated server memory for different kind of purposes. Two of them are plan cache and data cache which are used to store execution plans and actual data correspondingly.

My question: Do these two caches have different allocated space section in Buffer pool, or in contrary, they have just one section in Buffer pool which they share between each other?

In other words, if plan cache is filling up, does space for data cache is reducing as well?

Yes.  The Plan Cache and Page Cache share the available server memory.  The Plan Cache is capped, as growing a large plan cache rarely provides much marginal benefit.

AFAIK, this old doc page is still accurate:

  Memory Allocated To Plan Caching

  Most memory used by SQL Server is allocated to the Buffer Pool, which
  is used to store data pages. SQL Server steals a proportion of this
  memory for use in caching query plans.

Cache Pressure Limit SQL Server 2008 and  SQL Server 2005 SP2 [and later]

  75% of visible target memory from 0-4GB + 10% of visible target memory
  from 4Gb-64GB + 5% of visible target memory > 64GB

Which means that the plan cache will be trimmed when it exceeds that target, effectively capping the amount of memory used for plan caching.

Plan Caching in SQL Server 2008


PERCENT_RANK does not distribute over 100 I'm trying to find out the position of each given record of a query in 0-100 scale. I use PERCENT_RANK ranking function this way:

select Term, Frequency, percent_rank() over (order by Frequency desc) * 100
from Words

But when I look at the results, instead of seeing a column that starts from 0 and goes up to 100, I see a column that starts from 0 and goes up to 37.xxxx.

Though BOL does not explicitly mention that the result is distributed over 0-100 scale, my understanding from the word percent made me use this ranking function.

What do I miss here?


How to import Timestamp from a CSV file I am trying to Import a CSV file with a column called timestamp to MySQl to a column with the structure timestamp but all i get are NULLS in the columns is there a way that i can import the timestamp as the structure to my new table.

Transaction Timestamp,Batch Ref No,Transaction Type,Group Transaction No,Receipt No,Transaction No,Transaction Amount,Actual Cost,CErr,Resource Units,Resource Units Error,Operator Name,Account Number,Debt Reference No,Meter Serial No,Token,Comment,Station,Database Name,Resource,Tax Item Name,Fixed Charge Item Category,Debt Category,Utility Name,Vend Reason
6/1/2012 0:09,SH/EVG4/1108,Tax Transaction,EVG400021324,,EVG400050154,2761.54,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,VAT + EWURA + REA,,,Tanesco,
6/1/2012 0:09,SH/EVG4/1108,Fixed Cost Transaction,EVG400021324,,EVG400050155,3841,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,,Basic Support Charge,,Tanesco,
6/1/2012 0:09,SH/EVG4/1108,Tax Transaction,EVG400021324,,EVG400050156,845.02,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,VAT + EWURA + REA,Basic Support Charge,,Tanesco,

that is a sample of the CSV file that am trying to import everything else imports well except the timestamp, my structure is so that the timestamp structure is set to "timestamp"

You get NULL values probably because MySQL cannot determine automatically that first column in you CSV is a timestamp.
You could try to change table structure so first column becomes a VARCHAR, then import data, then transform dates using mysql date functions (change date to mysql standard format 'Y-M-D H:i:s'), then convert back this column to timestamp.

Alternatively, you could use LOAD DATA INFILE to insert CSV data directly, but for first column use SET directive in LOAD DATA INFILE to change first column data to MySQL standard format. Example:

LOAD DATA INFILE youfile.csv
  INTO TABLE yourtable
  FIELDS TERMINATED BY ","
  LINES TERMINATED BY "\n"
  (@var1, col_name_2, ...) SET col_name_1 = STR_TO_DATE(@var1,'%m/%d/%Y %k:%i');

This converts date to MySQL format while importing data.


why my MySQL binary log file size is so small? I have a lot of small binary log files , each of them just only 126 Bytes

-rw-rw----. 1 mysql mysql  126 Jan  6 16:13 mysql-bin.000001
-rw-rw----. 1 mysql mysql  126 Jan  6 16:16 mysql-bin.000002
-rw-rw----. 1 mysql mysql  150 Jan  7 10:34 mysql-bin.000003
-rw-rw----. 1 mysql mysql  126 Jan  8 09:57 mysql-bin.000004
-rw-rw----. 1 mysql mysql  126 Jan  8 11:08 mysql-bin.000005
-rw-rw----. 1 mysql mysql  107 Jan  8 11:09 mysql-bin.000006
-rw-rw----. 1 mysql mysql  126 Jan  8 13:21 mysql-bin.000007
-rw-rw----. 1 mysql mysql  342 Jan  8 16:25 mysql-bin.index

how to let them grow bigger? and my "binlog_cache_size" is more than 126 Byte

mysql> show variables like 'binlog_cache_size';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)

can anybody tell me why(⊙ˍ⊙) ? thank you very much :)

mysql logging

Is your MySQL Server restarting?  Check your uptime:

SHOW GLOBAL STATUS LIKE 'uptime';

The binary log gets closed at shutdown, and a new binary log created at startup.  The fact that you get "MySQL server has gone away" while querying above might also suggest the server restarted.


How do I render GeoHashes with PostgreSQL? Are there any geohash implementation for PostgreSQL? I couldn't find any.

PostGIS supports it with ST_GeoHash

  ST_GeoHash — Return a GeoHash representation (geohash.org) of the geometry.


Error installing SQL Server 2008 - all componets installed successfully except database engine Below is the error I copied, please someone help me, I have tried at least 10 times in the last 3 days. It is SQL Server 2008 R2 and I wanted to install it on Windows 7.

Microsoft SQL Server 2008 Setup
The following error has occurred:
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.0.1600.22&EvtType=0xE53883A0%25400xBE03358B%25401306%254024

Make sure the account doing the installation is a member of the following groups in local security policy:

Adjust Memory quotas for a process
Bypass Traverse checking
Log on as a batch job
Log on as a service
Replace a process level token
Backup files and directories
Debug programs
Manage auditing and security log

Additional Items to check:
Make sure the account doing the install is a local admin on the server, make sure you are not installing to a compressed drive or folder, make sure you are installing the right version, IE 32 or 64 bit based on your OS


Why are my queries in idle state? I'm new to postgres , I've aws rds instance running postgresql with engine version 11.5.
All my queries are clientRead has the wait_event.  Why do I have all my queries in idle status.Does this mean they are idle in transaction?
What are the steps should I take to fix this?
If I change to idle_in_transaction_session_timeout to 10 mins for example will it fix this?
select count(*),state FROM pg_stat_activity group by 2;
 count | state
-------+--------
     5 |
     1 | active
   451 | idle

Select pid, datname, usename, wait_event_type, wait_event, backend_type FROM pg_stat_activity where state='idle';
  pid  | datname  |         usename          | wait_event_type | wait_event |  backend_type
-------+----------+--------------------------+-----------------+------------+----------------
 14797 | xxxxx    | user                     | Client          | ClientRead | client backend

SELECT current_setting('idle_in_transaction_session_timeout');
 current_setting
-----------------
 1d
(1 row)

Your session is in idle state, not the query.
Sessions that are idle are not a problem. It simply means the backend is waiting for the client to send a SQL query.
The number of sessions is a little bit on the high side though. You will need to have a really powerful server to cope with that, once all of them start doing something.
Sessions that are idle in transaction would be a problem if they stayed in that state for too long. That's what the idle_in_transaction_session_timeout handles, not "idle" sessions.


SQL Server Reorganize Index Job I have an index reorganize job on SQL Server 2012 that fails because page level locking is disabled on it.  My three questions are:

Why would page level locking be disabled on an Index?

 If I go ahead and make changes and allow page level locking, it is not going to break anything, right?

Is there a script to make all the indexes allow page level locking?

Why would page level locking be disabled on an Index?

By default, [allow_page_locks] is set to 1, so, someone must have changed it in the past to avoid that level of locking.

  If I go ahead and make changes and allow page level locking, it is not going to break anything, right?

I would test this in a non-prod environment prior to changing.  I would think the outcome would be unexpected locking.

  Is there a script?

You could use this script to identify those indexes and then change it to modify those.  But, I would think there was probably a good reason for the change in the first place.

Select Indx.name,
    Indx.type_desc,
    Indx.is_disabled,
    Indx.allow_page_locks,
    Indx.allow_row_locks
from sys.indexes Indx
left outer join sys.objects OBJ
    on OBJ.object_id=Indx.Object_id
where allow_page_locks = 0


How to do a Join with optionally matching columns I have two tables as follows (in a Postgres 10 database):

Maize:

id|acreage|date      |region
1 |20     |2018-10-18|A
2 |10     |2018-10-07|A
3 |26     |2018-10-18|B
4 |06     |2018-10-07|B

Paddy:

id|acreage|date      |region
1 |17     |2018-10-18|A
2 |29     |2018-10-09|A
3 |24     |2018-10-18|B
4 |09     |2018-10-09|B

I want to get the following result:

Maize|Paddy|date      |Region
20   |17   |2018-10-18|A
Null |29   |2018-10-09|A
10   |Null |2018-10-07|A

Do note that when the dates do not match, I would like to get a Null in the cell.

I've been trying to write a query like this, but I only get the first row, and not the 2nd & 3rd Rows:

Select c1.acreage as "Maize", c2.acreage as "Paddy", c1.date, c1.region FROM
Maize c1
FULL OUTER JOIN Paddy c2 ON c1.date =c2.date AND c1.region=c2.region
order by c1.date;

Obviously the date in the Join is causing the issue; But I don't know how to remove it, and still get the Matching values in one row.

SELECT c1.acreage Maize,
       c2.acreage Paddy,
       COALESCE(c1."date",c2."date") "date",
       COALESCE(c1.region, c2.region) region
FROM Maize c1
FULL OUTER JOIN Paddy c2
    ON c1."date"=c2."date" AND c1.region=c2.region
ORDER BY "date";

fiddle


One table for all persons i have one table for all person (with fields ex. name, last name) because each person can has one or more role (customer, lead/prospect, supplier, employee).
And i have tables (customers, leads, suplliers, employees) for each role with custom fields.
Also I have a roles table and a table for person and his roles.

Questions:

Is correct save leads/prospects in the same person table? Because i can get thousands of leads. But this can down sql query performance?

Other question, some person (customer and employee) can be a user (can be login). Then i create other table named users, but the email is saved in person table. I can use a username for login but i want too the users can login with email.

What do i?
I use only use username (in user table), other option copy email (from person table) to user table or for last option a query using the two tables: person and user?

Thanks

You must divide what value is an attribute for what entity.

For example, Name is an attribute of a Person, so it is stored in Person table, and when you need the Name for some Employee you'll join these tables and get the Name by reference (i.e. there is no Name field in Employee table). But when you need Employee position you will take (and store) it from Employee table because the position is not an attribute of Person, but Employee.

  some person (customer and employee) can be a user (can be login)

The same.

If the login does not depend on whether the Person is an Employee or a Customer (i.e. after logging in the Person may perform actions which belongs to both/either as Employee and/or as Customer), it is an attribute of Person and is stored in the Person table.

But if a certain login is the login of the Employee only (can perform Employee actions, but cannot be used by the Customer and perform Customer actions) - it is not an attribute of Person, but Employee, and is stored in Employee table.


return subquery value into main query I have the following query:

SELECT *
FROM item_ledger
WHERE to_id='7'
AND (SELECT SUM(qty) AS qty_in
     FROM item_ledger
     WHERE from_item_ledger_id='5') < intransit_qty

the query works as expected. From that query, I also want to return qty_in to the main query. How to do that?

The subquery is uncorrelated, so you can simply make it a derived table (moving it to the FORM clause) or make it a CTE:

-- derived table
SELECT il.*, q.qty_in
FROM item_ledger AS il
  CROSS JOIN
    (SELECT SUM(qty) AS qty_in
     FROM item_ledger
     WHERE from_item_ledger_id = '5') AS q
WHERE il.to_id = '7'
  AND q.qty_in < il.intransit_qty ;

-- common table expression
WITH q AS
    (SELECT SUM(qty) AS qty_in
     FROM item_ledger
     WHERE from_item_ledger_id = '5')
SELECT il.*, q.qty_in
FROM item_ledger AS il
  CROSS JOIN q
WHERE il.to_id = '7'
  AND q.qty_in < il.intransit_qty ;

You could also use an inline subquery or a LATERAL join (available in 9.3+ versions) but those solutions would be more useful if the subquery were correlated.


What is going on with my user and max date query? OK query below.   Hitting about a half million rows and I am trying to grab the last time a user (uid) touched a page on our system denoted by LogDate.   I have used this query quite a few times but applying it to a bigger table seems to cause issues.   Let me review.

Keeping query is I can only grab about 8k rows or my query will just lock up - not getting much in logs.
If I limit to 1000 it takes 2 seconds.   If I change it to 2k it takes 77 seconds.   8k takes about 88 seconds.   After that... nothing.
If I take out the Distinct part it is obviously faster.   However I still can't grab the rows I need so it doesn't matter.    I have tried adjusting Innob memory settings which seems to have zero effect.

SELECT DISTINCT
  `t1`.`uid`,
  `t1`.`LogDate` AS `touchdate`
FROM
  (
      `ref_source_page_log_report` `t1`
      LEFT JOIN `ref_source_page_log_report` `t2` ON (
          (
              (`t1`.`uid` = `t2`.`uid`)
              AND (
                  (
                      `t1`.`LogDate` < `t2`.`LogDate`
                  )
                  OR (
                      (
                          `t1`.`LogDate` = `t2`.`LogDate`
                      )
                      AND (`t1`.`uid` < `t2`.`uid`)
                  )
              )
          )
      )
  )
WHERE
  isnull(`t2`.`uid`)
LIMIT 10000

I've tried to rid of the excessive parentheses and backticks so I've got the next query:

  SELECT
    t1.uid,
    t1.LogDate AS touchdate
  FROM      ref_source_page_log_report AS t1
  LEFT JOIN ref_source_page_log_report AS t2
         ON t1.uid = t2.uid
        AND ( t1.LogDate < t2.LogDate OR
            ( t1.LogDate = t2.LogDate AND t1.id < t2.id ))
                 -- Here was the problem: ^^^^^^^^^^^^^ (uids instead of ids)
  WHERE t2.uid IS NULL
  LIMIT 10000


How to prevent parallel running of SQL Agent jobs What would be good ways to design a group of jobs such that they can never run at the same time?
I am looking for a generic concept, like a semaphore, which makes it easier to ensure that some jobs are never running at the same time.
For example, I'm thinking of a "signal table". When a job starts, it sets the signal and at the end, it resets the signal. In addition, every job needs to wait until the signal is set. (How to implement a CPU friendly waiting?) However, when a job crashes, there must then be a solution to automatically reset the signal, otherwise jobs would never start.
We have 4 jobs which are doing various updates, imports, calculations. They all are running by scheduler, and in addition, some of them can also be started manually by super users in case a special run is needed. However, they should not run at the same time, because it often results then in locks or even deadlocks and the database does not properly respond to the users then.
EDIT: It's a data warehouse which is gathering and consolidating data from multiple external data sources. Every job is responsible for another data source, like this:

job #1 is running every 15 minutes
job #2 every 3 hours
job #3 and Job #4 run once per day or have extra runs when started by super users.

Aside from the comments, here's something you can use to accomplish what you're trying to do (since I'm not a fan of reinventing the wheel for locking systems).
You can use the system stored procedure msdb.dbo.sp_help_job to determine if your other jobs are running as the first step in each job. Then from there you can choose what logic you want it to take next when another job is running such as ending the job (if it's on a routine schedule) or implement a mock sleep using WAITFOR DELAY ('00:00:01') -- Sleeps for 1 second, which you can put inside a WHILE loop to recheck job status.
You can call the sp_help_job by passing in the job name:
EXEC msdb.dbo.sp_help_job @Job_name = 'TheJobName'

And you can use the columns current_execution_status and current_execution_step of the procedure's result set, to help you determine when it's safe to run the current job.
There's also some system job tables that might be helpful to you to query as well, which you can read up on in this StackOverflow answer.


Query execution was interrupted, max_statement_time exceeded I'm getting following error via Microsoft IIS 8' Error Log:

  Query execution was interrupted, max_statement_time exceeded

MySQL is 5.7.11-log running on Windows 2012 and per MySQL :: MySQL Server Version Reference :: 1.5 Option/Variable Changes for mysqld 5.7 max_statement_time was introduced in 5.7.4 and removed in 5.7.8, but error still there...

This feature with some changes was ported from Twitter MySQL patches Statement Timeout · twitter/mysql Wiki.

WITHOUT switching to MySQL fork maintained and used at Twitter, what are my other options?

Please advise.

* UPDATE *

MySQL Bugs: #81071: Query execution was interrupted, max_statement_time exceeded

I was experiencing the same issue.
Possible to set the global max_execution_time, but not the session max_execution_time:

SET global max_execution_time = 60000;
SELECT @@global.max_execution_time -- 60000

SET session max_execution_time=300000;
SELECT @@session.max_execution_time -- 15000

I used a hint as workaround:

select /*+ MAX_EXECUTION_TIME(300000) */ * from table
where ...

Source: https://stackoverflow.com/questions/415905/how-to-set-a-maximum-execution-time-for-a-mysql-query


Speed up PostgreSQL logical decoding plugin I'm writing a new logical decoding output plugin for PostgreSQL inspired by wal2json.  But mine produces JSON by using SPI to repeatedly invoke to_json(...) on column values (instead of wal2json's simpler JSON output that doesn't work well for some data types).

Testing shows that this is pretty CPU intensive.  Is there a way to achieve the same result with better performance?

Could you somehow directly invoke row_to_json on the tuple given to the change callback?

Is there a way to directly invoke to_json on each column value without SPI?

(My attempts to call those functions directly with DirectFunctionCall1Coll crashed the server, so any pointers are welcome!)

Here's what the functioning, but CPU-intensive, code looks like:

spiArgType = columnInfo->atttypid;
/* Prepare the SQL */
spiPlan = SPI_prepare("SELECT to_json($1)::TEXT", 1, &spiArgType);
/* Execute it */
spiReturnValue = SPI_execute_plan(spiPlan, &columnValue, NULL, false, 1);
/* And if it succeeded, append to output string */
if (spiReturnValue < 0) {
    elog(ERROR, "SPI_execute_plan returned %d", spiReturnValue);
    appendStringInfo(out, "null");
} else {
  jsonDatum = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isNull);
  appendStringInfo(out, "%s", TextDatumGetCString(jsonDatum));
}
SPI_freeplan(spiPlan);

A failed attempt using OidFunctionCall1Coll:

TupleDesc cols = ...;
HeapTuple tuple = ...;
Form_pg_attribute colInfo;
Datum colValue;
bool isNull;

for (i=0; i < cols->natts; ++i) {
  colInfo = TupleDescAttr(cols, i);

  // (Omitted checks for dropped or system column)

  colValue = heap_getattr(tuple, i+1, cols, &isNull);
  if (! isNull) {
    // The following call fails, and emits this error:
    // ERROR:  could not determine input data type
    jsonDatum = OidFunctionCall1Coll(F_TO_JSON, InvalidOid, colValue);
    // This is never reached:
    appendStringInfo(out, "%s", TextDatumGetCString(jsonDatum));
  }
  // ...
}

See this comment from fmgr.h:

/* These are for invocation of a function identified by OID with a
 * directly-computed parameter list.  Note that neither arguments nor result
 * are allowed to be NULL.  These are essentially fmgr_info() followed by
 * FunctionCallN().  If the same function is to be invoked repeatedly, do the
 * fmgr_info() once and then use FunctionCallN().
 */

extern Datum OidFunctionCall0Coll ...

This should be way cheaper than SPI.


Cannot startup Oracle due to ORA-15041 I'm troubleshooting an Oracle instance that won't startup  due to ORA-15041. The trc file shows this error:

Failed to create file '+FRA_DATA' (file not accessible?)
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA_DATA
ORA-15041: diskgroup "FRA_DATA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.
*************************************************************
ORA-19504: failed to create file "+FRA_DATA"
ORA-17502: ksfdcre:4 Failed to create file +FRA_DATA
ORA-15041: diskgroup "FRA_DATA" space exhausted
*** 2016-02-22 11:10:10.612 4132 krsh.c
ARCH: Error 19504 Creating archive log file to '+FRA_DATA'
*** 2016-02-22 11:10:10.612 2747 krsi.c
krsi_dst_fail: dest:1 err:19504 force:0 blast:1
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: '+RAC_DATA/oradb/redo01.log'
ORA-16038: log 1 sequence# 11427 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '+RAC_DATA/oradb/redo01.log'

*** 2016-02-22 11:10:10.685
USER (ospid: 25801): terminating the instance due to error 16038
ksuitm: waiting up to [5] seconds before killing DIAG(25722)

Is there a way to manage this with no connection to the database? So far all the solutions I have encountered from research requires a database connection, however I cannot even get the instance up.

Very new to database troubleshooting (actually a web developer), just tasked to troubleshoot this as our DBA left. Any help would be very much appreciated.

TIA!

ORA-15041: diskgroup space exhausted
Cause:  At least one disk in the diskgroup is out of space.
Action: If all disks are evenly balanced, add more disks to the diskgroup.

According to the log provided in the question, i found that it was unable to complete the archiving operation as the asm diskgroup '+FRA_DATA' is full. The DML operation on large table might have caused this error.
You can check if there are extra files - including orphaned archivelogs - that may be in the FRA_DATA.

RMAN>crosscheck archivelog all;

If there is no more space available in FRA_DATA then add disks to diskgroup using ASMCA or ASMCMD utilities.
After adding disks to the diskgroup also check the value of db_recovery_file_dest_size initialization parameter.
The error 15041 possibly relates to the disks in that particular diskgroup.
This can happen if the Diskgroup contains disks of unequal sizes.
See Oracle Support Document ORA-15041 IN A DISKGROUP ALTHOUGH FREE_MB REPORTS SUFFICIENT SPACE (Doc ID 460155.1)


Find Table Variables definitions in Stored Procedures As part of tuning of a large database, I am first of all interested in finding all the table variables.

I am using this script:

SELECT [Schema]=schema_name(o.schema_id), o.Name, o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like '%DECLARE @%TABLE%'

It returns many results but most of them are irrelevant.

How can I change the search pattern to match an exact definition?

The LIKE function is not designed to handle the level of complexity that is necessary for this type of string pattern. The % matches 0 or more of anything. Hence, DECLARE @%TABLE matches any code that has "DECLARE @" + anything + "TABLE", where anything can be 1000 lines, or 10 MB worth, of code. Or even just a space. For this you really need Regular Expressions (i.e. RegEx). However, there is no built-in RegEx functionality in SQL Server. However, as long as you are using SQL Server 2005 or newer (and not using Azure SQL Database [not including Managed Instances] or SQL Server 2017 on Amazon RDS), then you can get RegEx functionality via SQLCLR. You can either code these yourself (and there are plenty of examples floating around), or you can download and install SQL#, a SQLCLR library (that I wrote) that has a Free version which includes several RegEx functions.

One thing to keep in mind is that table variables can be declared using a user-defined table type (UDTT) as the definition. In such cases you would not look for the string TABLE but the name of the UDTT. And, to complicate things, UDTTs can be specified with an optional schema name followed by a period. Oh, and, the UDTT name and/or optional schema name may or may not be delimited by square brackets (we will ignore, for the moment, using double-quotes as identifier delimiters).  Oh, AND, if the optional schema name is specified, there can be white-space on either side of the period separator (fo' reelz, yo!). And let us not forget that @, #, and $ are also valid characters to use in identifiers. Fun stuff!!!

The following pattern should cover the vast majority of situations (not 100% of possibilities, but highly unlikely that someone would be doing something outside of what this will match ** ):

DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)

The example below uses the SQL# function, RegEx_Matches, and the pattern shown above to find table variable declared either directly as tables or by using a user-defined table type:

-- DROP TYPE dbo.Test_Table;
IF (TYPE_ID(N'dbo.Test_Table') IS NULL)
BEGIN
  CREATE TYPE dbo.[Test_Table] AS TABLE ([ID] INT);
END;

DECLARE @SQL NVARCHAR(MAX) = N'
Declare @a dbo.Test_Table;
Declare
@b dbo.[Test_Table];

Declare @c [dbo].Test_Table;
Declare @d [dbo ].[Test_Table];
Declare @e [dbo]
    .
        [Test_Table];

Declare             @f Test_Table;
Declare @g
[Test_Table];
Declare @h [Test_Table  ]; -- space(s) at the end, not tab(s)

DeclarE @T1     TABLE(col1 int);
DeclarE @T_2 TABLE
(col2 int);
DeclarE
    @$#@        TABLE (
    col3 int);
';

EXEC (@SQL); -- make sure that T-SQL is valid

SELECT * FROM SQL#.RegEx_Matches(@SQL,
N'DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)',
                                 1, N'IgnoreCase');

That SELECT returns the following results:

MatchNum   Value                                StartPos   EndPos   Length
1          Declare @a dbo.Test_Table            3          27       25

2          Declare
           @b dbo.[Test_Table]                  31         58       28

3          Declare @c [dbo].Test_Table          64         90       27

4          Declare @d [dbo ].[Test_Table]       94         123      30

5          Declare @e [dbo]
            .
                [Test_Table]                    127        162      36

6          Declare              @f Test_Table   168        191      24

7          Declare @g
           [Test_Table]                         195        218      24

8          Declare @h [Test_Table  ]            222        246      25

9          DeclarE @T1      TABLE(              287        305      19

10         DeclarE @T_2 TABLE
           (                                    318        338      21

11         DeclarE
            @$#@        TABLE (                 351        373      23

You should be able to find everything in your code by doing something along the lines of:

SELECT *
FROM   sys.sql_modules mdl
CROSS APPLY SQL#.RegEx_Matches(mdl.[definition],
  N'DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)',
                               1, N'IgnoreCase') tvar;

** Well, ok, this pattern doesn't account for comments placed between the "DECLARE" and variable name, or between the variable name and "TABLE" or UDTT name. But, that is a whole other level of complication that is, for the moment at least, maybe not best handled inside SQL Server, in which case something outside of SQL Server would be your best option. Something like TSqlParser, as Martin suggested in a comment on the question, or maybe SMO can handle this.

DECLARE       -- This
    @Valid    -- is
  /* valid
       /* DECLARE @NotReal TABLE([Col1] TINYINT);
    */
  */  TABLE   -- T-SQL
    ( [col1]  INT );


Postgresql: percentile_cont interpolation of values I have a couple of questions regarding percentile_cont in Postgresql. I would like to know which algorithm it uses for interpolating values when using percentile_cont ?
My second question is why is it that we have to use order by ? It just seems weird to me that it doesn't order values by default when using percentile_cont? I fail to see why this choice has been made. Would someone please explain the reason behind such a choice?

Because that is the syntax which is available for "ordered set" aggregate functions.  If the ORDER BY were removed, then it would not know what column or expression to calculate the percentile over.  I know what "the median height of a dog" means, and even "the median dog by height" (although that is of questionable utility, especially the continuous version) but what would the "median dog by no specified metric" mean?

We could invent some way of conveying that column-to-be-used information other than ORDER BY, but why do that? There is a machinery for "ordered set" aggregate functions in place, it should be used for the things that fit into it.


Storing daily yield curve data in SQL table I have daily yield curve data for integer maturities that should be stored in a MSSQL server.
Currency.csv
Currently, the data is stored in CSV files per currency in the format:
date, 1y, 2y, 3y, ...
08/12/2020, 0.4, 0.5, 0.6, ...

I am wondering what the best choice for a table would be.
Same Format Table
The current data could be directly translated to a table with columns:
+------------+------+------+------+------+
|    date    |  1y  |  2y  |  3y  |  ... |
+------------+------+------+------+------+
| 08/12/2020 |  0.4 |  0.5 |  0.6 |  ... |
+------------+------+------+------+------+

Alternate Table Design
However, I could also store it like:
+------------+-----------+-----------+--------+
|    date    |  currency |  maturity |  value |
+------------+-----------+-----------+--------+
| 08/12/2020 |  "EUR"    |         1 |    0.4 |
| 08/12/2020 |  "EUR"    |         2 |    0.5 |
| 08/12/2020 |  "EUR"    |         3 |    0.6 |
+------------+-----------+-----------+--------+

Obviously, the second approach would take more space than the first, but I would see the advantage that I am not restricting the value of the maturity in case in the future it should be changed to non-integer values or to maturities that don't have a column yet.
Which of the two table designs would be better? Are there other designs that would fit better?

Your second choice (long) is preferable for the reasons you outline, provided you use the right keys and constraints.
Something like this is probably in the right direction:
CREATE TABLE Currency
(
  CurrencyCd    CHAR(3)      NOT NULL  /* This is the ISO currency code */
 ,CurrencyDesc  VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_Currency PRIMARY KEY (CurrencyCd)
 ,CONSTRAINT AK_Currency UNIQUE (CurrencyDesc)
 ,CONSTRAINT CK_CurrencyCd_Length CHECK (LEN(CurrencyCd) = 3)
)
GO

/* This is solely to prevent invalid values being added */
CREATE TABLE MaturityAge
(
  MaturityAgeYr  TINYINT  NOT NULL
 ,CONSTRAINT PK_Maturity PRIMARY KEY (MaturityAgeYr)
)
GO

CREATE TABLE CurrencyYield
(
  CurrencyCd     CHAR(3)       NOT NULL
 ,MaturityAgeYr  TINYINT       NOT NULL  /* Possibly consider using months if finer detail possible */
 ,AsOfDt         DATE          NOT NULL
 ,Yield          DECIMAL(9,1)  NOT NULL /* Can increase decimal precision if necessary */
 ,CONSTRAINT FK_CurrencyYield_Of_Currency FOREIGN KEY (CurrencyCd) REFERENCES Currency (CurrencyCd)
 ,CONSTRAINT FK_CurrencyYield_At_Maturity FOREIGN KEY (MaturityAgeYr) REFERENCES MaturityAge (MaturityAgeYr)
 ,CONSTRAINT PK_CurrencyYield PRIMARY KEY (CurrencyCd, AsOfDt, MaturityAgeYr)
)
GO

Here the choice of the composite clustered index form by the primary key (CurrencyCd, AsOfDt, MaturityAgeYr) ensures the data will be (more or less) stored sorted according to those columns.  That means any queries of the type WHERE CurrencyCd = 'EUR' will only read the relevant pages.
This choice of clustered index + storing the data in a "long" format will make "widening" the data trivial - so if you need to view the data as you provide in the first example, it's a simple PIVOT.
Other considerations:

If the volume of data is very large, but most of the values do not change, you can explore only storing changed values.  It takes a little more code to retrieve the data for a given date, but you can create date tables/views to simplify that.  If this is the case, let me know in the comments and I'll expand my answer.
Over time the table will fragment (a large number of records will not be co-located according to the clustered index), so the clustered index will need to be rebuilt occasionally.  There are a number of readily available tools/scripts to make this relatively simple.


I only have backup of a TDE encrypted SQL Server database--can it be restored it to another server? My situation is little weird--I have no access to the source database server, but I do have the database backups of a database that is encrypted with TDE.  When I restore it to another server, I am getting an error about the certificate.

I cannot access the source master database.  How can I restore the encrypted database on another server?

The primary purpose of TDE is that someone can't just take the backup or database files and start up the database on another server.  Accordingly, the answer is no, you cannot restore it without restoring the certificate that was used to encrypt it.


Calculating a running balance to store with transactions I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements.

Balance
| id | member_id | product_id | balance |
|  1 |    100001 |         33 |  975.00 |
|  1 |    100002 |         15 |   10.43 |
|  1 |    100001 |         15 |    0.00 |

Transactions
| id | member_id | product_id |       date |    value | interest |
|  1 |    100001 |         33 | 2014-09-01 |  -100.00 |     0.00 |
|  2 |    100001 |         33 | 2014-08-20 |   600.00 |     0.00 |
|  1 |    100002 |         15 | 2014-06-01 |   -50.00 |     0.00 |
|  2 |    100001 |         15 | 2014-08-20 |  -600.00 |     0.00 |

This data is read-only, and due to external issues with the legacy system the data is coming from the tables are emptied and everything re-inserted every time the data is updated. Not ideal, and the legacy system should be replaced within 18 months.

The transaction data I import doesn't have a running balance column. I need this, and have come up with two options:

Do not store a calculated balance against each transaction, but calculate on-the-fly. This makes paginating for account statements a pain, as no matter how far in the past we have to start with the latest balance and work back through all the transaction history. And I know no fast way to do this recursive query.
Calculate a running balance when transactions are imported, and store this against each row.

The second is my preferred option (for ease when extracting "March 2009's statement"). I am stuck trying to devise an efficient way to update the running balance: the best I have is code selecting all transactions for a particular user's account, then looping over them updating the figures. This is not performant.

How would you deal with this? Mine has to work on MySQL but I am also interested in seeing more elegant solutions than it supports.

Attemping an answer based on comments

The easiest and fastest way to do this is to take a set based approach. Like this:

1) Read NOW() into a variable @N

2) Load new data into Transactions

3) Update the Balance like this:

UPDATE Balance B
INNER JOIN (
  SELECT SUM(value) as value, member_id, product_id
  FROM Transactions
  WHERE date >= @N) AS T
ON B.member_id = T.member_id
AND B.product_id = T.product_id
SET B.balance = B.balance + T.value

4) Profit


Convert Postgres TIMESTAMP to TIMESTAMPTZ I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller.

Stupidly, when I initially designed the thing I had all the fields as TIMESTAMP WITHOUT TIME ZONE, and now I'm paying for it with annoying time-zone related bugs. I want everything to be explicit, so want to convert the field to TIMESTAMP WITH TIME ZONE. I realise that this doesn't store extra information, and all my timestamps are already in UTC, so the migration should be trivial, but I was wondering if there are any things complications / potential tripping blocks that will prove problematic (this is a production database with customers relying on it)?

Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If you are not explicit about this, the time zone of the current session is assumed - typically not UTC.
ALTER TABLE tbl ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC';

Check a possible column default for sanity, too. Any expression working with data type timestamp (like LOCALTIMESTAMP or now()::timestamp) is subject to the same problem. To change:
ALTER TABLE tbl ALTER ts_column SET DEFAULT now();  -- or current_timestamp

Obviously, statements writing to the table also need to use timestamptz now - or you have another instance of the same problem with automatic conversion from the type timestamp [without time zone].
Since this is a production DB, best do it all in a single transaction to avoid race conditions - or even a single statement:
ALTER TABLE tbl
  ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC'
, ALTER ts_column SET DEFAULT now();

Basics:

Ignoring timezones altogether in Rails and PostgreSQL


Db Design of a Room Booking System I plan on building a web based room booking application for meeting rooms at a company. The requirements are not set in stone and are pretty much left to me, as long as these core ones are met:

Users can view monthly schedule for meeting room, so see what days and times there are bookings for a particular room.
Users can book a meeting room
Users can edit,delete a booking once made.
Receptionists have power to cancel bookings, even though they were not the ones that made them.

If I have missed any obvious ones, please point them out.

So with the above in mind, I have come up with the following ERD.

Can you see any obvious issues with the above ERD? I have gone through the above scenarios / requirements, and I believe the above design satisfies them all, but it is possible that I am very much mistaken (has happened before).

Any help and feedback would be greatly appreciated.

How do you know how long a room is booked for?  Your model indicates when a booking starts, but when does it end?

Also, your ERD shows foreign keys from BOOKING to USER and ROOM but these columns aren't acknowledged in your BOOKING table.  Some other answers have interpreted that as the columns being missing.  You should be explicit in your diagram to avoid confusion (or add the FK columns if you missed them!)


Max values on a group of fields Does anyone know how can I select rows that contain maximum value for some group of fields based on some criteria?

For instance, let's say I have the following table:

Table1:

ID   fieldA    fieldB    fieldC
1      A         X         1
2      A         X         2
3      A         X         3
4      A         Y         4
5      A         Y         1
6      B         Y         2
7      B         Z         3
8      B         Z         4
9      B         Z         5

Let's say that I want a table with results of maximum values of fieldC for each different entry of fieldA where fieldB=X, fieldB=Y, fieldB=Z and so on. The result should be:

ID   fieldA    fieldB    fieldC

3      A         X         3
4      A         Y         4
6      B         Y         2
9      B         Z         5

I know how to do it for one criteria, but not for a set of criteria:

SELECT    fieldA, MAX(fieldC) AS maxFieldC
FROM      table1
WHERE     fieldA = 'A'
GROUP BY ID

I tried a UNION clause, but I couldn't make it work.

If I understood this correctly, you can use ROW_NUMBER:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY fieldA, fieldB ORDER BY fieldC DESC)
    FROM dbo.YourTable
)
SELECT *
FROM CTE
WHERE RN = 1
;

Here is a demo of this.

The results are:

╔════╦════════╦════════╦════════╦════╗
║ ID ║ fieldA ║ fieldB ║ fieldC ║ RN ║
╠════╬════════╬════════╬════════╬════╣
║  3 ║ A      ║ X      ║      3 ║  1 ║
║  4 ║ A      ║ Y      ║      4 ║  1 ║
║  6 ║ B      ║ Y      ║      2 ║  1 ║
║  9 ║ B      ║ Z      ║      5 ║  1 ║
╚════╩════════╩════════╩════════╩════╝


How to grant MySQL user privilege to "SHOW TABLES" I have two MySQL databases hosted on my website, one for a MyBB forum and the other for data displayed on the website that comes from Raspberry Pis. The MyBB database has a user associated with it, and I've created another MySQL user to associate with the other database and granted it all privileges in cPanel.

When I connect to the database on the Raspberry Pi, I am able to use most SQL commands (INSERT, SELECT, DELETE, etc.) and they work fine. However, the command "SHOW TABLES" gives an "access denied" error for the MySQL user.

I tried deleting the database and repopulating it didn't help. Any ideas?

From the official MySQL Documentation, You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege.

Check the grants for the connected user. To check grants:

SHOW GRANTS FOR root(user);

For example, if you wish grant SELECT privileges to a user, login as root (user with grant privilege) and issue the following command statement.

GRANT SELECT ON *.* to 'user'@'host';

Also, from the same document, you can use the mysqlshow command to display database and table information.

I hope this answer will help you. Thanks.


CREATE TABLE query with SELECT having NOT NULL fields I tried searching on this topic but I couldn't find any help on it. I want to write a CREATE TABLE query with SELECT having NOT NULL fields. The query I am using to create the table is as follows:

CREATE TABLE tbl_tsLiLpLgIspFiFp
SELECT timeStamp, localIp, localport, localgeo, isp, foreignip, foreignPort,
COUNT(*) AS `countSamefPort`
FROM union_of_outbound_threats
GROUP BY timeStamp, localIp, localport, localgeo, isp, foreignip, foreignPort;

I want the fields to be set as NOT NULL. What extra parameters should I add? If this was addressed before please direct me to it :)

I don't see how MySQL could know anything about the content of your columns, unless you were basing the CREATE on another table ("LIKE ...").

It couldn't know anything about primary keys, default values, foreign keys, etc, right?

Looks like you're stuck doing an ALTER TABLE afterwards.


Dynamic Table partitioning on a daily basis I have a SQL Server database which contains two tables -- Acks and Logs.

These two tables are related in logic but not in a relational database way. Basically, every message that comes in gets saved in the Log table and, if our server acknowledges it, then that ack gets stored in the Ack table.

We are storing around 5 million Acks and 3 million Logs a day. I am trying to partition these two tables on a daily boundary so that we can easily remove older partitions from the table, and also improve query performance.

I haven't done table partitioning before, so I have been reading some on-line tutorials, however I am stuck on one thing. All the tutorials I have followed seem to manually add filegroups and manually add boundaries.

I want SQL Server somehow to do this daily, and this is what my question is about. I need it to create the new filegroups for the next day, every day at, say, 22:00. Then at 24:00 the inserts should start filling up the new day's partition.

Can anyone point me in the right direction on how to achieve this? Either a comprehensive tutorial or some good old advice will do as well.

My second question: can I somehow apply the same partition function to the two different tables?

They both have a datetime(2) column on which I want to partition, and the same rules
will apply.

How does that then fit in with my filegroups? Do I need a single filegroup for the day? Will each table have a file in that filegroup, or will both tables save to the same file in the filegroup?

Do I have to make a .mdf and .ldf for each filegroup? Or is there still one log file for the entire database?

From SQL Server 2008 SP2 and SQL Server 2008 R2 SP1 there is support for 15,000 partitions so to be honest, you don't really need to do that much dynamically.  Instead of having a complex daily process (Dynamically add filegroup and boundaries) with an opportunity to fail, simply create the partitions up-front from now to the year 2020, and you're well within your limits and pretty future-proof.

You could assign all partitions to one filegroup (ok not necessarily a great pattern), or round-robin between a limited number.  To put it another way, there is no technical need to have a filegroup per day, eg

-- Assign all to one filegroup; ok not necessarily great
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO ( [FG1] )

-- Or round-robin
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO ( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG1] ... etc )

Obviously use Excel or some tool to generate the scripts for you - no need to type them out : )

Use DMV sys.partition_range_values and metadata function $PARTITION to work out information about what data is where.  Create a daily job to switch out and truncate your oldest partition.  I would regard this as lower risk than the daily add.

Warning!! Carefully read the whitepaper as this needs to be enabled and there are some issues with this approach ( eg Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is not supported ).  If you're feeling risk averse the standard limit of 1,000 partitions would still allow you to pre-allocate just under 3 years.

As you really want to partition by DATE not DATETIME2, consider a computed column.  I would probably want to performance test this first though.

There is also a tool on codeplex (SQL Server Partition Management) which might be worth a look although I haven't used it.

To answer your other questions, there should only be one log file for the database.  Add other files as .ndf not .mdf.  To use the same partition scheme (not function) on the same tables, simply create them with the same scheme, they will divide the data up into the files beneath the filegroups eg

CREATE TABLE dbo.yourTable (    ...

    CONSTRAINT PK_yourTable PRIMARY KEY ( rowId, someDate )

    ) ON ps_test(someDate)

OK, this will make for a long answer but I've knocked up a demo of how something like this might work.  It does remind me that the great thing about partition switching is that as a metadata-only operation it's instant.  Just to be clear, this is a demo to show off principles and some example "how-to" code, it is not production quality.  Work through it and make sure you understand it before running in a dev or test environment.  You'll need about 200MB space.

------------------------------------------------------------------------------------------------
-- Setup START
-- Demo runs on my laptop in < 1 minute (ok on SSD)
-- You'll need 200MB space
------------------------------------------------------------------------------------------------

USE master
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
    ALTER DATABASE tooManyPartitionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
    DROP DATABASE tooManyPartitionsTest
GO
CREATE DATABASE tooManyPartitionsTest
GO

ALTER DATABASE tooManyPartitionsTest SET RECOVERY SIMPLE
GO

-- Add 7 filegroups with 4 files each
-- Add 365 files and filegroup
DECLARE @fg INT = 0, @f INT = 0, @sql NVARCHAR(MAX)

WHILE @fg < 7
BEGIN

    SET @fg += 1
    SET @sql = 'ALTER DATABASE tooManyPartitionsTest ADD FILEGROUP tooManyPartitionsTestFg' + CAST( @fg AS VARCHAR(5) )

    -- Add the filegroup
    PRINT @sql
    EXEC(@sql)

    -- Initialise
    SET @f = 0

    WHILE @f < 4
    BEGIN

        SET @f += 1
        --!!WARNING!! DON'T USE THESE SETTINGS IN PRODUCTION.  3MB starting size and 1MB filegrowth are just for demo - would be extremely painful for live data
        SET @sql = 'ALTER DATABASE tooManyPartitionsTest ADD FILE ( NAME = N''tooManyPartitionsTestFile@f_@fg'', FILENAME = N''s:\temp\tooManyPartitionsTestFile@f_@fg.ndf'', SIZE = 3MB, FILEGROWTH = 1MB ) TO FILEGROUP [tooManyPartitionsTestFg@fg]'
        SET @sql = REPLACE ( @sql, '@fg', @fg )
        SET @sql = REPLACE ( @sql, '@f', @f )

        -- Add the file
        PRINT @sql
        EXEC(@sql)

    END

END
GO

USE tooManyPartitionsTest
GO

SELECT * FROM sys.filegroups
SELECT * FROM sys.database_files
GO

-- Generate partition function with ~3 years worth of daily partitions from 1 Jan 2014.
DECLARE @bigString NVARCHAR(MAX) = ''

;WITH cte AS (
SELECT CAST( '30 Apr 2014' AS DATE ) testDate
UNION ALL
SELECT DATEADD( day, 1, testDate )
FROM cte
WHERE testDate < '31 Dec 2016'
)
SELECT @bigString += ',' + QUOTENAME( CONVERT ( VARCHAR, testDate, 106 ), '''' )
FROM cte
OPTION ( MAXRECURSION 1100 )

SELECT @bigString = 'CREATE PARTITION FUNCTION pf_test (DATE) AS RANGE RIGHT FOR VALUES ( ' + STUFF( @bigString, 1, 1, '' ) + ' )'
SELECT @bigString bs

-- Create the partition function
PRINT @bigString
EXEC ( @bigString )
GO

/*
-- Look at the boundaries
SELECT *
FROM sys.partition_range_values
WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' )
GO
*/

DECLARE @bigString NVARCHAR(MAX) = ''

;WITH cte AS (
SELECT ROW_NUMBER() OVER( ORDER BY boundary_id ) rn
FROM sys.partition_range_values
WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' )
UNION ALL
SELECT 1    -- additional row required for fg
)
SELECT @bigString += ',' + '[tooManyPartitionsTestFg' + CAST( ( rn % 7 ) + 1 AS VARCHAR(5) ) + ']'
FROM cte
OPTION ( MAXRECURSION 1100 )

SELECT @bigString = 'CREATE PARTITION SCHEME ps_test AS PARTITION pf_test TO ( ' + STUFF( @bigString, 1, 1, '' ) + ' )'
PRINT @bigString
EXEC ( @bigString )
GO

IF OBJECT_ID('dbo.yourLog') IS NULL
CREATE TABLE dbo.yourLog (
    logId       INT IDENTITY,
    someDate    DATETIME2 NOT NULL,
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
    dateAdded   DATETIME DEFAULT GETDATE(),
    addedBy     VARCHAR(30) DEFAULT SUSER_NAME(),

    -- Computed column for partitioning?
    partitionDate AS CAST( someDate AS DATE ) PERSISTED,

    CONSTRAINT pk_yourLog PRIMARY KEY ( logId, partitionDate )  -- << !!TODO try other way round

    ) ON [ps_test]( partitionDate )
GO

IF OBJECT_ID('dbo.yourAcks') IS NULL
CREATE TABLE dbo.yourAcks (
    ackId           INT IDENTITY(100000,1),
    logId           INT NOT NULL,
    partitionDate   DATE NOT NULL

    CONSTRAINT pk_yourAcks PRIMARY KEY ( ackId, logId, partitionDate )

    ) ON [ps_test]( partitionDate )
GO

IF OBJECT_ID('dbo.yourLogSwitch') IS NULL
CREATE TABLE dbo.yourLogSwitch (
    logId       INT IDENTITY,
    someDate    DATETIME2 NOT NULL,
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
    dateAdded   DATETIME DEFAULT GETDATE(),
    addedBy     VARCHAR(30) DEFAULT SUSER_NAME(),

    -- Computed column for partitioning?
    partitionDate AS CAST( someDate AS DATE ) PERSISTED,

    CONSTRAINT pk_yourLogSwitch PRIMARY KEY ( logId, partitionDate )

    ) ON [ps_test]( partitionDate )
GO
-- Setup END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Data START
------------------------------------------------------------------------------------------------

-- OK load up data for Jan 2014 to today.
DECLARE @startDate DATETIME = '1 Jan 2014', @rand INT

WHILE @startDate < GETDATE()
BEGIN

    -- Add between 1 and 10,000 rows to dbo.yourLog for today
    SET @rand = RAND() * 10000

    ;WITH cte AS (
    SELECT TOP 10000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
    FROM master.sys.columns c1
        CROSS JOIN master.sys.columns c2
        CROSS JOIN master.sys.columns c3
    )
    INSERT INTO dbo.yourLog (someDate)
    SELECT TOP(@rand) DATEADD( second, rn % 30000, @startDate )
    FROM cte

    -- Add most of the Acks
    INSERT INTO dbo.yourAcks ( logId, partitionDate )
    SELECT TOP 70 PERCENT logId, partitionDate
    FROM dbo.yourLog
    WHERE partitionDate = @startDate

    SET @startDate = DATEADD( day, 1, @startDate )

    CHECKPOINT

END
GO

-- Have a look at the data we've loaded
SELECT 'before yourLog' s, COUNT(*) records, MIN(someDate) minDate, MAX(someDate) maxDate FROM dbo.yourLog
SELECT 'before yourAcks' s, COUNT(*) records, MIN(partitionDate) minDate, MAX(partitionDate) maxDate FROM dbo.yourAcks

-- You'll see how pre-May data is initially clumped together
SELECT 'before $partition' s, $PARTITION.pf_test( partitionDate ) p, MIN(partitionDate) xMinDate, MAX(partitionDate) xMaxDate, COUNT(*) AS records
FROM dbo.yourLog WITH(NOLOCK)
GROUP BY $PARTITION.pf_test( partitionDate )
ORDER BY xMinDate
GO

-- Data END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Maintenance START
------------------------------------------------------------------------------------------------

-- Oh man, we're behind with our switching and truncation.
-- Create a job that sweeps up.  Do we get blocking?

-- ALTER TABLE dbo.yourLog SWITCH PARTITION 1 TO dbo.yourLogSwitch PARTITION 1
-- TRUNCATE TABLE dbo.yourLogSwitch

-- Let's pretend we only want to maintain up to 30 days ago
DECLARE @testDate DATE
SET @testDate = DATEADD( day, -30, GETDATE() )

-- Create local fast_forward ( forward-only, read-only ) cursor
DECLARE partitions_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT boundary_id, CAST( value AS DATE )
FROM sys.partition_range_values
WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' )
  AND value < @testDate

-- Cursor variables
DECLARE @boundary_id INT, @value DATE, @sql NVARCHAR(MAX)

OPEN partitions_cursor

FETCH NEXT FROM partitions_cursor INTO @boundary_id, @value
WHILE @@fetch_status = 0
BEGIN

    -- Switch out and truncate old partition
    SET @sql = 'ALTER TABLE dbo.yourLog SWITCH PARTITION ' + CAST( @boundary_id AS VARCHAR(5) ) + ' TO dbo.yourLogSwitch PARTITION ' + CAST( @boundary_id AS VARCHAR(5) )

    PRINT @sql
    EXEC(@sql)

    -- You could move the data elsewhere from here or just empty it out
    TRUNCATE TABLE dbo.yourLogSwitch

    --!!TODO yourAcks table

    FETCH NEXT FROM partitions_cursor INTO @boundary_id, @value
END

CLOSE partitions_cursor
DEALLOCATE partitions_cursor
GO

-- Maintenance END
------------------------------------------------------------------------------------------------

-- Have a look at the data we've maintained
SELECT 'after yourLog' s, COUNT(*) records, MIN(someDate) minDate, MAX(someDate) maxDate FROM dbo.yourLog
SELECT 'after yourAcks' s, COUNT(*) records, MIN(partitionDate) minDate, MAX(partitionDate) maxDate FROM dbo.yourAcks

-- You'll see how pre-May data is initially clumped together
SELECT 'after $partition' s, $PARTITION.pf_test( partitionDate ) p, MIN(partitionDate) xMinDate, MAX(partitionDate) xMaxDate, COUNT(*) AS records
FROM dbo.yourLog WITH(NOLOCK)
GROUP BY $PARTITION.pf_test( partitionDate )
ORDER BY xMinDate

-- Remember, date must always be part of query now to get partition elimination
SELECT *
FROM dbo.yourLog
WHERE partitionDate = '1 August 2014'
GO

-- Cleanup
USE master
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
    ALTER DATABASE tooManyPartitionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
    DROP DATABASE tooManyPartitionsTest
GO


Hyper-V Dynamic Memory and SQL Server I will probably get voted down for this, but does anyone have a good place to start other than the Best Practices Documentation for using Dynamic memory with SQL server.

We are experiencing issues and keep having to set to static for it to work.  The best practices guide basically says it should all work, but I am looking for some real world issues anyone might have faced.

Dynamic memory with SQL is good for letting the memory expand as required and taking the pressure off the disk subsystem as SQL can hold more in memory.  It's a bit of a swizz though in the real world as you are supposed to set the "Lock Pages In Memory" (LPIM) setting to stop the OS from taking memory back from SQL, which can be bad, so you end up creating a one-way street.  You set the max server memory setting (in SQL) to be higher than the startup memory (Hyper-V setting), ideally you match it to the Max mem setting (Hyper-V) of the VM.  Thus SQL starts and takes whatever you set at startup and then it grows over time to reach the maximum setting.  The thing is it always does this.  It's not like a safety net, having the extra headroom "just in case" SQL needs it, it will always take it eventually and then because of LPIM it won't give it back.  You might as well have set the memory to static at the max memory level because that's what it's going to get anyway.

I'm still not sure of the benefit of Dynamic memory for SQL and I've read a lot of white papers and articles, everyone affiliated with MS seems to recommend it but with LPIM and my own real-world observations, I can't see the point of it.  The behaviour with SQL is not "dynamic" it just takes and keeps.


How to calculate MySQL Transactions per second How can I calculate MySQL Transactions per second.

I tried to use formula (global com_commit+global com_rollback)/uptime since flush.

I had also tried (global insert+ global delete+ global update)/uptime since flush.

The outcome of inserts+deletes+updates is less than commits+rollback. The outcome of commits+rollbacks is 8 times larger than inserts+deletes+updates. So confused on the formulas which one to take.

BEGIN; INSERT...; INSERT...; COMMIT; = 2 inserts, 1 begin, 1 commit.
SET autocommit = ON; INSERT...; = 1 insert, 0 commits.

I don't see any way to get the 'right' answer of '2 transactions'.

However,

if your code always uses BEGIN...COMMIT, then you might use Com_begin as the number of transactions.
if your code never uses BEGIN...COMMIT, then you might use Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi as the number of transactions.

(I consider autocommit=OFF...COMMIT to be an abomination.)


SQL Server 2008: How do I change the default schema of the dbo? When I attempt to change the default schema of the dbo in SQL Server Management Studio, the ALTER fails and states that the dbo account's schema cannot be changed. Are there any tricks that can make is possible to change the the dbo default schema of "dbo" to "xxx"?

Thank you!

Each database is owned by a server principal (aka login).  Inside that database, the owning principal is known as dbo (aka *D*ata*B*ase *O*wner).  The database principal (aka user) loses its real name.

For example, for a database I own:

select  [user].name as UserName -- Database specific
,       [login].name as LoginName -- Server wide
from    sys.databases d
join    sys.database_principals as [user]
on      [user].sid = d.owner_sid
join    sys.server_principals as [login]
on      [login].sid = d.owner_sid

Will print "dbo", "Andomar".  If you'd change the owner to sa:

exec sp_changedbowner 'sa'

The query would return "dbo", "sa".

You cannot modify the default schema for the user that owns a database.  It is always user name dbo with default schema name dbo.


Cumulative sum using hiveql I have a table in Hive which looks like:

col1       col2
b           1
b           2
a           3
b           2
c           4
c           5

How do I, with hiveql, group up col1 elements together, sum them up, sort by the sum, as well as create a cumulative sum (csum) based on the sum?

id       sum_all     csum
a         3           3
b         5           8
c         9           17

I've only managed to come up with the grouping and sum, but am out of ideas regarding the cumulative sum. Correlated sub-queries are not supported in Hive

select col1 as id
      sum(col2) as sum_all
from t
group by col1
order by sum_all

With the following result:

id       sum_all
a         3
b         5
c         9

Since correlated subqueries are not allowed, try using derived tables and then joining them.

select
    a.id,
    a.sum_all,
    sum(b.sum_all) as csum
from
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  a
    join
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  b
     on
        ( b.sum_all < a.sum_all )
     or ( b.sum_all = a.sum_all and b.id <= a.id )
group by
    a.sum_all, a.id
order by
    a.sum_all, a.id ;

This is essentially a self-join on the derived group-by table. It might be more efficient to first save the group-by results into a temporary table and then do the self-join.

According to the manual, Hive also has windowing aggregates, so you could use them as well:

select
    a.id,
    a.sum_all,
    sum(a.sum_all) over (order by a.sum_all, a.id
                         rows between unbounded preceding
                                  and current row)
        as csum
from
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  a
order by
    sum_all, id ;

or with:

select
    col1 as id,
    sum(col2) as sum_all,
    sum(sum(col2)) over (order by sum(col2), col1
                         rows between unbounded preceding
                                  and current row)
        as csum
from
    t
group by
    col1
order by
    sum_all, id ;


How to remotely connect SQL Server 2016. Since we have 2012 and 2016 in same server Issue

I am not able to connect to a SQL Server 2016 instance on a remote system/server.

My Existing Environment

Windows Server 2012
SQL Server 2012 and 2016 installed on same server

I tried to enable a firewall rule for SQL Server 2016 with port 1433. But it is not working.

Same thing I tried with SQL Server 2012 it is all working fine.

Settings I Have Tried

Enabled remote connection in SQL server.
Created Firewall rule to browse SQL Server.
Enabled TCP Port.
Enabled default port 1433.

Error Message I Am Receiving

It would be great if anybody could help me with this.

Sometimes you need to open firewall rule for UDP 1434 SQL Browser port. Be sure SQL 2012 and 2016 don't use same TCP port (1433). If multiple instance on same machine they should be configured to use separate TCP port.


Concatenation of varchar-converted DATEDIFF and placing a string afterwards - issues? Everyone, the table relationship diagram for the database is the below one

In case the full DDL for the database is requested, I can provide it (although it will probably exceed the character limit of Stackexchange).
Question 1)
The task is to query a 'Duration' column that shows the duration between the ArrivalDate and ReturnDate in days. If a trip is canceled, the value in that column should say 'Canceled' accordingly. I have researched a lot and could not find a solution without sub-querying to show the 'Duration' in the format e.g. '6 days'.
I have managed to achieve it the below way - by converting the DATEDIFF() first and then using the result as a sub-query (please disregard the ELSE condition and it does not set the field name to 'Canceled')
SELECT [Id], Firstname + ' ' + ISNULL(MiddleName,'') +  ' ' + LastName AS [Full Name], [From],
            CASE
                WHEN CanceledOrNot IS NOT NULL THEN 'Canceled'
                ELSE CONCAT(Duration, ' ', 'days')
            END AS Duration
FROM (
    SELECT Trips.[Id] AS [Id], Accounts.FirstName, Accounts.LastName, Accounts.MiddleName AS Middlename, Cities.[Name] AS [From], Hotels.CityId, Hotels.[Id] AS HotelId,
                 CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) AS [Duration], Trips.CancelDate AS CanceledOrNot
            FROM Accounts
                    LEFT JOIN Cities
                    ON Cities.Id = Accounts.CityId
                    JOIN AccountsTrips
                    ON AccountsTrips.AccountId = Accounts.[Id]
                    JOIN Trips
                    ON Trips.[Id] = AccountsTrips.TripId
                    JOIN Rooms
                    ON Rooms.[Id] = Trips.[RoomId]
                    JOIN Hotels
                    ON Rooms.HotelId = Hotels.[Id] ) AS ToAddDays

Every time I try to add the string directly after the converted DATEDIFF() in the CAST statement. without using a sub-query, an error appears. Why is it so - the DATEDIFF() output is converted to varchar(30) which should be fine for concatenation with the 'days' string? Here is the error-causing query:
SELECT Trips.[Id], Accounts.FirstName, Accounts.LastName, Cities.[Name] AS [From], Hotels.CityId,
                (CASE
                    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' ' + 'days')
                    --CONCAT(CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS VARBINARY(MAX)), 'days')
                    --((CONVERT(VARCHAR(100), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate))),
                    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS int)
                END) AS [Duration]
            FROM Accounts
                    LEFT JOIN Cities
                    ON Cities.Id = Accounts.CityId
                    JOIN AccountsTrips
                    ON AccountsTrips.AccountId = Accounts.[Id]
                    JOIN Trips
                    ON Trips.[Id] = AccountsTrips.TripId
                    JOIN Rooms
                    ON Rooms.[Id] = Trips.[RoomId]
                    JOIN Hotels
                    ON Rooms.HotelId = Hotels.[Id]

The corresponding error is:
Conversion failed when converting the nvarchar value '4 days' to data type int.

I have tried multiple viations, with CONCAT_WS, FORMAT but without success. Is the only way the sub-query approach?
Question 2)
May I ask you as well how is it possible to SELECT and show in an additional column 'To' that includes the City of the Hotel?
The correct result set should look like this (please disregard the missing [Full Name] column in my query - this is not a problem as concatenation or simple addition works as the datatypes of FirstName and LastName are identical.
Thanks for your support!

This expression is explicitly returning two different data types (nvarchar and int):
(CASE
    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' ' + 'days')
    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS int)
END) AS [Duration]

When CancelDate is not null, the value with be of type nvarchar(max).  Otherwise, it will be of type int.
A column only has one data type, which means SQL Server has to choose between those two and make every row the same type.
int has a higher data type precedence than nvarchar, so SQL Server will implicitly convert the nvarchar values from the first part of your CASE statement to ints.
It fails to convert the strings produced by the first part to int (because they contain letters and spaces), resulting in the error message in your question.
One way to resolve the problem would be to explicitly convert the number to a string, if you want to show both numbers and strings in this column:
(CASE
    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' days')
    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS nvarchar(max))
END) AS [Duration]

As a side note:

I used nvarchar(max) for consistency with the first part of your query.  You should consider how many days are really supported, and reduce the size of the output column

You have 5 characters of fixed length (" days"), so I would expect 7 or 8 characters max would do the trick (for 2 or 3 digit durations - unless your tracking 3+ year trips)
the final data type size can affect things like query memory grants, which will have an impact on performance and concurrency

You probably only need varchar, as it doesn't seem your expression would ever produce unicode data


It looks like almost all the time is spent just reading the data.  I don't think the window function really has anything to do with the poor performance.  The timestamp selection would probably be better written as this:
insert_time>='2020-10-01' and insert_time < '2020-11-01'

especially if you had an index it could use, probably on (smart_device_id, insert_time).
Finally, clustering your table so that all the data which will be needed together is found together should help a lot.  Maybe use partitioning on smart_device_id so that it stays clustered.


SQL Server 2008-R2/2012 with Failover Cluster - minimizing downtime of program updates I have SQL Server 2008-R2 with Failover Cluster like this. Planning to upgrade to SQL Server 2012.

Problem -

The downtime of my program updates (not Microsoft updates/patches).
Both of my cluster nodes have separate sets of connected IIS and App servers.
I need to upgrade all of them: IIS + App server + SQL.
Cannot run old IIS+App against upgraded SQL or the other way around (causes version conflicts).So the minimum downtime is the time of SQL upgrade running (can at least prepare one node's IIS+App in advance and then switch to it when ready).

Now I hear there is something clever in SQL Server 2012 to minimize the downtime even further in this kind of situations. But what exactly might it be?

(please be specific, I know they have this big AlwaysOn Availability Groups banner :-)
PS basically can change the entire approach if needed (like dropping Failover Cluster in favor of something better)

I am not sure if you are proposing an in-place or migration upgrade. As this answer indicates, I prefer migration upgrades when possible.

Assuming a migration - you shouldn't need to rely on any SQL Server 2012 features to assist in the migration and reduce the downtime. For your new environment planning, you might look into AlwaysOn Availability Groups and some of the benefits and differences from using failover clustering instances. Failover Cluster Instances are still a fine option in SQL Server 2012, though. Especially when you want to use shared storage, keep a copy of the data, have the DBs, jobs, logins, etc. all just work the same on the passive node as the active node.

As to how to make the migration faster - I talk about it a bit in the linked answer above - but basically you could do a plan like this if migrating:

Build the new environment and test it/prep it for readiness
Do a test migration to it, verify apps look good and work. Hopefully you can freeze jobs and logins at this point so you have the jobs and logins you need moved over
For the actual live cutover - restore full databases to it specifying with norecovery (this is assuming your DBs are already in full recovery mode on the source, if not they'll need to be properly there.) from your latest full backup.
Using a combination of differential and transaction log backups, restore the backlog of diffs and transaction logs (again specifying with norecovery) until you get to
When you are "caught up" on your restores and have your "cutover window" - you can hold activity, do your last log restores - finally specifying with recovery on the last ones and ensure all apps are good.

That is an approach to minimize the downtime when doing a migration style. When you do an in-place it's a different story and there frankly isn't a whole heck of a lot to be done to change or speed up processing.

Obviously this is a decision you would have to have made well ahead of the upgrade - in place doesn't need new hardware.. Migration does.


Client times out, while MySQL query remains running? We experienced an issue in which a read-only query, run through MySQL workbench, timed out from a user's UI perspective and remained running on the server (and apparently consuming more and more resources) until we had an outage.

Questions

Is there a standard way to deal with this sort of issue in MySQL?
Is there a fundamental cause that we need to avoid?

You need to look at what default values are in place for timeouts:

mysql> show variables like '%timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout        | 60    |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 60    |
+----------------------------+-------+
10 rows in set (0.00 sec)

Usually, I watch for several timeout variables. This is very imperative if you use MySQL remotely from MySQL Workbench, mysql client, or PHP app on an app server contacting MySQL on a DB Server.

Here is what the MySQL Documentation says one these settings:

wait_timeout (Default 28800 [8 hours]) : The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.
interactive_timeout (Default 28800 [8 hours]) : The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.
net_read_timeout (Default 30) : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
net_write_timeout (Default 60) : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

Please make sure these timeouts are set high enough to accommodate queries that may run for a very long time, which may include:

Mass UPDATEs
Mass DELETEs
ENABLE KEYS on a Large MyISAM

To deal with queries that keep running after you lose touch with it, you have to run KILL against the process ID of the long running query. Even with the KILL command, you will have to wait for any query that is in the middle of disk-intensive steps or have internal mutexes in progress.


SQL Server: How to add all dates of the month? I need help. I am trying to add all dates for the month. I have this information:

Month | Year
=============
  1   | 2012
  2   | 2012
  3   | 2012
  4   | 2012

I need to add all dates of the month (for example: 01/01/2012 to 31/01/2012).

I do not have any idea how to add all dates. Please, any help is very useful.

Edited

I resolved my problem using a cursor, the cursor runs the records in the table "MontsOfYear" and with each register obtains the last day of the month (Example: Month = 1 and Year = 2015) that obtain the last day to January (31), next is iterate between the first day to the last day of the month.

This is the code:

DECLARE db_Cursor CURSOR FOR SELECT Years,Months FROM MontsOfYear;
DECLARE @Years INT, @Months INT, @LasDate date, @DatePart date, @Flag int;

OPEN db_Cursor;

FETCH NEXT FROM db_Cursor INTO @Ano, @Mes
WHILE @@fetch_status = 0
BEGIN

SET @LasDate = (SELECT (DATEADD(MONTH, DATEDIFF(MONTH, 0,       DATEFROMPARTS(@Years,@Months,1)) + 1, 0) - 1));
SET @DatePart = DATEFROMPARTS(@Ano,@Mes,1);
SET @Flag = 1;

    WHILE @DatePart < @LasDate
        BEGIN
            SET @DatePart = (DATEFROMPARTS(@Ano,@Mes,@Flag));
            INSERT INTO Days VALUES(@Ano,@Mes,@DatePart);
            SET @Flag = @Flag + 1;

        END;
FETCH NEXT FROM db_cursor INTO @Ano, @Mes;
END;
CLOSE db_Cursor;
DEALLOCATE db_Cursor;

Use a function like this one, from my answer to Get a list of dates between two dates using a function:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

...and query it like:

SELECT *
FROM YourTable t
CROSS APPLY dbo.ExplodeDates(
    DATEFROMPARTS(t.[Year], t.[Month], 1),
    EOMONTH(DATEFROMPARTS(t.[Year], t.[Month], 1))
) AS d;


Are having TRIGGERs for every upvote/downvote detrimental in terms of performance? I plan to have three tables: Users, User_Reputation and Voting_Records. The Users table would hold the primary key id for the registered users, along with personal information such as email and passwords. If they upvote something, it would record it in the Voting_Records like so:

+------+-------------+-----------+--------+
|  id  |   user_id   | author_id |  vote  |
+------+-------------+-----------+--------+
|  3   |   12389129  |  123421   |   -1   |
+------+-------------+-----------+--------+

With the user_id and author_id being a foreign key to the id of the Users table, this would subtract 1 from the overall reputation.

So instead of using SUM() for every time a user queries for a total of their reputation, would it be more beneficial to have TRIGGER to update the total_votes on the User_Reputation table every time someone upvotes/downvotes on their profile? It would definitely be more easy to implement features such as badges, which would be automatically awarded to people with more than X points.

This is called an aggregate table. This is a valid strategy.

You could also defer the calculation of user reputation if you are not bothered with real-time updates. Some sites do DB maintenance during the night, rebuild indexes etc. Since we are talking about user reputation, it's no big deal if the current stats are only reflected the day after. I don't think people will even notice.

User_Reputation could be a field in your table Users. It is simply an integer value that is incremented or decremented when an upvote or downvote is recorded. If you are writing more than one records, then wrap it up in a transaction:

Add a record in Voting_Records
Update table users: increment/decrement the value of User_Reputation

It doesn't have to be a trigger, it could be a standalone SQL statement or a block of code in a stored procedure, as long as there is a transaction to ensure consistency and as mentioned above, you have to consider concurrent updates.

You can even make the DB read-only for a short while when maintenance is in progress, I think that's what Stack Exchange does (sometimes).


Remove the log shipping configuration on the secondary server, when the primary is no longer available My primary server with SQL Server 2005 broke down, so I've started up the log-shipped database on the secondary server. The database is live on the secondary server, and I just need to remove the log shipping settings for it, while the primary server is no longer accessible.

It seems that the sp_delete_log_shipping_secondary_database stored procedure should be executed on the secondary server to do this, which "removes a secondary database and removes the local history and remote history", as it is stated on msdn.

Would this also remove the database itself? Or only the log shipping settings for it? And what will it do when the primary server is not accessible?

I must not loose the database on the secondary server, as it is already in use.

You are facing what is called orphaned entries in logshipping tables in msdb. You can use below tsql to just manually clean up logshipping orphan entries. For your case, since you don't have the primary database available, you should just run the secondary part or you can try running sp_delete_log_shipping_secondary_database N'secondary_database'. It will just remove the secondary database settings from msdb.

----- on primary
delete from [dbo].[log_shipping_monitor_primary]
where primary_database = 'your database name'       -- Change the database name
go
delete from [dbo].[log_shipping_primary_databases]
where primary_database = 'your database name'        -- Change the database name
go
delete from [dbo].[log_shipping_primary_secondaries]
where secondary_database = 'your database name'      -- Change the database name

----- on Secondary
delete from [dbo].[log_shipping_monitor_secondary]
where secondary_database  = 'your database name'     -- Change the database name
go
delete from [dbo].[log_shipping_secondary]
where primary_database = 'your database name'        -- Change the database name
go
delete from [dbo].[log_shipping_secondary_databases]
where secondary_database = 'your database name'      -- Change the database name

Note: Above methods are safe and will delete ONLY logshipping related information.


Update non key ID column for multiple rows at once I've inherited of a poorly designed database running on Webdev18.
Most of the tables are not related at the database level but rather in the code.
I ought to insert and update new values in some of the tables, however, due to poor design the table I'm working on doesn't have an explicit primary key, but use the id column as such with no uniqueness being implemented.
--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
---------------

The id is populated via the code for each insert and is not auto-incremented. The function evaluate the current Max() and increment it by 1.
The default value is O.
Each year, we're supposed to register the patients including those of previous year at are still present.Up to now this was done through the application by users this being cumbersome and adding no added value.
So a new record of the same patient is created for each year, thus resulting in duplicate id.
--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
0 |Carine|2017
0 |Prince|2017
---------------

I'm to update the new rows id with new values so as to a result as below:
--------------
id |name|year|
---------------
1 |Carine|2016
2 |Karl  |2016
3 |Prince|2016
4 |Deo   |2016
5 |Carine|2017
6 |Prince|2017
---------------

However, I've got the following constraints in doing so:
Constraints
Approaches that can't be used:

No code update,re-writing, and triggers to be added to the table.
No looping construct allowed in the SQL Query command interface.
No variable declaration in the SQL Query command interface.

RDBMS
WebDev 18 - HyperfileSQL
Therefore is it possible to achieve this result within the constraints mentioned above with simply using an
INSERT INTO ... SELECT FROM... .
Sql Fiddle

You can use the maximum id value and add to each id. This will probably produce a lot of gaps. That can be avoided if the DBMS has window functions - like ROW_NUMBER() - implemented:

INSERT INTO tableX
    (id, name, year)
SELECT
    m.maxid + t.id,       -- m.maxid + ROW_NUMBER() OVER (ORDER BY t.id),
    t.name, 2017
FROM
    ( SELECT MAX(id) AS maxid
      FROM tableX
    ) AS m
  CROSS JOIN
    mytableX AS t
WHERE
    t.year = 2016 ;


Copying a table while maintaining consistency in Oracle We have a PL/SQL stored procedure that is called on user demand which takes a copy of another table (ie. drop index > delete (not truncate) > insert > create index) and we've noticed the odd race condition where an index will not always get recreated.

This leaves us a table with no indexes, so future calls to the procedure fail (yes we could simply ignore the failed drop index). However, I'd like to get to the bottom of why the table is losing its indexes.

I don't think LOCK TABLE is the answer because after every DDL statement (eg. drop index) I'd keep losing the lock due to the implicit commit.

If you keep a separate table to maintain whether your procedure is running or not, then you only need to keep a lock as long as it takes you to update the table to ensure that another session is not doing the same.  Here is some code to setup the table and then use it to run your procedure.

--Setup
CREATE TABLE ProcedureLock AS (SELECT logon_time FROM v$session WHERE rownum<=1);
UPDATE ProcedureLock SET Logon_Time = NULL;
COMMIT;

--Use
DECLARE
   vLogon_Time ProcedureLock.Logon_Time%Type;
   eInUse      Exception;

   PRAGMA EXCEPTION_INIT (eInUse, -54);
BEGIN
   Begin
       SELECT Logon_Time INTO vLogon_Time FROM ProcedureLock FOR UPDATE NOWAIT;
   Exception
      When eInUse Then
         DBMS_Output.Put_Line('The procedure is running.');
         Return;
   End;

   UPDATE ProcedureLock SET Logon_Time =
    (SELECT min(logon_time) FROM gv$session WHERE SID = SYS_CONTEXT('USERENV','SID'))
   WHERE Logon_Time IS NULL;

   If (SQL%ROWCOUNT <> 1) Then
      DBMS_Output.Put_Line('The procedure is running or failed to complete.');
      Return;
   End If;

   COMMIT;

   DBMS_Output.Put_Line('The procedure is NOT running.');
   DBMS_Output.Put_Line('Run Procedure here.');

   UPDATE ProcedureLock SET Logon_Time = NULL;
   COMMIT;
END;
/

First we lock the new ProcedureLock table and then update it.  If either of these fail we know the procedure is running and therefore we should not run it again.


Equivalent functions in MySQL that exist in SQL Server In an EXIT HANDLER block, what are the MySQL equivalents of

OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
ERROR_MESSAGE()

Edit

What I want to do.
In SQL Server I'd have

CREATE PROC ...
AS
SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY
 ...
END TRY
BEGIN CATCH
   ROLLBACK etc
   EXEC someGenericProc @@PROCID, @errmsg OUTPUT;
   RAISERROR ( @errmsg, 16, 1);
END CATCH
GO

CREATE PROC someGenericProc
   @LoggingProcID int
AS
..
SELECT
    @LoggingObject = OBJECT_SCHEMA_NAME(@LoggingProcID ) + '.' + OBJECT_NAME(@LoggingProcID),
    @ErrorObject = ERROR_PROCEDURE(),
    @ErrorMessage = ERROR_MESSAGE();

-- some processing to deal with nesting etc

INSERT SomeErrorTable (...)
VALUES (@LoggingObject, @ErrorObject, @ErrorMessage)
...
GO

Cheers

I believe SIGNAL (introduced in MySQL 5.5) is what you want, for automatic logging.

However, the current implementation cannot use the various conditions such as SCHEMA_NAME after the SIGNAL executes:

  Other condition information items can be set, but currently have no effect, in the sense that they are not accessible from error returns. For example, you can set CLASS_ORIGIN in a SIGNAL statement, but cannot see it after SIGNAL executes

The only available information is the MYSQL_ERRNO and MESSAGE_TEXT, so if you want the schema_name, you'll have to include that in your MESSAGE_TEXT.

Update From the SIGNAL/RESIGNAL worklog

  Since /include/mysqld_error.h says "#define ER_NO_DB_ERROR 1046"
     MESSAGE_TEXT will be 'No database selected'.
     Or, if language = German, 'Keine Datenbank ausgewhlt'.
     Things get trickier if the default message has '%s' in it.
     We won't support the above.

So you can set a signal to:
SIGNAL SQLSTATE '22012' SET MYSQL_ERRNO=1365, MESSAGE_TEXT='Division by 0' explicitly, but it won't automatically inherit the text and errornumber just from the sqlstate.

Caveat:
 I was able to get some ERROR information using RESIGNAL. Consider this:

DROP PROCEDURE IF EXISTS p;
DELIMITER |
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  DROP TABLE xx;
END|
DELIMITER ;
CALL p();

Query OK, 0 rows affected (0.00 sec)

However with this:

DROP PROCEDURE IF EXISTS p;
DELIMITER |
CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL;
  END;
  DROP TABLE xx;
END|
DELIMITER ;
CALL p();

ERROR 1051 (42S02): Unknown table 'xx'

Example (slightly modified) found from RESIGNAL documentation

Update2

Alas, it's not yet possible to create a generic exception handler. Here is a 6-year-old feature request for it: http://bugs.mysql.com/bug.php?id=11660

The (relatively) good news is, the fix is to create the ability to view the diagnostics stack. This is good because the pre-requisite for this feature is the SIGNAL/RESIGNAL mentioned above. The bad part about it is the worklog for the diagnostics stack is not publicly visible yet.

So for now you'd have to create an exception handler for each state your procedure would come across to log into a table, and only log the error number (hard-coded, as it's not available by the handler) and maybe the hard-coded message text.


Recommended postgres parameters for index creation with 244gb RAM and 32 CPUs? I have a 200 GB database with 26 tables that I need to create indexes on.

Currently index creation takes a long time on the biggest tables, as they have ~12gb and 400,000,000 rows in them (>4 hours).

I've set maintenance_work_mem to 100gb, and set max_parallel_workers to 30.

Are there any other parameters I should tune to improve the index creation speed?

This is on AWS Aurora, using postgres-10.6, in case that makes a difference.

There is no one else using the database and downtime/full locks are fine.

This is on AWS Aurora, using postgres-10.6, in case that makes a difference.

Yes, this matters quite a lot.  Native parallel btree index builds were introduced in v11, so your "max_parallel_workers" setting won't matter for index builds under v10.

Unless you upgrade, you will have to parallelize them yourself by opening multiple sessions in parallel and building one index in each one.  You will probably want to lower "maintenance_work_mem" as well if you have parallel processes (either of the manual or the v11 variety) as each one can claim that much memory.


Referencing PL/SQL variables in FOR loop I have written a PL/SQL script to find the size of a long column in a table.
Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying that table or view does not exist. the details are :

ORA-06550: line 8, column 34:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 11:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 42:
PLS-00364: loop index variable 'J' use is invalid
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

The Script is :

declare
a number := 0;
x number := 0;
i number := 0;
tablename varchar2(100):= 'FILES';
columnname varchar2(100):= 'FILESIZE';
begin
for  j in (select columnname from tablename) loop
  a:=UTL_RAW.LENGTH (UTL_RAW.CAST_TO_RAW(j.columnname));
    i := i+1;
dbms_output.put_line(i);
  x:= x + a;
end loop;
dbms_output.put_line(x);
end;

The table name is FILES. And the column name is FILESIZES.

Can you suggest what I am doing wrong. And what can I do to find the size of long Column??

Thanks.

The error in you script is that the script now expects a table named tablename, having a column named columnname. In this case you don't know the table and column names so you should use dynamic sql to run this.
Next to that, if possible, forget about LONG and implement lobs instead.

For docu see http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057

sample code slightly modified to fit your needs:

decLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  v_stmt_str      VARCHAR2(200);
  z               long;
  x               number := 0;
  a               number;
  tablename       varchar2(100):= 'dba_views';
  columnname       varchar2(100):= 'text';
BEGIN
  -- Dynamic SQL statement
  v_stmt_str := 'SELECT '||columnname||' from '||tablename;
  -- Open cursor
  OPEN v_emp_cursor FOR v_stmt_str;
  -- Fetch rows from result set one at a time:
  LOOP
      FETCH v_emp_cursor INTO z;
      EXIT WHEN v_emp_cursor%NOTFOUND;
      a:=UTL_RAW.LENGTH (UTL_RAW.CAST_TO_RAW(z));
      x:= x+a;
  END LOOP;
  -- Close cursor:
  CLOSE v_emp_cursor;
  dbms_output.put_line (x);
END;
/