texts: Managing schema changes between transactional and analytical databases

texts:
How would you manage changes in a transactional database that affect an analytical database?
Environment:

Transactional database is a current, or near current version of MySQL. Size of the transactional database appears to be around a few terabytes, but the growth of the database is just a few hundred megabytes a day.
Analytical database is a current, or near current version of Mondrian

Scenario: Given an analytical database that is automatically loading data from a transactional database -- when there are schema changes that result in large segments of the data being repositioned within the database transactional database that will result changes in the analytical database too. The only example I was given was that small portion of the schema for the transactional database changed in the database which to my understanding was about 300 tables; meaning I'd guess that 5-10% of the schema for the transactional database changes in some way either by adding/removing/moving column/tables/rows, and that in some cases there are global changes to how the data is formatted within the transactional database.
related meta question for this question.


texts:
My setup:

I am currently running a SQLServer 2012 database paired with a SSAS.
My database features sales per store at specific times.

My goal:

I am interested in predicting future sales (amounts) for each store on an hourly basis.
Utilizing my base tables and creating a time series works out fine but I'm trying to factor in the sales that might occur during holidays. (Christmas sales etc)

My question:

Thinking one step ahead: say that I know I have sales inventory left over, they will go on increased discount right after the holidays(or say that older stock needs to get out so I can make space for holiday stock) How would I be able to have SSAS account for this added logic?

My reasoning tells me that I could include my second table in my mining model and use the isHoliday flag as an input for my sales prediction, however would the SSAS algorithms be smart enough to recognize patterns that occur before or after these dates and attribute them to the presence of this flag?

My tables look like this:

   Store_Id  |  TimeIndex  |  salesCount  |
-------------+-------------+--------------+
    1        |  01-01-2014 |  23          |
    1        |  02-01-2014 |  33          |
    2        |  03-01-2014 |  13          |
    3        |  01-01-2014 |  88          |

   TimeIndex  |  isHoliday |
--------------+------------+
   01-01-2014 |   yes      |
   02-01-2014 |   no       |
   03-01-2014 |   no       |


texts: Improving performance of query with `WHERE  ''`

texts:
I have a qeuery that checks where two columns are not '' (blank) - [blank and NULL are not the same in this case]

Is there any way to improve the performance of the query

SELECT *
FROM myTable
WHERE Currency <> ''
AND Country <> ''

From what I understand, <> is causing a complete table scan?


texts: Is using innodb_file_per_table improve speed of alter table command?

texts:
As innodb_file_per_table option separates table files instead of putting all data and indexes of DBs into one ibdata file, is using this option improve speed of alter table?

I have a table of 40M rows and when I alter a specific table it takes about 5 to 6 hours.

Does this solution help? Is there other ways around to improve alter table speed on heavy tables?


texts: SqlPackager.exe excluding users in report

texts:
Using SSDT 2012 I generate the following to determine the changes required between two databases and end up with Diff.xml

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe" /Action:Extract /SourceDatabaseName:MyDb_TestData /TargetFile:MyDb_TestData.dacpac /p:IgnoreExtendedProperties=True /p:IgnorePermissions=False /p:ExtractApplicationScopedObjectsOnly=True
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe" /Action:Extract /SourceDatabaseName:MyDb /TargetFile:MyDb.dacpac /p:IgnoreExtendedProperties=True /p:IgnorePermissions=False /p:ExtractApplicationScopedObjectsOnly=True

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe" /a:DeployReport /sf:MyDb.dacpac /tf:MyDb_TestData.dacpac /tdn:MyDb_TestData /op:Diff.xml

A snippet looks as follows:

however I don't want to include SQL Users in the comparison. However in the EXTRACT and REPORT actions there does not appear to be any way to exlude these (although PUBLISH does). Setting IgnorePermissions to FALSE also does not do the trick.

Is there any way to exclude users somehow?


texts: Hi I am planning to drop and recreate the listener with same name what is the best approach? Can i drop the listener from WFCS by removing the resorces or Drop the listener from SQL using SQL command? Please let me know which is the best approach?


texts: Would Mongo's ObjectID work well in InnoDB's clustered index?

texts:
Mongo's ObjectID is defined like this:

  ObjectId is a 12-byte BSON type, constructed using:

  a 4-byte value representing the seconds since the Unix epoch,

  a 3-byte machine identifier,

  a 2-byte process id,

  and a 3-byte counter, starting with a random value.

Assuming the PRIMARY key was BINARY(12), will this work as well as an auto incremented INT? Does it count as sequential for InnoDB's clustered index? Would gaps in the timestamp be a problem?


texts: Where do caching servers conceptually fit in my architecture?

texts:
Given the simple well-known architecture below:

Where (conceptually) does the caching server(s) sit?


texts:
SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo

I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind,
dateFrom, dateTo and a combination of dateFrom and dateTo.

But nothing changed, with or without indexes the query time is the same. I know BETWEEN doesn't use indexes, is it true?

As  M Khalid Junaid suggested, this is the EXPLAIN PLAN of the above query:

+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+
| id | select_type | table        | type | possible_keys                  | key  | key_len | ref  | rows  | Extra                                          |
+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+
|  1 | SIMPLE      | table1       | ALL  | dateToFind                     | NULL | NULL    | NULL | 32027 |                                                |
|  1 | SIMPLE      | table2       | ALL  | dateFrom,dateTo,dateFrom_dateTo| NULL | NULL    | NULL |  6951 | Range checked for each record (index map: 0xE) |
+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+

Any suggestion?


texts: Consistent logical backup of databases that use MyISAM and InnoDB engines

texts:
I have a question regarding the logical backup of MySQL databases
that use both MyISAM and InnoDB.

The mysqldump utility supports these two options:

--single-transaction - Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does)
[...]
Option automatically turns off --lock-tables.
-x, --lock-all-tables - Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns --single-transaction and --lock-tables off.

For InnoDB, we need --single-transaction
for MyISAM, we need - lock-tables or lock-all-tables (in case we need cross-database consistency).

So, how is a hybrid database (a database that uses both MyISAM and InnoDB engines) supposed to be backed up?

Edit:

Just to clarify, the question could be reformulated like this:

Do the lock-[all-]tables options guarantee a consistent backup of InnoDB tables?


texts: Why not rebuild indexes with page count <1000?

texts:
I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
ORDER BY indexstats.avg_fragmentation_in_percent desc

In my case, the avg_fragmentation was over 70% for 15 indexes and over 30% for 28 indexes.

So, I rebuild every index using Ola Hallengren's solution. When I ran the query again, this was the result:

Fragmentation over 70% for 12 indexes, over 30% for 15 indexes.

I figured, the reason was because of the page_count, which was lower than 1000 for each of the indexes that were still very fragmented. For example, one of the indexes with a
page_count
 of 967 has a fragmentation percentage of 98,98%! To me, it seems worth rebuilding that index! I did, and afterwards, the fragmentation was 0%. Also, a index with a page_count of 132 went from 95% to 0%

So, my question is, what reasons would there be to NOT rebuild those indexes? One reason might be that rebuilding costs time and resources, but because the indexes are small, doesn't this mean it costs relatively few resources and it would still be benfecial to rebuild it anyway?

There are multiple related question on this site, but all of them answer the question why a index would not defragment, or if indexes are still useful if they are small and you don't defragment them, whereas here the  statement DOES decrease fragmentation, with the question being, why not do it anyway?


texts: Oracle Linked Server in Azure SQL Database Managed Instance

texts:
I am migrating my On Premise SQL Server to Azure SQL Database Managed Instance. But I have linked server to Oracle database in my SQL Server.

Can we create linked server to an On Premise Oracle Database in SQL Azure Database Managed Instance? Can anyone please guide?

If not, what is the alternative?


texts: Selecting data from multiple rows into a single row

texts:
I have a table that has columns like following. Batch number is a database design thing like row number

demand_id        batch_number    debit status     customer
     34               1             yes
     34               2                             jack
     35               1             no
     35               2                             kate

I want to create a query that would return this:

   demand_id         debit status      customer
     34                  yes             jake
     35                  no              kate

How can I do that?


texts: Actual rows read in a table scan is multiplied by the number of threads used for the scan

texts:
I'm running into quite a strange problem. I'm running the same script to generate data and do some matching later on, on an older 2008R2 instance. The last query (an UPDATE) does a single table scan and returns all of the 250.000 rows, whereas on a newer 2017 instance, the table is scanned in parallel and each of the 4 threads reads 250.000 rows, and returns 1 million "actual rows read".

I changed the compatibility mode to 2008 in my 2017 instance and the actuals stayed the same, at 1.000.000.

Is there any valid reason for why this would happen or does this seem like it should be a Connect item?

Plans contain the same operators, but one of them does the scan in parallel and instead of splitting the 250.000 rows per each of the 4 threads (and only read 62.500 rows on each thread) all the threads read 250k each *4 = 1.000.000

Both execution plans can be found at pastetheplan:

2017 version here
2008R2 version here

Also, the full script I was running can be found down below:

create table #targets (id int identity(1,1), start_point int, end_point int, refference_type_id int, bla1 int, bla2 int, bla3 int, bla4 int, bla5 int, bla6 int, bla7 int, bla8 int, bla9 bit, assignedTouch varchar(10));

;with cte as (
    select
        1 sp
        , abs(checksum(newid())) % 11 + 2 ep
        , 1 rn
        , abs(checksum(newid())) % 3 b
        , abs(checksum(newid())) % 3 c
        , abs(checksum(newid())) % 3 d
        , abs(checksum(newid())) % 3 e
        , abs(checksum(newid())) % 3 f
        , abs(checksum(newid())) % 3 g
        , abs(checksum(newid())) % 3 g2
        , abs(checksum(newid())) % 3 h
        , abs(checksum(newid())) % 3 x3
    union all
    select
        sp
        , abs(checksum(newid())) % 11 + 2 ep
        , rn + 1
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
        , abs(checksum(newid())) % 8
    from cte
    where rn < 250000)
insert into #targets
select *
    , 'Unassigned' [Default State]
from cte
option (maxrecursion 0)

select top 250 *
    , char(abs(checksum(newid())) % 85 + 65) [class]
into #matching
from #targets
where end_point in ( 11, 14, 22, 33 )
order by abs(checksum(newid())) % 13

