Why does this stored proc cause a clustered index scan, but seek when using OPTION RECOMPILE? I think I may know the answer based on my research, but am looking for confirmation on how/why the engine compiles the plan the way it does with

Parameters being passed in:
@ID int
,@OtherID INT

SELECT  b.Column1
        ,b.Column2
        ,b.Column3
        ,b.Column4
        ,b.Column5
        ,c.Column1
        ,b.Column1
        ,e.Column1
FROM    Table1 AS b
        inner join Table2 AS t
        on b.ID = t.ID
        left join [LINKED SERVER].[DB].dbo.Table3 as c
        on b.ID = c.ID
        left join Table4 AS e
        on b.ID= e.ID
where   (b.ID = @ID or @ID= 0)
        And b.ID = @OtherID
        And b.ID IS NOT NULL
        and e.ID = 1

Now I have determined that the cause of the index scan is because of this line: where  (b.ID = @ID or @ID= 0). More specifically, @ID = 0.  To clarify even further, 0 for that ID field does not exist as a value in the underlying table, it was simply something a developer did to allow a user to pull back all of the results by passing in 0 to the parameter and then checking to see if that parameter is 0 so as a result more rows are pulled back (typically, you would just return 1-3 results).

Now, what is extremely odd, is that if I add OPTION RECOMPILE, the engine is able to create a much better plan at the cost of overhead (compilation time) of course:

What I would like to know is how is this possible.  From what I have read online, by using OPTION RECOMPILE, the engine will literally replace the value with the actual value passed into the parameter and it can very easily see that @ID 1234 does not equal 0.  However, if you don't use OPTION RECOMPILE the engine will take the total # of records, which is 120,000, and divide it by the total number of distinct possibilities, 107,000.  This comes out to about 1.1 estimated rows being returned and I confirmed this by looking at the estimated properties of the plan that has the index scan, but why would the engine continue to index scan if the estimation is correct?  I even updated stats just to be sure.

b.ID = @ID OR @ID = 0

The optimizer has to produce a plan with an index scan, because the plan is cached and reused.

On a subsequent execution, the parameter @ID might be zero. An index seek is of no value in that case, because there is no ID value to seek to. Other times, there will be a non-zero value provided for @ID, but the cached plan has to work correctly for all possible parameter values.

When OPTION (RECOMPILE) is used, the Parameter Embedding Optimization (PEO) means the current value for @ID is used in place of the parameter on each execution, and no plan is cached.

Say @ID is 1234. After PEO, the optimizer sees:

b.ID = 1234 OR 1234 = 0

That is simplified by the contradiction detection logic to:

b.ID = 1234

...which enables a seek on ID.

For further reading please see my article Parameter Sniffing, Embedding, and the RECOMPILE Options.


Error while accessing remote MongoDB Trying to access remote MongoDB. Got the following error:

C:\Users\ranjj_000>mongo <SERVER_IP_HERE>:28017

MongoDB shell version: 2.6.0
connecting to: 111.93.128.60:28017/test
2014-04-22T12:19:45.084+0530 Socket recv() errno:10054 An existing connection was     forcibly closed by the remote host. 111.93.128.60:28017
2014-04-22T12:19:45.087+0530 SocketException: remote: <SERVER_IP_HERE>:28017 error: 9001 socket exception [RECV_ERROR] server [<SERVER_IP_HERE>:28017]
2014-04-22T12:19:45.088+0530 DBClientCursor::init call() failed
2014-04-22T12:19:45.093+0530 Error: DBClientBase::findN: transport error: 111.93.128.60:28017 ns: admin.$cmd query: { whatsmyuri: 1 } at src/mongo/shell/mongo.js:148

exception: connect failed

Server is: CentOS release 6.4 (Final)

Port 28017 is usually the HTTP Console interface, not the port you should be using to connect via the shell.  Change the port to 27017 instead and retry.


Using plpgsql to remove all functions I intended to drop all functions in all schemas within a certain database, however, after I executed the bash file containing the following code block, the functions are still there

# use pg_dump and pg_restore to migrate some databases from another server

psql $DBNAME << EOF
DO \$$
DECLARE
    myschema RECORD;
    myview RECORD;
    myfunc RECORD;
    myseq RECORD;
BEGIN

    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE 'DROP VIEW ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myview.viewname) || ';';
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE 'DROP SEQUENCE ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myseq.sequence_name) || ';';
        END LOOP;

        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name )
        LOOP
            EXECUTE 'DROP FUNCTION ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myfunc.routine_name) || ';';
        END LOOP;

    END LOOP;

END;
\$$ LANGUAGE plpgsql;

After I partition the do block into three separate do blocks for dropping all views/functions/sequences, I managed to drop all sequences. I assume this is because the transaction must be atomic. However, because I intend to clean the databases and drop all unused functions/sequences/views, I cannot know ahead which are dependent ones and will raise errors. Can I ask plpgsql to ignore the error so that the unused views/functions/functions can be successfully dropped?

I revised my code to based the suggestions from @LaurenzAlbe. However, I got to error ERROR:  syntax error at or near "EXCEPTION". Did I place the EXCEPTION statements in the wrong place?

psql $DBNAME << EOF
DO \$$
DECLARE
    myschema RECORD;
    myseq RECORD;
    myview RECORD;
    myfunc RECORD;
BEGIN
    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP
        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name)
        LOOP
            EXECUTE format('DROP FUNCTION %I.%I', myschema.schema_name, myfunc.routine_name);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myfunc.routine_name;
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE format('DROP SEQUENCE %I.%I', myschema.schema_name, myseq.sequence_name);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myseq.sequence_name;
        END LOOP;

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE format('DROP VIEW %I.%I', myschema.schema_name, myview.viewname);

            EXCEPTION
            WHEN dependent_objects_still_exist THEN
            RAISE WARNING 'Could not drop object %.% because of dependencies', myschema.schema_name, myview.viewname;
        END LOOP;
    END LOOP;

END;
\$$ LANGUAGE plpgsql;

Your code is executed in a single DO statement and hence in a single transaction. So if any of the statements cause an error, all statements will be rolled back.

If you want to avoid that, you can use subtransactions by using an EXCEPTION clause, for example:

BEGIN
    EXECUTE format('DROP SEQUENCE %I.%I',
                   myschema.schema_name),
                   myseq.sequence_name);
EXCEPTION
    WHEN dependent_objects_still_exist THEN
        RAISE WARNING 'could not drop object %.% because of dependencies',
                      myschema.schema_name,
                      myseq.sequence_name;
END;

Then the failed statement is rolled back, but processing continues.

Another thing you could consider is using

DROP ... CASCADE

which will remove dependent objects automatically.


How to connect to microsoft azure? We have been saving the backups of one of our production servers (which is actually an Always On availability group)

this is how I am doing my backups and saving to azure:

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master
-Q "EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'SQLAG',
@URL = 'https://MYCOMPANYbackups.blob.core.windows.net/SQLMAINSERVER-datafiles-full',
@Credential = 'SQLAzureCredential',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@LogToTable = 'Y'" -b

This is what I see when attempting to connect to Azure:
what would be my storage account?

I have a job that I have scheduled to send me an email about all the backups on a server. The script that collect information about the backups and send the email is here.

The email tells me where the backup is.
for example:

here is a picture for illustration purposes:

How can I have access to the backup files?

Where are these azure permissions managed?

EDIT:

The answer by David Browne got me there, thank you David.

I am just adding here a set of steps that took me to my backups (after successfully connecting to azure).

01 - Click on Blob

02 - select one of the containers

03 - the files will be there - ready for dowloading

This article is also very interesting:

First look: back up files and folders in Resource Manager deployment

what would be my storage account?

MYCOMPANYbackups

  How can I have access to the backup files?

http://portal.azure.com
or
AzCopy
or
PowerShell
or
Azure Storage Explorer

  Where are these azure permissions managed?

Permissions for accessing the blobs is controlled by the storage account keys, or Shared Access Signatures.  See Azure Storage Security


ERROR: date/time field value out of range: "2020-06-0700:00:00" I am trying to run the Select query(linked server) from SSMS to get the data from PostgreSQL on AWS cloud. My query is running fine in SSMS but as soon as I enter the following line of code

and c.created_date >=concat(to_char(CURRENT_DATE - interval '7 day', 'yyyy-mm-dd'),' 00:00:00'):: timestamp

and c.created_date <= concat(to_char(CURRENT_DATE - interval '1 day','yyyy-mm-dd') ,' 23:59:59') ::timestamp

it starts giving me ERROR: date/time field value out of range: "2020-06-0700:00:00";

created_date field in my PostgreSQL is timestamp without timezone

Which datatype should I chose which is compatible with SQL Server?

You are overcomplicating things. concat() and to_char() are not needed and the root cause of the problem.

The start of the day 7 days back can be obtained using: current_date - 7. As that doesn't have a time part, a comparison with a timestamp column will automatically assume 00:00:00.

If you want to compare everything up to yesterday midnight, simply use < current_date.

So your expression can be simplified to:

and created_date >= current_date - 7
and created_date < current_date

Note the < instead of <= in the second condition.

If you want to be explicit about the time, you could use

and created_date >= (current_date - 7) + time '00:00:00'
and created_date < current_date + time '00:00:00'

But that's not needed and is absolutely identical to the first condition.

created_date < current_date is roughly equivalent to created_date <= (current_date - 1) + time '23:59:59' (which is your initial intention).

However, it's only roughly equivalent because your attempt isn't actually correct as it misses the time between 23:59:59.000000 and 23:59:59.999999, that's why it's better to use < with the next day's start.


Restore from copy of SQL Server backup file produces invalid data error We have an instance of SQL Server 2008 R2 SP2 sitting in a DMZ.  When I back up one of its databases to a drive local to that server, then run a RESTORE VERIFYONLY against the backup file, it declares the backup set valid.

If I subsequently robocopy the backup file to a mapped drive, pointing to a server located in our domain, then run a RESTORE VERIFYONLY (or a full RESTORE DATABASE, for that matter) against the copied backup file, while connected to a SQL Server instance which is also in the domain (running SQL Server 2008 R2 SP3), the subsequent RESTORE returns the following error message:

    Msg 3203, Level 16, State 1, Line 1
    Read on "<Backup file path>" failed: 13(The data is invalid.)
    Msg 3013, Level 16, State 1, Line 1
    VERIFY DATABASE is terminating abnormally.

