Same field but different contents I'm building a database for a divination system. Just to make it easy, I'll explain it in terms of the i ching.

Every element of a hexagram is a different table. There's one for yin and yang, one for the 3 positions in a trigram, one for the trigrams, one for the hexagrams. There's also a table for the correspondences, which is linked to each of the other tables - correspondences for yin or yang, for the 3 positions, the trigrams, and the hexagrams. There is also a table with commentary, which also links to each of the other tables (except for the correspondence table).

So, the table for the trigrams, for example, might be structured like this:

TriID
TriName
Line1                    -FK (YinYangID)
Line2                    -FK (YinYangID)
Line3                    -FK (YinYangID)
TrigramMeaning
Correspondence           -FK (CorrespondencesID)
Commentary               -FK (CommentariesID)

With data like:

 TriID  TriName Line1 Line2 Line3 TriMeaning Correspondence  Commentary
 1      Qian    Yin   Yin   Yin   Receptive  1               1
 2      Kun     Yang  Yang  Yang  Generation 2               2

The Correspondence table looks something like this

 CorrID    Person    Season     Color     Animal    Number     Element
 1         Father    Winter     Blue      Bear      3          Metal
 2         Mother    Summer     Red       Deer      6          Earth

(I know that all my I Ching data's wrong...)

The idea is that the same correspondences are required for every table, so the correspondence table would have an entry for each of the other tables. CorrespondencesIDs 1 through 4 might apply to the YinYang table, for example (Old and New Yin, old and new Yang), and then 5 through 13 to each of the trigrams, and so on.

It seems like this would work just right. On the other hand, it feels intuitively like I'm doing something wrong and breaking some rules. Since every entry on the correspondence table will be different, it seems like there should maybe just be similar entries on every table:

TrigramPerson
TrigramSeason
TrigramColor
TrigramAnimal

and then

HexagramPerson
HexagramSeason
HexagramColor

and so on.

What is the smart / best way to structure the database? Apologies if this is a dumb question - I'm new to database design, and some of these concepts still confuse the heck out of me.

I had to re-read your question a few times to get the sense of what you are trying to do.  What I suggest is something I call a class-based multi-multi join.  In some db's like PostgreSQL, you can then do some advanced referential integrity checks on the joins, but that's outside my answer.

suppose you have a table:

CREATE TABLE correspondence_class (
     id int not null unique, -- hand-assigned
     label text primary key, -- hand-assigned, don't want duplicate labels
);

Then we can:

INSERT INTO correspondence_class (id, label)
VALUES ('1', 'person'), (2, 'color'), (3, 'season'), -- and so forth

From there we can then have two more tables:

CREATE TABLE trigram_assoc (
   trigram_id int references trigram(id),
   c_class_id int references correspondence_class(id),
   correspondence text not null,
   PRIMARY KEY (trigram_id, c_class_id)
 );

you would then do the same for hexagrams, lines, etc.  In PostgreSQL, you could change your primary key to include correspondence too, and uniquely index only those correspondences attached to some correspondence classes.  You could also have deferrable constraint triggers that could be used to ensure that all relevant associations were populated, but how exactly to do this on other dbs is a good question.


show grants for someuser not working for root I have two servers both running on Version(): 10.3.21-MariaDB

On server1 when I log into mysql as root and execute the command: show grants for someuser; It shows me all the grants for that user.

On server2 when I log into mysql as root and execute the command: show grants for someuser; I get this error:

  ERROR 1141 (42000): There is no such grant defined for user 'someuser'
  on host '%'

On both servers when I execute the command:

select user from mysql.user;

I get a list of the users. So the users that I am trying to see their grants are there ...

What is would be different with server2 that root can not see the grants ?

on all the servers is_role = N

but I did notice another difference among the 3 servers - the root user has different host entries
Also I have added the vm type that the server is running
also have results for a 3rd server

server3 (virtuozzo)
show grant must have the @somehost
using all of the different host entries give a result with the only difference being the @somehost is whatever was used in the for someuser@somehost

GRANT USAGE ON . TO 'someuser'@'somehost'  indentified by ....
GRANT USAGE ON . TO 'someuser'@'ip address'  indentified by ...
etc.

root user entries:
| root      | localhost             | N       |
| root      | server.example.com    | N       |
| root      | 127.0.0.1             | N       |
| root      | ::1                   | N       |

server2 (vmware)
show grant must have the @host
only the @ip address gives a result

root user entries:
| root                | localhost             | N       |
| mysql.session       | localhost             | N       |
| mysql.sys           | localhost             | N       |

server1 (kwm)
show grant does not have to have the @host
using all of the different host entries give a result with the only difference being the @somehost is whatever was used in the for someuser@somehost

GRANT USAGE ON . TO 'someuser'@'somehost'  indentified by ....
GRANT USAGE ON . TO 'someuser'@'ip address'  indentified by ...
etc.

root user entries:
| root               | localhost                         | N       |
| root               | cent6base-cpanel.pxe.bluehost.com | N       |
| root               | 127.0.0.1                         | N

I'm only guessing here, but could it be that you have defined different host values for someuser on server1 and server2?

When you execute SHOW GRANTS FOR someuser; it really executes SHOW GRANTS FOR someuser@'%'; - that is, if you don't specify the host part, the it will default to using the wildcard (%) for the host part.

So if you have someuser@'%' on server1 and e.g. someuser@'localhost' on server2, then you will get the result you have observed.

You can see the host part of the users by executing SELECT user, host FROM mysql.user;

And then you can view the grants for the different users by executing SHOW GRANTS FOR someuser@'localhost'; or similar for users with other host values in their mysql.user rows. Note that each row in the table is a different user - it doesn't matter that some of them might have the same user names as long as they have different host values.


Azure SQL Database throwing error 1132 "The elastic pool has reached its storage limit" error, even if it's not full I'm trying to execute the following (sample) batch in an Azure SQL Database inside an Elastic Pool:
drop table if exists [dbo].[InsertTest];
create table [dbo].[InsertTest] (
    [id] uniqueidentifier,
    [filler] nvarchar(max)
);

insert into [dbo].[InsertTest] ([id], [filler])
select top 1000 newid(), replicate('X', 2048)
from [sys].[objects] as [T1]
    cross join [sys].[objects] as [T2];

/* drop table if exists [dbo].[InsertTest]; */

But the insert statement fails with the error message:
Msg 1132, Level 16, State 1, Line 1
The elastic pool has reached its storage limit. The storage used for the elastic pool cannot exceed (51200) MBs.

I tried to see if I ran out of space by running the query
select
    [type_desc]         as [file_type],
    [size] / 128        as [size_in_MB],
    [max_size] / 128    as [max_size_in_MB]
from [sys].[database_files];

but it seems that I still have space left.

file_type
size_in_MB
max_size_in_MB

ROWS
320
256000

LOG
584
1048576

FILESTREAM
0
0

What can I do about it? Am I missing something?

The error message refers to the elastic pool so the problem might relate to another database than the current one. You can retrieve the information about the elastic pool using the following DMV query vs the Master database
SELECT TOP 5 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB, *
FROM sys.elastic_pool_resource_stats


Oracle 11 query functions default value Is there any way to select the default value a procedure/function has for a specific parameter in the procedure/function ?
user_arguments has a field called default_value (long) but I do not see any value in the field.
Thanks.

As documentation says, it is not available yet.

  DEFAULT_VALUE    LONG      Reserved for future use

ALL_ARGUMENTS

Bug:183707  PLSQL COMPILE DOES NOT POPULATE DEFAULT ARGUMENT COLUMNS IN ARGUMENT$


what server role that needs to be configured for the user to enable sql server agent I'm on SQL SERVER 2008,

I want to enable the SQL Agent for a particular user, who has only read access to the database. I don't want to give sysadmin role?

Is there any way, I can do this?

from BOL :

  To configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.


When I run mssql-cli, I get "No module named mssqlcli" I just installed mssql-cli with pip,

pip3 install mssql-cli

Then I try to run mssql-cli and I get

$ mssql-cli
/usr/bin/python: No module named mssqlcli

I tried to fix this by submitting a patch #234 (approved but not yet committed). You'll have to edit the file ~/.local/bin/mssql-cli and replace

python -m mssqlcli.main "$@"

With this line

( command -v python3 && python3 -m mssqlcli.main "$@" ) || python -m mssqlcli.main "$@"


Know what tables each user is accessing 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?

This would have a considerable impact on your system, but you could enable the MySQL General Query Log by running the following:

SET GLOBAL general_log = 'ON';

The default setting for the logs will output to a file that can be identified by running this:

SHOW GLOBAL VARIABLES WHERE Variable_name = 'general_log_file';

Otherwise, you can change the setting to log in a table with the line below, and then you would be able to run queries on the table mysql.general_log

SET GLOBAL log_output = 'TABLE';


Information on a Database that was restored with NO RECOVERY I'm working with SQL Server 2008Sp3.

I have a database that was restored from a full backup WITH NO RECOVERY and it then was restored with Transaction log backup files - also WITH NO RECOVERY.

At the moment the database is in Restoring status.

Let's say that couple of hours after the last transaction log restoration I want to continue restore the new 2 hourly transaction logs that were created, and then restore it WITH RECOVERY.

How can I query the status of the database, so I will know what was the last Transaction log backup that was restored on that database?

The reason I'm asking that is because I would like to automate this in a script -

I want to create a daily full restoration of a database on a separate server with all the available Transaction log backups WITH NO RECOVERY, and then upon request (when it's necessary), I'll be able to execute another script that will restore the new Transaction log backups that were created since the end of the daily script + restore it WITH RECOVERY.

My question is regarding the second separate script - finding the rest of the needed Transaction log backups.

Thanks in advance,

Roni.

I'm not sure if I fully understand your question. First you say you want to find the last restore made to a certain database. You can find all relevant information (databases, files, lsns, times) on these restores through

SELECT *
FROM [msdb].[dbo].[restorehistory] r
INNER JOIN [msdb].[dbo].[backupset] b
ON b.backup_set_id = r.backup_set_id

But I'm not sure what you mean with "finding the rest of the needed backups". If you want to look at the backups made in an instance you can do this through

SELECT * FROM [msdb].[dbo].[backupset]

Hope that helps.


When normalizing a relational database, should multiple "type" tables be combined into one? Before making substantial changes to the content, functionality and design of my website (I'll be refactoring everything, almost a complete rewrite), I'm setting up a new PostgreSQL database to hold all the data previously in a MySQL database. I also have a heavily-customized WordPress blog that I'm going to move over to this website, effectively merging the two sites. Apart from the front-facing content for the website, there is also a lot of other information that I store in the database for my own use, such as client information, sales history, invoices, event income, etc. I'm taking a lot of time thinking things through carefully and trying to re-design the database properly with both data integrity and performance in mind.

The front-facing website will have many different "content" types: pages, events/gigs, multimedia samples, sheet music/charts (some of which will be available for sale in PDF format on the site), blog articles, etc.

One of the changes I've made is to the way I store venues/places. I used to have a "venues" table that referenced a "venue type" table as well as a "cities" table, which referenced a "states/provinces" table, which referenced a "countries" table. Most of this information was used by the "events", but then I added client information which needed to reference the cities table. Further still, all of my blog articles are geotagged, so they'll need to reference these places too. Since all the tables had a very similar schema, what I've done is create a new "places" table with "id" and "parent_id" columns so that places can be stored in a hierarchal fashion. I also have a "places_type" table so that I can enforce that the parent_id for each row has to be of a higher hierarchal type than the place itself. (So for example, a city can have a state or country as its parent type, but a city can't have a restaurant as its parent type.)

In doing this, I've created a lot of other tables with the exact same schema (id, parent_id, name) to hold "types" of other content in a hierarchy. Genres, payment methods, chart types, event types, media types, invoice types, etc.

Since all the tables have the exact same schema, would it be better to combine all these tables into a single "types" table and add a "category" field to the table?

I think it would be much easier to manage a single "types" table than eleven similar tables. I've calculated that this "types" table would hold only about 60 rows or so, for now.

I'm wondering what would be the best practice for this sort of thing in terms of database design? The data will be retrieved far more frequently than it will be updated/added.

At the same time, since this one "types" table would hold all the types for everything, it will need to be joined (via a view) many times to many tables. For example, to retrieve event information, I already join an "events" table to the "places", "artists" and "media" tables, but would also need to join these tables to the "types" table to get event types, place types, artist types and media types. So would it be best to keep the type tables separate, or combine them into one? Why?

I would go for separate tables for each type.  With so few rows performance will not be a consideration either way.  Individual tables will only have a page or two each.  Similarly for the combined table.

Either way you will have to join a "type" table to complete your views.  Inserts are infrequent at this level of abstraction so locking is unlikely to be an issue either way.

For me, though, having different tables keeps logically distinct items separated, which is a cleaner design.

There will be many different tables which will have a relationship to a type table of some sort.  If there are many different type tables the foreign key relationships are self-documenting and enforcable through DRI.  With one combined table it is difficult to use DRI to say "Venue.TypeID comes from Type.TypeID but only if the Type.Category = 'V'", for example.  The complementary side of this relationship - "Type.TypeID with Type.Category = 'V' can only be used in the Venue table" - is also not supported in any RDBMS's DRI that I know of.


Cannot open database properties dialog on a database NOT in an availability group Here are four database servers. Database A is in an availability group. Database B is not - an instance exists on each server. On all but one of the servers, I am able to right-click on database B in SQL Server Management Studio and view its properties. On server 2, I get an error message that "the target database ('A') is in an availability group". This is true, but I'm not trying to access that database.

How can I view the properties of the instance of database B on server 2?

To re-iterate: I right-click on database B and select "Properties", and it tells me database A is in an availability group.

I'm logging into SSMS with Windows Authentication. I changed the connection details so my default DB is master, just in case that was the issue (it wasn't).

If it makes a difference, in the availability group for database A, this is the server that has a synchronized copy with automatic fail-over turned on.

Closing and re-starting SQL Server Management Studio fixed the problem.

Thanks to @SeanGallardy-Microsoft.


What happen if i start a Postgresql replication using tar Hi i am following this tutorial every time that i need to set up a binary replication ,in fact i am using the second method called:
"Starting Replication with only a Quick Master Restart"
found here:
https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

That works fine.

In a test environment, i tried to use tar instead of this rsync:

 rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postgresql.pid \
     data/*  192.168.0.2:/var/lib/postgresql/data/

Like this:

tar -cz data >data.tar.gz

Generating the .tar file with the data and uncompressing that on the slave, how bad can this be for the database?

In the logs, slave shows that it connects sucessfully to the master.

Assuming the replication data directory is starting out empty (which it should be, or you are probably doing something wrong), these commands are basically equivalent.  Both read all the data and do something with it.

The tar is going to use a bit more CPU on the server, because it will be running gzip, which will probably max out one CPU as long as there is one free.  It will also consume a bit more IO capacity, because it will be writing the .tgz file to disk rather than streaming directly over the network (but you could change the tar command to stream over ssh instead)

Neither of these should make much of a difference, unless you are already on the brink.

Considering your comment about how much slower rsync is:  I find rsync somewhat slower, but not drastically so.  I wonder if you have dysfunctional implementation of it.  I've heard (vaguely) of versions of rsync which interacted with versions of the Linux kernel in a way that cause context swapping storms.  Anyway, if tar is so much faster, than it will naturally be imposing a higher IO load on the server as it gets all the data read in less time, this might interfere with server operation while it occurs.  On the other hand, whatever rsync is doing that makes it slow might also be consuming resources the database would like to use.

Unfortunately when you got to issues of implementations of one tools interacting with kernel versions, etc. you have an issue that can really only addressed experimentally.

If you are concerned about correctness, both rsync (when used like you show and on an empty target directory) and tar should preserve your data integrity, it is just a matter of which one performs better, and causes the least performance impact on the production server by competing with it for resources.  I prefer tar, because rsync invites people to try to do clever things which place their data at risk.


Any disadvantage to using oraToolkit for installing oracle I am going to install oracle 11gR2 on a redhat enterprise 5.6 machine. Is there any disadvantage to using oraToolkit? For example, could it cause any problem or difficulty for future complimentary installations, like data guard, or RAC?

This is an open question.  It's a tool kit so its certainly possible for you use it in a way that would not be optimal for some other future use.  It may not include tools for some of the things you want to set up or in the way you'd like to set them up.

The oracle installation process isn't difficult, is well scripted and has a nice graphical interface.  It is designed for that specific job and guides you through each step and wherever possible it stops you doing insane things and prompts you if you want to do something odd.  A great many people use this method and so it is well tested and highly supported not just by a few folks but by an army of people at Oracle and on the internet.  That means there is help available from multiple sources and that most likely an answer there for your issue before you even have it.

IMO it is better for you to understand the system you will have to support.  Spend a bit of time reading the installation guide and plan your installation out beforehand based on your needs (give some thought to Oracle Flexible Architecture, backups and redundancy).  Then do a software only install.  After that you can create a database with DBCA which isn't hard to use.  It does all the work but you make most of the decisions (usually from a small list of sensible choices).  The benefit of all this is that you will understand much more about how your installation is set up and that will pay off later.

Later - that leads on a another consideration.  Oracle will always have an installer and a database creation utility for their databases.  It will be designed to work with the version it ships with and it'll get regular updates over time.  Third party tools come and go, tend to lag behind releases and tend only to work in a sub-set of situations.  They might not be available for the next job.  Use caution investing your time in non-standard tools that save your time by reducing your understanding - and having an good understanding of the basics is vital if you are to move onto more complex tasks, especially those not covered by the tool-kit in question.


How do I find the longest string in a row amongst various text fields? Does anyone have an easy query to find the longest string of characters in a data row from ALL alphanumeric (text, nchar, varchar, etc) type columns from ALL tables in a single SQL Server 2008 R2 database?

The following code should return a single column that has the largest piece of text in the current database, along with the Schema, Table, and Column name, and the size of the piece of text:

DECLARE @cmd NVARCHAR(max);
DECLARE @sep NVARCHAR(max);
SET @cmd = '';
SET @sep = '';
SELECT @cmd = @cmd + @sep +
    'SELECT ''' + QUOTENAME(sc.name) + '.' + QUOTENAME(t.name) +
    '.' + QUOTENAME(c.name) + ''' AS ColumnName, ' +
    QUOTENAME(c.name) + ' AS ColumnValue, LEN(CAST(' +
    QUOTENAME(c.name) + ' AS NVARCHAR(max))) AS ColumnLength ' +
    ' FROM ' + QUOTENAME(sc.name) + '.' + QUOTENAME(t.name)
    , @sep = ' UNION ALL '
FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty on c.system_type_id = ty.system_type_id
    INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE t.is_ms_shipped = 0
    AND ty.name IN (
        'ntext'
        , 'text'
        , 'varchar'
        , 'nvarchar'
        , 'nchar'
        , 'char'
        , 'sysname'
        , 'sql_variant'
        );
SET @cmd = 'SELECT TOP(1) * FROM (' + @cmd + ') t ORDER BY 3 DESC'
SELECT @cmd; /* This displays the resulting SQL Text that will be
            EXEC'd by the following statement */
EXEC sp_executesql @cmd;


How to import large sql file in postgres? Trying to import sql file around 8GB using these commands

sudo -u postgres psql yourdb -f my_file.sql
sudo -u postgres psql yourdb < my_file.sql
sudo -u postgres cat my_file.sql | psql yourdb

sudo -u postgres psql
\i my_file.sql

but all of them returning out of memory error message.
Also tried configuration given here

psql generally doesn't need much memory when playing large SQL files, since it doesn't buffer the whole file, only one query at a time, or it uses a COPY stream.

The main situation when it may run out of memory is not when importing, but when SELECT'ing a large resultset, especially on 32 bits systems. This situation is generally solved by setting FETCH_COUNT.

On import, the dump would need to contain unusually large rows to cause client-side memory issues. The maximum size of a column is 1Gb, so it's theorically possible that it would cause trouble on import, if the database has that kind of contents.

If the system doesn't have enough RAM, maybe you could just add swap space.


Tables shared information I would like to have a table pattern on my db. I.e. every column should have

Record_Insert_Date
Record_LastChange_Date
Record_User_Insert
Record_User_Update

The obvious approach is to add these columns on each tables, but is not so easy to mantain. I would like to have this informations shared across db in a single table. Schema of this table should be somthing like

FOREIGN_TABLE_ID
FOREIGN_ROW_ID
Record_Insert_Date
Record_LastChange_Date
Record_User_Insert
Record_User_Update

The questions are

how to join with other tables (two possible options are)

1) Use sys.tables table id as FOREIGN TABLE ID. May change? Should be dangerous in case of db refactory? Performance of query on sys.tables to retreive table name to join dynamically?
2) Add an extra field each table on db to have an integer code that identify it. I.e tableA.TABLE_ID=1, tableB.TABLE_ID=2, ..., tableXX.TABLE_ID=N. Using this approach a simple join is enough to match record. Difficult to use is a table have a multiple key.
3) REVERSE OF 2) Add an extra field each table on db to have an FK to shared table. In this case shared table have an IDENTITY() key instead of (FOREIGN_TABLE_ID, FOREIGN_ROW_ID)