update #matching
set bla8 = case
            when bla1 - bla2 > 0
                then NULL
            else bla8
        end
    , bla7 = case
            when bla2 - bla3 > 0
                then NULL
            else bla7
        end
    , bla6 = case
            when bla4 - bla5 > 0
                then NULL
            else bla6
        end

create nonclustered index nc_assignedTouch on #targets (assignedTouch);

update t
set assignedTouch = m.class
from #targets t
    inner join #matching m
        on t.start_point = isnull(m.start_point, t.start_point)
            and t.bla1 = isnull(m.bla1, t.bla1)
            and t.bla2 = isnull(m.bla2, t.bla2)
            and t.bla3 = isnull(m.bla3, t.bla3)
            and t.bla4 = isnull(m.bla4, t.bla4)
            and t.bla5 = isnull(m.bla5, t.bla5)
            and t.bla6 = isnull(m.bla6, t.bla6)
            and t.bla7 = isnull(m.bla7, t.bla7)
            and t.bla8 = isnull(m.bla8, t.bla8)
            and t.bla9 = isnull(m.bla9, t.bla9)
where t.assignedTouch = 'Unassigned';

sp_configure info:


texts: Our replica set config is invalid or we are not a member of it - MongoDB

texts:
I have started the mongodb server with replica set name 'rs01' enabled on port 27001.

Then started another command window to start the mongo db on the same port.

The status says the 'rs0' is not a member of the replica set.

rs0:OTHER> rs.status()
{
        "operationTime" : Timestamp(1552575009, 1),
        "ok" : 0,
        "errmsg" : "Our replica set config is invalid or we are not a member of it",
        "code" : 93,
        "codeName" : "InvalidReplicaSetConfig",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1552575009, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        }
}

But, if I try to register the member, below error message happens. Any help would be appreciated.

rs0:OTHER> rs.initiate({_id:"rs0",members[{_id:0,host:"localhost:27001"}]})
2019-03-14T22:14:39.840+0530 E QUERY    [js] SyntaxError: missing : after property id @(shell):1:30
rs0:OTHER> rsconf={ _id:"rs0", members: [ { _id: 0,host:"localhost:27001"}]}
{
        "_id" : "rs0",
        "members" : [
                {
                        "_id" : 0,
                        "host" : "localhost:27001"
                }
        ]
}
rs0:OTHER> rs.initiate(rsconf)
{
        "operationTime" : Timestamp(1552575009, 1),
        "ok" : 0,
        "errmsg" : "already initialized",
        "code" : 23,
        "codeName" : "AlreadyInitialized",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1552575009, 1),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        }
}

mongodb


texts: Bringing web service data into SQL server

texts:
I need to retrieve a data set daily from a hosted Web service. I can retrieve the XML "column names" in an SSIS package. But I need to retrieve all the data in the data set in csv format or bring directly into SQL Server.  (Rows and columns, header data and dataset).

Is this feasible on SSIS? Or is there a better approach? Any input or advice is greatly appreciated.


texts: How can I move a MySQL database to another drive?

texts:
I'm using MySQL 5.5 on a local machine to analyze a large amount of government data. I've created a local database that resides on my default drive (Win7 C: drive). I'd like to store the data on my E: drive, a large eSATA external drive.

What steps should I take?


texts: I am in searching of where mysql stored its indexes for innodb engine.I found it stores in ibd file but where I will found those files on system.Can anyone help please?


texts: Archiving with hot standby streaming replication log shipping is not working

texts:
I get the following error message in my postgres logs:
>LOG:  archive command failed with exit code 1
>DETAIL:  The failed archive command was: (ssh postgres@backupdb.com "test ! -f /var/lib/pgsql/data/pg_wal/000000010000000000000014") && rsync --ignore-existing -a /var/lib/pgsql/data/pg_wal/000000010000000000000014 postgres@backupdb.com:/var/lib/pgsql/data/pg_wal/000000010000000000000014

I can successfully SSH from the master to the backup database, so that's not the issue. Probably something about the wal file is causing the problem, but I've tried this with a brand new database starting from scratch and still get the same error. Rsync is installed, so it's not that either.
This is Postgres 10. Streaming replication is confirmed working, it's just the archiving that is failing. The base backup command I used on the backup db server was:
pg_basebackup -h masterdb.com -D /var/lib/pgsql/data -R -P -U replication --wal-method=stream

Here are relevant settings in the postgresql.conf
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 8
archive_mode = on
archive_command = '(ssh postgres@backupdb.com "test ! -f /var/lib/pgsql/data/pg_wal/%f") && rsync --ignore-existing -a /var/lib/pgsql/data/pg_wal/%f postgres@backupdb.com:/var/lib/pgsql/data/pg_wal/%f'


texts: What's the purpose of /*!*/; in mysql binlogs?

texts:
The title pretty much sums up the extent of my question.

They seem to appear after every statement.

Sometimes on a new line following. At other times, at the end before the ;.

What's the point?


texts:
In the USER_TAB_PARITIONS, there is the attribute HIGH_VALUE, formatted TIMESTAMP' 2017-01-01 00:00:00'.

My problem:
I want to convert HIGH_VALUE into a date with a simple query (without procedure, and HIGH_VALUE is as long stored).

So far I could transform it with that procedure:

set serveroutput on size 30000;
DECLARE
   CURSOR c1 IS
   SELECT HIGH_VALUE, PARTITION_NAME
   FROM USER_TAB_PARTITIONS
   WHERE TABLE_NAME = 'MY_TABLE';

   partition_date   VARCHAR2(100);
   partition_name   VARCHAR2(1000);
   date_val         DATE;
   name_val         VARCHAR2(100);
BEGIN
   FOR c1rec IN c1 LOOP
      partition_date := 'SELECT ' || c1rec.HIGH_VALUE || ' FROM DUAL';
      partition_name := 'SELECT ' || c1rec.PARTITION_NAME || ' FROM DUAL';
      EXECUTE IMMEDIATE partition_date INTO date_val;
      DBMS_OUTPUT.PUT_LINE(date_val);
      DBMS_OUTPUT.PUT_LINE(c1rec.PARTITION_NAME);
   END LOOP;
END;
/

My question:
Is there any possibility to do it without a procedure? For example like:

SELECT HIGH_VALUE, PARTITION_NAME
FROM USER_TAB_PARTITIONS
WHERE 1=1
  AND TABLE_NAME = 'MY_TABLE'
  AND HIGH_VALUE < sysdate-1;


texts: Why “Multi-statement transaction required more than 'max_binlog_cache_size' byte s of storage” exception just happened in parts of the slaves

texts:
We have a MySQL replication composed of one master and 5 slaves, and we just find three of the slaves met the following errors and both max_binlog_cache_size and binlog_cache_size are the same to all of the nodes in the cluser and the binlog format is "row". My question is: Why the master and the other two slaves didnt have the exception? What the possibles this situation happened?

"Could not execute Update_rows event on table xxxxx; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log mysql-bin.223903, end_log_pos 2326335819, Error_code: 1197"


texts: MS-Access Beginner Replace Textbox Caption With Combo Box Selection on Button Press

texts:
I'm a complete beginner, just trying out/looking into Databases as more of a hobby, and think Microsoft Access would be an easy way to start with the whole front-end forms and back-end combination.
I just have a form with a combo box [UserNameEntry] that you can select a value from a table 'Users' in a 'Alias' field and then just press a button [Command27], and another text box will have its text replaced by that string. This is more of a test/precursor before me wanting to set it to get the record matching the 'Alias' value selected in the combo box, then checking if another field in that same record matches a particular value.
I don't mind if any help is in the expression, macro or VBA builder, all is good to learn.
I tried the following expression in the On Click event of the button [Command27] (and many other alternatives) but haven't had much luck:
=[Command27].[Caption]="[Users]![Alias]=" & "[UserNameEntry]"

Any help would be appreciated!


texts:
I have a list of employees for our application.  One of the fields is job title.  I use the 4000 job titles to roll people into 1 of 50 "job roles".  We then give people access to our apps via job roles.

On this list we have their supervisor email associated too.  Their supervisor can assign them something on our apps but needs a job role.  Note that supervisors have a "generic manager" job title which rolls into a generic manager "job role".

So based on

employeeid
supervisorid
jobrole

How can I parse through my list get all of the employees under a supervisor and then assign the supervisor a "suprole" base on what jobrole most of their employees have?

Example is:

Ted, Dan, Fred, Susan, and Bill report to Robert.

Their job roles are:
Ted - dishwasher
Dan - cook
Fred - server
Susan - cook
Bill - cook

I would expect Robert's "suprole" to be cook.


texts:
Is it possible to backup multiple databases to a single .bak file and then restore individually?

The answer should be an obvious "yes" - this is one of the main options when setting up a Maintenance Plan.  Select multiple databases, then specify a single file.  When I do this, I get one .bak file containing a backup set for each database. The individual backup sets for each database are all there and look fine when I look into the contents of the file.

But when I try and restore any database after the first in the set, the restore wizard still only sees the files associated with the first database.  On the Files page, I only have two entries (data file, log file) always for the first database.  The restore task then throws an error:

  System.Data.SqlClient.SqlError: Logical file [first source db] is not part of database [target db]. Use RESTORE FILELISTONLY to list the logical file names.

And when I use RESTORE FILELISTONLY I only get results for the first database (same as what I see on the Files page in the restore wizard).

What gives?

I could script out the restore task and set the Logical File Names manually but I don't see any easy way to discover them, if they even exist. It looks like the first backup in the media set establishes the Logical File Names for the entire media set, making any other databases effectively inaccessible.

I'm finding almost zero information online about multiple databases in one backup file; most people want to span one database across multiple files.  But in my case, I have a CMS with 4 small related databases that need to be managed as a set. It would be vastly easier to have one consolidated backup file (and one Maintenance Plan, and one agent job) rather than having to do everything 4x.

There's an old SO question question here for SQL Server 2005 describing the exact same problem that says it's a bug that was patched with SP3.  But I'm running a fully-patched SQL Server 2014 (12.0.5207.0). I'm seeing this on two separate systems, one running Web Edition, one running Dev Edition.

Additional information

Going by this old article by Paul Randal, it seems you can simply reference the position number (WITH FILE = xx) if you're restoring in-place (WITH REPLACE) but since I am restoring to a different server (with different file paths) I also need to use WITH MOVE [Logical File Name] TO [new file path]. The same problem would exist if you're restoring a copy with a different name on the same server (like PROD => DEV).