This happens consistently and is repeatable.  Anyone have any ideas as to what might be corrupting the copied backup file?  Does robocopy feature any validation flags that might be useful?  What's the best way to troubleshoot this?

Since the local copy of the backup file is validated successfully via RESTORE VERIFYONLY, the most likely cause of the issue is corruption of the target file while it is being copied over the network.

You can determine if the two copies of the backup file are binary-identical with the command-line utility fc:

fc /b C:\some\local\path\file1.bak \\server\share\path\file2.bak

Alternately, you could generate a cryptographic hash of each file, and see if the hashes are identical.  Microsoft offers a command-line tool for that purpose at https://www.microsoft.com/en-us/download/details.aspx?id=11533

If both files are binary-identical, the restore should complete without error.


Can I run concurrent backups of multiple read only filegroups? I have 62 readonly filegroups in a SQL server 2008 enterprise database. Can I backup multiple read only filegroups at the same time? I would assume this to be the case but given this database is over 20TB in size I do not want to invalidate any files by giving it the ol' college try.

Yes you can do multiple full file-group backups concurrently. The primary concern I would have would be for disk I/O and network I/O throughput doing so many at once. They'll still complete, just all slowly. Unless you have the I/O to handle 20TB at once.


Performance recommendations stuck on 'Validating' I have been tasked with migrating a 'database from hell' to Azure SQL. It has some complicated and long-running queries (20 seconds on the S6 tier, >600 seconds on S4 tier) and I'd like to optimise performance so we can drop to a lower tier, without making any structural changes to the database.

I haven't explicitly created any indexes myself. I don't really have the skillset to do this properly as I'm not a DB admin.

I was hoping the the Azure performance recommendations would help by creating those indexes that are beneficial and avoiding any that would hurt performance. But so far results have been very disappointing. It has created one index resulting in just a 0.13% DTU improvement. It has rejected another index, and is currently validating two more although it looks like it will reject both (overall DTU regression).

But it has now been stuck on validating for nearly 24 hours, even though the database has been serving a normal workload all that time. (There are four different problem queries, invoked every ten minutes.)

Is this normal? Can coerce Azure into generating and validating performance recommendations a bit more quickly?

I found in the documentation that sometimes Azure will take 72 hours to validate a performance recommendation. And indeed after 72 hours it finished evaluating and reverted the recommendations.