performance of each approach?

I have a couple of questions:

1) Do you need to keep a detailed history of changes? Or only who/when was the record created and who/when last updated it?

2) Assuming no detailed history is needed, the main question I have is whether your alternate solution, of using a separate tracking table(s), will be any easier to maintain than adding the columns to every table. You will either need to: a) add code into every query/stored procedure that manipulates data to update the tracking table; b) add a trigger to every table that needs to be tracked.

3) How often do you expect to query this data? My experience with this type of auditing is you only need it when something bad has happened.

My impression of your options are:

1) I think using the Object_id for the tables as your FOREIGN TABLE ID is a good idea. Why would this change? Do you expect to drop and recreate tables during deployments? And if you do, you can easily map the old object_id with the newly created one and update the tracking table. You could add this logic to your deployments.

2) Wouldn't this negate your reason for not adding the tracking columns into the main tables?

3) See comment for 2), plus I don't think this would work as well as 2).

As far as performance, if you use a separate tracking table, I think it would be best to keep it as separate from the main tables as possible (option 1).

The biggest potential performance boost I can see by using a tracking table is for querying the data. You will be able to tune the indexes specifically for those queries. Also, all your data will be centralised. If the columns are on the base tables additional indexes will probably be an overall disadvantage. BUT!!! Back to my question 3): How often are you going to need to query this data?