texts:
My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up.

I have some options to perform but need your recommendations :

Shrink the database ( does shrinking gives the space to OS ? )
Detach and attach DB from an external disk or network storage. ( Is network storage supported by SQL Server 2005 ? )
Change RAID config from 1 to 5 to double the disk size. ( this is safest option but also dangerous of RAID config change. )

Thanks for these great and helpful answers regarding to my question. I have checked the log file and 99% of space is unused. So, I shrunk it. Since the DB is just used when we need some reports ( on time request, not always need to access to db ) I think performance won't be an issue for me.


texts:
In PostgreSQL I can do this:
CREATE TABLE Test(
   Effective_Date timestamp NULL,
   Max_Age_Date timestamp generated always as dateadd(month, 5, Effective_Date) stored
);

How do I create a computed / generated column in RedShift?
I found this: https://stackoverflow.com/questions/15532084/how-do-i-add-a-calculated-column-in-sql-workbench-j
But that is not a real computed column.


texts: Does any SQL engine have navigation support in CHECK clauses?

texts:
Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date and associated account).

Also, some transactions may be part of a transfer between different accounts and not a legitimate expense/income on its own (for instance a money cash out from your checking account), these should be linked together somehow. I could start modelling the application with the following schema:

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY
);

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  amount INT NOT NULL,
  date DATE NOT NULL,
  account INT NOT NULL REFERENCES accounts
);

CREATE TABLE transfers (
  outgoing_tx INT REFERENCES transactions,
  incoming_tx INT REFERENCES transactions,
  PRIMARY KEY (outgoing_tx, incoming_tx)
);

Now comes the question part. When I find myself modelling a domain like this I tend to heavily rely on CHECK clauses to further constrain the schema to the domain and ban unwanted values/states at runtime.

For instance, in this example I wouldn't care for empty transactions:

ALTER TABLE transactions
ADD CONSTRAINT no_pointless_txs CHECK (amount <> 0);

However, when it comes to rules that involve more than a single nice little row I always feel like I need to write too much boilerplate code. For transfers you would want to check that the transactions they point to belong to different accounts, are from the same day and have the same amount, except outgoing_tx must be negative and incoming_tx positive.

This is more or less what I would like to write, i.e. I'd like to navigate through foreign keys to access other table columns by doing select/joins under the hood:

ALTER TABLE transfers
ADD CONSTRAINT no_same_accounts
  CHECK (outgoing_tx->account->id <> incoming_tx->account->id),

ADD CONSTRAINT no_different_dates
  CHECK (outgoing_tx->date = incoming_tx->date),

ADD CONSTRAINT correct_balance
  CHECK (outgoing_tx->amount < 0 AND 0 = outgoing_tx->amount + incoming_tx->amount);

Instead, every time I want to enforce rules like these I have no option but to write lenghty boilerplate PL/SQL functions such as get_date(int) or get_account_id(int) to do the queries manually.

CREATE OR REPLACE FUNCTION get_account_id(tx_id int) RETURNS int AS $$
  SELECT account FROM transactions WHERE id = $1;
$$ LANGUAGE SQL;

-- Every other function...

ALTER TABLE transfers
ADD CONSTRAINT no_same_accounts
  CHECK (get_account_id(outgoing_tx) <> get_account_id(incoming_tx));

-- Every other constraint using its corresponding function...

So I guess I just want to know if there are easier, more mantainable ways to go about this kind of use case, and OCL-style navigation came to my mind.


texts:
What I did before the disaster was:

vi /etc/my.cfg

I added

innodb_buffer_pool_size=4G
innodb_buffer_pool_instance=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

and after that

$ service mysql stop
$ cd /var/lib/mysql
$ rm -f ibdata1 ib_logfile*
$ service mysql start

I receive ERROR 1146 (42S02): Table 'db.table' doesn't exist for every table.

My site is down, database backup does not help.


texts:
I have these tables

select * from mass_categories;
+----+--------------------------+
| id | categorie                |
+----+--------------------------+
|  1 | ESI-QTOF                 |
|  2 | MALDI-TOF                |
|  3 | FT-MS                    |
|  4 | GC-MS                    |
|  5 | IT-MS                    |
|  6 | ICP                      |
|  7 | Standard MS Analysis     |
|  8 | Analysis Scientist Cost  |
|  9 | Analysis Technician Cost |
+----+--------------------------+

select * from mass_groups;
+----+-----------+------------------+
| id | groupname | groupdisplayname |
+----+-----------+------------------+
|  2 | LOCAL     | LOCAL (U1)       |
|  4 | ACADEMIC  | ACADEMIC (U2)    |
|  5 | EXTERNAL  | EXTERNAL (U3)    |
+----+-----------+------------------+

and "mass_prices" which is empty but with this structure