(It turned out that identifying the indexes that would really make a big difference was quite easy and I didn't need Azure Performance Recommendations. I copied one of the long-running queries into SSMS, chose a representative set of inputs, and then ran Query > Display Estimated Execution Plan. The result showed me one step in the query was taking 99.9% of the time and it even suggested an index to add. Result was the query time came down from 20 seconds to 0.1 second. I realise this will not be news to DB admins but I hope it will help someone.)


mysqli::query(): (HY000/1194) I have a server on Azure with a MySQL database, we have two apps there and sometimes we get this error on the code:

Warning: mysqli::query(): (HY000/1194):
Table 'noticia' is marked as crashed and should be repaired
in D:\home\site\wwwroot\classes\ConnectionFactory.php on line 122

Some questions:

What kind of things could cause this error?
Where can I find the logs in order to get the cause of the error?
Could this be could a PHP problem?

This is too strange because I have been using the same ConnectionFactory class to connect to the database for several year and this is the first time that we get this error.

I assume this is a MyISAM table. These are not crash safe. InnoDB tables on the other hand, are.

There could be several reasons, such as:

disk failure
VM reboot or crash
mysql daemon crash - open MyISAM tables are likely to crash if the mysql daemon crashes

See also: Why do MySQL tables crash? How do I prevent it?
The MySQL error log is by default located in the datadir. By default, the file name is the name of the host with the suffix '.err'.
I don't think so, not unless PHP managed to crash the mysql daemon.

You may be able to repair the table by issuing a mysql command like:

REPAIR TABLE your_database_name.noticia;

Or, from the command shell, you can run something like this to check and repair all tables:

mysqlcheck -u root -p --auto-repair --check --all-databases


MySQL count multiple columns and sum the total occurrence I have the following table, say, MyDRAW ...

  DRAWNO    PRIZENO FIRSTD  SECONDD THIRDD  FOURTHD
M123/14     8458    8   4   5   8
M123/14     7214    7   2   1   4
M123/14     1640    1   6   4   0
M123/14     2103    2   1   0   3
M123/14     2557    2   5   5   7
M123/14     2975    2   9   7   5
M123/14     3767    3   7   6   7
M123/14     4409    4   4   0   9
M123/14     4887    4   8   8   7
M123/14     5844    5   8   4   4

I would like to count and sum them UP how many times of occurrence of digit from 0 to 9 (0,1,2,3,4,5,6,7,8,9) for 4 different differenct columns, which is FIRSTD, SECONDD, THIRDD, FORTHD ...

Expected result is ..

DRAWNO   [D0][D1][D2][D3][D4][D5][D6][D7][D8][D9]
M123/14  3   3   4   2   8   5   2   6   5   2

I managed to know how to select one Columns, and Question how to select all columns () and sum them up into TOTAL occurrence of digit 0 to digit 9?

..........

SELECT
DRAWNO,
FIRSTD,
COUNT(FIRSTD) AS COUNT1
FROM t4d
GROUP BY DRAWNO, FIRSTD
ORDER BY DRAWNO, FIRSTD

..........

Thanks.

This is a query with an idea like in MySQL count multiple columns and sum the total occurrence

SELECT
drawno,
SUM(CASE WHEN FIRST = 0 THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN FIRST = 1 THEN 1 ELSE 0 END) one,
SUM(CASE WHEN FIRST = 2 THEN 1 ELSE 0 END) two,
SUM(CASE WHEN FIRST = 3 THEN 1 ELSE 0 END) three,
SUM(CASE WHEN FIRST = 4 THEN 1 ELSE 0 END) four,
SUM(CASE WHEN FIRST = 5 THEN 1 ELSE 0 END) five,
SUM(CASE WHEN FIRST = 6 THEN 1 ELSE 0 END) six,
SUM(CASE WHEN FIRST = 7 THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN FIRST = 8 THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN FIRST = 9 THEN 1 ELSE 0 END) nine
FROM
(
  SELECT
    first
    ,drawno
  FROM table1
  UNION ALL
  SELECT
     second
     ,drawno
  FROM table1
  UNION ALL
  SELECT
    third
    ,drawno
  FROM table1
  UNION ALL
  SELECT
    fourth
    ,drawno
  FROM table1
) AS t GROUP BY drawno;

Two queries, this and from first answer:
http://sqlfiddle.com/#!2/2eb93d/2


Last value in two levels of group by in MySQL I have the following data set:

2018-01-02  Oranges 1479    1480
2018-01-03  Oranges 1478    1504
2018-01-04  Oranges 1507    1494
2018-01-05  Oranges 1510    1551
2018-01-02  Apples  2150    2196
2018-01-03  Apples  2161    2167
2018-01-04  Apples  2192    2169
2018-01-05  Apples  2169    2194

I'd like to get the following result:

2018-00 Oranges 1479    1551
2018-00 Apples  2150    2194

Where:

2018-00     Oranges   1479                        1551
year-week   grouped   first value third column    last value fourth column

Is there a way to do this in one query with MySQL?
My query actually is that:

select
    CONCAT(YEAR(date), '/', WEEK(date)) week,
    fruit,
    init,
    something_like_last_column_value_fn(end)
from fruits_flow
    where CONCAT(YEAR(date), '/', WEEK(date)) = '2018/0'
    and fruits in ('Oranges', 'Apples')
group by week, fruit;

I'm limiting in where clause only to test.

The third column value is easy because after group is the first value in line. But the last value of forth column, I really tried a lot of things and zero success.

SELECT DISTINCT
    CONCAT(YEAR(`date`),'/',WEEK(`date`)) as `week`,
    fruit,
    FIRST_VALUE(init) OVER (PARTITION BY fruit,`week`
                            ORDER BY `date` ASC) AS 'first',
    FIRST_VALUE(end) OVER (PARTITION BY fruit,`week`
                           ORDER BY `date` DESC) AS 'last'
FROM fruits_flow


Count children of a parent in recursive CTE I use this query to find comments by a parent id:

WITH RECURSIVE cte (id, content, path, parent_id, depth)  AS (
    SELECT  id,
        content,
        array[id] AS path,
        parent_id,
        1 AS depth
    FROM    comment
    WHERE   id = 1

    UNION ALL

    SELECT  comment.id,
        comment.content,
        cte.path || comment.id,
        comment.parent_id,
        cte.depth + 1 AS depth
    FROM    comment
    JOIN cte ON comment.parent_id = cte.id
    WHERE depth < 3
    )
    SELECT id, content, path, parent_id, depth FROM cte
ORDER BY path LIMIT 200;

It works well except I'm limiting the depth to 3. How can I find out if the current row has more children and if so get the count of them so I can display the number, ex. "Load X more".

What is the best way to go about this?

In this fiddle, id 4 and 5 have children, so I want to count them.

To count all children, there is no way around following each path to the last element. You can then show rows down to some depth while counting the rest:

WITH RECURSIVE cte AS (
   SELECT id, parent_id, content, array[id] AS path
   FROM   comment
   WHERE  id = 1

   UNION ALL
   SELECT c.id, c.parent_id, c.content, cte.path || c.id
   FROM   comment c
   JOIN   cte ON c.parent_id = cte.id
   )
SELECT DISTINCT ON (path[1:2])
       id, parent_id, content, path
     , count(*) OVER (PARTITION BY path[1:2]) - 1 AS children
FROM   cte
ORDER  BY path[1:2], path <> path[1:2]
LIMIT  200;  -- arbitrary limit, unrelated to the question

SQL fiddle.

path[1:2] is the array slice from the 1st up to the 2nd element. Details in the manual.

This works for a depth of 2. Replace all occurrences of 1:2 with 1:3 to do the same for a depth of 3 etc.

The main feature is to "group" by the first n elements of the path. Apply the technique laid out in this related question:

PostgreSQL: running count of rows for a query 'by minute'

The last ORDER BY expression path <> path[1:2] produces a boolean result that is only true for the root element of the group (where the whole path is equal to the leading elements), and FALSE sorts before TRUE. You could simply use path instead - not sure which performs better.


How to determine if SQL Server Reporting Services database is being used? I have a SQL 2005 server that needs to be decommissioned. For regular databases, I have a couple scripts that look at sys.dm_db_index_usage_stats to determine last access. My normal process is to make sure the last access date is back quite a while, then take offline and wait to be sure there are no problems before taking a final backup and then deleting the database.

However, since ReportServer and ReportServerTempDB are associated with SSRS, should they be treated differently? Is there an easier way to tell if SSRS is in use?

ExecutionLog databases seems to be the way to go:

SELECT COUNT(*) AS RptExecutions
FROM     ReportServer.dbo.ExecutionLog
        ,ReportServer.dbo.ExecutionLog2
        ,ReportServer.dbo.ExecutionLog3


When a record is locked in Oracle, can we know which record is locked? When a record is locked ,can we know which one is locked?

How can I get the record rowid or something else info?

I can get some info by this sql

SELECT c.ROW_WAIT_OBJ#,c.ROW_WAIT_FILE#,c.ROW_WAIT_BLOCK#,c.ROW_WAIT_ROW#
   FROM v$locked_object a, dba_objects b, v$session c
WHERE a.object_id = b.object_id
    AND a.SESSION_ID = c.sid(+)

I found a method in web to get rowid by using function DBMS_ROWID.ROWID_CREATE()

But it doesn't seem to work.

You can't really list all rows that are being locked by a session. However, once a session is being blocked by another, you can find which session/row is blocking it.

Oracle doesn't maintain a list of individual row locks. Rather, locks are registered directly inside the rows themselves -- think of it as an extra column.

You can find which session has acquired a lock on an object through the V$LOCK view, but this will only list general information, not at the row level.

With this view you can also find if a session is being blocked by another. In that case, if a session is blocked by another session, the row information is displayed in the V$SESSION information.

You can retrieve the rowid, let's build an example with 2 sessions:

SESSION1> create table test as select * from all_objects;

Table created

SESSION1> select rowid from test where object_name = 'TEST' for update;

ROWID
------------------
AAMnFEAAaAAALTDAAz

/* setting identifiers to help with identifying this session later */
SESSION2> exec dbms_application_info.set_client_info('012345');

PL/SQL procedure successfully completed

SESSION2> select 1 from test where object_name = 'TEST' for update;
/*  this will block */

Session 2 is now waiting on Session 1. We can discover the blocking row with:

SESSION1> SELECT o.object_name,
       2         dbms_rowid.ROWID_CREATE (1,
       3                                  s.ROW_WAIT_OBJ#,
       4                                  s.ROW_WAIT_FILE#,
       5                                  s.ROW_WAIT_BLOCK#,
       6                                  s.ROW_WAIT_ROW#) rid
       7     FROM dba_objects o, v$session s
       8    WHERE o.object_id = s.row_wait_obj#
       9      AND s.client_info = '012345';

OBJECT_NAME     RID
--------------- ------------------
TEST            AAMnFEAAaAAALTDAAz

For further reading: a description of the process by Tom Kyte.


Trigger in MySQL to prevent insertion I want to create the trigger which prevents insertion in the reservation table of flights (numflights) which does not exist.

Tables
----------------------------------------------
flights(numflight, origine, destination, dep, arr)
airports(code, city, country)
reservation(people, numflight, date_travel)

The columns origine and destination contain airport codes.

The following example is based on a jerry-rigged error trapping method in triggers from Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers':

DELIMITER $$

CREATE TRIGGER reservation_bi BEFORE INSERT ON reservation FOR EACH ROW
BEGIN

DECLARE found_count,dummy INT;

SELECT COUNT(1) INTO found_count FROM flights WHERE numflight=new.numflight;
IF found_count = 0 THEN
    SELECT 'Cannot Insert This Reservation Because Flight Number is Invalid'
    INTO dummy FROM reservation WHERE numflight=new.numflight;
END IF;

END; $$

DELIMITER ;

If there is other validation to be done, follow this same error trapping method!!!

UPDATE

The reason the book suggests preempting the Trigger in this manner stems from the fact that the MySQL Stored Procedure Language did not have SIGNAL implemented into the language (of course, SIGNAL is ANSI standard).

The authors of the book created work arounds by calling on SQL statements that are syntactically correct but fail at runtime. Pages 144-145 (Chapter 6: Error Handling) of the book gives these examples on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL emulation (Examples 6-19 and 6-20).

# Example 6-18. Using a nonexistent column name to force an error to the calling program

    CREATE PROCEDURE sp_update_employee_dob2
        (p_employee_id INT, p_dob DATE)
    BEGIN
        IF datediff(curdate(),p_dob)<(16*365) THEN
            UPDATE `Error: employee is too young; Employee must be 16 years or older`
                SET x=1;
        ELSE
            UPDATE employees
               SET date_of_birth=dob
            WHERE employee_id=p_dob;
       END IF;
    END;

# Example 6-19. Standard procedure to emulate SIGNAL

    CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
    BEGIN
        SET @sql=CONCAT('UPDATE `',in_errortext,'` SET x=1');
        PREPARE my_signal_stmt FROM @sql;
        EXECUTE my_signal_stmt;
        DEALLOCATE PREPARE my_signal_stmt;
    END;

# Example 6-20. Using our SIGNAL emulation procedure to raise an error

    CREATE PROCEDURE sp_update_employee_dob2
        (p_employee_id INT, p_dob DATE)
    BEGIN
        IF datediff(curdate(),p_dob)<(16*365) THEN
            CALL my_signal('Error: employee is too young; Employee must be 16 years or older')
        ELSE
            UPDATE employees
               SET date_of_birth=dob
            WHERE employee_id=p_dob;
       END IF;
    END;

Unfortunately, the SIGNAL emulation from Examples 6-19 and 6-20 cannot be done in Triggers. The book's authors used the technique in Example 6-18 to preempt triggers on pages 254-256. This is why the book's suggestion has to be the way to go.


Geographical replication zones in MySQL Cluster? In a default installation, data is shared indiscriminately across all data nodes.

Is it possible to specify that certain tables can only be replicated across a specific group of data nodes?

If so, do the tables need to be created on those nodes, or can they be migrated from other nodes?

In MySQL Cluster tables are spread among all nodes by default.
It is possible to specify a table to only reside on a subset of
the node groups by either setting the number of partitions or
by setting PARTITION_BALANCE (in 7.5). It is not intended to use
this for geographic replication.

It is not possible to specify exactly on which node group these
partitions will reside (at least I don't think, there was some
work in 5.1 to provide NODEGROUP when creating partitions, but
this is not fully supported, so a bit unsure what works here and
what doesn't).

For geographic replication MySQL Cluster supports replication from
one cluster to another cluster. This is using MySQL Replication and
can thus be done selective for certain tables. It is possible to
replicate different tables to different clusters by using multiple
MySQL replication servers inside one MySQL Cluster.


Restore fails on unencrypted database I tried to restore an unencrypted database to a different server and it failed with the error:

  "Cannot find certificate with thumbprint".

This suggests the database is encrypted but it is not.  There are other databases on this server that are encrypted, but not this one.

When I query the sys.dm_database_encryption_keys table the encryption_state for this database is 1 = Unencrypted.

I see other databases where the encryption_state value is NULL = Not Encrypted.

So I have 2 questions:

What the difference is between the values NULL and 1?
Why am I getting this error on an Unencrypted database?

Thank you for your time considering my question.

The error is potentially caused by this database being encrypted previously and had encryption disabled. If portions of the log contain encrypted data, the backup will also have this encrypted data and hence need the certificate.

Try clearing the log (backup the transaction log) then taking a full backup again and you should be able to restore the new backup.


What settings to use to export mysql table rows that I'll be importing to another table with data using phpmyadmin If I'm using phpmyadmin to manage mysql database, what settings should I use to export only two rows of data that I'll be importing to another table with the same name that contains many other rows?

I used the quick option, but I'm getting an error that table already exists and nothing gets imported.

If I use the default custom option with the default settings, I get the same error message.

So, what's the best settings to use to export settings that I'm going to import to another table?

You have two possibilities.

use the export at the botton of the table, after selecting you data with the correct data

see

use SELECT INTO OUTfile

LIKE

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table WHERE n= 1;

Please check the limitations in the link for secure_file_priv

You can after import it via phpmyadmin or use LOAD DATA Statement


Most efficient way to return multiple aggregates in a single stored proc? What is the best or most efficient way to get multiple aggregate result values?

Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, archived, flagged... ) as defined below.

Here is a sample of things I am trying to do in one stored proc call. (where [uid] is the userID)

SELECT * FROM Message
Inbox    WHERE [to]    = [uid]
Unread    WHERE [to]    = [uid] and isread = 0
Flagged    WHERE [to]    = [uid] and isFlagged = 1
Drafts    WHERE [to]    = [uid] and isDraft = 1
Sent Messages    WHERE [from] = [uid]
Archived Messages    WHERE [to]     = [uid] and isArchived = 1

COUNT..CASE is the usual way. For "Sent" though it's bit trickier because it's a different uid filter which requires 2 queries on the Message table

I've used this construct to allow for zero  row for to/from. It avoids an OR and adding a uid filter to each CASE.

Each derived table (can write as a CTE) will return only one row and I've used a MAX on uid to avoid GROUP BY too in case anyone wonders

SELECT
    ISNULL(M.Inbox, 0) AS Inbox,
    ISNULL(M.Unread, 0) AS Unread,
    ISNULL(M.Flagged, 0) AS Flagged,
    ISNULL(M.Drafts, 0) AS Drafts,
    ISNULL(S.Sent, 0) AS Sent,
    ISNULL(M.Archived, 0) AS Archived
FROM
    (
    SELECT
        COUNT(*) AS InBox,
        COUNT(CASE WHEN isread = 0 THEN 1 ELSE NULL END) Unread,
        COUNT(CASE WHEN isFlagged = 1 THEN 1 ELSE NULL END) Flagged,
        COUNT(CASE WHEN isDraft = 1 THEN 1 ELSE NULL END) Drafts,
        S.Sent,
        COUNT(CASE WHEN isArchived = 0 THEN 1 ELSE NULL END) Archived
    FROM
        Message
    WHERE
        [to] = [uid]
    ) M
    FULL OUTER JOIN
    (
    SELECT COUNT(*) AS Sent
    FROM Message
    WHERE [from] = [uid]
    ) S ON 1=1

Edit:

An aggregate without GROUP BY will always return a result (see SO here). I forgot that.

I've updated my SQL


Find out foreign key reference table How to find out all the foreign key reference tables for a table in Sybase by writing a Query .

The system provided sp_helpconstraint should do the trick.

If you want to write your own code then I'd suggest you take a look at the source code for sp_helpconstraint, see where/how it derives the constraint data, then write your own query(s) to generate the desired result set.

To find the source code for sp_helpconstraint you can either look in the installmaster script, or run exec sybsystemprocs..sp_helptext sp_helpconstraint,null,null,showsql.


Performance impact of Extended Events sessions looking at the exact same event type I have created an extended events session that watches the module_start event type and filters down based on the object_name: equal_i_sql_unicode_string]([object_name])
The purpose of this session is to simply record basic information whenever a proc in the filter list is called so that I can answer developers questions with 99.9% guarantee whether or not a proc is still called in production. The idea is to run this for ~1 month 24/7 (yes it doesn't account for things that are ran annually, but it is what it is).
The issue I am running into is that the list of procs the developer gave me is about 90 or so long and the filter list of an EE session is limited to 3,000 characters. The only idea I have come up with in order to increase the rate at which we can track the procs is to have 2 separate EE sessions that are identical except the filter predicates are different.
I am not asking "how much of a CPU impact will this be", but more or less is their any concern with running 2 of the same EE sessions with different filter predicates? It is odd to me that Microsoft would limit the filter list to 3,000 characters when 'more filtering == better performance' because the way EE is built into the engine it is very optimized unlike a trace that acts more like a proxy than a "trigger based off an event".
Is it safe to assume that whatever the performance impact of running 1 session is I can multiply it by 2 or is their further concerns I am not considering?

I'm not an expert on Extended Events by any means but I do know there is potential for performance implications if not done carefully. You'll only really be able to find out by testing and monitoring throughout the day if there's any meaningful impact on your current server / database's workload. I would think by filtering it down to specific entities which aren't necessarily even being used might be ok though.
An alternative idea is to modify each stored procedure such that it inserts into a table the name of the procedure and the current datetime.
For some other alternative solutions you can see this StackOverflow post's answers.
I would be careful using sys.dm_exec_procedure_stats if you go that route though, as it isn't 100% guaranteed to give you the results you're looking for, if an event has cleared out the plan cache.


Whether or not to create separate tables I am working on a project, where I need to store the information related to A and B, the A and B both have identical columns.There might be thousands of As and Bs. Should I use "type" column to differentiate between these two ? Is there any performance issue because each time it will require a full table search ?
I have 3 tables that are having similar structure for A and B.

Indexes which are not very selective are usually ignored by the query optimization engine.  If you have roughly equal numbers of A's and B's then an index on either won't help you.  If you want to filter by this type, then that part of the query will involve a scan.

However, if you don't have some more selective filter criteria on other columns, then you are looking at scans anyway, so there is no real additional penalty for keeping the A's and B's together.

Whether you keep them together or apart should depend on whether they are semantically related, not whether they have (coincidentally) identical columns.


PostgreSQL: When should I use fillfactor < 100 for tables on SSD? PostgreSQL documentation says that choosing smaller fillfactor lets table reserve space for future updates on the same page and

  "This gives UPDATE a chance to place the updated copy of a row on the
  same page as the original, which is more efficient than placing it on
  a different page."

Indeed this could improve UPDATE performance if cluster was stored on regular HDD drives, but mine is on SSD. As far as I understand setting fillfactor < 100 will not give me any performance benefit but only waste space on a (relatively) expensive storage.

Do you have a counterexample for my case?

You're addressing the performance problems that arise from random access, which as you point out is solved well enough by eliminating the cost of random access altogether with an SSD. However, postgres does offer "Heap Only Tuple (HOT) updates" (some notes here and here and here), which may benefit you from having a fillfactor less than 100% regardless of the physical medium in the case that you make updates moderately to frequently (the more frequent, the more the savings) and those updates do not affect any indexed columns.

Imagine if the fillfactor were exactly 100% and over time your table's data fills exactly one page. On the next update (assume it's of just one row), a copy of the updated row is made to the next page and the original row is left on the current page; if the transaction is committed the pre-update version of that row is marked as dead to be removed by a vacuum and the post-update version is marked as live. Now, let's say that update only affected a column "data_value" for a specific PK value, like update table_name set data_value = 1 where pk = 3. In this case, the heap location of the data changed pages, so the index supporting the PK constraint has to be updated to reflect that change. Regardless of the physical medium, that's a potential performance hit. Moreover, that dead tuple is left lingering until vacuum reclaims the space.

With HOT updates, as long as you don't update an indexed column, the database can avoid having to update index entries by keeping the post-update tuple on the same page as the pre-update table, which can speed up your updates. On top of that, HOT updates provide a bit of automatic maintenance and keep the page freer of those dead rows, reducing the need for vacuuming and auto-vacuuming.

Also, even in the case of data access, you might still benefit from having your data localized. Even if a random page read from an SSD takes a negligible amount of time, having to perform many reads is still (many * negligible time) more expensive than just reading the minimum amount of pages required to hold the data.


Debate on which id to use as reference when quering a one to many table I have a database that has a One to Many relationship. A few changes are in order and now when I query the output I need to provide just one ID field. The output contains all iterations of the one to many relationship so that if the parent has many child, the parent and each child will show up as one row in the output.

I'm debating how to handle giving the output an id, (or reference number) and I see that I have three options.

Option one

I can make a new table, or alter one I already have, which would give each Parent/Child pair it's own unique ID.

Option Two

Since it is a one to many relationship I could always just reference the child id, since that would be unique in it's own right.

Option Three

I can 'generate' an id on the fly which would Concat the parentid, and the childid, padding the childid with 4 leading zeros for later expansion.

Option One, Make a new table referencing the id of the Parent/Child Relationship

Pros

If you need to obfuscate the data, or generate a GUID, or a custom id
for an external department working with the data, this might be a valid option to consider.

Cons

It's not useful for any other situation that this first person can think of.

Option Two, Use the Child Id

Pros

The number is already there.
The parent id is assumed to be in the same table as the child id which is convienent
This is probably the simplest way to go.

Cons

Unlike option one, whoever receives the query will then know the child id. In most cases it's fine. But could potentially be a security risk?

Examples:

Option Three, Use the existing Parent and Child ID to generate a new one

Pros

You get an id number that has the data for the Parent and the Child
A query can generate this number on the fly, no need to store it.

Cons

Security wise you are giving away two id numbers
You can potentially reach a limit. Padding with zeros means that once there are enough entries in the database, which may or may not ever happen, you will hit the upper limit on the number you generate and thus potentially risk creating duplicate ids

Example

Parent id = [1 to 300]
Child id = [1 to 3000]
Concat = Parentid + string formated child id that always shoes 4 numerals (0000)

10001 First possible entry [1] + [000] + [1]
3003000 Last possible entry [300] + [3000]

If in the future the child has 10023 as an id you might get

    110023
    is this
    [1] + [10023]
    or is it
    [11] + [0023]

It may not matter if the result is always exported from the database, but if you need to return data to the database based on using these ids, then it might cause issues.

Interesting similar questions

Which is better: identity columns or generated uinique id values?


How to take undo datafile offline ,which is in recovery mode? I am trying to take undo datafile in offline mode using following command

alter database datafile '<datafile path>' offline;

this datafile is in recovery mode.  Oracle shows message as database successfully altered.

But after executing this command when I select entries in v$tablespace. The file that I just made offline remains in this table.

Can somebody please tell how to take undo datafile  offline.

OS: RHEL
Oracle version: 11g
Datafile status: Recovery

Shouldn't you be looking in v$datafile to see if a datafile is offline rather than v$tablespace?

Offline Datafile:

08:09:38 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
ONLINE  F:\ORACLE\ORADATA\CD\UNDO4.DBF

08:09:47 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' offline;
Database altered.

08:09:58 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
RECOVER F:\ORACLE\ORADATA\CD\UNDO4.DBF

08:10:02 coredev>select * from v$tablespace where name='UNDO';
       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
        33 UNDO                           YES NO  YES

Online Datafile:

08:10:53 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online;
alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online
*
ERROR at line 1:
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: 'F:\ORACLE\ORADATA\CD\UNDO4.DBF'

08:11:04 coredev>recover datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF';
Media recovery complete.

08:11:13 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online;
Database altered.

08:11:49 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
ONLINE  F:\ORACLE\ORADATA\CD\UNDO4.DBF


DML operation against synonym oracle I have been reading the GoldenGate 11g R2 installation guide and it states:

Non-supported objects and operations in Oracle DML:
[...]
Synonyms
[...]

But I see in the next section that DDL operations are supported, so CREATE, ALTER, etc...
So, what is exactly a DML operation against a synonym and how is it not supported?

From reading the documentation, it looks like DML on a synonym for a normal object will not be replicated. For example, you have a table called FOO & a synonym called BAR that points to FOO. An INSERT into FOO will succeed, but an INSERT into BAR will not, as it is a synonym for another table & not a real base table.

Test this in your dev environment to verify.


Group_by and having vs Joins when table is self joined 5-6 times Now the situation due to which this question came to me.
Schema is

    Table User_Read_Book
       user_id | book_id

Now I want to get users who have read certain books. Say give me users who have read book number 1 and 2. Number of books to test can go upto 10.

First query I wrote :

Select user_id from User_Read_Book Where book_id In (1,2) Group by user_id Having count(book_id) = 2

Second query:

Select  user_id from User_Read_Book as U join User_Read_Book as U1 On
 U.user_id = U1.user_id And U1.book_id = 1 where U.book_id = 2

As said in this answer https://stackoverflow.com/a/621891 that prefer joins in case of group by and having I did the second query.

But my question is which is better query when number to match up are large. Say when you have to find users who have read 7 books

   Having Count(book_id) = 7
   or
   6 joins to the same table.

I know this question is best answered when tested on large and real time data.
Still what is the opinion of experts on this?

With 7 books, my guess would be that 7 joins is faster than GROUP BY / HAVING.

But it depends on the DBMS, the version, the optimizer's settings, the database settings, the RAM you have, the hard disks' performance, the indexes fragmentation, the overall pressure on the server and possibly several other parameters. Even more, even all the previous are set still, it depends on your data (and its distribution) and the specific parameters of the query. For example, if the 7 books are the 7 books of Harry Potter and all your users are Harry Potter's fans, then the GROUP BY/HAVING may be faster.

Plus, you shouldn't trust others, however experts they may (look to) be, when you can test. Why don't you test - with your data in your server and your settings - the performance of both ways, using variable number of books (and titles)?

Check also this question (with a similar query) where several other (more than 10) ways are shown (and benchmarked in PostgrSQL): How to filter SQL results in a has-many-through relation

UPDATE

explanation of the "guess" that 7 Joins are usually better than GROUP BY / HAVING:

Imagine you have a million users and about a million books. Now, on average, a user has read 100 books (in your database, totally fictional data and distribution). So, the table has about 100M rows.

Now, the GROUP BY query would have something like WHERE book_id IN (1,2,3,4,5,6,7). Lets assume that book_id=1 is the most popular (the Bible) and has about 100K readers and the other 6 are not so popular, having between 100 to 1000 readers each. This would limit the rows to be grouped to something between 100K and 106K. That translates (roughly) to the SQL engine reading 106K data from the proper index and then doing the GROUP BY user_id. So, (it will probably choose to use the (user_id, book_id) index), and it will do about 100K calculations of the COUNT(book_id) - and reject anyone that is not 7.

In the 7 JOIN query, it has more options. The optimizer may choose to use the other index, the (book_id, user_id) one. Imagine "taking out" 7 smaller parts of this big index, the (1, user_id) part (remember: 100K data (user_ids) in it), the (2, user_id) part (less than 1000 data in here), ..., up to the (7, user_id) part (less than 1000 data in here, too). So now, it has to somehow combine those 7 index-parts (which is just 7 lists of userids) and find which userids are in ALL of the 7 lists. There are some clever algorithms that do just that, without having to do a whole reading (full scan) of the 7 lists. Just notice that even a dumb algorithm that combines first the 6 smaller lists, may end up with only a handful of userids (lets say just 1). To find if this 1 user_id is in the big (first) list, only a binary search is needed (remember it's not really a list, it's an index and that's what is good about indexes, you can search fast in them). So, even if there are only 100 user_ids, 100 searches in the big 100K list/index will only need less than 100*17 operation ( log(100K) ~= 17 ). Which is 1700 operations, much less than the GROUP BY 100K operations. And no COUNT(*) is needed.

Therefore, with the Joins, if most books are not very popular (or just one book and we get lucky), the query will be quite efficient because it will have to look at the index on a very small number of places.
(Another thought is that with the Group By method, the query has calculated - before rejecting them - how many books have read all those users that have read 1 or 2 or ... or 6 books. But we don't care if they read 1 or 6. We just need to know if they have read all 7 !)

The situation is different off course if all the 7 chosen books are very popular. Now, the 7 index parts are all big and combining them may be less efficient than using the GROUP BY method which uses only one pass at one index.
(And the other thought says that the Group By is efficient now because almost all Count calculations will be a 7, so a very small number of calculations is wasted)


Occasional error shrinking transaction log files I have a transaction backup job which runs every hour.

First it runs a backup of the database log to another drive, then it shrinks the log and deletes old .trn files.

The middle task shrinks the log fle, using this code:

USE myDB
DBCC SHRINKFILE('myDB_LOG', 0, TRUNCATEONLY)

However, sometimes I received the following error.

  Description: Executing the query "USE myDB  DBCC
  SHRINKFILE('myDB_LOG'..." failed with the following error: "Backup,
  file manipulation operations (such as ALTER DATABASE ADD FILE) and
  encryption changes on a database must be serialized. Reissue the
  statement after the current backup or file manipulation operation is
  completed.  Changed database context to 'myDB'.". Possible
  failure reasons: Problems with the query, "ResultSet" property not set
  correctly, parameters not set correctly, or connection not established
  correctly.  End Error  DTExec: The package execution returned
  DTSER_FAILURE (1).  Started:  22:00:19  Finished: 22:04:17  Elapsed:
  238.26 seconds.  The package execution failed.  The step failed.

Why is this happening?

Is there a better way to backup and shrink the database log?

There is NO NEED to run shrinkfile command after taking transaction log backup. What is the need of shrinking a log file which would eventually grow again? This is a blunder in my opinion considering the fact that you are doing it every day.

Whenever the log file grows, transactions have to wait for this event to complete so SQL server becomes still until this growth happens and considering the fact that instant file initialization is not there for log files it takes time for these growth events to complete, in turn hampering performance.

Regarding error message

  Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed

This means that you were trying to run dbcc shrinfile command when some backup operation was running on the database. It can be any backup full,differential and transaction log. All this message is saying that please try running shrikfile command when backup operation has completed. Now you have to find what backup was running at that time. See errrlog or msdb job history you could find more details.

If you refer to BOL

  Operations that cannot run during a database or transaction log backup
  include:

  • File management operations such as the ALTER DATABASE
  statement with either the ADD FILE or REMOVE FILE options.

  • Shrink database or shrink file operations. This includes auto-shrink
  operations.


Postgres – how to alter the WHERE clause of a postgres index I need to insert a new two-field index on an existing table with about 600 million rows in it. The table contains data accumulated over about a year with approximately 2 million new rows added per day.

I know adding a new index to one very large table in one go will take ages, so to save time I started by only indexing the last few days worth of data using the following command…

CREATE INDEX  idx_001
ON message (deviceNumber, messageTime)
WHERE messageTime >='07-May-2018 00:00:00' ;

This took about 3 hours, or about 40 minutes to index one day.

Ideally I’d like to index the whole table, but at 40 minutes for a days data doing it on one go will take over a week.

I guess I could create lots of small indexes, each for a few days of data, maybe something like this…

CREATE INDEX  idx_002
ON message (deviceNumber, messageTime)
WHERE messageTime <'07-May-2018 00:00:00' AND messageTime >='01-May-2018 00:00:00'  ;

But I’d prefer to stick with one index. Is there any way of ALTERing the where clause of the existing index to increase the days covered? Or do I have to create lots of small indexes?

No, you cannot alter an index. Because, even if PostgreSQL allowed this, it would need to rebuild the index anyway. Unless it implemented very smart checks on the WHERE clause, which would serve quite rare use cases.

Having many indexes is also not optimal. Every time you run a query, the planner has to consider all existing indexes and pick up the best one. If you have several indexes, the planner takes more time. Not ages - but still, this delay affects each and every query involving that table.

My suggestion is... be patient and build a big index. You can use CREATE INDEX CONCURRENTLY to avoid locks.


MariaDB Update Column Values Based on Another Column's Value I have a column 'StormID' 'Basin' which both hold text based values.

StormID   Basin
EP012018   NEP
EP022018   NEP
CP012018   NEP <--  I want this value to change to NCP if the first 2 characters are 'CP' in the StormID column.

Thanks for any suggestions.
Regards,
Bryan

You can use left() to get the first n characters of a string.

UPDATE elbat
       SET basin = 'NCP'
       WHERE left(stormid, 2) = 'CP';


Data loading problem with csv file from staging server to Database I am trying to load data into oracle database from csv file using below statement
CREATE TABLE EDWOPS.SAD_344_478_Excld_list
(
  MSISDN  VARCHAR2(1000 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY STAGING_HW_EDWOPS
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE  FIELDS TERMINATED BY ',' optionally enclosed by
        MISSING FIELD VALUES ARE NULL
        )
     LOCATION (STAGING_HW_EDWOPS:'DS_AT_47_20_AND_DS_AT_D_34_Nov_20_1_Exclude_list.csv')
  )

Error I am getting is  given below
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "missing": expecting one of: "double-quoted-string, hexprefix, single-quoted-string"
KUP-01007: at line 2 column 9

My csv data format is like
1234567
2338848
2338888
.......

There are almost 6M entry in my csv file.

The error has nothing to do with the content of the file.  The key message is:
syntax error: found "missing":

Literally, when parsing the control commands it found the word "missing" where it was expecting something else.  So what was immediately preceding the word "missing"??  This:
 FIELDS TERMINATED BY ',' optionally enclosed by
        MISSING FIELD VALUES ARE NULL

So, what are the fields 'optionally enclosed by' ????


Use BIGINT and twice the storage space or INTEGER but with extra operations? Suppose I've a table named agency with some columns:
  internal_id(integer, unique)
, external_id(bigint, unique)
, name, location, created_at, ...

internal_id and external_id are each one unique and candidates for being as the primary key column.
There are some other tables (says A, B, C, D, E) that reference to this table. Suppose each of these tables may contains millions or billions of rows.
Normally I have the external_id when I need to filter the tables A, B, C, D, E data.
Which of the following scenarios are the best way to go, considering performance and storage space:

Use internal_id as primary key in agency, and as foreign key in other tables. Because this field takes 4 bytes of storage space, we can save billion of bytes. However as I normally have the external_id, I have to do an extra JOIN for each query as a penalty:

    SELECT A.* FROM A
         INNER JOIN agency ON A.internal_id=agency.internal_id
    WHERE agency.external_id=5;

Use internal_id as primary key in agency, and as foreign key in other tables. But to get rid of an extra JOIN, in my application I could first map external_id to internal_id with a simple query (SELECT internal_id FROM agency WHERE external_id=5), and then use the fetched internal_id for another simple query:

    SELECT * FROM A
    WHERE internal_id=59; -- 59 is the fetched internal_id from the other query

Does it have better performance than JOIN considering an extra round trip between app and database?

forgetting internal_id and use external_id as the primary key and foreign key, with the penalty of 4 more extra bytes per record in each other tables (A, B, C, D, E) and cost of billions of more storage space or potentially even slower database operations (because of bigger database files):

    SELECT * FROM A
    WHERE external_id=5

Update:

agency table may contains 10s of thousands or at most a few millions of rows.
internal_id and external_id will not change over time, but other non-identity columns may rarely change.
There are about 5 to 7 related tables (A, B, C, D, E, ...) that a few of them may get too large over time, say a few million rows per day (billions of rows over a year)

The third option for the reason you outline: you do not have to query agency every time.  It's not that the join/lookup would be particularly expensive for queries that return a small amount of rows, but:

Your database is easier to work with
Your code is more concise and easier to debug
It's easier to optimize queries that return more than a few rows
Avoids the overhead of the meaningless internal_id on the agency table.

That's certainly worth 4 bytes/row.  We're not storing data on tape any more, it's not as big of a consideration as it used to be.

You are right, but how about when the related tables contains trillions of rows. So for 1 trillion rows, it takes more 4TB data. Doesn't it affect the overall performance of database?

If you are reading the whole table, yes.  But most of the time we look for a few dozen/hundred rows at most.  And why extend to "trillions" of rows?  If you're dealing with that volume the hardware required isn't going to break due to an extra 4TB in the heap.


Concatenate two columns into one column in a view in MySQL I am trying to create a view and would like to display the firstName and lastName columns from a table as one column (User) in a view.

The view is using a left join. What I would like to do is join on userID from tableA with concat of firstName and lastName from tableB.

Is this possible in MySql? I have seen some proposed solutions for SQL Server.

Instead of displaying emailAddress column as per below view, I want to display a concat of firstName and lastName columns.

create or replace view document.Documents
as
select
    d.documentID
    ,cu.emailAddress as createdByUser
from document.Document as d
        left join customer.User as cu
            on cu.userID = d.recCreatedByUserID

Yes, use concat or concat_ws:

CREATE VIEW myview AS
  SELECT concat_ws(' ', firstName, lastName) as "User"
  FROM mytable;


how to access my password in SSIS 2005 by VB.NET A VB.NET component delivered following message:

  Login failed for user MyDBUser

It is about an OleDbConnection.

btw The user works fine togehter with SQL Server Management Studio.

Retaining a password via the connection manager does not work even with enabling Save my password.
In my case, it is not a security issue to retain the password because the SSIS package will not be published for others.
Hence, what's the fastest way -concerning the VB.NET code- to enable an access to the (SQL Server) password?

EDIT:
Workaround: A SSIS variable was attached to the scope of the required control flow.
Me.readVariable was applied to concat the connection string.

Given your symptoms, I believe you have changed your PackageProtectionLevel from the default setting of EncryptSensitiveWithUserKey to DontSaveSensitive This is a good thing in my world view but it does require that you now manage sensitive data.

The hackish approach you are using is to create a password Variable and then use that in an Expression as part of the Connection String. That works but you are now storing a password in clear text which is what the default PackageProtetionLevel was designed to protect against.

Fixing it "right" in my mind would involve storing that password as an external, secured resource. We always used SQL Server configurations in our packages and extended the base table with a column indicating whether this configuration was sensitive or not. In production, we, the developers, only had access to rows that were set as insensitive. The service account which ran all the packages had no such restrictions.

Alternate approaches are to store these configuration values in an XML file, registry key, environment variable or a parent package.

You can also skip the configuration route if you know your packages will only ever be launched through method X (batch file, Agent Job, etc). In that case, simply provide the password as a run-time value to the invoking process and all will be well.


What process(s) writes to ALERT log file in Oracle? Team:
I have been searching for the process(s) which write to alert log file but couldn't find any reference to the documentation. I believe the background processes write to alert log. Not sure if it's correct. Also, does a server process write to alert log as well? We see a lot of entries in the alert log which states some info is written to trace file abc.trc etc, I was wondering if the particular background process involved with that error write inti that particular trace file.

Any process can write in the alert log.
Even you can write in the alert log using the PL/SQL DBMS_SYSTEM package.
Trace file names clearly indicate which process they belong to. Trace files are named like ORCL_smon_1234.trc, where ORCL is the name of the instance, smon is the name of the server process, 1234 is the OS PID of the process.


Naming foreign key columns Now I hear that the best practice for naming foreign key columns is the name of the table and field (and maybe the function if there are multiple foreign keys to the same table), for example, a foreign key that references the users table id column would be:

creatorUserId

Now the thing is that right now I am building my application and for the sake of development speed, I am going to be referencing everything by a surrogate foreign key (INT AUTO_INCREMENT).  I really don't want to get into the whole which is faster than what in which case this early in development, I rather leave that stuff to the end (to try to prevent premature optimizations and I am more interested in working on the front-end of this application then the backend).

With that being said, a concern I have is if I in-fact want to change some of the foreign keys from the surrogate key to a natural key (all relavent tables will have both) and I use column names like

creatorUserId

I am going to have to go through all the backend code in order to make the change, a process that would not be very pleasant.

I was thinking about doing something that just use the function and table name for foreign keys column like:

creatorUser

In the case, if I change what column I am using for the foreign key, the backend code changes are a lot simpler and there would be a lot less of them (if you are using an ORM which I am).  Also, since surrogate keys are generally numbers and natural keys are generally alpha characters, it should still be relative easy to know which one is being used just by looking at the value.

Would this be considered bad practice?

The name "creatorUser" fails when there is a multi-column foreign key relationship.

As with the other posters here, I strongly support using the same column name for the same attribute, regardless of what table it is in.

Imagine a table of books, called "Books". If the primary key is "ISBN" then the foreign key in referencing tables should be "ISBN" - not "BookID" or "BOOKISBNID" or "BookISBN".

My reference for questions like these is Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems).


Modelling constraints on subset aggregates? I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and moreover, that solutions for them may inspire solutions for me, so don't consider this PostgreSQL-specific.

I know I am not the first to try to solve this problem so I figure it is worth asking here but I am trying to evaluate the costs of modelling accounting data such that every transaction is fundamentally balanced. The accounting data is append-only. The overall constraint (written in pseudo-code) here might look roughly like:

CREATE TABLE journal_entry (
    id bigserial not null unique, --artificial candidate key
    journal_type_id int references  journal_type(id),
    reference text, -- source document identifier, unique per journal
    date_posted date not null,
    PRIMARY KEY (journal_type_id, reference)
);

CREATE TABLE journal_line (
    entry_id bigint references journal_entry(id),
    account_id int not null references account(id),
    amount numeric not null,
    line_id bigserial not null unique,
    CHECK ((sum(amount) over (partition by entry_id) = 0) -- this won't work
);

Obviously such a check constraint will never work. It operates per row and might check over the entire db. So it will always fail and be slow doing it.

So my question is what is the best way to model this constraint? I have basically looked at two ideas so far. Wondering if these are the only ones, or if someone has a better way (other than leave it to the app level or a stored proc).

I could borrow a page from the accounting world's concept of the difference between a book of original entry and a book of final entry (general journal vs general ledger).  In this regard I could model this as an array of journal lines attached to the journal entry, enforce the constraint on the array (in PostgreSQL terms, select sum(amount) = 0 from unnest(je.line_items).  A trigger could expand and save these to a line items table, where individual column constraints could more easily be enforced, and where indexes etc could be more useful.  This is the direction I am leaning.
I could try to code a constraint trigger that would enforce this per transaction with the idea that the sum of a series of 0's will always be 0.

I am weighing these against the current approach of enforcing the logic in a stored procedure.  The complexity cost is being weighed against the idea that mathematical proof of constraints are superior to unit tests.  The major drawback of #1 above is that types as tuples is one of those areas in PostgreSQL where one runs into inconsistent behavior and changes in assumptions regularly and so I would even hope that behavior in this area might change over time.  Designing a future safe version is not so easy.

Are there other ways to solve this problem that will scale up to millions of records in each table? Am I missing something?  Is there a tradeoff I have missed?

In response to Craig's point below about versions, at a minimum, this will have to run on PostgreSQL 9.2 and higher (maybe 9.1 and higher, but probably we can go with straight 9.2).

As we have to span multiple rows it cannot be implemented with a simple CHECK constraint.

We can also rule out exclusion constraints. Those would span multiple rows, but only check for inequality. Complex operations like a sum over multiple rows are not possible.

The tool that seems to best fit your case is a CONSTRAINT TRIGGER (Or even just a plain TRIGGER - the only difference in the current implementation is that you can adjust the timing of the trigger with SET CONSTRAINTS.

So that's your option 2.

Once we can rely on the constraint being enforced at all times, we need not check the whole table any more. Checking only rows inserted in the current transaction - at the end of the transaction - is sufficient. Performance should be ok.

Also, as

  The accounting data is append-only.

... we only need to care about newly inserted rows. (Assuming UPDATE or DELETE are not possible.)

I use the system column xid and compare it to the function txid_current() - which returns the xid of the current transaction.
To compare the types, casting is needed ...
This should be reasonably safe. Consider this related, later answer with a safer method:

How to view tuples changed in a PostgreSQL transaction?

Demo

CREATE TABLE journal_line(amount int); -- simplistic table for demo

CREATE OR REPLACE FUNCTION trg_insaft_check_balance()
    RETURNS trigger AS
$func$
BEGIN
   IF sum(amount) <> 0
      FROM journal_line
      WHERE xmin::text::bigint = txid_current()  -- consider link above
         THEN
      RAISE EXCEPTION 'Entries not balanced!';
   END IF;

   RETURN NULL;  -- RETURN value of AFTER trigger is ignored anyway
END;
$func$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER insaft_check_balance
    AFTER INSERT ON journal_line
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE PROCEDURE trg_insaft_check_balance();

Deferred, so it is only checked at the end of the transaction.

Tests

INSERT INTO journal_line(amount) VALUES (1), (-1);

Works.

INSERT INTO journal_line(amount) VALUES (1);

Fails:

  ERROR:  Entries not balanced!

BEGIN;
INSERT INTO journal_line(amount) VALUES (7), (-5);
-- do other stuff
SELECT * FROM journal_line;
INSERT INTO journal_line(amount) VALUES (-2);
-- INSERT INTO journal_line(amount) VALUES (-1); -- make it fail
COMMIT;

Works. :)

If you need to enforce your constraint before the end of the transaction, you can do so at any point in the transaction, even at the start:

SET CONSTRAINTS insaft_check_balance IMMEDIATE;

Faster with plain trigger

If you operate with multi-row INSERT it is more effective to trigger per statement - which is not possible with constraint triggers:

  Constraint triggers can only be specified FOR EACH ROW.

Use a plain trigger instead and fire FOR EACH STATEMENT to ...

lose the option of SET CONSTRAINTS.
gain performance.

DELETE possible

In reply to your comment: If DELETE is possible you might add similar trigger doing a whole-table balance check after a DELETE has happened. This would be much more expensive, but won't matter much as it rarely happens.


How do you structure a database that allows for groups to be formed/added by users and also be a part of like facebook? I'm new to databases and web development so a laymen explanation is best for the time being. I'm only in the data modeling phase so no need for specific code. I'm just trying to wrap my head around how the data would be managed and I'll get around to figuring out code when the time comes. I already understand the concept of OneToMany and ManyToMany, so no need to explain that to me.

Basically, what I'd like to do is for users to be able to create a group and name it whatever, and also have a list of already existing groups for a user to add themselves to, similar to the way Facebook's "interests" works. Users can type in whatever interest they want and if it already exists, they get added to a group of people who have that interest.

Which tables need to be created? Obviously I'll need a UserTable, InterestTable... anything else? How would the tables relate to one another? What keys would each table need?

You need three tables - User, Interest and an associative table for the many-to-many relation between them (a User can have many Interests and an Interest will have many Users), call it UserInterestLink or something. User and Interest will have autoincrement int fields as a surrogate primary key, UserInterestLink will have FKs to both User and Interest, like so:

|=================================================|
| TABLE: User                                     |
|=================================================|
| Name       | Type                               |
|=================================================|
| UserID     | int, autoincrement, PK             |
| << Other User fields here >>                    |
|=================================================|

|=================================================|
| TABLE: Interest                                 |
|=================================================|
| Name       | Type                               |
|=================================================|
| InterestID | int, autoincrement, PK             |
| << Other Interest fields here >>                |
|=================================================|

|=================================================|
| TABLE: UserInterestLink                         |
|=================================================|
| Name       | Type                               |
|=================================================|
| InterestID | int, FK to Interest.InterestID     |
| UserID     | int, FK to User.UserID             |
|=================================================|

I didn't mention the PK on UserInterestLink, for efficiency it would be a compound PK on (UserInterestLink.InterestID, UserInterestLink.UserID), but there's also an argument to be made for a seperate surrogate primary key (which I personally think is a waste of space, unless you need to allow for multiple UserInterestLinks between one User and one Interest). For a purely associative table in a many-to-many relationship, though, the compound primary key should work fine.

Edit: The complicated part of this isn't in the relational design, it'll be in the application code, because you'll need best-match/partial matching in order to show users the groups that most closely match their interests (rather than having accidental splinter groups all over the place). And if you want to out-Facebook Facebook, you'll need a smoother and better user experience than they offer.


The following did the trick. Laravel raw expressions.

DB::raw('GeomFromText(\'POLYGON('.$polycoords.')\')


MySQL setting that affects memory I'm trying to setup a linux server with low memory < 1G+- available, so the goal is not to exceed 512mb of total mysql usage. I have below simple setup:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd

max_connections=1501 #we only use up to 200, setting this to avoid too many conn error
innodb_flush_log_at_trx_commit=0 #we are not doing data critical application, we will sacrifice this to hit higher throughput, as we focus more on throughput
innodb_buffer_pool_size=128M
innodb_log_file_size=64M
max_allowed_packet=2G #due to large queries during data migration
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

As you can see, the buffer pool size is only configured to 128M, and file is at 64m. innodb_buffer_pool_instances is 1 by default, checked this using show variables too.

But based on my TOP graph, the mysql will occasionally use up to 700MB or my RAM, does anyone have a good guess what went wrong?

Thank you.

UPDATE:

SHOW VARIABLES LIKE '%buffer%';

Variable_name   Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_chunk_size   134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    1
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  ON
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 134217728
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_log_buffer_size  16777216
innodb_sort_buffer_size 1048576
join_buffer_size    262144
key_buffer_size 8388608
myisam_sort_buffer_size 8388608
net_buffer_length   16384
preload_buffer_size 32768
read_buffer_size    131072
read_rnd_buffer_size    262144
sort_buffer_size    262144
sql_buffer_result   OFF

SHOW GLOBAL STATUS LIKE '%buffer%';

Variable_name   Value
Innodb_buffer_pool_dump_status  Dumping of buffer pool not started
Innodb_buffer_pool_load_status  Buffer pool(s) load completed at 190216  0:54:10
Innodb_buffer_pool_resize_status
Innodb_buffer_pool_pages_data   5230
Innodb_buffer_pool_bytes_data   85688320
Innodb_buffer_pool_pages_dirty  0
Innodb_buffer_pool_bytes_dirty  0
Innodb_buffer_pool_pages_flushed    340624
Innodb_buffer_pool_pages_free   2903
Innodb_buffer_pool_pages_misc   58
Innodb_buffer_pool_pages_total  8191
Innodb_buffer_pool_read_ahead_rnd   0
Innodb_buffer_pool_read_ahead   128
Innodb_buffer_pool_read_ahead_evicted   0
Innodb_buffer_pool_read_requests    1251760651
Innodb_buffer_pool_reads    4327
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   2331193

low memory < 1G+- available, so the goal is not to exceed 512mb of total mysql usage

I'll assume 0.5GB.

max_connections=1501 #we only use up to 200, setting this to avoid too many conn error

That will chew up a lot of RAM that you do not have.  I would set it to no more than 50 and then work on making sure the applications throttle back how many connections they ask for.

If you have more than, say, 10 active threads, you could encounter them stumbling over each other -- this leads to performance issues of its own.

innodb_buffer_pool_size=128M

This is probably all you can afford in that tiny RAM.  More than that will lead to swapping; less than that could lead to cases where it simply can't get the job done.

innodb_log_file_size=64M

That's a disk file limit; it has no impact on RAM allocation.

innodb_log_buffer_size  16777216

is in RAM.  Cut that back to 4M.

The 80% rule is fine if you have more than 4GB of RAM.  A better rule is "80% of available RAM".  The issue is that you need the OS, the mysql code, and many other caches, buffers, etc.

max_allowed_packet=2G #due to large queries during data migration

Duh?  That is allocated in RAM, but you don't have that much RAM??  Imagine how much swapping will happen!  If you are loading a dump from mysqldump, rebuild the dump so that it does not build huge INSERT statements.  If you have 2GB BLOBs, you may be in big trouble.  Then cut that back to 32M (I guess).

innodb_buffer_pool_chunk_size is relevant only if you dynamically resize the buffer_pool.  (You are not likely to do this.)

Toss the query cache:  query_cache_size = 0 and query_cache_type = OFF.

tmp_table_size and max_heap_table_size -- Set to 1M.  These may be allocated once per connection or even more often (in the case of a complex SELECT).  MySQL has a fallback if these are "too small", so you won't lose functionality.

sort_buffer_size -- 4M

There may be more.


Linked tables change during dump Having difficulty cloning a live DB.  In the table structure i have a number of tables that extend are linked from one another, they are quite big and the names means the tables aren't very close in the listing.

An example of what happens when I mysqldump is the last record in table A has ID 123123 then by the time the dump gets to table Z, the ID's in the live system have jumped to 123234.  The knock on effect is that when I try to use my newly cloned DB in my development environment I get a lot of duplicate ID errors.

A few questions, is there a way to lock groups of tables for a dump (don't want to lock the whole db as its ~5GB and its too long to be offine) Or is there a nice way to clean up orphaned IDs that get created this way.

InnoDB?  Or MyISAM?  Or a mixture?

If MyISAM, why are you not moving to InnoDB?

Look at the arguments to mysqldump.  One says to lock all the MyISAM tables for the duration of the dump.  (This, of course, is nasty for updates/inserts.)  Another says to "lock" InnoDB tables as a --single-transaction.  (This is not so nasty.)

Xtrabackup (see percona.com) is an alternative to mysqldump and an excellent way to backup a live system of InnoDB tables.  So is MySQL Enterprise Backup ($$).


Tables with engines of "MEMORY" does not get dropped when server is restarted I read a description from When to Use MEMORY or NDB Cluster
from the MySQL website.

  Operations involving transient, non-critical data such as session
  management or caching. When the MySQL server halts or restarts, the
  data in MEMORY tables is lost.

but my created tables with ENGINES = MEMORY were not dropped upon mysql server restart.

CREATE TABLE MY_TEMPORARY_TABLE (
  COLUMN_1 varchar(255) DEFAULT NULL,
  COLUMN_2 varchar(255) DEFAULT NULL,
  COLUMN_3 varchar(255) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

Every table created with CREATE TABLE will create a .frm file followed by other files (such as .MYD and .MYI for MyISAM or .ibd for InnoDB).

When it comes to a table created with ENGINE=MEMORY will create only the .frm file and actual data and index storage will occur in RAM.

If mysqld crashes, the data will be lost, but the .frm will remain.

In your case, you ran CREATE TABLE MY_TEMPORARY_TABLE. This will create a file called MY_TEMPORARY_TABLE.frm.

Had you created the table using CREATE TEMPORARY TABLE MY_TEMPORARY_TABLE, then the table itself would disappear upon mysqld crash or shutdown.


Transition to innodb_file_per_table for 30+ databases Currently I have a MySQL Server with innodb_file_per_table = 0
Based on a number of factors, it seems to make sense for me to change that to innodb_file_per_table = 1
I understand that to make this all work I need to

Use mysqldump to export all databases into individual files
Shutdown mysql
Change the innodb_file_per_table setting
Restart mysql
Import each database

Question is, can I do the following
Day #1

Shutdown mysql
Change the innodb_file_per_table setting
Restart mysql
Export some databases
Import those databases back into mysql

Day #2

Export some databases
Import those databases back into mysql

Until I've exported an re-imported all of the databases?
I'm assuming that during the transition some databases will continue to use the ibdata1 file while the exported and re-imported database will use their own datastores.  Will having a mix of datastores cause issues or am I OK doing a phased transition?

I honestly wouldn't bother unless you have a specific problem you are solving.
Its possible to do simpler with:

Shutdown
change setting innodb_file_per_table = 1
start
ALTER TABLE xxx FORCE for each table


SQL Server 2008 R2 There is insufficient system memory in resource pool 'default' to run this query I have a 32-Bit Windows 2003 Enterprise Edition SP2 Server running SQL Server 2008 R2 (10.50.1617.0) Enterprise Edition. The server is a dedicated SQL Server box for our SharePoint 2007 environment.

The server has 36 GB of ram and I have enabled AWE in SQL Server.

I am continuously receiving the error: "There is insufficient system memory in resource pool 'default' to run this query" in the Windows event logs.

I am running perfmon and monitoring the SQL Server Memory Manager counters for 'Target Server Memory' and 'Total Server Memory' as well as the Memory counter for 'Available KBytes'. Currently the 'Total Server Memory' counter is sitting at 5 GB.

I have configured the 'Minimum server memory' to 25 GB and the 'Maximum server memory' to 30 GB in SQL Server. The server is showing 31 GB of available memory, but I continue to constantly receive this error.

The index creation memory is set to 0 KB (dynamic memory) and the Minimum memory per query is set to 1024 KB.

I have experimented with the Minimum memory per query value but it did not stop the error from occuring.

If my server has 31 GB of available memory (20 GB of this are available to SQL Server), why am I receiving this error?

I have spent hours searching in forms, but others who are receiving this error seem to be low on virtual memory, which is not the case for me.

Does anyone have any ideas what could be causing this error?

I have tried restarting SQL Server and even restarting the server itself, but this error always returns within a minute or two.

I am planning on updating to Service Pack 1 as soon as I have approval to do so, but I doubt that will solve my problems.

Any advice would be greatly appreciated.

You say you enabled AWE on SQL Server but did you activate /PAE in the boot.ini file?

PAE enables Large Memory support.

Activate PAE in Windows 32Bit Enterprise

c:\boot.ini

[boot loader]
timeout=30
default=multi(0)disk(2)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE

http://support.microsoft.com/kb/283037

Lock Pages in Memory

As part of enabling AWE, you would also need to give the SQL Server Service user the right to Lock Pages in Memory.  Enabling this option in the Group Policy dialog (gpedit.msc), prevents Windows from paging memory out of the SQL Server working set for its own needs, especially if memory starts to run low on Windows.

http://msdn.microsoft.com/en-us/library/ms190730.aspx

Monitoring AWE Memory Usage

To test AWE usage, I setup a Windows 2003 Enterprise SP2 with PAE enabled in boot.ini.  I used SQL Server 2005 32bit Standard SP4.

The total physical memory is 8192Mb.  The max memory setting for SQL Server is configured at 8192 and the minimum is at 2048.

Does SQL Server see AWE on startup.

  2012-02-09 07:27:51.35 Server      Address Windowing Extensions is
  enabled. This is an informational message only; no user action is
  required.

Is SQL Server using Locked Pages for the Buffer Pool

According to some blogs, I should be seeing this message, but I'm not.

  Using locked pages for buffer pool.
  This is an informational message only; no user action is required.

Memory being allocated through AWE

SELECT  SUM(awe_allocated_kb) / 1024 AS awe_allocated_mb
FROM    sys.dm_os_memory_clerks ;

[3648]

Memory allocated to Multi-Page memory.

select sum(multi_pages_kb)/1024 as [MultiPage Memory, MB] from sys.dm_os_memory_clerks

[14]

  Multi-page memory can not use AWE allocated memory

Memory being used outside the buffer pool

SELECT sum(multi_pages_kb
 + virtual_memory_committed_kb + shared_memory_committed_kb) AS
 [Memory used outside BPool, mb]
FROM sys.dm_os_memory_clerks
WHERE type <> 'MEMORYCLERK_SQLBUFFERPOOL'

[24]

Overall memory allocation by component

SELECT type, (single_pages_kb)/1024 as Single_Pages_MB, (multi_pages_kb)/1024
AS Multi_Pages_MB, (awe_allocated_kb)/1024 as AWE_allocated_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC

This will show you how single-page and multi-page memory is being used by each component.  It will also tell you which one is able to benefit from AWE.

Mutli-page : when request exceeds 8Kb

Single-page : when request is less than or equal to 8Kb

As a side note, I have found sp_whoisactive very helpful in finding slow queries, memory hogs and just about everything that's going on in SQL Server.  Here is a link that Brent Ozar provides on how to set it up and use it.

http://www.brentozar.com/archive/tag/sp_whoisactive/


no pg_hba.conf entry for host "10.1.1.91", user "user" database "testing", SSL off I've come across different versions of this same issue on stackoverflow and on this forum. I've tried the recommendations posted on these forums but I am still unable to connect to a database. I've run out of leads to try and I'm hoping someone can explain to me what is happening and why I am unable to connect. The context of my question follows:

I pg_dumped a database on a server host named test-crawl-1 and have pg_restored the database onto a new server host named test-qa-db where it resides with 4 other databases. I've changed the database. yml file to look like this:

testing:
  db_host: test-qa-db
  db_port: 5432
  db_name: testing
  db_user: user
  db_pass: password

Next, because of the error I'm receiving I tried the following, I sshed into a VM named test-utility-1 and modified the pg_hba.conf file there to reflect the following:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

#Trying to fix the SSL error
host    all             all             0.0.0.0/0               md5

Then I tried this:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

#Trying to fix the SSL off error attempt 2
 local  all      all   (test-utility-1 address)10.1.1.1/32      md5

And then I tried to implement in the postgresql.conf file:

postgresql.conf

listen_addresses = "*"

I pretty much tried that line with the above variations but I have still yet been able to make it work. Is there anything that I'm missing. I'm not sure what else is there to try. Help would be appreciated thank you.

------------EDIT----------

I forgot to mention that I also hard coded the database, user, and Ip address into the corresponding fields but the SSl but the error still persists.

Thanks for the reply @Imarin. I figured it out. I was configuring the wrong pg_hba.conf file. I needed to configure the file in test-qa-db because that was where the database resided.


How to check if a table exists in Informix What would be the query to check that a table exists in Informix ?

To check whether an Informix table exists, use the following query:

SELECT       COUNT(*)
       FROM  systables
       WHERE tabname = 'yourtablename';

If the result is not 0, then the table exists.


How to improve performance for read only innodb MySQL Database I have a particular task which is to maximize the concurrent performance. There is only one particular type of query, which is

select * from table where col1 between ? and ? and col2 between ? and ?

I have created a composite index for (col1, col2). The table is about 20G in size and 100 million rows

However, even in peak concurrent requests, the CPU utilization for MySQL is only 30%. I have tried various techniques like increase max_connections, innodb_buffer_pool_instances but none of them are working.

How to maximize the configuration so that it can perform such read-only query to extreme?

Can't optimize it without some serious surgery.  Think of it as a "bounding box" as discussed in http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

It discusses 5 ways of doing "find nearest".  You are on the second-worst, and probably do not have the best indexes for that.  Then it moves on to 3 faster ways to run the query, but they require non-trivial effort.  But the speedup is quite significant.

As for concurrency, those algorithms provide performance by decreasing the I/O and CPU.  This allows for more concurrency and even lower CPU usage.


Database design question: Tags with values(duration) linked to them I am creating a database that takes in daily inputs such as:

Date
Events in that date (e.g. project foo: 5 hours, bar: 2 hours, etc...)
Rating of the date

There's more but these are probably the only relevant inputs regarding the problem.
The problem is how do I construct my tables so that I have stored somehow the specific duration an event lasts. Currently what I have is:

A users table storing user log in credentials (1)
A rating table to store input date, rating of the date, etc... (2)
A tags table storing unique tags (3)

I will use the tags and ratings to look for possible patterns in the future in case that's relevant.

I don't find it difficult at all if I just had to deal with tags alone related to specific dates, as I would just have to have an unique table of tags and associate the tag_id with a date. But I have no clue how I store the duration of a tag on a specific day efficiently. I could of course only use a single table, accept redundancy with overlapping tags on different days and store tags, events and duration in a single string field. But that doesn't seem efficient to me.
I am very novice when it comes to databases and I appreciate the help.

I am using Python 3.7.2, SQLite and Flask-SQLAlchemy 2.4.0 to construct the tables.

Like so:

(1)

class User(db.Model):
__tablename__ = 'user'

id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
password_hash = db.Column(db.String(128))

(2)

class Rating(db.Model):
__tablename__ = 'rating'

id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.Date, unique=False, nullable=False)
rating_day = db.Column(db.Integer, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
# etc...

(3)

class Tags(db.Model):
__tablename__ = 'tags'

id = db.Column(db.Integer, primary_key=True)
tag = db.Column(db.Integer, unique=True, nullable=False)

Microsoft SQL Server guy here. Not sure if I'm understanding how you are capturing your events, but an approach I've used is to capture a start time and an end time, then calculate the duration after the fact. If a person is manually starting and ending an event, then that would be my approach.
As far as associating tags to events, a join table would address the issue of multiple tags on a single event.

/****** Object:  Table [dbo].[event_tags]
CREATE TABLE [dbo].[event_tags](
    [id] [int] NOT NULL,
    [event_id] [int] NOT NULL, --Foreign key to table events' primary key
    [tag_id] [int] NOT NULL --Foreign key to table tags' primary key
)