As far as day-to-day performance your real impact will come down to how you implement updates of the tracking table. Changes to the stored procedures would be better than triggers in my opinion.

I'm sorry if I haven't really answered your question, but I hope my comments help.


Does Postgresql support different transaction isolation per connection? I just discovered that H2 doesn't support concurrent connections using different transaction isolation levels. Meaning, changing the transaction isolation of one connection affects all other connections.

Does Postgresql support the use of different isolation levels for each connection?

Transaction isolation level is set up per transaction basis. Even one connection can have different isolation level on each transaction.

Of course different connection can have different isolation level.

Here's some testing :

postgres=# begin;
BEGIN
postgres=# show transaction_isolation ;
 transaction_isolation
-----------------------
 read committed
(1 row)

postgres=# set transaction isolation level serializable ;
SET
postgres=# show transaction_isolation ;
 transaction_isolation
-----------------------
 serializable
(1 row)

At the same time on another session :

postgres=# begin;
BEGIN
postgres=# show transaction_isolation
;
 transaction_isolation
-----------------------
 read committed
(1 row)

postgres=# set transaction isolation level read uncommitted ;
SET
postgres=# show transaction_isolation
;
 transaction_isolation
-----------------------
 read uncommitted
(1 row)

Once You commit a transaction and begin another new transaction, default isolation level will be used which set on postgresql.conf :

#default_transaction_isolation = 'read committed'

I try this on Postgres 9.1.

Hope this help, cheers.


Login failed for user even though it has read access I have a SharePoint server which is using service account "spsharepoint".

For some reason this user has started giving 'Login failed for user DOMAIN\spsharepoint' when I checked SQL Server logs. Turns out it is trying to access 'master' database on which it doesn't have access so the error.

I gave read only access to this user on master database but error is still coming. What could be the issue and how do I fix this?

Verify if your user has permision to connect to database Engine.
Right click on Logins -> Properties -> Status.

Or grant CONNECT for user.

CREATE LOGIN [mydomain\mygroup] FROM WINDOWS;
GRANT CONNECT SQL TO [mydomain\mygroup]


A plain text based database The list of the files on my hard disks has grown up to a million, the more or less, of records, each consisting of a eigth or ten fields, es.
support id - file size -  full path name - md5 hash - sha256 hash

All is stored in plain text files realized with the DOS's DIR command.

I must restructure the archive (suppressing unwanted redundance, organizing backup procedures ...), therefore I need a (free for personal use) database system, running under Windows Xp, able to perform simple queries on  the file resulting from the unification of aforementioned lists (or import it) and exporting the result as plain text or XML.
In the past, I have unsuccessfully tried Microsoft Access.

I have no need to share or publish the dataset, because it is for my personal use only.

Please, can you suggest any suitable software ?

Thanks in advances !!

I'm no huge fan of Microsoft Access, but I'd be curious to know why you were unsuccessful at it.  It should easily be able to hold that information and is probably easier to learn than other RDBMS systems.
Having said that, I think either MySql or Sql Server Express would work great.  You would use a text import process (Sql Server has a wizard that makes it easy), bring all the data into one table and then dedup and organize anyway you see fit.

1 million rows with 10 columns wont be a very large table, my guess is it will be somewhere near 500Mb.


MySQL stalls and strange threads/connections behavior is observed We have a MySQL instance which most of the time works just fine. From time to time, however, we observe strange behavior. What happens is that suddenly MySQL connections reach the limit (currently set to 1000) and MySQL pretty much stalls. The following is observed when we check the graphs for this server:

The image is from a Grafana instance running a MySQL exporter. What troubles me is why threads from the cache are not reused. The other question is why the threads in cache suddenly drops to zero but at the same time no new threads are created (according to the second graph - blue line stays at 0).

Ultimately, my goal is to understand why 1000 connections are used all of a sudden and if this is a problem with MySQL or the app. This is what the MySQL docs say in regards to threads:

  How many threads the server should cache for reuse. When a client
  disconnects, the client's threads are put in the cache if there are
  fewer than thread_cache_size threads there. Requests for threads are
  satisfied by reusing threads taken from the cache if possible, and
  only when the cache is empty is a new thread created.

Any assistance or guidance will are highly appreciated!

Thundering herd.  Or, so many people in a grocery store that no one can move their cart.

When Threads_running is 349, each of them is waiting and waiting and waiting for a tiny slice of CPU and a tiny amount of I/O and ...  The effect is that they all take 'forever' to finish.  Meanwhile, others trying to get in are let in because you have such a high max_connections, etc.

But, that does not explain what pushed the herd over the cliff.  It could be something as trivial as a big SELECT that happens to block what the herd normally does.

Do you have the slowlog turned on?  With a low value of long_query_time?  If so, you may already have the answer in the slowlog.  Since you did not panic and reboot, that slow Select (if it really exists) will be in the slowlog.  Based on the graphs, I would guess it might have a time of 80-110 seconds.

As for the dip in Threads_cached, that gets a bit complex.  First, let me explain the non-dip parts.  Before the 'event', about 200 connections were made; each lasted long enough for there to be a 'steady' 200 connected.

After the event, there were so many waiting to get in that it pushed the number up to about 350.

Having thread_cache_size = 500 wastes memory; suggest you lower it to 250.

As for the dip, I will do some guessing.  So many connections were trying so hard to get things done that new connections were denied access.  (This may be deliberate on the part of MySQL, or it may be a side effect of everyone clambering for various mutexes.)

It is also unwise to have max_connections = 1000; this just invites scenarios like this.  Instead, have the clients 'take the heat' when something hiccups -- by being denied access to MySQL.  Furthermore, this makes it easier to say "Oops, were are having a problem please wait, and please do not press Send again, nor Refresh!"  Or simply give a "500".

Bottom line:  I think the slowlog can point to the cause of the event.


Best approach for Legal/Natural can have many Orders I'm working on this model:

where as you may see one NaturalPerson can have many Order and the same for LegalPerson the only difference between NaturalPerson and LegalPerson are a few so in that case will be better to repeat all the Order fields in NaturalPerson and in LegalPerson or will be better to leave as I have right now? My only concern around my model is if is not this a serious inconsistency to leave an empty column at Order table (legal_person or natural_person depends on which the Order belongs to) as the Orders belongs to a Natural or a Legal but not both of them?

What will yours do in this case?

1st approach

For all the pros mentioned at this post I tough the best here is go with Class Table Inheritance so if I understood the explanation my model now is this one:

It's right?

2nd approach

See my own answer below ...

This is an alternative to your solution. I suppose your id columns are just for mysql internal use, so you could have:

person table with id, name, and type which would be a bit or 1 character to say if it is a natural or legal person. This table will connect to order table where you will only need one column - person_id
On natural_person table you remove the name column and connect it to the person table
On legal_person table you remove the name column and connect it to the person table


2 databases in sql server 2005 "Production Server" Can 2 databases decrease the performance in a SQL Server 2005 production server?

Server specs:

Dell Power edge 2950, 2 Quad core Xeon, 16 GB of RAM, 3x73 GB SATA hard drive hot plug, Windows Server 2003 R2 Standard Edition 64 bits and SQL Server 2005.

Our accounting/service software company asked me to make a second database "test", basically is a copy of the live database which is 30Gb. I don't have enough disk space on my server. The live database is on a second partition and 42.2 free space. I have setup SQL Server to use a max of 13Gb RAM and lock pages in memory, leave the rest of memory ram to the OS. I'm concern that to have a copy of the live database in the same server will have a high degradation in performance and to start dumping memory.