CREATE TABLE `mass_prices` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `id_categorie` smallint(5) unsigned NOT NULL,
  `hour_price` float(6,2) DEFAULT NULL,
  `id_group` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_categorie` (`id_categorie`,`id_group`),
  KEY `id_group` (`id_group`)

I could launch this select which returns me what I want when the mass_prices is empty

select categorie, mg.id as groupid, hour_price
    from mass_categories mc, mass_groups mg
    LEFT JOIN mass_prices mp on mp.id_group=mg.id;
+--------------------------+---------+------------+
| categorie                | groupid | hour_price |
+--------------------------+---------+------------+
| ESI-QTOF                 |       2 |       NULL |
| ESI-QTOF                 |       4 |       NULL |
| ESI-QTOF                 |       5 |       NULL |
| MALDI-TOF                |       2 |       NULL |
| MALDI-TOF                |       4 |       NULL |
| MALDI-TOF                |       5 |       NULL |
| FT-MS                    |       2 |       NULL |
| FT-MS                    |       4 |       NULL |
| FT-MS                    |       5 |       NULL |
| GC-MS                    |       2 |       NULL |
| GC-MS                    |       4 |       NULL |
| GC-MS                    |       5 |       NULL |
| IT-MS                    |       2 |       NULL |
| IT-MS                    |       4 |       NULL |
| IT-MS                    |       5 |       NULL |
| ICP                      |       2 |       NULL |
| ICP                      |       4 |       NULL |
| ICP                      |       5 |       NULL |
| Standard MS Analysis     |       2 |       NULL |
| Standard MS Analysis     |       4 |       NULL |
| Standard MS Analysis     |       5 |       NULL |
| Analysis Scientist Cost  |       2 |       NULL |
| Analysis Scientist Cost  |       4 |       NULL |
| Analysis Scientist Cost  |       5 |       NULL |
| Analysis Technician Cost |       2 |       NULL |
| Analysis Technician Cost |       4 |       NULL |
| Analysis Technician Cost |       5 |       NULL |
+--------------------------+---------+------------+

but as soon as I add lines in this mass_prices, this select query is returning too many lines.

So to resume, I need a select which takes each "mass_groups" then join each "mass_categories" and mass_prices even if prices are not defined.

I'll use that on a webpage to allow users to change those prices.

Thanks !


texts: Microsoft SQL Server equivalent to Oracle Enterprise Manger Top Activity Interface?

texts:
My DB performance management experiences is primarily with Oracle and I am looking for an equivalent set of tools in MSSQL. In particular something to easily spot historical SQL peaks and identify the culprit SQL. It seems most of the built-in tools (Activity monitor/DMV) offered by MS only caputure active issues, but cannot retrive and chart issues that occurred much longer than a few minutes in to the past.

For Oracle, we have Oracle Enterprise Manager which views the AWR repository and by default can see, 7 days worth of Activity including the exact Sessions/SQL statements that caused an issue and why.
Example screen shot:
https://docs.oracle.com/cd/B28359_01/server.111/b28275/tdppt_sqlid.htm#TDPPT142

For MSSQL aka SQL Server, the only thing I have been able to find is a 3rd party utility from Solar Winds.
Example screen shot:
http://logicalread.com/wp-content/uploads/2017/05/meritsSQLblogpost5.png

Does Microsoft have something native that offers this capability?


texts: PostgreSQL CHECK constraint is evaluated wrongly

texts:
Following schema allows to violate the CHECK constrait, but only once. Then the constraint is correctly enforced.

drop database if exists example;
create database example;

create table example_table (
    id integer not null,
    value integer not null,
    constraint pk primary key (id));

CREATE OR REPLACE FUNCTION checkunique()
  RETURNS boolean AS
$BODY$
begin
    return NOT Exists (
        Select 1
        FROM example_table AS a, example_table AS b
        WHERE a.id != b.id AND a.value = b.value);
end
$BODY$
LANGUAGE plpgsql STABLE;

alter table example_table add constraint unique_value check (checkunique());

The check constraint substitutes UNIQUE constraint because I need to store large VARCHARs in production case and there is a limit on size of indexed cell.

Now data that breaks the CHECK constraint can be inserted:

insert into example_table (id, value) values (0,0);
select checkunique(); -- returns true
insert into example_table (id, value) values (1,0);
select checkunique(); -- returns false

Even the database itself admits that the constraint function evaluates to false, however data are stored.

Now one can't insert any, even constraint satisfying data:

insert into example_table (id, value) values (2,2);
--error output:
--ERROR: new row for relation "example_table" violates check constraint "unique_value"
--SQL state: 23514
--Detail: Failing row contains (2, 2).

How can I make the constraint work correctly?

Environment: PostgreSQL 9.3.6, Fedora 20 x64


texts:
Is it possible to structure a CASE statement as follows?

For each Path column I want to update the Value column, but the values aren't being set and I'm not sure how to proceed. The columns are all nvarchars(255).

I've taken this code from an answer on StackOverflow; however in that case the columns listed after SET and CASE were the same. I realize that there are other methods achieve the same result, but this is the least verbose I've found.

UPDATE Database.[dbo].[Table]
SET Value =
CASE Path
    WHEN '\Folder1' THEN '0'
    WHEN '\Folder2' THEN 'xyz'
    WHEN '\Folder3' THEN '0'
    ELSE Value
END


texts: Script Rename of SQL Server Database and Database Files

texts:
As part of our development lifecycle, we would like to do regular comparisons of versions of our database between two different versions of source control.

I expect that there are many automated solutions to do this, but right now we do the following:

Update repository to a particular version
In Visual Studio

Build solution
Build database

In SSMS

Rename database
Detach database

In File Explorer

Rename database files

In SSMS

Attach database

Repeat steps 1-5 for another version
Compare the two databases with SqlCompare

It would be very helpful if we could script all of steps 3-5 (as they're currently done via GUI), probably as a TSQL script, but we'd be open to other options. Can this be done easily?


texts: MySQL: Privileges to view Views/Stored Procedure for user Regardless of Host

texts:
I've created stored procedures on a (remote hosted - DreamHost) MySQL Database using the HeidiSQL client.

One problem i seem to have is that viewing/editing the content of stored procedures is only possible when connecting from the same "host".

For example, i connect primarily from home, via a BT connection (I'm a BT customer)

But when i try connecting in from work (a different provider/ISP) i can do everything, run queries, edit tables etc, but cannot edit/view the Views/Stored Procedure queries despite connecting with the same username.

I'm assuming the "@") may be something to do with it?

Is there anyway i can prevent this from being a problem?


texts: What are the events that EVMd process in Oracle RAC distributes across cluster?

texts:
I am new to Oracle RAC. I was studying about Oracle RAC background processes, in which I came across EVMd process which distributes the event message across other nodes in the cluster.

Could you please make me understand, what are these event message? I worked on it but didn't get proper answer.

Thanks
Prabhakar


texts:
I have a database which has a user's real age and the age that people have guessed them to look like in an uploaded photo.

I want to find the photos which have the greatest difference between guessed age and actual age.  What Query could I run?

For example, the two rows that I am interested in are as follows.

age | avg
18 | 34.45
32 | 33.23
45 | 28.21

Thank you


texts: SQL Server Always-On Availability Groups LeaseTimeout

texts:
One of my AG environments encountered the error

  The lease between availability group XXXXXXX and the Windows Server
  Failover Cluster has expired. A connectivity issue occurred between
  the instance of SQL Server and the Windows Server Failover Cluster. To
  determine whether the availability group is failing over correctly,
  check the corresponding availability group resource in the Windows
  Server Failover Cluster.

Now I know why this issue occurred however I want to understand why the availability group crashed instead of failing over to another node i.e. primary role changed to resolving was the next error. It took around 1 minute for the availability group to come back online automatically on the same node. Surely it should failover to the next available node? This is a 5 node cluster all having votes within quorum.

Is  this just the way SQL Availability groups work? i.e. if the AG loses connection to the windows failover cluster dll then it cannot failover and crashes to avoid a split brain scenario?

Where do I configure this default behavior of it trying to reconnect first before failing over to another node?


texts: Why would MySQL 5.6.26-74.0 lock a select on a database1 while dropping a table on database2?

texts:
I have a large table on database2, e.g., that I need to drop.  When I do, it takes 15 seconds or so, and during this time, selects on database1 (same server) block.  This happens every time on a mysql server that I run on a percona server I run on ubuntu 14.04, but I cannot reproduce it at all on my local box, running OS X, with the same percona server version.  I'm using Innodb on both.

Additional information: If I run delete from table1;  It works without hanging, but then still hangs when I drop the table with no rows in it.  I'm thinking it is doing some kind of file system cleanup that is blocking the whole server, but I can't find any documentation about / how to work around it.  my OS X box is using an HFS+ filesystem, and the Ubuntu box is using ext4.

Thanks :)


texts: How do I hide the "read ....save as" notification for an accdr file

texts:
When I make the accdb file to an accdr file and open it with Access 2010, the database is created with Access 2010 and OpenGateSoftware’s UI builder. I get a notification bar at the top saying: Read only ... save as.

The meaning with me creating an accdr file is that it shall be Read only NOT how to get rid of it. Anyone knowing how to hide this Notification bar? The database is a catalogue for Air Mail collectors.


texts:
I have a table:

Table1(DDate date,Deposit_Key Number , Amount Number)

some example data are:

DDate       Deposit_key        Amount
1/1/2019          1              10
1/2/2019          1              20
1/3/2019          1              30
1/4/2019          1              40
  ----           ---             ---
1/31/2019         1              100

I have data for one month and I need to retrieve the Amount for the last day of the month . I mean I need to have 100 . What is the best query possible for this? I'm new to oracle and I want to know whether there are better ways than using row_number() over(partition)?
Thanks in advance


texts: Is there any way to get how long it takes the optimizer to optimize a query in Oracle?


texts: Display multiple rows of one table into one row without using PIVOT

texts:
I'm currently trying to form a SELECT query to from a table without using PIVOT.

Examples of data and expected result are shown below:

DB Data

| Id |  TYPE      |  Amount |
| 1  |  Deposit   |  50     |
| 2  |  Withdraw  |  10     |
| 3  |  Withdraw  |  30     |

Expected Result

| Row |  TYPE      |  Amount |   TYPE     |  Amount |
|  1  |  Deposit   |   50    |  Withdraw  |  10     |
|  2  |     -      |    0    |  Withdraw  |  30     |

The deposits and withdrawals are not related to each other, just displaying in ascending order of id. The expected end result is two row of all deposits while another two row with withdrawals. The purpose of doing this is to maximize spaces.

Thought of using left join but there's no unique id that can let me use GROUP BY

Thanks in advance for helping.


texts:
i installed dbachecks module in a server. but when i am trying to execute get-dbccheck, it is throwing the below error
Populating RepositorySourceLocation property for module Pester.
VERBOSE: Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\Pester\5.0.4\Pester.psm1'.
[18:59:41][dbachecks.psm1] Failed to import suitable version - Do you have Version 4* installed ?
Get-DbcCheck : The 'Get-DbcCheck' command was found in the module 'dbachecks', but the module could not be loaded. For more information, run 'Import-Module dbachecks'.
At line:1 char:1
+ Get-DbcCheck
+ ~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Get-DbcCheck:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CouldNotAutoloadMatchingModule

I have updated the pester and psframework modules to the latest, still i am unable to execute the commands.
Can someone help me in this.?


texts:
I have a database with a lot of users, all of them with full read privileges on all the tables but not all the users are reading all the tables. I want to have each user having as little privileges as they need. Each user belongs to a different large legacy application for which auditing the code is not feasible.

How do I go about obtaining this information?


texts: Can I import a specific vssettings file when launching SSMS?

texts:
In the interest of disambiguating between when I am connecting to non-production and production, I utilize vssettings for a specific theme in Management Studio (fire-and-brimstone style when in prod, calming blues in non-prod).

Previously our non-production access was via one account, and our production access was via a more restricted account.  With that method I was able to set a theme for each account one time and all was well.  Now all access is using the elevated account.

Is there a way to tell SSMS at launch time to use a specific theme?  Or a programmatic way to do this without going through "Tools > Import and Export Settings" every time?  I'm using the SQL 2014 version of SSMS.

Thanks.


texts: Importing plain (raw) files into a postgres table (bytea and large objects)

texts:
What is a good way to insert/copy plain files into a postgres table, preferrably using the psql command-line?

In my case the files are a bunch of eMails from Maildir archives, so I tried to use COPY:

psql -c "COPY emails (data) FROM '/tmp/emailfile' WITH (FORMAT text);" emails

which I would use in a for-loop shell script (for file in $(ls dir); do psql ...; done).

However, I have trouble finding a good "delimiter" that couldn't possibly be in the file,
and I'm getting these errors: ERROR:  extra data after last expected column.

So I considered using the COPY ... FORMAT binary version and a BYTEA field in the db (then convert the column to TEXT inside the db),
but that requires a file header and trailer, which I have no easy way of building on-the-fly.

Is there an easy way to do this from the commandline, or do I need to write a python script for this?


texts: List all mysql records in a master table and last record in a related child table

texts:
I want to list all records in a MySQL master table and the last record in the child table that is related to the master. Here is my code

$strsql="SELECT contact.id, contact.title, contact.init, contact.first_name, contact.last_name, contact.city, contact.home_phone, contact.mobile_phone, contact.e_mail,  status.status AS status, fundsource.fundsource AS fundsource, category.category AS category,contact.score, ROUND(value/1000000,1) AS value_M, jstate.jstate AS to_do, journal.l_date AS last_activity, journal.note AS journal FROM contact

LEFT JOIN journal ON journal.con_id=contact.id
LEFT JOIN jstate ON jstate.jstate_code=journal.jstate_code
LEFT JOIN category ON category.cat_code=contact.cat_code
LEFT JOIN status ON status.st_code=contact.st_code
LEFT JOIN fundsource ON fundsource.fundsource_code=contact.fundsource_code

WHERE journal.l_date=(SELECT MAX(journal.l_date) from journal WHERE journal.con_id = contact.id) GROUP BY contact.id " ;

But it outputs only related records which matching the PK and FK. Not displays all records in the master. Can any one help me?


texts:
I am working on designing requirements for new SQL Server installation. I have been researching disk storage and everything seemed to point to using RAID.

However, I found this article, which suggests RAID is no longer relevant today.

Can anyone point me in the direction of where I can learn about the latest disk storage for SQL Server.


texts: Unable to update SQL Server via Access, Reserved error (-7776)

texts:
I'm trying to use Access as a front-end for an SQL Server 2017 database. The objects I'm interested in are 6 temporal tables and a view joining them. I created an ODBC user data source and linked the tables and the view. For the view, I created an instead of update trigger that updates the underlying tables, and that works just fine.
Since the primary keys of the underlying tables are identity, I figured it would be easier to insert/delete directly against the underlying tables instead of via the view (I had to SET IDENTITY_INSERT ON when manipulating the view via SQL).
I can add a row to one of the tables without a problem, but when I try to delete or update a row in the table, I get an error saying "Reserverat fel (-7776). Det finns inget meddelande för felet". Loosely translated that would be something like "Reserved error (-7776). There is no message for the error".
I visit the world of Windows on a regular basis every 20 years or so, so I have no real clue on where to start looking for any clues on what might be the error. Any insight?
Funny thing is that if I create a view that joins two of the tables (the primary key of one of the tables is also unique in the view), I can update the view, and the underlying table is also updated (no instead of trigger necessary).
It is also possible to insert a new row (for delete it seems like I will need an instead of trigger). So I guess this is a workaround, but it seems rather odd that I can update the view, but not the table.


texts: Linux Error: 29: Illegal seek in LSNRCTL for Linux Version 11.2

texts:
I'm trying to get Oracle Database 11g Express set up on my server so that it is accessible from the outside.  I can talk to the DB locally using SQLPlus, so the database is definitely running.  I think my issue is with the listener.

Here's what I'm getting:

$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 27-AUG-2012 16:14:38

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr: please wait...

TNS-12537: TNS:connection closed
 TNS-12560: TNS:protocol adapter error
  TNS-00507: Connection closed
   Linux Error: 29: Illegal seek

I've tried two solutions I found online, and neither seemed to work.  The first, from dba-oracle.com, suggests changing tcp.validcode_checking=yes to tcp.validnode_checking=no in sqlnet.ora.  There was no such line, so I added it, and no luck.  My sqlnet.ora is otherwise unchanged from when I installed.  Note that this referred to version 10.2.

The second suggested adding 127.0.0.1   localhost.localdomain localhost to /etc/hosts, which I did, again with no change.  No processes are listening on port 1521 (netstat -aopn | grep 1521 returns nothing).

Any suggestions would be most welcome.

Update

It seems I misunderstood what I was looking at.  The sqlnet.ora file is in the samples directory, so it's not actually being read by the listener.  Instead it is using the listener.ora file, which looks like this:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

The tnsnames.ora file looks like this:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

These are again the files that came with the application.  Is there something I need to change here?


texts: Xampp 3.2.1 error: MySQL shut down unexpectedly

texts:
I logged into Xampp today and it wouldn't launch MySQL.  It said there was an error and it shut down unexpectedly.  Here are the errors from the error log.  Hoping someone has seen this before, because I have little experience fixing this stuff.

2013-11-12 18:21:39 8324 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect key file for table 'user'; try to repair it
2013-11-12 18:27:36 5800 [Note] Plugin 'FEDERATED' is disabled.
2013-11-12 18:27:36 16ac InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-11-12 18:27:36 5800 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-12 18:27:36 5800 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-11-12 18:27:36 5800 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-12 18:27:36 5800 [Note] InnoDB: Not using CPU crc32 instructions
2013-11-12 18:27:36 5800 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2013-11-12 18:27:36 5800 [Note] InnoDB: Completed initialization of buffer pool
2013-11-12 18:27:36 5800 [Note] InnoDB: Highest supported file format is Barracuda.
2013-11-12 18:27:36 5800 [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1957328 in the ib_logfiles!
2013-11-12 18:27:36 5800 [Note] InnoDB: Database was not shutdown normally!
2013-11-12 18:27:36 5800 [Note] InnoDB: Starting crash recovery.
2013-11-12 18:27:36 5800 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-11-12 18:27:36 5800 [Note] InnoDB: Restoring possible half-written data pages
2013-11-12 18:27:36 5800 [Note] InnoDB: from the doublewrite buffer...
2013-11-12 18:27:37 5800 [Note] InnoDB: 128 rollback segment(s) are active.
2013-11-12 18:27:37 5800 [Note] InnoDB: Waiting for purge to start
2013-11-12 18:27:37 5800 [Note] InnoDB: 5.6.11 started; log sequence number 1957328
2013-11-12 18:27:37 5800 [Note] Server hostname (bind-address): '*'; port: 3306
2013-11-12 18:27:37 5800 [Note] IPv6 is available.
2013-11-12 18:27:37 5800 [Note]   - '::' resolves to '::';
2013-11-12 18:27:37 5800 [Note] Server socket created on IP: '::'.
2013-11-12 18:27:37 5800 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect key file for table 'user'; try to repair it


texts: Backup Strategy for Transaction Log. Tail or Standard Log Backup?

texts:
We are forced to perform some essential maintenance on one of our production environments. Instructions from the vendor state the recovery model of the database should be set to SIMPLE before the work is undertaken.

I'm OK with that and the business has approved the change, but I'm wrestling with the best way forward in terms of disaster recovery planning.

I want to ensure that the database can be restored up to the point of changing the recovery model, if needs be.

We take a full database backup at midnight every night, and log backups every hour between 4am and 9pm (operational hours).

This work will commence at 9am, just after a scheduled log backup, but of course I'm prepared for slippage into that hour based on various factors. I want to ensure we have a log backup in place to cover, lets say, 9.00am to 9.20am.

EDIT: Users are being locked out of the system at 9.00am or thereabouts, however I can't rely on the last log backup alone, as there may be transactions slipping past 9.00am that need to be restored. It depends when Management push the button...

What's the best course of action?

Should I just take an ad-hoc Transaction Log backup which I can, if needs be, apply after the latest Full Backup and sequence of Log Backups?

Or should I be looking at taking a Tail Log Backup, despite the fact I may never use it?


texts: Finding the application behind ".net sqlclient data provider"

texts:
I'm trying to find which application is opening various connections on the SQL Server, all named ".net sqlclient data provider".

From system monitor I got the machine name;
On the client with netstat -a -b -o | Find "SQLServer", I have found 4 connections all with the PID 4 (ntoskrnl) ... yes this is windows and it is a MS SQL server.

"Trace process in SQL server profiler", crashes the Management Studio.

So, before I start killing applications, one by one, do you have an idea how to trace back this connection?

Best Regards;
Ezeq


texts: Is there a way to have multiple error messages displayed with RAISERROR conditionally?

texts:
I am thinking of something like the CASE statement for RAISERROR, but I want the remaining code to be checked too, and not skip anything after encountering the first RAISERROR. Right now I have something like this.

Now, I want a way where I can RAISERROR at the very end and do it conditionally. So if there are two errors from three checks, both should be displayed to the user. Thank you.


texts:
I have a hobby site where users can find other hobbyists of similar interest.  Each user can set a series of preferences that get saved into user_prefs.  Right now there are 4 types of preferences, an example of those are genre.  Any user can select a like/dislike for a subject under genre like 'Science Fiction'. That subject under genre is then saved as a 1 or 0 in the user_prefs table.

Right now all 4 types of preferences are being saved in the user_prefs table.  But with only a few hundred users as my hobby site is getting started, thats over 8,000 rows.  A single user could have 135 possible rows right now.  Am I right in thinking that with 10k users at 135 rows, 1.35 million rows might be a bit silly for fast page load?

The query that looks at user_prefs might be run 4 to 100 times or more on a page.  Let me explain why.

The query runs inside a radial distance loop that uses postal codes to display users within a maximum distance.  So only users within that distance will be found, the hard maximum is 90 miles although the user can select a much smaller range.

When a user searches a distance, a query runs this statement:

foreach ($central_location->getZipsInRange($zip_link,$minimum, $radius) as $miles => $zip) {

For every single postal code in radius, there is another query that checks if any user's match that postal code.  If there are users in that particular postal code, it lists them.  Then each single user's preferences are looked at 4 times inside the postal code loop.

Each of those 4 queries is this statement with only genre changed to another keyword:

SELECT user_list.user_name, user_list.first_name, game_genre.gamer_genre
    FROM user_list
    JOIN users_prefs ON user_list.user_index = users_prefs.user_id
    JOIN game_genre ON users_prefs.prefs_index = game_genre.genre_index
    WHERE users_prefs.prefs_type = 'genre'
    AND users_prefs.declared_prefs = 1
    AND user_list.user_name = '$user_name'
    ORDER BY game_genre.gamer_genre ASC

So should I further denormalize the 4 types of preferences into different tables and keep the 4 statements?

Or should I leave the table together, assume that a query against a few million rows won't slow things down but combine the preferences query into a single statement?

I was pondering making the 4 statements into 1 single statement then using PHP to separate it out but the idea of a million rows or more feels pretty slow to me.

Either way for me, this has been a learning experience.  The site is using PHP v5.4.29 and the MYSQL clone Percona v5.5.37-35.1.  My host is HostGator on the baby hatchling plan, so I don't have a server power.

I'd be interested in not only suggestions about my 2 main questions but also other ideas.  Keep in mind my skill level is that of "adventurous novice."  Looking forward to some great suggestions.


texts: Index rebuild after SQL Server upgrade to 2014

texts:
The company I work for decided to upgrade the database engine from SQL Server 2005 to SQL Server 2014, the database is big and uses multiple files, and each table has a complex set of indexes to insure fast performance, and we are calculating the time required to upgrade, one missing bit of information I didn't find an answer for is:

Do we have to rebuild indexes after we upgrade the databases?


texts: Trying to combine one row from one table with mulitple rows from another table but not sure if I'm doing it right?

texts:
The Situation

I have two tables unit and unit_translations defined as:

CREATE TABLE `unit` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL COMMENT 'Title of unit',
  `description` longtext NOT NULL COMMENT 'Description of the unit',
  `idLevel` int(11) default NULL COMMENT 'Foreign key referencing level id',
  `position` smallint(6) NOT NULL COMMENT 'List sort order',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `title` (`title`),
) ENGINE=InnoDB;

Table has this data:

#id, title, description, idLevel, position
'1', 'Unit 001', 'Unit 001', '1', '1'

and

CREATE TABLE `unit_translations` (
  `id` int(11) NOT NULL auto_increment,
  `idUnit` int(11) default NULL COMMENT 'Foreign key referencing unit id',
  `locale` varchar(8) NOT NULL COMMENT 'Translation locale',
  `field` varchar(32) NOT NULL COMMENT 'Field in object being translated',
  `content` longtext COMMENT 'Translated value',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `lookup_unique_idx` (`locale`,`idUnit`,`field`),
  KEY `IDX_14213810232D562B` (`idUnit`),
  CONSTRAINT `FK_14213810232D562B` FOREIGN KEY (`idUnit`) REFERENCES `unit` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

That holds this data:

# id, idUnit, locale, field, content
'1', '1', 'en', 'title', 'A Day at the Beach'
'2', '1', 'en', 'description', 'How a typical day at the beach could be'

(Don't get caught up in the data it is silly right now)

The Task

Create a Select that selects everything from unit but replaces the content in unit.title and unit.description with the translated value from unit_translations. So the result would like like:

# id, title, description, idLevel, position
'1', 'A Day at the Beach', 'How a typical day at the beach could be', '1', '1'

I've managed to accomplish this with the following SQL:

SELECT u.id, trans1.trans as title, trans2.trans as description, u.idLevel, u.position
    FROM unit u
    JOIN (SELECT t.idUnit as trans_id, t.content as trans FROM unit_translations t JOIN unit u ON t.idUnit = u.id WHERE t.field = "title" AND t.locale ='es') AS trans1 ON u.id = trans1.trans_id
    JOIN (SELECT t.idUnit as trans_id, t.content as trans FROM unit_translations t JOIN unit u ON t.idUnit = u.id WHERE t.field = "description" AND t.locale ='es') AS trans2 ON u.id = trans2.trans_id

The Question:

Is there a build-in way to fetch the two rows/values form unit_translations and merge them into the one row from unit? Or is what I have come up with the only way to do this?


texts: Is there a better alternative than LIKE for this query?

texts:
I am trying to retrieve data from a table where there are codes for projects which are identified by the first three numbers, the rest are unique. So, for example, the query is:

SELECT * FROM
TableA
WHERE LEFT(acccode,3) IN ('121','131','141','151','161')

Now, I want this condition to be true for all these codes except for one. Say, for the first one, I want it to look four more characters for anything like '121-111%'. How can I alter my IN operator there or do I have to just go with a LIKE operator with something like:

SELECT * FROM
TableA
WHERE acccode LIKE '121-111%'
    OR acccode LIKE '131%'...

and so on...?

To clarify: codes starting with 121-111, 131, 141, 151, 161 are the ones I want to select.

All columns are nvarchar.


texts: What database platform better at scaling?

texts:
I am planning a for a database that i expect will be very big in terms of number of records rather than tables and batabases.  My Question is what product (i.e, SQL 2008, Cassandra, Azure) handles scaling better?

I expect that there will be 100k or so records will be added everyday, It should easily retrieve a single row and a batch of them.

The app will crawl the web and for each word in a dictionary will find the same word used in different sentences and record them to a database.
The developers are mainly .net oriented but they are fluent in C/C++ as well and because this is a academic project anything is basically affordable.

One platform/product per answer please.


texts: How to split the comma separated list in the stored procedure

texts: I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.


texts: SQL Server. Do reads on Read-Only Replica in Availability Group fire Auto Update Statistics on Primary?

texts:
We have MS SQL Server 2014 Enterprise Edition with an Availability Group with Readable Secondary. Our application reads data only from Secondary.

As far as I know, auto update statistics starts when reads (SELECT and maybe something else) performed. But our reads execute only on Secondary. Does it fire Auto Update Stats on Primary?

Thanks in advance.

P.S. And please, excuse my poor English.


texts: DATETIME2FROMPARTS alternatives for SQL Server 2008

texts:
I am trying to part a datetime value and build it together removing minutes, seconds and so on. I want the values to be hourly.

Usually this works on a SQL Server 2012 machine:

DATETIME2FROMPARTS( DATEPART(year, datum), DATEPART(month, datum), DATEPART(day, datum), DATEPART(hour, datum), 0, 0, 0, 0) as dtHour

But unfortunately in SQL Server 2008 it does not! Which function do I need, to fulfill the same job?

I also looked up this reference: Date&Time functions but this were all only SQL Server 2012 compliant.


texts: Reporting on a SQL DB Server with many write operations and few read operations

texts:
I know this is a generic question, but I have to clarify some basic questions.
I know that when a write is happening to a SQL Server database table, a read to that table will have to wait.
My question here is: the SQL database server I am working on is being exposed to many writes and a few reads. And a few SSRS reports are also being run on the same server.

What is the ideal or recommended way to accommodate this?

I know that it is always best to perform reporting from a duplicated instance of the same database. But for a few reports, is it worth to set up a duplicate instance for reporting?

I get complaints sometimes that some reports are running slow.

Can it be the read wait while a write happens?


texts: Update files or a table every 5 seconds or for every update to a table whichever is the less frequent

texts:
There is a table 'votescount' which is being updated every time a user vote.

I need to display three rows with maximum votes.

Now the problem is If the users are less in number and less frequent to update, a trigger will be better to extract the rows, otherwise, if users are more frequently voting, an event will be better.

So, is there a lightweight solution in between to solve the problem?

I'll prefer to write the results in a file because it will be faster to access from php.

I am working on a LAMP stack.


texts:
I have a DB [MainData] with the following data in [dbo].[Table1]

Customer    Value1   Value2
--------    ------   ------
CustA       Foo           1
CustA       Bar          25
CustB       Foo          22
CUSTA       Baz         259
CUSTC       Zoo          31

So - then, I have a DB called
CustA with a view defined as follows:

CREATE DATABASE CustAData;
GO

USE [CustAData]
GO

CREATE VIEW Table1 AS
(
    SELECT * FROM [MainData].[dbo].[Table1] WHERE Customer = 'CustA';
)

In this way, when customer A connects to the [CustAData].dbo.Table1 view - he only sees his 3 records - and CustB and CustC's data is automatically hidden.

Then - i create a user for CustomerA which ONLY has access to database [CustAData].  The problem is that when this user executes a SELECT * FROM dbo.Table1 I get an error that the user does not have access to the [MainData] database.

Is there any way to setup the security so that this user can ONLY access their database, which shares data (selectively) from another database without giving them direct access to the source database?

Thanks - maybe this doesn't make any sense, but it would be a very convenient way to configure this setup.  Thanks in advance.

EJ


texts: Hard Limit to SQL Server 2012 Memory Usage

texts:
It seems that even in SQL Server 2012, the maximum memory usage settings is not a hard limit. I've read some SQLOS Team blogs about this, and those as well as several MSDN articles seem to state that the maximum memory usage is just that - a maximum.  But that doesn't appear to be accurate.  I have a QA environment and the app and SQL are on the same server (this is typical for our application). I've tried setting maximum memory usage to both 8GB and 4GB, but eventually, usually within 24 hours, SQL is utilizing about 13GB (all available) of memory.

Is there an actual way to hard limit SQL Server to not use more than x GB of memory?

Edit: is it possible that the databases themselves being in SQL Server 2008 compatibility mode is causing this?

Target Memory (Performance Counter) is 100% of the 4GB maximum memory value I gave it. Now, of course, it's currently lower because I had to restart SQL Server services to allow anything else to work properly.

select * from sys.dm_os_process_memory:


texts: Does changing the length limit (type-modifier) of varchar() result in a table or index rewrite?

texts:
Following on the heels of this question does changing the length limit (type-modifier) of varchar() result in a table rewrite or a lock that takes more time than changing the CHECK constraint? From that question you can see that claim by Brandur,

  If you ever want to change the length, ALTER TABLE requires an exclusive lock (see https://www.postgresql.org/docs/current/static/sql-altertable.html …). Changing CHECK is instant. When answering a question that called into question text CHECK (char_length(email) <= 255) vs varchar(255)

It seems this may have originated with a post by Depesz, in his post from 2010, "CHAR(X) VS. VARCHAR(X) VS. VARCHAR VS. TEXT – UPDATED 2010-03-03"

  So, what happens with when you make the limit larger [with varchar]?

  PostgreSQL has to rewrite the table. Which has 2 very important drawbacks:
  1. Requires exclusive lock on the table for the time of operation
  2. In case of non-trivial tables, will take considerable amount of time

You can see this again in a comment here (2017),

  Still, I'd take a sub millisecond diff on every INSERT over a possible table lock when I'll want to convert my VARCHAR(50) to VARCHAR(250).

And again in here (2012),

  As an aside: I never use varchar if I can avoid it - especially not with length modifier. It offers hardly anything that the type text couldn't do. If I need a length restriction, I use a column constraint which can be changed without rewriting the whole table.

And clearly there are others that doubt the claim.

It may be worth just addressing this single claim.


texts:
I have a data set similar to the one below. What I'm trying (and failing) to do is calculate the number of days past due each payment is on any given day between the first payment and today.

But here's the catch, each amount paid must be deducted from the earliest amount due first then anything left over will be deducted from the next amount due.

So in the example below the earliest payment overdue would be row 1 until this is fully paid, which is on the 26/08/16 then row 4 would become the earliest payment overdue. This is because the cumulative amounts paid up to an including the 26/08/16 would be £2656.15 which would cover amounts due on rows 1,2 and 3.

RowNumber   AccountNumber   date        AmountDue   AmountPaid
1           1000            05/11/2015  1422.5      0
2           1000            26/11/2015  474.17      0
3           1000            26/12/2015  474.17      0
4           1000            26/01/2016  474.17      0
5           1000            26/02/2016  474.17      400.31
6           1000            26/03/2016  474.17      474.17
7           1000            26/04/2016  474.17      0
8           1000            26/05/2016  474.17      0
9           1000            26/06/2016  474.17      474.17
10          1000            26/07/2016  474.17      0
11          1000            26/08/2016  474.17      1307.5
12          1000            26/09/2016  474.17      0
13          1000            26/10/2016  474.17      0
14          1000            26/11/2016  474.17      434.17
15          1000            26/12/2016  474.17      434.17
16          1000            26/01/2017  474.17      0

Any help would be very much appreciated!


texts: How to select second last row from table?

texts:
Table Name :- ModelParam_EMS

Primary Key :- ParameterID

No of Rows :- 500

want to select 499th row from table only.

Note :- No. of rows can be less or more but not fixed.

Create Table :-

            CREATE TABLE `modelparam_ems` (
              `MakerModelID` smallint(5) unsigned NOT NULL,
              `ParameterID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
              `Category` tinyint(3) NOT NULL DEFAULT '1',
              `ParameterName` varchar(50) NOT NULL,
              `ParameterAdd` smallint(5) NOT NULL,
              `ParamterType` tinyint(3) unsigned NOT NULL,
              `DataTypeIndex` tinyint(3) unsigned NOT NULL,
              `DefaultResolutionIndex` tinyint(3) unsigned NOT NULL,
              `ParamUnit` varchar(20) NOT NULL,
              `ParamMaxVal` varchar(40) NOT NULL,
              `ParamMinVal` varchar(40) NOT NULL,
              `ParamVal` varchar(40) NOT NULL,
              `CreatedByID` smallint(5) unsigned DEFAULT NULL,
              `CreatedOn` datetime DEFAULT NULL,
              `ModifyById` smallint(5) unsigned DEFAULT NULL,
              `ModifyOn` datetime DEFAULT NULL,
              `chanelno` int(11) DEFAULT NULL,
              PRIMARY KEY (`ParameterID`),
              KEY `MkrMdlId` (`MakerModelID`),
              KEY `DTIndex` (`DataTypeIndex`),
              KEY `ResIndex` (`DefaultResolutionIndex`),
              KEY `CrtByID` (`CreatedByID`),
              KEY `ModfyById` (`ModifyById`),
              KEY `paramTp` (`ParamterType`),
              KEY `Categor` (`Category`),
              KEY `idx_modelparam_ems_parameterid` (`ParameterID`),
              CONSTRAINT `Category` FOREIGN KEY (`Category`) REFERENCES `category_ems` (`categoryid`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `m2m3` FOREIGN KEY (`MakerModelID`) REFERENCES `makermodel_ems` (`MakerModelID`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `modelparam_ems_ibfk_1` FOREIGN KEY (`CreatedByID`) REFERENCES `userlogin_ems` (`UserID`) ON DELETE SET NULL ON UPDATE SET NULL,
              CONSTRAINT `modelparam_ems_ibfk_2` FOREIGN KEY (`ModifyById`) REFERENCES `userlogin_ems` (`UserID`) ON DELETE SET NULL ON UPDATE SET NULL
            ) ENGINE=InnoDB AUTO_INCREMENT=10012 DEFAULT CHARSET=utf8;


texts:
Goal
Locate a specific word among tables in a database and display the name of the table and column in a list.
Problem
There are TOO many tables to locate a specific word in the database.
Info

Using SQL Server 2012
Every table use a 'schemaname.tablename'


texts: 'Row Chaining/Overflow' in SQL Server when adding column to table?

texts:
Each row in a table can be 8060 bytes, since page size is 8KB. If a row size exceeds this, the largest column is moved to another page ROW_OVERFLOW_DATA and a pointer is created on the original page.

What happens when a new column is added to an existing table ? Is the data stored at the 'end' of the table, and pointers are created after each existing row in a page to point to this new data ? Clearly this would cause performance issues..

Or is the entire table reorganized so the new column 'fits' in the original page ?

We're running SQL 2012 (& above) Enterprise, but also interested to hear about lower versions.
I'm interested in both heaps & clustered indexes, because in either one, the existing row must be modified - to add the new column, or to add a pointer to the new column, right ?


texts: Can you use SQL Server Development Edition for a website?

texts:
I've got a website I'm developing, and it's database driven.  If I use SQL Server Development Edition as the back end, is that "legal"?  I'll be the only developer using the software, but it will be accessed/queried by dozens/hundreds/thousands of other people.

I apologize if this seems rudimentary, but I've never done this outside of a company so I don't really understand product licensing.


texts: Constraint to prevent 'duplicates' only when column > 0

texts:
I have a table with a fk column that should be unique when another int column is > 0. If that sentence doesn't make sense, here is an example of valid rows:

CREATE TABLE foo
AS
  SELECT fk,int
  FROM ( VALUES
    ( 1, -12 ),
    ( 1,  20 ),
    ( 1,   0 )
  ) AS (fk, int);

Adding a new row to the table with a fk value of 1 and a int column that is > 0 would be invalid.

Is there a way to do this with a constraint or index? I feel like an exclude constraint nearly gets me there, but I couldn't figure out a way to get it to work.

I'm using Postgres 9.6.2.


texts: How to identify how many instances of Oracle are installed on Linux environment

texts:
Last day, I was at one training for Oracle installation and service start & shutdown. There I came to know about some Linux commands to start and shutdown an Oracle instance on a Linux machine.

To identify the instance running : The command below will return all Oracle instances which are currently running on that machine.

ps -ef | grep pmon

This command returns following results:

oracle    823     1  0 Dec03 ?        00:00:17 ora_pmon_instance1
oracle  19632     1  0 Nov17 ?        00:06:35 ora_pmon_instance2
oracle  24199     1  0 Nov20 ?        00:05:23 ora_pmon_instance3

Where instance1, instance2 and instance3 are 3 installations of Oracle on Linux, which are currently running.

My query: So, ps -ef will only show processes currently running. Suppose, instance3 is down and you need to start that instance. But, you don't know how many Oracle instances there are on the machine.

How would you get to know that this instance is down?

In Windows, there is a way called services, where you could came to know that these installations are done on Windows for Oracle.


texts:
As per MSDN

  There are currently ten SQL Server languages. The report server
  language for a given report server instance will always be one of the
  ten available languages. Support for all ten of the SQL Server
  languages is provided in a single Reporting Services installation.
  There are no language packs to install.

However, as per the page for sys.syslanguages there are many more listed

How should these 2 pages be interpreted together? Once says there are 10 SQL Server languages, and Report Server supports those 10. The other says there are many more, so are these supported by Report Server or not?


texts: We have a SSIS package which is using a Excel destination. It was brought to my notice that the job was failing with the common error message 'Microsoft.ACE.OLEDB.12.0' is not registered. I checked the server and could locate this driver in the registry. I exported the package into my local and execute it and it ran successfully. I thought of checking to rebuild the package again and test it from the server. Is there any other idea/solution? Please suggest.


texts: Replication from MySQL 5.0 to MariaDB 10.2.8 - Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193

texts:
We are finally upgrading our MySQL 5.0.90 environment to MariaDB, and we would like to enable replication from MySQL 5.0 to MariaDB 10.2.8 and then cutover thus providing minimal downtime. I tried this originally with MariaDB 10.1.18 and it worked fine. MariaDB 10.2.8 became GA so we moved our upgrade process to that version. Now when I try to begin replication I get the following error in the MariaDB mysqld.err log:

Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193

When I execute 'show slave status' I see the following:

Slave_IO_Running: No
Slave_SQL_Running: Yes

Last_IO_Errno: 1595
Last_IO_Error: Relay log write failure: could not queue event from master

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

I've tried to do some research on this but have been unable to resolve the problem.

Here is the entire section of the log where replication is starting.

2017-11-20  8:26:43 140154253575936 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.002735' at position 98, relay log './mysqld-relay-bin.000001' position: 4
2017-11-20  8:26:43 140154252973824 [Note] Slave I/O thread: connected to master 'repl@scvm50.dev.ut1.omniture.com:3306',replication started in log 'mysql-bin.002735' at position 98
2017-11-20  8:26:43 140154252973824 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193
2017-11-20  8:26:43 140154252973824 [ERROR] Slave I/O: Replication event checksum verification failed while reading from network, Internal MariaDB error code: 1743
2017-11-20  8:26:43 140154252973824 [ERROR] Slave I/O: Relay log write failure: could not queue event from master, Internal MariaDB error code: 1595
2017-11-20  8:26:43 140154252973824 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.002735', position 98


texts: What is an incremental checkpoint (in the context of indirect checkpoints)?

texts:
I was using extended events to trace checkpoint activity of a database with indirect checkpoints enabled. The event stream looks like this:

And the details of the selected event are:

The description of the selected event is:

  Chose to enqueue an incremental checkpoint as we are near our recovery
  target

It was determined using the following query:

SELECT name, description FROM sys.dm_xe_objects WHERE name LIKE '%checkpoint%' ORDER BY name

I'm trying to better understand how indirect checkpoints work under the covers. What is an incremental checkpoint? What IO patterns will it cause? How does it relate to a "normal" checkpoint?

I have studied the docs about checkpoints but I could not find any information about this.


texts: How does postgres use the schema search path when making indexes?

texts:
Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app.

I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table in a non-PK field, say email.

If I set my schema search path with SET search_path TO A,B;, then I can run:

CREATE TABLE foo (...);

and it creates table foo in schema A.  The following migration fails, though:

CREATE TABLE bar (...);
CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);

with

Index name 'index_bar_on_email' on table 'bar' already exists

This suggests that create table and create unique index handle the search path differently.  Is there a way to coax PG into making the indexes in schema A?

Update

The issue seems to be related to how Rails interfaces with Postgres.  After the Users table is created, the following queries are run before Rails attempts to create the index:

SELECT DISTINCT i.relname, d.indisunique, d.indkey, t.oid, am.amname
 FROM pg_class t, pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND t.oid = d.indrelid
 AND t.relname = 'users'
 AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('B','A') )
 AND i.relam = am.oid
 ORDER BY i.relname

SELECT a.attnum, a.attname, t.typname
 FROM pg_attribute a, pg_type t
 WHERE a.attrelid = 311384
 AND a.attnum IN (2)
 AND a.atttypid = t.oid

Based on the result of one or both of these, which seem to be schema-agnostic queries, I am guessing that a non-empty return is telling Rails that this index already exists and so it's not even bothering to try.

For now, I'm just going to rename the indexes, but this might be a bug in the Rails Postgres adatper.


texts: send_db_mail with attachment query can't see user databases

texts:
I have a SQL Agent job that calls send_db_mail with the following code:

DECLARE @FileName VARCHAR(30)
SET @FileName= 'FileName'+CONVERT(VARCHAR(20),GETDATE(),112)+'.csv'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Admins',
    @recipients = 'my@email.address.com',
    @body = 'Attached are the results of the query.',
    @query = 'SELECT columns from MyDatabase.dbo.MyTable',
    @subject = 'Test Run',
    @attach_query_result_as_file = 1 ,
    @query_attachment_filename = @FileName,
    @query_result_separator = ',',
    @query_result_no_padding=1,
    @query_result_header = 0,
    @query_result_width = 32767,
    @exclude_query_output=1,
    @append_query_error = 1;