Having a test database on a production instance sounds like a very bad idea to me, even without taking performance into account.

However, a second database on the instance does not consume resources per-se. It's the user or application activity that drives load to the instance. Obviously, you will need storage to accomodate the data and log files and that could degrade the NTFS performance if the free space on the volumes goes lower than 20%.

Other than that, buffer consumption and CPU/disk/network time depend on activity, not on the database existance alone.


How to move SSAS tabular instance databases? We have a SQL Server 2012 SSAS tabular instance in our environment that is sitting in domain1. We need to move these SSAS databases in a different SQL server 2012 that is on domain2 which has no trust established with domain1. This is the first time I am tasked to move SSAS in to another server. What would be a good approach in moving these SSAS tabular databases? Is a normal backup and restore would do the process? Or does it have to be attach detach process? Also, when I migrated our SQL Server databases, I have to adjust each account logins since there is not trusts between domains. Do I have to do the same thing with SSAS in this case?

You can back up and restore SSAS Tabular 2012 databases, but you will have to adjust the security role memberships to include users in the correct domain. You can perform the backup and restore manually or through a script in Management Studio (or executed via PowerShell).

You can also re-deploy an SSAS database from the SSDT project or using the SSAS Deployment wizard. The wizard will allow you to deploy roles and ignore members. Next populate the role memberships with appropriate users. From there, you can process the model to populate it and bring the model online.

Another option is to script the SSAS database from Management Studio. Once you do this you can remove the  collection from each role and execute the XMLA on your target server.


Enabling Constraint Silently Fails Why would SQL Server silently fail to re-enable a foreign key constraint?

I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of the constrains are still reported as disabled by sys.foreign_keys.

Example:

ALTER TABLE [dbo].[Table] WITH CHECK NOCHECK CONSTRAINT FK_ConstraintName;
-- Command(s) completed successfully.

SELECT is_disabled FROM sys.foreign_keys
WHERE name = 'FK_Constraint' AND parent_object_id = OBJECT_ID('[dbo].[Table]');
-- returns "1", indicating that the constraint is still disabled

The problem you're facing is that you are not actually re-enabling your constraint (as defined by the NOCHECK).

The syntax for re-enabling a constraint is as follows:

ALTER TABLE <tablename>
WITH { CHECK | NOCHECK }
CHECK CONSTRAINT <constraintname>;

The WITH CHECK|NOCHECK tells SQL Server whether or not you want to check the existing data in the table against the constraint. If you state NOCHECK here, then your constraint will be untrusted, defined by is_not_trusted = 1 in sys.foreign_keys.

By stating NOCHECK as the second part, you're actually saying disable the constraint.

In your case, if you do not want to check the existing data, you'll need to NOCHECK CHECK.

ALTER TABLE [dbo].[Table] WITH NOCHECK CHECK CONSTRAINT FK_ConstraintName;

Further reading on trusting your constraints.


Would a date clustered index speed up queries with date ranges? (SQL Server) I'm not the expert on indexes really - I'm trying to work my way through a few materials now.

Anyway, let's say I have a table with 7 million records spanning 3 years. However the most common query will pull say the last 3 months -- using an integer field (unix epoch time).

This 3 month query usually takes about 18 seconds --- but many other queries may be built atop it, so I was trying to speed it up. I created a clustered index on the unix_epoch_time int field that would eventually be in a 'where' statement filter.

However, this doesn't appear to have sped up the query time at all. Does this mean this clustered index is a spurious waste? Or accounts for something like 1% of the query time? I thought it would be much faster.