When I run that code through SSMS, I get an email, and it works fine. When I run the job, it succeeds, but I receive no email.

I have checked the following:

Nothing appears in sysmail_allitems
No errors appear in sysmail_event_log
If I change @query = 'select 1 as squirrel' it will send an email from the job.
If I change @query = 'select name from master.sys.databases' it will send an email from the job.

There is no error message given when no email is sent, and the job even lists as succeeded.

I'm not sure where to look next - can anyone suggest where to look for problems?


texts: Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5

texts:
How do I calculate the percentile of the nth element in a field relative to the ones previous to it in a time series for each subset (partition) of my data?

For example, this is exactly what I want to do, except it is a cumulative sum, not a percentile:

select *
from
(SELECT trade_date, daily_val, id, company_id, sum(daily_val)
    over (partition by company_id order by trade_date rows between unbounded preceding and current row) AS sum
FROM daily_data
where (company_id = 858 or company_id = 726) and wh_calc_id = 156) as x
order by company_id,trade_date

This is what I want to do:

select *
from
(SELECT trade_date, daily_val, id, company_id, percent_rank(daily_val)
    over (partition by company_id order by trade_date rows between unbounded preceding and current row) AS pctl
FROM daily_data
where (company_id = 858 or company_id = 726) and wh_calc_id = 156) as x
order by company_id,trade_date