You ask if the clustered index is a waste: ideally a clustered index is unique, relatively small and does not change once created (see https://www.red-gate.com/simple-talk/sql/learn-sql-server/effective-clustered-indexes/ ) Your integer field is certainly small but my guess is it's not unique, although it's not possible to tell from the question.

To answer the question you could use an IDENTITY(1,1) as your clustered index key, then create a non-clustered index using your Unix time field. To make the queries faster you can INCLUDE commonly used columns in the non-clustered index, then if queries are covered by the index they need not even visit the main table.

Also if your last 3 months data could be defined in terms of particular date ranges, you could even build a filtered index to limit the size of the index and speed the searches. Based on experience with tables of 1m+ rows, if you are in control of the indexing you should be able to satisfy most queries in less than a second.


Equivalent of MRG_MYISAM in databases other than MySQL? Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?

I know about fragmenting, but this is not quite the same AFAIK.  We're using MRG_MYISAM to avoid large amounts of duplicate data across customer specific databases, so MRG_MYISAM is perfect.

That said, I'd like to know if there are equivalent things in other DBs, particularly other open source DBs.

From the description of the MERGE table type in the MySQL documentation at https://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html it appears this is similar to creating a view that uses a UNION statement to connect multiple tables, either from a single database or multiple databases.

In Microsoft SQL Server (which admittedly is not open-source), you could do something like this:

CREATE VIEW MergedTablesView
AS
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table1
    UNION
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table2
    UNION
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table3

    ...

The UNION statement will remove duplicate rows from the result set, which seems to be your primary requirement.

I'm certain other database engines, including PostgreSQL, also implement the UNION keyword in a very similar manner.  Here is the PostgreSQL Manual on the UNION statement.


Performance difference between single large Application instance vs multiple small Application Instance in Netweaver Can anyone please comment on the performance impact of having a single large application server instance vs multiple small application server instance in SAP Netweaver on a single machine.

Let's consider a situation when you need to build a server with SQL Server used by multiple client applications. Here are two options to consider.

Single instance that host ALL the databases
Multiple instances with each having subset of the databases.

We`ve created a table with comparison of these two options.

Comparison of single instance to multiple instances SQL Server installation

Single instance is Better for

Memory usage
TempDB disk usage
CPU usage
IO
Ease of administration
Overall performance
Cost of licensing
Monitoring

Multiple instances is Better for

Separate Security between
databases and applications
SLA differences
Multiple test and dev environments

Conclusion
From the table above we can see that having multiple instances of SQL Server on a single server could be beneficial only in a specific cases when you need a great degree of isolation for the environment, otherwise consider using single instance installation.


How to check if backups are reliable? I generally have to restore databases to different servers after important data imports or other important and delicate database operations.
I need to be sure that my backups up to that point in time or transaction mark are reliable.
I don't have the time and the disk space and to do a full restore followed by a dbcc checkdb to completely verify the backup files (including corruption).
The RESTORE VERIFYONLY will check for :

backup set is present   and header fields of pages

Are volumes readable?

checksum (if enabled , BACKUP by default does not use CHECKSUM)

destination drive – space check.

I use a lot the following query:
--query by Jack Vamvas - any suspect database?
SELECT DB_NAME(database_id),[file_id],page_id,
CASE event_type
   WHEN 1 THEN '823 or 824 or Torn Page'
   WHEN 2 THEN 'Bad Checksum'
   WHEN 3 THEN 'Torn Page'
   WHEN 4 THEN 'Restored'
   WHEN 5 THEN 'Repaired (DBCC)'
   WHEN 7 THEN 'Deallocated (DBCC)'
END,
error_count,
last_update_date
FROM msdb..suspect_pages

Other than restore verify only with checksum, is there any other way to check if my backups are reliable?

The only way to check if backup is reliable is to restore it.
DBCC Checkdb is not needed after successful restore if you just want to be sure that backup is reliable.


mysqldump generating inconsistent dumps I cannot get mysqldump to generate a dump that will properly restore.  The dump is being generated to setup a new slave, but whenever the slave starts up, it gets foreign key constraint failures.

I'm generating the dump with slave positions and as a single transaction so that the dump is (allegedly) atomic, but when I essentially execute the following:

mysqldump \
    -h master \
    --flush-logs --single-transaction --master-data \
    --apply-slave-statements \
    --databases my-db \
    | mysql -h slave

I get the following error when I "SHOW SLAVE STATUS":

Last_Errno: 1452
Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails ...

All of the tables use the InnoDB engine, and the foreign key is an integer.

Using MariaDB 10.3.

The --apply-slave-statements parameter doesn't make sense here, as it is used when you dump from a slave, not from a master.

And this one most likely causes your problems: --databases my-db

My guess is, that you expect, that MySQL/MariaDB only replicates the database that you've dumped and restored on the slave. But without filtering MySQL/MariaDB replicates everything from the master to the slave. To filter you can use the following manual page: https://mariadb.com/kb/en/library/replication-filters/

If that is not your problem, then the first thing I would check is, if someone has disabled foreign key checking on your master with select @@global.foreign_key_checks; If this returns 0 it's bad. Then you can use my stored procedure from this answer to check for data inconsistencies.


How MongoDB indexes differs from MySQL indexes? I am a newbie to MongoDB. I know how Indexes works and what it's importance in MySQL. Are MongoDB indexes are the same as MySQL ?

The quick answer is yes. Indexes in MongoDB mostly follow the same logic for usage and creation as you would do in MySQL.

Nevertheless, as the two databases are different (MongoDB is document based, not relational, etc) there are some aspects you might want to consider (for instance, there are no joins - your data model/organization needs to reflect this differences to ensure a good performance).

You might want to check the Indexing Strategies docs page that covers the best ways to take advantage of MongoDB indexes.

Also, besides the "traditional" option, MongoDB has much more index types that can help you achieve different things.


Help optimizing very slow mySQL query I have a query which takes about 6 secs to execute.

SELECT * FROM
(SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
   FROM table_orders_1_products AS op
   LEFT JOIN table_orders_1 AS ord ON ord.ID=op.orderID
   LEFT JOIN cms_user_aditional AS USER ON ord.userAdditional=USER.ID
   LEFT JOIN table_status AS stat ON stat.ID=op.statusID
   LEFT JOIN table_product_1 AS sp ON op.productID=sp.ID
   LEFT JOIN table_partners AS spp ON sp.supplier=spp.ID
   WHERE op.statusID!='0'
   ORDER BY ord.ID DESC LIMIT 1000) AS allO
ORDER BY allO.ID DESC

If I remove the "bolded" order by statement it takes only 40ms.

Is there any work around to fix this issue?

Create table for test_orders_1

CREATE TABLE `com_shop_orders_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `date` int(11) NOT NULL,
  `email` text NOT NULL,
  `name` text,
  `userID` int(11) DEFAULT NULL,
  `status` int(3) NOT NULL DEFAULT '1',
  `productsTotal` double NOT NULL,
  `total` double NOT NULL,
  `tax` double NOT NULL,
  `discount` double NOT NULL,
  `shippingID` int(3) NOT NULL,
  `shippingPrice` double NOT NULL,
  `paymentID` int(3) NOT NULL,
  `userAdditional` int(11) NOT NULL,
  `transacitonID` text NOT NULL,
  `paymentPrice` double NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`),
  KEY `shippingID` (`shippingID`),
  KEY `paymentID` (`paymentID`),
  KEY `ID_index` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=208180 DEFAULT CHARSET=latin1;

See this article about the performance problems and possible solutions using ORDER BY ... LIMIT.

I would create an index on ord.ID DESC and remove the subquery. This is assuming that ID is not a primary key and indexed already.

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
FROM table_orders_1_products AS op
LEFT JOIN table_orders_1 AS ord ON ord.ID=op.orderID
LEFT JOIN cms_user_aditional AS USER ON ord.userAdditional=USER.ID
LEFT JOIN table_status AS stat ON stat.ID=op.statusID
LEFT JOIN table_product_1 AS sp ON op.productID=sp.ID
LEFT JOIN table_partners AS spp ON sp.supplier=spp.ID
WHERE op.statusID!='0'
ORDER BY ord.ID DESC LIMIT 1000

See this page on creating indexes. (Or a Primary Key if it does not already exist. Creating a primary key on unique columns, usually ID's in this case, will help as they are automatically indexed.)

To add a Primary Key:
ALTER TABLE table_orders_1 ADD PRIMARY KEY(ID)

To create an Index:
CREATE INDEX ID_index ON table_orders_1 (ID DESC)

EDIT:

In general it is good practice to look at and understand your execution plans to see if you can identify any performance bottlenecks. See if you can narrow the scope of your table selects, for example, do you really need to LEFT JOIN to all the tables in your query? INNER JOIN is typically faster, unless the tables are very small (~10 rows) or if the tables do not have sufficient indexes to cover the query. Also on second examination it does not look like you are selecting anything from table_partners SPP, if not it does not need to be included. It may also be faster to split the table joins into two queries like below.

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, ord.userAdditional, op.statusID, op.productID
INTO #TEMP
FROM table_orders_1_products AS op
INNER JOIN table_orders_1 AS ord
  ON ord.ID=op.orderID
  AND op.statusID <> '0'
ORDER BY ord.ID DESC LIMIT 1000

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
FROM #TEMP T
INNER JOIN cms_user_aditional AS USER
  ON T.userAdditional = USER.ID
INNER JOIN table_status AS stat
  ON stat.ID = T.statusID
INNER JOIN table_product_1 AS sp
  ON T.productID = sp.ID


SELECT permission on view querying table from another database SQL Server 2008 R2 SP1

My company uses the Great Plains (GP) financial system with several customizations.  Our Value-Added-Reseller (VAR) for GP has setup most of these customizations in a separate database from the GP data called DYNCUSTOM.

In the DYNCUSTOM database is a View that is merely a SELECT * FROM a table in the GP company database (called PARTS).  I was approached by a user having problems trying to select on this view.

The error he was getting:

Msg 229, Level 14, State 5, Line 2
SELECT permission denied on object 'BM010415', database 'PARTS', schema 'dbo'

I looked at how his login was mapped and he had a database user in the DYNCUSTOM database belonging to a database role that was granted select on the view.

However, he did not have a database user in the PARTS database.

Usually the intent of a view is so that one can grant select permissions on the view without exposing access to the underlying table.  With SQL Server however, if the view crosses databases does it then change the security context being used?  Therefore, the user would also need a database user in the PARTS database as well as granted select access to the underlying table?

Sounds to me like you have a case of ownership chaining. The link should provide you with the details on how to make sure the chain stays intact.


default permissible datatype conversion matrix Today I wanted to define a uuid of value 00000000-0000-0000-0000-000000000000. Being a SQL Server man, I'd usually...

select cast(0x0 as uniqueidentifier);

...but I'm in postgres world now so I whipped out a sensible...

select cast('\x00'::bytea as uuid);

ERROR:  cannot cast type bytea to uuid
LINE 1: select cast('\x00'::bytea as uuid);
               ^

Damn! So I head over to the postgres docs on Type Conversion hoping to see a doc like this one to review which datatypes I can cast between by default without needing to create an explicit cast.

If such a chart does exist in the docs, it's well hidden. Google is likewise not super helpful in this regard.

  Is there a good documentation reference for default permissible type casts in postgresql?

To be clear, I don't actually care about the uuid

You can get a list using the psql client with

\dCS

This will show all casts defined between system data types.

In addition, all types can be cast to and from text using the type input and output functions.


Redshift: Performance wise Is storing delimited values in a single column ever preferable to normalizing them out to another table? I have been looking at increasing the performance of some tables that have been uploaded to a redshift database. One of the columns in a table has many delimited values which need to be split out and queried at runtime. The table has around 30 million rows and there would be on average 10 delimited values in this particular column.

Coming from an OLTP background my instinct is to normalize these values out to another table. However, I have seen various posts that suggest that this is not the way to go. Looking for some advice

In a word, NO!

You should not breach Codd's Rules unless you have no choice but to do so.

I fail to see what performance gains you will obtain by having to parse strings over having an Associative Entity (AKA joining, bridging tables or mapping - amongst others) and using SQL JOINs.

Most importantly from an RDBMS perspective, this allows you to enforce DRI (Declarative Referential Integrity) constraints on your data. DRI is your friend.

From the link:

  There are old-wives' tales about FKs being unnecessary overhead. This
  notion was kept alive by the fact that (for good, but probably now
  legacy, notably space, reasons) some RDBMSs did not/don't
  automatically create indexes on Foreign Key fields (this should be done in the majority of cases). This led to the mistaken view that Foreign Keys were overhead without benefit which is not
  true for the vast bulk of use-cases!

p.s. you mention that I have seen various posts that suggest that this is not the way to go- where are these references? I doubt if they'll stand up to much scrutiny!


Is it safe to rely on transactions in Firebird 2.5 Embedded DB in case of power outage? How it's safe to say that the following list of action will never be reflected in DB in case of power outage somewhere in the middle of #2 line, before transaction is committed?
#1 begin transaction
#2 delete record in table A that cascade deletes another record in table B
#3 update another record in table C
#4 commit transaction

That is usually safe. Firebird uses an MVCC (Multi-Version Concurrency Control) architecture. Updates (and deletes) are written as a back version and a new version. The new version overwrites the previous version, and the back version is a delta to recreate the previous record version from the new record version. Deletes are written as a 'stub' record version that marks the record as deleted.
Each version is marked with the transaction that originally created the record version. If that transaction is not committed, that version of the record will not be visible. Firebird will follow the chain of back version pointers of the record to find the version that is visible to the transaction and reconstruct that version.
After a crash or other abrupt termination, Firebird will at some point detect the transaction is not active, and the transaction will be marked as rolled back. At that time, or at some later point during garbage collection, Firebird will rewrite the record versions to make the latest committed version the 'new' version, and eliminate unnecessary back versions.
The fact that you have a cascading delete in your example isn't really relevant, as long as the consistency of individual records is OK.

How can a transaction go from Active to Rolled Back if it exits abnormally?
This can happen in one of two ways.

When a transaction starts, it takes out a lock on its own transaction id. If a transaction (B) attempts to update or delete a
record and finds that the most recent version of the record was
created by a transaction (A) whose TIP state is ACTIVE, transaction B
tries to get a conflicting lock on A's transaction id. A live
transaction maintains an exclusive lock on its own id, and the lock
manager can probe a lock to see if the owner is still alive. If the
lock is granted, then B knows that A died and changes A's TIP state
from Active to Rolled Back.
When a transaction starts, it checks to see if it can get an exclusive lock on the database - if it can no other transactions are
active. Every active transaction has a shared lock on the database. If
it gets an exclusive lock, it converts all Active TIP entries to
Rolled Back.

(from: Firebird for the Database Expert: Episode 4 - OAT, OIT, & Sweep)
To prevent dataloss, Firebird employs what is called a "careful write" strategy to ensure on-disk consistency, by ensuring data is written in an order that maintains the dependencies between data pages (and versions).
The new record is written in the same place as the old record, while the back version is written to reconstruct the old record. There are basically two scenarios:

The new version and its back version fit on the same data page.
Both new and back version are written to the in-memory image of the page, and the page is written to disk.
In this situation, the only real 'failure' mode would be that the disk write itself is only partially done when the power fails. This problem can be addressed by using disk controllers with a backup battery unit (BBU) or similar solution.

The back version doesn't fit on the same page, and has to go on another data page.
The back version and new version are written to the in-memory image of their respective pages. As the new version depends on the back version (it points to the back version), the page with the back version is written to disk first, then the page with the new version is written to disk.
If the process terminates after the back version is written, the back version is orphaned, but the previous version of the record is still intact. It also has the same failure mode as the previous one.

So, in case of a power outage, or other type of termination of the Firebird process, this means that you possibly have orphaned pages (pages allocated but not yet registered, or pages released but not yet marked as available), or orphaned records (a back version that is written, but not yet correctly linked in a version chain of records). These orphaned pages or back versions waste space, but do not affect consistency. It might be necessary to use gfix to fix and reclaim space wasted that way.
That said, besides having failures mid-write and no BBU, I believe there are scenarios where a power outage leaves the database in a state where it first needs to be repaired using gfix, but I haven't encountered those scenarios myself and I can't really think of one right now.
This all assumes that you have synchronous writes (forced writes) enabled on your Firebird database (the default). If you disable synchronous writes, then writing pages to disk is left to your OS, and this might result in data-loss as pages might be written in a different order, for example the new version has been written to disk, but the back version not yet at the time of the outage, then you have essentially lost the committed version of the row, orphaned previous back versions, and the back version pointer of the new version points to garbage.
Further reading:

Firebird for the Database Expert: Episode 3 - On disk consistency
Firebird for the Database Expert: Episode 4 - OAT, OIT, & Sweep
A not-so-very technical discussion of Multi Version Concurrency Control
Understanding Firebird Transactions


what’s the criteria SQL server (2014) will automatically recompile a stored procedure which has table variables? Can someone explain what’s the criteria SQL server (2014) will automatically recompile a stored procedure which has table variables? I know there is criteria for statistics but how about a proc using table variable not the temp table?

Tables variables doesn't trigger recompile.
Well, they do, but not until SQL Server 2019 where they are treated as temp tables in this aspect.


AND operator in ORDER BY clause While debugging some code, I stumbled into an error pretty much like the following:

SELECT *
FROM person
WHERE family_size > 3
ORDER BY birthday AND registered=0;

The error is that the AND clause was intended to be in the WHERE clause. Ie:

WHERE family_size > 3 AND registered=0

The "flawed" query throws no SQL syntax error or warning.
So I assume it's valid SQL. (right?)

But more interestingly, what is it supposed to do?
I tested this is a couple of RDBMS and my best bet is that it's doing something like:

ORDER BY (birthday AND registered=0);

Am I right?

Is this intended SQL behavior? (such as expected resolution of operator precedence)
Or is it some sort of fall-back?

Some details I should add after reading comments:

The original query was for use in MySQL, but I'm mostly interested in the intended use of the SQL language.
I did a quick test in MSAccess and behaved pretty much the same.
The columns were originally integers, but for the MSAccess test I did, I used Date/Time for 'birthday', just because.

It's valid SQL as long as both sub-expressions, (birthday) and (registered=0) are boolean expressions (or can be implicitly converted to). And while registered = 0 is boolean, the birthday is probably not.

In MySQL though, integer expressions can be converted to boolean and vice versa, according to the rules: FALSE is 0, TRUE is 1 (and anything other than 0 is TRUE for the integer->boolean conversion).

So, that's what happens, an implicit conversion. For any birthday value other than 0, the expression is evaluated as:

(TRUE AND registered=0)

which is equivalent to just (registered=0).

In Access, if I remember right, -1 stands for TRUE (instead of 1), but anything other than 0 is also converted to true, so the observed behaviour would be the same.

The final use of the expression in the ORDER BY depends on how the DBMS sorts boolean values. I suppose that both (MySQL and Access) use FALSE < TRUE


MongoDB Export/Import Failed: error counting I am trying to export data from local database using:

mongodump --db ride4you --collection rides

But I receive the next error:

Failed: error counting ride4you.rides: not authorized on ride4you to execute command { count: "rides", query: {} }

What is this and what to do?
Plus, is there a way to export the whole database at once, instead by each table?

@Raz, I have gone through your mongodump statement. You are doing mistake.

  mongodump --db ride4you --collection rides

As per your mongodump code , As i am able to understand in your mongodump code, there is database name (ride4you) and collection name (rides) without the path of dump (.JSON) file.

Let's start from beginning. As per MongoDB BOL , if you are in BIN directory . Just type along with mongodump --help. You shall get the mongodump command all details. Which i am copy and paste from my environment.

C:\Program Files\MongoDB\Server\3.2\bin>mongodump --help
Usage:
  mongodump <options>

Export the content of a running server into .bson files.

Specify a database with -d and a collection with -c to only dump that database or

See http://docs.mongodb.org/manual/reference/program/mongodump/ for more informat

general options:
      /help                                                 print usage
      /version                                              print the tool
                                                            version and exit

verbosity options:
  /v, /verbose:<level>                                      more detailed log
                                                            output (include
                                                            multiple times for
                                                            more verbosity,
                                                            e.g. -vvvvv, or
                                                            specify a numeric
                                                            value, e.g.
                                                            --verbose=N)
      /quiet                                                hide all log output

connection options:
  /h, /host:<hostname>                                      mongodb host to
                                                            connect to
                                                            (setname/host1,host-

                                                            2 for replica sets)
      /port:<port>                                          server port (can
                                                            also use --host
                                                            hostname:port)

authentication options:
  /u, /username:<username>                                  username for
                                                            authentication
  /p, /password:<password>                                  password for
                                                            authentication
      /authenticationDatabase:<database-name>               database that holds
                                                            the user's
                                                            credentials
      /authenticationMechanism:<mechanism>                  authentication
                                                            mechanism to use

namespace options:
  /d, /db:<database-name>                                   database to use
  /c, /collection:<collection-name>                         collection to use

query options:
  /q, /query:                                               query filter, as a
                                                            JSON string, e.g.,
                                                            '{x:{$gt:1}}'
      /queryFile:                                           path to a file
                                                            containing a query
                                                            filter (JSON)
      /readPreference:<string>|<json>                       specify either a
                                                            preference name or
                                                            a preference json
                                                            object
      /forceTableScan                                       force a table scan

output options:
  /o, /out:<directory-path>                                 output directory,
                                                            or '-' for stdout
                                                            (defaults to 'dump')
      /gzip                                                 compress archive
                                                            our collection
                                                            output with Gzip
      /repair                                               try to recover
                                                            documents from
                                                            damaged data files
                                                            (not supported by
                                                            all storage engines)
      /oplog                                                use oplog for
                                                            taking a
                                                            point-in-time
                                                            snapshot
      /archive:<file-path>                                  dump as an archive
                                                            to the specified
                                                            path. If flag is
                                                            specified without a
                                                            value, archive is
                                                            written to stdout
      /dumpDbUsersAndRoles                                  dump user and role
                                                            definitions for the
                                                            specified database
      /excludeCollection:<collection-name>                  collection to
                                                            exclude from the
                                                            dump (may be
                                                            specified multiple
                                                            times to exclude
                                                            additional
                                                            collections)
      /excludeCollectionsWithPrefix:<collection-prefix>     exclude all
                                                            collections from
                                                            the dump that have
                                                            the given prefix
                                                            (may be specified
                                                            multiple times to
                                                            exclude additional
                                                            prefixes)
  /j, /numParallelCollections:                              number of
                                                            collections to dump
                                                            in parallel (4 by
                                                            default)

Mostly i am doing mongodump in my environment like

For a single database dump

mongodump -d myfirstdb -o C:\Test\TestDB.json

For all database dump at a time

mongodump --verbose C:\Test\

Here In first statement myfirstdb is database in my MongoDB Server. -o is for output directory options and C:\Test  is a folder where TestDB.json my dump (.JSON) File reside.

In second statement i have used --verbose . for more details output

Note: mongodump import all dump (.JSON) files in MongoDB Server at a time. But when doing mongoimport then it import one dump (.JSON) file at a time.

For Further your ref mongodump


SQL Server Archiving Data Functionality Our company is looking for functionalilty/or automated way to archive data in SQL Server.
Currently, we create archive tables with identity surrogate, and develop stored procedures to import data. We are conducting this for 100+ tables, data is 2 years or older. Does SQL Server have functionality or automated way to archive data (Wizard tool, UI) or does person have to develop stored procedures? I am thinking of creating dynamic sql to write script for 100+ tables.

Thanks,

-- If no functionality exists, will create dynamic sql for tables using template below

create table dbo.CustomerTransaction
(
    CustomerTransactionId bigint primary key identity(1,1) not null ,
    CustomerName varchar(255) null ,
    ProductName varchar(255) null,
    Quantity int null ,
    CreateDatetimestamp datetime not null
 )

create table dbo.CustomerTransactionArchive
(
    CustomerTransactionArchiveId bigint primary key identity(1,1) not null ,
    CustomerTransactionId bigint null,
    CustomerName varchar(255) null ,
    ProductName varchar(255) null,
    Quantity int null ,
    CreateDatetimestamp datetime not null
 )

create procedure dbo.CustomerTransactionArchive
as

    declare rowmin bigint = (Select min(CustomerTransactionId) from dbo.CustomerTransaction where Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))
    declare rowmax bigint = (Select max(CustomerTransactionId) from dbo.CustomerTransaction where Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))
    declare rowcounter bigint = rowcountermin

      -- insert into 100,000 record block, do not want to lock too many pages in table
    while rowcounter <= rowmax
    begin

        insert into dbo.CustomerTransactionArchive  (
        select * from dbo.CustomerTransactionArchive
        where CustomerTransactionId between rowcounter and (rowcounter +100000)
        and Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))

        rowcounter = rowcounter + 100001
    end

Archive tables will be in different database

There is no built in functionality.

The stored procedure route will work, but you might also want to look into an ETL package (like SSIS) to do this as it may be easier to maintain.

You could also look into partitioning on CreateDate for all your tables  in order to efficiently drop older data, but that would still require some custom code to rotate the partitions. They could, however, use a common partition scheme and function if the DDL on the CreateDate is the same for each.

If you were to upgrade, you could also look at using Temporal Tables or Stretch Database from SQL 2016, though those aren't out of the box auto-archive features necessarily.


Un-attaching SPL 2008 BD not willing to Attach I have a 5 year old SQL Server 2008 database.  There are actually 3 databases

NL_Dev
NL_Test
NL_Prod

Each has a log file. We have been using the development database, but all three seam to be linked together. The .MDF files are all about 3.2GB, but the dev log file is 450GB.

I was trying to shrink the files, but only had 10GB's of space left on the array.

I had a backup of NL_Dev and didn't think it would make any difference to delete the test log, so I stopped SQL Server and deleted the test log File. It didn't make any size difference, but now I can't access the data in NL_Dev.

I tried un-attaching the DB, but now I can't attach it since the file is missing.

I am not a DBA, just a small business owner that has worked with Access and SQL Server for 20 years, and I've run out of rope to hang myself. Please Help

Gordon Mills
Newlife Electronics

I was trying to Shrink the files, but only had 10GB's of space left on the Array.

Get another LUN assigned to you. Extend the LUN. Buy a bigger disk. Add an external drive. Do something other than compromise your data because of disk space shortages. This is a problem that can be solved without ever touching your database in ways it doesn't like.

  I Stopped SQL and deleted the Test Log File

Please don't ever do this - it is a pretty good way to corrupt your data, because stopping the SQL Server service and deleting the log file can make it impossible for SQL Server to recover in-flight transactions that were running when you stopped the service.

  I tried Un-attaching the DB, but now I can't Attach it since the file is missing.

You can try CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, which will re-create the log file (see the documentation), but this still may not help because it still requires a clean shutdown of SQL Server (we don't know that that happened), and who knows what has happened to your .mdf file in the meantime.

My suggestions:

Forget trying to re-attach the .mdf file and just restore your backup.
Read this post in full:

Why Does the Transaction Log Keep Growing or Run Out of Space?

If that is too much for you, and you care about your data at all, you need to consider hiring someone who can handle it. You don't necessarily need someone full-time; a remote DBA service will probably be sufficient.


sp_BlitzCache install syntax errors Trying to install sp_BlitzCache on SQL Server 2008, I get:

Msg 102, Level 15, State 1, Procedure sp_BlitzCache, Line 2705 Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Procedure sp_BlitzCache, Line 2715 Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Procedure sp_BlitzCache, Line 2865 Incorrect syntax near '.'.

Line 2705 is :

/* END Testing using XML nodes to speed up processing */

Line 2865 is the FROM line in:

/* Set configuration values */
SELECT  @ctp = NULLIF(CAST(value AS INT), 0)
FROM    sys.configurations
WHERE   name = 'cost threshold for parallelism'
OPTION (RECOMPILE);

It appears to be version 5.5

The SQL Server version is 10.0.6000.29.

When I page through the code, the first error I see highlighted is on #1162 in the source file: (and 4 more after that.)

SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')

"Cannot call methods on int."

The next error is on line 2032 in the source - a select statement with

q.n.query('.') AS statement

Can't find q as a column, function, or q.n.query is ambiguous.

After that there are more cascading errors.

You're running it in a database in 2000 or 2005 compatibility level, which isn't supported.


Conditionally join table I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me.

I have a master table which is then joined to another table but the table that is joined is based on some data in another column.

My application will in turn run queries such as:

SELECT f.dataA, m.data1, m.data2
FROM Master_Table m
INNER JOIN Foo f ON f.id = m.TableKey
WHERE TableName = 'Foo'

and

SELECT fb.dataA, fb.dataB, m.data1, m.data2
FROM Master_Table m
INNER JOIN Foobar fb ON fb.id = m.TableKey
WHERE TableName = 'Foobar'

This works as desired but I don't like the fact that my Master_Table.TableKey should probably be held to a foreign key constraint but this is impossible with this design (or at least I think so).

Should the TableKey column be split out to being a FooKey, BarKey and FoobarKey? This also then seems odd because I then guarantee that BarKey and FoobarKey will be null where TableName = "Foo".

Or is there a completely different way I should be approaching this?

EDIT: Funnily enough this recent meta.dba post seems to touch on this idea about inheritance but I'm not too sure what the best approach still is.

This is not a great design. You have a couple of options to improve it:

Go with your alternative (separate keys). You don't list your requirements, but if it's imperative you only have ONE child record per master record you can enforce this with check constraints.
Put the parent key in the child table. If every child record refers to one master record, just put the masterid in the child table. This also gives you the option of multiple child records per master/parent. If you want to have only one child per parent, remove the surrogate key in the child table and just have the related master id.

Whatever you choose, I would recommend against storing the child table name in a column in the master record. You can make this a "Type" field, but putting meta data into your data always leads to pain down the road. It's also unnecessary if you have separate keys, as you know if you have a FooID value the record type is Foo.


When mysql innodb undo log write and fsync to disk? Before transaction commit , every dml operation will modify innodb page which will flush to disk and generate undo log , so when the undo log write and fsync to disk to ensure rollback ?

Make data changes to the blocks in the buffer_pool, but do not write them to disk.
Put changes to secondary indexes (eg for INSERT) into the Change Buffer for later processing.  Again, no write to disk (yet).
Write and fsync the undo log file if innodb_flush_log_at_trx_commit = 1 (else this write/fsync is delayed).
Tell the client that the COMMIT is finished.

The idea is to have no more than one fsync per COMMIT.  Otherwise, performance would be really bad.

If you are using Replication, then the binlog is written to (see log_bin) and possibly fsync'd (see sync_binlog).  And the data is shipped to each Slave.  I don't know exactly where these happen in the sequence.

If you are using Galera / FXC / Group Replication, the syncing to the other nodes adds another dimension to the question.  (I'll assume you don't need these details.)


Why MongoDB collections are dropped automatically? I have installed mongodb 3.6 standalone instance and created some collections through insert query in mongo shell. My app points to the db server, the app creates some new collections, it reads, writes and so on. This is running for more than two months. If we restart the instance we can see in mongodb collections which are created through insert query are lost. Now we can see the same behavior in other environments. Also, while analyzing this problem, we found that during mongodb server start there was a error saying "mongodb failed to create WiredTiger bulk cursor: Device or resource busy". Any idea what that error line implies?

The below messges are found in mongodb log file during server shutdown

**2018-08-28T12:44:46.138+0530 I CONTROL [signalProcessingThread] got signal 1 (Hangup), will terminate after current cmd ends**

**2018-08-28T12:44:46.138+0530 I NETWORK [signalProcessingThread] shutdown: going to close listening sockets...**

**2018-08-28T12:44:46.138+0530 I NETWORK [signalProcessingThread] removing socket file: /tmp/mongodb-27017.sock**

**2018-08-28T12:44:46.152+0530 I FTDC [signalProcessingThread] Shutting down full-time diagnostic data capture**

**2018-08-28T12:44:46.158+0530 I STORAGE [signalProcessingThread] WiredTigerKVEngine shutting down**

**2018-08-28T12:44:46.265+0530 I STORAGE [signalProcessingThread] shutdown: removing fs lock...**

**2018-08-28T12:44:46.265+0530 I CONTROL [signalProcessingThread] now exiting**

**2018-08-28T12:44:46.266+0530 I CONTROL [signalProcessingThread] shutting down with code:0**

And during server startup the below messages are found in the log file,

**2018-08-28T14:19:39.256+0530 I STORAGE [thread2] createCollection: config.system.sessions with generated UUID: e981a966-4f87-45a9-9adb-6b7e2fdb8803**

**2018-08-28T14:19:39.277+0530 I INDEX [thread2] build index on: config.system.sessions properties: { v: 2, key: { lastUse: 1 }, name: "lsidTTLIndex", ns: "config.system.sessions", expireAfterSeconds: 1800 }**

**2018-08-28T14:19:39.277+0530 I INDEX [thread2] building index using bulk method; build may temporarily use up to 500 megabytes of RAM**

**2018-08-28T14:19:39.278+0530 W STORAGE [thread2] failed to create WiredTiger bulk cursor: Device or resource busy**

**2018-08-28T14:19:39.278+0530 W STORAGE [thread2] falling back to non-bulk cursor for index table:index-12--7471022903774062594**

**2018-08-28T14:19:39.278+0530 I INDEX [thread2] build index done. scanned 0 total records. 0 secs**

I have gone through your error log and find out the MongoDB Bug affected version error , which are able to see in your error log such as:

  "mongodb failed to create WiredTiger bulk cursor: Device or resource
  busy"

As per MongoDB jira documentation here as it is Server Bug having affected version was 2.8.0-rc0.

The Bug fix version of MongoDB is 2.8.0-rc1.

And the second Bug error, which is

  2018-08-28T12:44:46.138+0530 I CONTROL [signalProcessingThread] got
  signal 1 (Hangup), will terminate after current cmd ends

As jira documentation here It's affected Bug in MongoDB version 3.0.12.

As per @RamonFernandez from jira documentation, seems like mongod is receiving the SIGHUP signal and terminating, which is expected behavior. You need to check who or what is sending this signal.


Connection pooling with python mysql-connector I would like to use connection pooling with mysql-connector 2.1.5

On Ubuntu everything works fine, but I would like to switch to Alpine and have used this file to install the connector (as described here).

The installation seems to work. I can use mysql.connector.connection(), but whenever I try mysql.connector.pooling.MySQLConnectionPool() I get the error AttributeError: 'module' object has no attribute 'pooling'.

I have also tried creating a pool implicitly using cnxpool = mysql.connector.connection(pool_name="...") but I cannot get the connection. Everytime I try cnx = cnxpool.get_connection() I get the error AttributeError: 'MySQLConnection' object has no attribute 'get_connection'

Is there any way to use pooling with the platform independent install package, or is it just not implemented there?

I did some experimentation in console trying to figure out why this was happening for me as well. In the end, I searched right into the file folder for the package looking to ensure that the pooling file was present in the mysql.connector package version I had.

Sure enough there it was.

In the end, I simply changed my import statement. I had been using

import mysql.connector
pool = mysql.connector.pooling.MySQLConnectionPool(**config)

After playing in console for a bit I realized that it would be happy if I just used;

import mysql.connector
from mysql.connector import pooling
pool = pooling.MySQLConnectionPool(**config)

Worked fine. Couldn't tell you "why", but it worked.


Oprhaned entries in msdb..sysjobactivity In this question:
https://stackoverflow.com/questions/18445825/how-to-know-status-of-currently-running-jobs

This answer was proposed:

SELECT sj.name
   , sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL

However for one of my servers this has an entry dating back 10 months ago. Looking in SQL Agent Job Activity Monitor shows no running jobs. Looking in msdb..sysjobs shows a mere 3 jobs. Can anyone explain this phenomenon?

The orphaned row has NULLs for queued_date, stop_execution_date, job_history_id and next_scheduled_date. Rest of the values look reasonable.

A StackOverflow answer has a possible explanation:

https://stackoverflow.com/questions/13037668/what-does-it-mean-to-have-jobs-with-a-null-stop-date/13038752#13038752

To quote:

  Each time the SQL Agent starts, it puts a new row in syssessions and
  subsequently any jobs run will get that session_id in sysjobactivity.
  For your jobs that have a null stop date, my guess is that they're not
  for the "current" session which would mean that they were still
  running when the agent was stopped.

I had a similar issue where two entries in sysjobactivity appeared to be "stuck", with a start_execution_date from six weeks back and no stop_execution_date.  Following the suggestion in the quote above I checked the session_id for each of those stuck records and found in each case the stuck records were from previous sessions.

The following StackOverflow answer shows how to get only the activity for the current session:

https://stackoverflow.com/a/18062236/216440

Their code:

SELECT
    job.name,
    job.job_id,
    job.originating_server,
    activity.run_requested_date,
    DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
FROM
    msdb.dbo.sysjobs_view job
JOIN
    msdb.dbo.sysjobactivity activity
ON
    job.job_id = activity.job_id
JOIN
    msdb.dbo.syssessions sess
ON
    sess.session_id = activity.session_id
JOIN
(
    SELECT
        MAX( agent_start_date ) AS max_agent_start_date
    FROM
        msdb.dbo.syssessions
) sess_max
ON
    sess.agent_start_date = sess_max.max_agent_start_date
WHERE
    run_requested_date IS NOT NULL AND stop_execution_date IS NULL


A nonrecoverable I/O error occurred on file after DB Shrink After shrinking an SQL Server database & files, I encountered an error message saying "A nonrecoverable I/O error occurred on file" while performing database backup.

Why am I getting this error?

After I did the following steps below, I was able to produce a database backup again.

Step 1:

use Test_DB

GO

Step 2:

DBCC CheckDB

GO

Step 3:

Right click the Test_DB database then click properties.
Choose Option page
From Restrict Access option, select Single
Click OK and Open Connections dialog will appear. Click Yes if you want to close all connection connected to the database and change the database on single user mode.

-OR-

ALTER DATABASE Test_DB SET SINGLE_USER WITH NO_WAIT

GO

Step 4:

dbcc checkdb ('Test_DB',repair_rebuild)

GO

Step 5:

dbcc checkdb ('Test_DB',repair)

GO

Step 6:

ALTER DATABASE Test_DB SET MULTI_USER WITH NO_WAIT

GO


Shrinking log files with SMO I know I can use the following t-SQL to shrink a log file:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);

How do I do the same with SMO? I tried:

$server = new-Object Microsoft.SqlServer.Management.Smo.Server()

$dbBackup = new-object Microsoft.SqlServer.Management.Smo.Backup
$dbBackup.Action = "Log"
$dbBackup.Database = "db1"
$dbBackup.Devices.AddDevice("\\server\share\db1_log1.trn", "File")
$dbBackup.SqlBackup($server)

$db = $srv.Databases.Item("db1")
$db.TruncateLog()

But here, I found that the TruncateLog() method does not work with anything higher than SQL 2005. That link recommended using:

$db.RecoveryModel = RecoveryModel.Simple
$db.Alter()
$db.LogFiles[0].Shrink(0, ShrinkMethod.TruncateOnly)

However, switching to Simple Recovery Mode is going to break my log chain and I don't want to do that. Shrinking the log file doesn't break my log chain so I'm looking for an equivalent SMO method to do that. Does such a method exist?

Read up here before you shrink your log file.

You dont have to change the recovery model ...

Update:

you can use Invoke-sqlcmd cmdlet.

eg.

$query = "DBCC SHRINKFILE(db1_log)"
Invoke-Sqlcmd -ServerInstance $instanceName -Query $query

EDIT: Below is an excerpt from Powershell V3 cookbook ...

Not all DBCC commands are wrapped in SMO methods. For other DBCC commands that are not nicely wrapped in methods, you can still execute them using the Invoke-Sqlcmd cmdlet.


How can I load csv data into mongodb and choose the types I'd like to have created for each column? I'm looking for a method to load csv data in mongodb and specify the types that I'd like to have created for each column?

I've been using mongoimport but I find it quite painful because there doesn't appear to be any way to specify the datatype to be used for a particular column in the csv file.

I load the data ...

mongoimport --drop -d myDatabase -c myCollection --type csv --fields name --file myFile.csv

Then I do analysis to see what types mongo actually created.

> db.artist.itunes.feed.count()
36545
> db.artist.itunes.feed.count( { 'name' : { $type : 2 } } )
36511
> db.artist.itunes.feed.count( { 'name' : { $type : 1 } } )
1
> db.artist.itunes.feed.count( { 'name' : { $type : 16 } } )
33

Then I do surgery to correct errors like so ...

db.artist.itunes.feed.find( { 'name' : { $type : 1 } } ).forEach( function (x) {
  x.name = new String(x.name); // convert field to string
  db.artist.itunes.feed.save(x);
});

This is tedious.

It would be great if there were a method that allowed me to specify at import time what type to create for each column in the csv file like so:

mongoimport --drop -d myDatabase -c myCollection --type csv --fields field1,field2 --types 2,2 --file myFile.csv

http://docs.mongodb.org/manual/reference/operator/query/type/

Ensure you're using mongoimport version 3.4 or higher, and use the --columnsHaveTypes parameter to tell the import what type each column is.

See the MongoDB Reference Documentation for details.

The following types can be specified:

auto()
binary(<arg>)
boolean()
date(<arg>)
date_go(<arg>)
date_ms(<arg>)
date_oracle(<arg>)
decimal()
double()
int32()
int64()
string()

Consider the following CSV data:

Katherine Gray, 1996-02-03, F, 1235, TG9yZW0gaXBzdW0gZG9sb3Igc2l0IGFtZXQsIGNvbnNlY3RldHVyIGFkaXBpc2NpbmcgZWxpdCwgc2VkIGRvIGVpdXNtb2QgdGVtcG9yIGluY2lkaWR1bnQgdXQgbGFib3JlIGV0IGRvbG9yZSBtYWduYSBhbGlxdWEuIFV0IGVuaW0gYWQgbWluaW0gdmVuaWFtLCBxdWlzIG5vc3RydWQgZXhlcmNpdGF0aW9uIHVsbGFtY28gbGFib3JpcyBuaXNpIHV0IGFsaXF1aXAgZXggZWEgY29tbW9kbyBjb25zZXF1YXQuIER1aXMgYXV0ZSBpcnVyZSBkb2xvciBpbiByZXByZWhlbmRlcml0IGluIHZvbHVwdGF0ZSB2ZWxpdCBlc3NlIGNpbGx1bSBkb2xvcmUgZXUgZnVnaWF0IG51bGxhIHBhcmlhdHVyLiBFeGNlcHRldXIgc2ludCBvY2NhZWNhdCBjdXBpZGF0YXQgbm9uIHByb2lkZW50LCBzdW50IGluIGN1bHBhIHF1aSBvZmZpY2lhIGRlc2VydW50IG1vbGxpdCBhbmltIGlkIGVzdCBsYWJvcnVtLg==
Albert Gilbert, 1992-04-24, T, 13, Q3VwY2FrZSBpcHN1bSBkb2xvciBzaXQgYW1ldCB0b290c2llIHJvbGwgYm9uYm9uIHRvZmZlZS4gQ2FuZHkgY2FuZXMgcGllIGNyb2lzc2FudCBjaG9jb2xhdGUgYmFyIGxvbGxpcG9wIGJlYXIgY2xhdyBtYWNhcm9vbi4gU3dlZXQgcm9sbCBjdXBjYWtlIGNoZWVzZWNha2Ugc291ZmZsw6kgYnJvd25pZSBpY2UgY3JlYW0uIEp1anViZXMgY2FrZSBjdXBjYWtlIG1hY2Fyb29uIGRhbmlzaCBqZWxseS1vIHNvdWZmbMOpLiBDYWtlIGFwcGxlIHBpZSBnaW5nZXJicmVhZCBjaG9jb2xhdGUgc3VnYXIgcGx1bS4gU3dlZXQgY2hvY29sYXRlIGNha2UgY2hvY29sYXRlIGNha2UganVqdWJlcyB0aXJhbWlzdSBvYXQgY2FrZS4gU3dlZXQgc291ZmZsw6kgY2hvY29sYXRlLiBMaXF1b3JpY2UgY290dG9uIGNhbmR5IGNob2NvbGF0ZSBtYXJzaG1hbGxvdy4gSmVsbHkgY29va2llIGNha2UgamVsbHkgYm

The --fields option specifies which field type mongoimport will use when importing the data into MongoDB (command-line wrapped for readability):

mongoimport --db users --collection contacts --type csv
  --columnsHaveTypes --fields "name.string(),birthdate.date(2006-01-02),contacted.boolean(),followerCount.int32(),user thumbnail.binary(base64)"
  --file /example/file.csv


Monitoring streaming replication We're setting up streaming replication with one master and one (read-only) slave. We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date.

Is it possible to do this — preferably from the slave? If so, how?

The documentation on streaming replication mentions the following:

  On systems that support the keepalive socket option, setting tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count helps the primary promptly notice a broken connection.

What does "promptly notice" mean in this context?  Is there some error log where a broken connection gets reported?

We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date.

For monitoring replica lag, there are several ways that give slightly different answers, depending on which version of Postgres you are using. A simple query that can be done directly on the standby is:

SELECT (CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
        THEN 0
        ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
        END) AS log_delay;

Note this check will not report "lag" in cases where, say, the connection to the primary has been severed, since this check of difference between XLOG segments received and applied doesn't know anything about XLOG segments which have not been received yet -- there are additional checks of pg_stat_replication on the primary which may be helpful as well, see e.g. here.

  What does "promptly notice" mean in this context?

In this context, "promptly notice" is talking about how much time may or must elapse before a TCP client (i.e. the standby) notices that its connection to the other side has been severed. (I think the docs should really say "helps the standby" not "helps the primary" here, since it is the standby which must detect a broken connection and re-establish a new connection to the primary.)

See also this relevant question talking about how keepalive settings affect this time.

  Is there some error log where a broken connection gets reported?

I believe that you will see messages in the postmaster logs on both the primary and the standby side (particularly if you have log_connections / log_disconnections enabled) when SR connections are dropped or re-established.