(Note: the only difference between the two is sum() vs percent_rank())

It seems I need a percentile rank function available in PostgreSQL 9.5 that takes a sorted expression argument. I know it exists for more recent versions:
https://www.postgresql.org/docs/9.5/static/functions-aggregate.html

And the 9.3 version doesn't seem to take an argument:
https://www.postgresql.org/docs/9.3/static/functions-window.html

I really don't want to upgrade PostgreSQL as I already have a lot going on right now.

Here is some simplified example output of what I want (created in Excel):

trade_date  daily_val   pctl    company_id
1/1/1900    1484.881621 1.000000    1
1/2/1900    1465.465315 0.000000    1
1/3/1900    1471.244094 0.500000    1
1/4/1900    1475.109578 0.666000    1
1/5/1900    1484.881621 0.750000    1
1/6/1900    1508.863507 1.000000    1
1/7/1900    1519.089431 1.000000    1
1/8/1900    1504.819339 0.714000    1
1/9/1900    1523.210769 1.000000    1
1/10/1900   1514.990335 0.777000    1
1/11/1900   1550.595515 1.000000    1
1/12/1900   1529.454907 0.909000    1
1/13/1900   1546.324863 0.916000    1
1/14/1900   1542.077672 0.846000    1
1/15/1900   1561.396941 1.000000    1
1/16/1900   1519.089431 0.533000    1
1/17/1900   1498.774337 0.312000    1
1/18/1900   1486.847801 0.294000    1
1/19/1900   1488.819195 0.333000    1
1/20/1900   1506.83871  0.473000    1
1/21/1900   1500.777505 0.400000    1
1/1/1900    149.113683  1.000000    5
1/2/1900    155.505015  1.000000    5
1/3/1900    154.15655   0.500000    5
1/4/1900    160.425942  1.000000    5
1/5/1900    156.29799   0.750000    5
1/6/1900    164.058078  1.000000    5
1/7/1900    159.935225  0.666000    5
1/8/1900    158.537455  0.571000    5
1/9/1900    154.147639  0.125000    5
1/10/1900   155.722962  0.444000    5
1/11/1900   155.505015  0.300000    5
1/12/1900   162.766282  0.909000    5
1/13/1900   169.264949  1.000000    5
1/14/1900   167.73179   0.923000    5
1/15/1900   162.280915  0.714000    5
1/16/1900   159.906452  0.533000    5
1/17/1900   161.04602   0.687000    5
1/18/1900   157.656379  0.411000    5
1/19/1900   154.478044  0.166000    5
1/20/1900   154.576546  0.210000    5
1/21/1900   154.147639  0.050000    5

ntile doesn't work either.


texts: Cannot enter psql command "FATAL: the database system is starting up"

texts:
I cannot login into postgresql server using  psql command (to create / update / delete table) because some sort of error like below.

the command :

[root@Server-Database pg_log]# su postgres
bash-4.1$ psql
psql: FATAL:  the database system is starting up
bash-4.1$

I am using postgresql 9.2 in CentOS 6.3

here is log file

[root@Server-Database pg_log]# tail -f postgresql-Mon.log
FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
....

startup log :

[root@Server-Database data]# tail -f /var/lib/pgsql/9.2/pgstartup.log
could not write to log file: No space left on device
could not write to log file: No space left on device
could not write FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 5872) running in data directory "/var/lib/pgsql/9.2/data"?

for information, this server have a crash one day ago, because empty hdd space, this is the space after crash solving :

Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_serverdatabas-lv_root
                      439G  408G  8.5G  98% /

server successfully restarted (look command below), but things still not resolved, I cannot enter psql command

[root@Server-Database data]# /etc/init.d/postgresql-9.2 restart
Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]


texts: Soccer league ER diagram (implementing a many to many relationship)

texts:
I am planning on creating a database to track teams and players in a soccer league. The stats I wish to track are as follows...

goals scored/conceded(gk), assists, minutes played, cards received and leading scorers.

As well as overall standings and fixtures and results. I have created an ER diagram and I think I have all the required tables but have some m:n relationships which I know shouldn't exist so am hoping my relationships are correct

Update
updated er diagram

I may or may not include the second tire of this league otherwise I don't think I actually need the league table.

will it cause me any problems if i implement it as is. i am not sure if it is ok to have 2 cols in the fixtures table that are referencing one column from the teams table. And my goals_scored table has the same two fk's as my player_match table.

Am I anywhere near the right track? Any help would be appreciated.


texts: Existence of Missing Index in MySQL. Does it exist?

texts:
I have been working a bit with MySQL and SQL Server, and I have a question: if the "Missing Index" script exists in MySQL

In SQL Server I know that it shows the missing indices of each table in a database, and by that I would like to know if it exists in MySQL or something similar to this SQl Server script that can be executed.

Beforehand I would be very grateful.


texts: MySQL is using swap memory while I have plenty of free RAM

texts:
I have a 6 GB buffer pool size for my database; however, mysqld process is moving to swap while there is plenty of RAM available. I think pool size and pages along with pool of reads and pool of read requests should be tuned to avoid such situation.

Below is my current configuration:

[fuad@damra mysql]$ free -m
         total       used       free     shared    buffers     cached
Mem:          7872       7138        734          0        304        678
-/+ buffers/cache:       6155       1717
Swap:        32767        456      32311

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_pages_data         | 316153      |
| Innodb_buffer_pool_bytes_data         | 5179850752  |
| Innodb_buffer_pool_pages_dirty        | 20          |
| Innodb_buffer_pool_bytes_dirty        | 327680      |
| Innodb_buffer_pool_pages_flushed      | 51645307    |
| Innodb_buffer_pool_pages_free         | 75279       |
| Innodb_buffer_pool_pages_misc         | 1776        |
| Innodb_buffer_pool_pages_total        | 393208      |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 0           |
| Innodb_buffer_pool_read_ahead_evicted | 0           |
| Innodb_buffer_pool_read_requests      | 26268346860 |
| Innodb_buffer_pool_reads              | 496         |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 207971337   |
+---------------------------------------+-------------+

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 6442450944     |
+-------------------------------------+----------------+

Do I need to tune these values to achieve better results and to not let MySQL switch to swap disk unless RAM is fully utilized?

This is a dedicated database server holding two MySQL schemas. Both schemas are serving the same application (one schema is to store statistics).

I can't change swappiness as this is not an option for the VMware administrator. As for RAM, I have 734+678 (cached memory can be re-used).

I have Java installed on this server to serve the JDBC driver between my application and MySQL.

Red Hat Enterprise Linux Server release 6.7 (Santiago).


texts: I am reading SQL Server 2012 Internals book now. There are several phrases with SOS acronym, such as "SOS Scheduler" (in chapter about the scheduler), "SOS Memory node" (in chapter about the free buffer list and the lazywriter). But I can't find the meaning anywhere.


texts: What are the differences between these two database structures?

texts: Sorry if this question seems broad. These two database structures are based off the same thing made two different ways. I was wondering if there are advantages/disadvantages to the two.


texts: Commands creating format files to import txt file into sql server

texts:
I'm trying to create a format file using bcp utility following the instructions on this page:

Using a format file to bulk import data

https://technet.microsoft.com/en-us/library/ms178129(v=sql.105).aspx

However, this page only discusses how to create format files for tables in the databases. My target is creating a format file for a txt file on my disk and then using the format file to import the raw data in the text file to the database. I tried the following command:

bcp C:\data.txt format nul -c -f txt1.fmt -T

and get the following error:

A valid table name is required for in, out, or format options.

I'm using sql server 2016 and my system is windows 10 64 bit.

Can anyone help me with this, thanks in advance for your help!

Jason


texts:
Query:

select date,TO_CHAR(date,'dd/MM/yyyy HH24:mm:ss')  from user.table  where rownum<10;

02-08-18 05:21:02.301000000 PM  02/08/2018 17:08:02

Problem is minutes in above time are different 'mm' loses the precision of minutes and probably providing some garbage value. How do I format dates without losing precision?


texts: Difference between Generate Script option at database level and backup and restore option

texts:
When doing that generate scripts on the database level what difference does it do from doing a backup and restore on the database? It seems to me that they both provide the same purpose. What would be best to use between the two? Is one better than the other?
I am migrating database from one domain to another and both are different domain but have sql server 2012


texts: How to create a DB Link to a Database which has failover in TNS

texts:
Could you please help me to understand how my DB LINK creation sql look like to a database (RAC env, Oracle 12c) which have fail over  option in tns entry?

I read below post from Oracle-(not having access to view below url since I do not have Oracle Support membership)

  Don't create a DBLink using a TNS connect string configured for failover. #Database links don't support TAF or Failover. See the following: Database Link: DBLink Creation using TAF or SCAN TNS Connect String (Doc ID 399453.1)
  https://support.oracle.com/knowledge/Oracle%20Database%20Products/399453_1.html

Does it means that we have to remove fail-over, connect etc options from tns entry and then create db link?
My TNS entry is below(just a sample)

panda.host.com=
(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=OFF)
(CONNECT_TIMEOUT=3)(RETRY_COUNT=10)(RETRY_DELAY=1)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))

I am now trying to create a DB link in another database pointing to this database so based on above fact which of below sql is correct and will be working? In 'using' clause I am providing connection descriptor since I am not allowed to make changes in tnsnames.ora.

1.

   CREATE DATABASE LINK TEST_LINK connect to REMOTE_SCHEMA identified by "MyPass" using
'(DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=OFF)
    (CONNECT_TIMEOUT=3)(RETRY_COUNT=10)(RETRY_DELAY=1)
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))';

2.

CREATE DATABASE LINK TEST_LINK connect to REMOTE_SCHEMA identified by "MyPass" using
'(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda.host.com )(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP )(HOST = panda-sb.host.com )(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = panda_app.host.com ))))';

//failover and connect options are removed.