Mandatory and non mandatory attributes for products

I think further information is still required before a design can be decided; namely, whether it is important to validate the attribute data in any way. This may affect the data type you choose for storing attribute data.
For example, you will probably want to store your "expire date" attribute using a date-based data type. There are many reasons for this, but consider one: if you accept just strings in which people type information representing dates, you may get a variety of formats for those dates, eg) "1/2/20" for first of February, 2020, in Australia, "1/2/20" for second of January, 2020, in USA, "Feb 1st 2020", "1 Feb 20" and "1 February 20" - all representing the same date. Yuck.
You could mitigate that issue by introducing rules in your application logic - for example, use a date picker which always outputs dates in a standard format ... but now the robustness of your data model depends on application developers doing what you ask them to.
However, even without a decision on data types, I can propose some guidelines that have their caveats (relating to the questions of validation).
I would have a table called product. If there is data that every product has, regardless of category, then I would store it here - think name, code, activeFlag, dateCreated, createdBy, dateLastModified, lastModifiedBy, etc.
I would have a table called category. This will probably have a name field. (If you are building a category tree then you will need fields to allow you to put categories into a hierarchy. For now I will assume the category list is a straight list of categories - not a hierarchy).
I would have a table called attribute. This will have a field named code. The code is a unique, non-null value that, once set, should never be modified. This means if you need to introduce business rule logic into your application that relates to a given attribute, your application can reference the attribute by its code value - I am talking about hard-coding the data value in application code - and not have to worry about the logic breaking in future (because that data value will never change). A concrete example: Say "colour" is an attribute - let's put a field on the table called name and put the word "Colour" in that field. Now, in the code field we might put a value like "COL" (short for "colour"). In your application you might have a business rule saying that managers can update the colour value for products, but other staff cannot, so your application code will have a line of code something like if userIsManager===1 and fieldCode==='COL' then renderFieldAsEditable().
All good so far. Now I would create a table categoryAttribute which contains the PK columns from category and attribute (probably autoincrement integer values - I didn't bother mentioning them before). This table essentially says "this category has this attribute". Now I would put further fields on this table, such as mandatory - perhaps as a bit data type where 1 means this attribute is mandatory in this category, and 0 means it is optional. I might include sortOrder of some kind - so you can dynamically define the order in which these fields should be rendered by the application code - which, if you haven't guessed, should just be looping through all attributes for a given category when it generates the data entry form in the application. If you want to get really clever, maybe have a field for validationMask which will contain a regular expression - in the language of your choice, to be applied by the application at data entry time: user data fails to match the regular expression? Too bad - you can't update. Oh, then why not have validationMessage too, to show them why we can't save that bad data (like "Dates must be in the format DD/MM/YYYY").
Coming back to that question of data validation - all well and good if you can validate with a singular regular expression (and the mandatory flag), but if you need to validate on the database server prior to updating/modifying data, then consider the following additions. You might like to put another column on your attribute table called something like type or attributeType. It is a pseudo-representation of data type. It might contain string values from a fixed set that you define, like "date", "int", "string". You might then write a few database functions along the lines of validateIntAttribute, validateStringAttribute and validateDateAttribute. They might make use of native functions like SQL Server's try_cast to check that the data value received from the application (ie. received as string input) can be cast to the data type we want.
Lastly, we come to saving user-contributed data. For this we have a table for data. It relates to product and attribute. This means for a given product-attribute combination, here we are saving the data value - probably in a field like value or data. This means literally every single data value becomes a separate row on this table - regardless of which product relates to or how many attributes are saved for a given product. If you wanted to, you could actually have a few tables like this - for the different data types (I mentioned date, int and string before) - and then you could actually cast your data to the relevant data type, but splitting across tables means that when you retrieve data you will have lots of "if the attribute is of type 'int' then join to the intData table" - and that's really bad for performance and legibility - I don't recommend it.
So... the above works fine, if you are ok with the caveat of all your data being saved in a string-based type (and therefore building whatever you need to, to ensure consistency in data representation).
You might also want to think about another type of attribute: the dropdown list. I'll leave that modeling to you.
I have built all the above, and it works.
It takes some people time to wrap their heads around the notion of fields being records (rows) on a table (here, attribute) and the data values for a single "record" (like your products) being split across multiple rows (in your data table). It also takes time to think about how you're going to query your data and edit or delete it, but it can be done. The result is a very dynamic and incredibly extensible approach to storing attribute data which has a good measure of future-proofing. In fact, during development of the system I've designed, the business encountered an excellent opportunity - but needed to be able to store two new kinds of information against their records. Two new fields were defined and added to the system in less than five minutes and the application rendered them - in correct sort order, with validation rules implemented - automatically; no coding/deploy/release required.
Oh! One last comment. You mentioned you are looking for a simple solution and always come up with something overly complicated. I wouldn't call the above design simple, but what I will say is that any time you need new fields for storing data in future, you simply define them dynamically (in data) in minutes without the need for any application deployment. Secondly, the system I built was geared around contact information - I found at least seven different areas in the application where contact information had been modeled in different ways: that's seven iterations of design, code, test, deploy - each with a different data model, each with its own issues, none of them extensible. This one design will, in due course, replace all seven of those - making that information stored in a consistent way across the entire application. So that's your second trade-off - building something extensible is more work now for trivial work in future. Building something "simple" now means less work now and ongoing work - and reduced business responsiveness - in future.


DBCC loginfo contains thousands of VLF entries with status 2

if this is happening only for one db, check the default initial size of the log file for that DB. I suspect the minimum size has to be large enough so that after restoring or even after backing up the log SQL server increases the log size to specified minimum size and initialize all VLF with zero.

Alternately, if you have already taken a full backup of the db try to create dummy DDL stmt, backup the DB again, restore it on another machine, and confirm the DDL operation you did. now you can detach and attach the DB w/o using the existing log file that will create new log file with the default size you have in your instance.


How do I install/enable the uuid-ossp extension on Postgres 9.3?

You seem to have two versions (9.6 and 9.3) installed (because the psql version is 9.6).  Because the postgresql-contrib package automatically defaults to the currently supported PostgreSQL database contrib package (as noted if you do apt-cache show postgresql-contrib), you'll have to install the contrib package for 9.3:

apt-get install postgresql-contrib-9.3


database design for car part site. (relational database)

you should have a table for the brands, another one for the models related to their respective brand, another one for the submodels related to the submodels and in the last table use compound keys with the keys of the tables and the year and create views for queries.


How to set up tns listeners for Multiple Oracle Installations on One machine

You need to have different keys for the IPC entries in the two configurations, e.g. KEY = EXTPROC10 and KEY = EXTPROC11. IPC uses memory and semaphores etc. that may be shared across the machine, so you need to provide a way for the instances to be differentiated.

Also make sure you're starting the listeners explicitly, e.g. lsnrctl start listener10, and you have the environment set properly for your two accounts, including LD_LIBRARY_PATH pointing to the right ORACLE_HOME.


How to get index last modified time in Oracle?

I will elaborate on the @Jeffrey Kemp's comment. Since the information about authors of modifications is not collected in the objects' metadata, you will want to use database auditing mechanisms.

As per Verifying Security Access with Auditing chapter of Database Security Guide, there are several types of auditing, but Standard Auditing is probably the only type of auditing you will need according to the requirements you mentioned in the question.

Before you can use standard auditing, you should enable it (if it already isn't) setting the AUDIT_TRAIL initialization parameter to the appropriate value (refer to the link). I evaluated standard auditing with AUDIT_TRAIL set to DB,EXTENDED which allows to capture SQL statements.

SQL> alter system set audit_trail = db,extended scope=spfile;
System altered.

SQL> startup force;
ORACLE instance started.

Once you have enabled standard auditing, all the auditing policies existing in your database will start to capture the auditing details.

There are four levels in standard auditing: Statement, Privilege, Object, Network. (See the table in the same chapter of Security Guide.)

With the following standard auditing statements of different levels I was able to collect some useful auditing information regarding index creation, index rebuild, index drop, and making index unusable.

audit index by access;
audit create any index;
audit index on hr.employees by access;

Sample output based on my activity is below.

SQL> select username, sql_text from dba_audit_object order by timestamp;

USERNAME          SQL_TEXT
--------          ------------------------------------------------------
hr                create index audidx on employees(last_name)
hr                alter index audidx rebuild
scott             drop index hr.audidx
scott             create index hr.audidx on hr.employees(last_name)
scott             alter index hr.audidx rebuild
scott             alter index hr.audidx unusable

Make sure you understand what's written in the relevant chapter of Security Guide I referred to, and do some experiments before using your newly implemented audit policies in production.

For the other types of auditing and useful information regarding auditing check my previous answers.


If you schedule a TSQL Job in SQL Server 2008 R2 and additionally need to copy files with it, is it better just to use a script?

Even though you can use xp_cmdshell to copy the directory, I would not recommend to do it using SQL Server.

You were right in thinking of using PowerShell and/or robocopy to copy files or directory to wherever you want. Powershell will even allow you to send email once the task is completed.

Currently, in my company, we use native sql server backups with compression and then have powershell scripts kick off the move to backup server on a different schedule.


How to get SQL Server 2012 to use the invariant culture in format()?

I have found only one way to get FORMAT to use the invariant culture: pass a string starting with the null character for the culture parameter.

-- Setting session language to Traditional Chinese to demonstrate that this
-- isn't just falling back to the session language.
SET LANGUAGE N'Traditional Chinese';
PRINT N'Session Language:  ' + FORMAT(SYSDATETIME(), N'Y');
PRINT N'US English:        ' + FORMAT(SYSDATETIME(), N'Y', N'en-US');
PRINT N'Invariant Culture: ' + FORMAT(SYSDATETIME(), N'Y', NCHAR(0));

  Output:

Session Language:  2013年9月
US English:        September, 2013
Invariant Culture: 2013 September

(The above example is using the "year month" format string of 'Y' since it produces different output between en-US and the invariant culture.)

This works due to what appears to be a quirk of how the .NET implementation of CultureInfo looks up the culture data. The string comparisons it is doing seem to ignore any data following a null character, likely because they are being done within native code in the CLR via functions intended for use with null-terminated strings. It therefore sees a string starting with a null character as being equivalent to the empty string, which as you noted is the documented identifier for the invariant culture.

CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("\0foo")) //returns true

DISCLAIMER: This is obviously rather hackish since this behavior is not documented for FORMAT nor for CultureInfo. I have only tried this with SQL Server 2012 running with .NET 4.0.3. I personally would not rely on this behavior for anything that cannot be easily changed if it were to stop working due to a future update.


Upgrade PostgreSQL 9.1 to 9.3 and server no longer starts

The config variable unix_socket_directory has been changed to unix_socket_directories in 9.3 - found in postgresql.conf


I'll take a swing at this :) My colleagues and I are pouring over how to make a succinct but accurate answer for this;

  on what undo logs are, and how they are different from the history list,

Undo logs are produced when data modifications are made. If a transaction modifies data with an INSERT, UPDATE, DELETE for example it will create discrete undo logs for each operation. Once a transaction is committed MySQL is free to purge the undo logs created in that transaction. There's a one to many relationship between Transaction and Undo Log (one per operation, remember).

My understanding is that the Innodb History List Length is the count of the discrete operations in the Undo Log(s). Undo logs are part of a structure called Rollback Segments and live in the global table space (ibdata1) or in UNDO tablespaces (a recent feature in MySQL 8.0).

  Does the InnoDB undo log contain two types of data, one being views of rows for transaction to use stored as linked-lists, and the other being the history list

No. The undo log will contain instructions on how to revert data to previous state and if other transactions need to see an old version of a row, it will need to use the undo logs (instructions to rollback) in order to honour the scope of it's read view and present previous versions of the data.

There is a background purge operation (analogous to Postgres vacuum) that garbage collects aged undo logs. It may only purge up to the oldest live transaction.

I hope my answer helps you to visualise things a little clearer. If I can tidy up further I'll make edits or if any InnoDB experts wish to chime in, please do so.


Use cases for NoSQL databases: Time series

Oracle is a SQL DBMS not a truly relational database.  It implements as its logical model of data a variant of SQL.  Its architecture was developed in the late 1970s along the same lines as IBM's System R which was an initial implementation of a DBMS based on the relational model using SQL as the data sub-language.  This short background is necessary to understand that SQL and Oracle are not the same as relational.  The relational model, as defined by Codd and further developed by researchers like Date, is a purely logical model of data where data is to be presented as relations, with a relational algebra defined to manipulate the relations, and a data integrity component to make it possible for the DBMS to maintain data consistent with its real world intent.  The relational model is mute on implementation by the DBMS.  Therefore, when identifying a use-case that a given SQL DBMS does not handle well purely due to performance reasons, the issue lies with the implementation, not the relational model.
Given this, I suspect NoSQL solutions are sometimes recommended over SQL DBMS' for time series analysis as time series analysis is a very narrow use case and the SQL DBMS architecture was aimed at more generalized online transaction processing use cases.  I know little about time series analysis but do recognize that it is purely analytical and not OLTP, and so transaction support - which is a mainstay of SQL DBMS' but orthogonal to the relational model - is pure overhead in such a use case.  I recall seeing Michael Stonebraker a few years ago discuss time series analysis and argue the solution was to store data in arrays not rows.  Since all the SQL DBMS's are row-stores, that may be another reason why other solutions are recommended.
I would caution against diving right into a NoSQL solution.  These systems are much less mature than traditional DBMS'.  Secondly, time series analysis I believe is pretty heavy statistics and you will likely have to add that yourself with a NoSQL solution.  A mature SQL DBMS like Oracle may have some built in statistical features that are much easier to use.  Third, SQL is, despite its flaws, a complete query language giving you the power to write queries of arbitrary complexity.  Most NoSQL solutions require you to write programs to perform the analysis you want.  Finally, and perhaps most importantly, it is likely that to get any useful information out of your time series data you need to "enrich" it with other related data.  For example, I work for an electric utility and in this business just having a huge amount of time series data on how much power was used for an interval of time isn't very useful unless you can correlate it to weather, demographics, and so on.  A SQL DBMS, precisely because it is a generalized data management solution, makes that easy.  You can place the time series data in the same database as the enriching data and have the full power of SQL to join and analyze it.  With a NoSQL solution you will have to perform the enrichment yourself as an additional step - potentially extracting, transforming, and loading the data from the very SQL DBMS that wasn't used to store the time series data in the first place!  It will be a lot of extra work to write the ETL programs, and you have to decide at the time you write them what data will be useful for analysis.  If later you decide you didn't have everything useful, you now have to write more programs.  If instead you placed the time series data in Oracle right along with all your other data it is already in place and ready for analysis once you discover a need.
Bottom line I would say that unless can prove you have so much data coming in so fast as to exceed the capacity of your existing SQL DBMS installation, and you have the time and the skill sets necessary to write the additional infrastructure on top of a NoSQL solution (assuming of course the NoSQL solution you choose does have the capability to scale to the data volume and velocity), you are better off sticking with the SQL DBMS.


Attach database failed for Server '{SERVER_NAME}\{INSTANCE_NAME}'. (Microsoft.SqlServer.Smo)

You need the "AdventureWorks2008R2_Log.ldf" file.  And put it in this folder:
"C:\Program Files\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\MSSQL\DATA\"

The easiest thing to do is download the "full backup" file from that site and then restore it, (instead of attaching.)


Move database physical location using a loop for database names. MSSQL

This is a script I developed in the past for helping me migrate lots of databases from one drive to another. It only works on one drive at a time (figured it was safer and easier to manager that way) but you can easily change that.
SET NOCOUNT ON;

DECLARE @OldDriveLetter VARCHAR(2) = 'D:';
DECLARE @NewDriveLetter VARCHAR(2) = 'N:';

DECLARE @Script_SetDatabaseOffline AS NVARCHAR(MAX) = 'USE master;' + CHAR(13)
DECLARE @Script_ModifyMDFLocation AS NVARCHAR(MAX) = ''
DECLARE @Script_ModifyLDFLocation AS NVARCHAR(MAX) = ''
DECLARE @Script_SetDatabaseOnline AS NVARCHAR(MAX) = '';

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- SET DATABASES OFFLINE
SELECT @Script_SetDatabaseOffline = @Script_SetDatabaseOffline +
'
    ALTER DATABASE [' + D.[name] + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time
    AND MF.[file_id] = 1; -- This ensures we de-dupe the result set so we don't have multiple dupe "ALTER SameDatabase SET OFFLINE"

--PRINT @Script_SetDatabaseOffline

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- MODIFY MDF FILE LOCATION
SELECT @Script_ModifyMDFLocation = @Script_ModifyMDFLocation +
'
    ALTER DATABASE [' + D.[name] + ']
    MODIFY FILE (name=''' + MF.[name] + ''', filename=''' + REPLACE(MF.physical_name, 'D:', @NewDriveLetter) + ''');
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time
    AND MF.[type] = 0 -- MDF (ROWS)

--PRINT @Script_ModifyMDFLocation

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- MODIFY LDF LOCATION
SELECT @Script_ModifyLDFLocation = @Script_ModifyLDFLocation +
'
    ALTER DATABASE [' + D.[name] + ']
    MODIFY FILE (name=''' + MF.[name] + ''', filename=''' + REPLACE(MF.physical_name, 'D:', @NewDriveLetter) + ''');
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time
    AND MF.[type] = 1; -- LDF (LOGS)

--PRINT @Script_ModifyLDFLocation

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- SET DATABASES ONLINE
SELECT @Script_SetDatabaseOnline = @Script_SetDatabaseOnline +
'
    ALTER DATABASE [' + D.[name] + '] SET ONLINE;
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time
    AND MF.[file_id] = 1; -- This ensures we de-dupe the result set so we don't have multiple dupe "ALTER SameDatabase SET OFFLINE"

--PRINT @Script_SetDatabaseOnline

/*
SELECT @Script_SetDatabaseOffline
SELECT @Script_ModifyMDFLocation
SELECT @Script_ModifyLDFLocation
SELECT @Script_SetDatabaseOnline;
*/

SELECT @Script_SetDatabaseOffline + @Script_ModifyMDFLocation + @Script_ModifyLDFLocation + @Script_SetDatabaseOnline;

The comments explain what's going on in details but in short it's basically using dynamic SQL and the sys schema objects to build out strings of the queries needed to reconfigure the locations of the MDFs and LDFs of all user databases. (You can add your own additional logic if you want to exclude certain user databases too.)
The steps it basically does are:

Sets the databases offline
Modifies the MDF locations
Modifies the LDF locations

*You'll want to move the physical files between step 3 and 4 (because the databases have to be offline before you can move them).

Sets the databases back online


Database design - do I need another table?

Another option is to just add StartDate and EndDate fields to your "Employment" table.
Then when a client comes in with a change in Employer you update the EndDate for his previous Employer and add a new record for the new Employer with no EndDate. You can always look up his current employment by checking which record for that ClientID has no EndDate. If you don't need start dates or end dates you could just add a field that's basically a flag to indicate whether this is present or past employment.


There's three separate parts to this:

Q: Do I use SQL Server's built-in log shipping or roll my own? A: I'd roll your own. What you're doing is beyond what the built-in tools are normally used for.

Q: If I roll my own, where do I start? A: I'd use normal SQL Server full and transaction log backups on the primary. On the secondary, start with MSSQLTips' script to automatically restore backup files from a folder. That example will restore all of the files, though, not just some of them - you'll need to adapt it to only restore log backups in there. In a perfect world, you'd only attempt to restore log files that haven't been restored yet, but if you're in a rush, you could restore all of the log files every time - SQL Server will automatically skip logs that have already been applied.

Q: How do I alert when files aren't being restored? Rather than focus on the files, I'd focus on the data. Do restores with standby when you restore the logs so that you can run queries against them, and after each restore job finishes, query your most transactional table looking for the newest record. If it's older than X minutes, your data may not be coming across. (Granted, this only works for databases with decent change rates, like adding records every few minutes - if you don't have that, then some DBAs add a dbo.LastUpdated table with a datestamp in it, and have a SQL Agent job that updates the only record in there every 5 minutes to set the datestamp to current.)


Split a very large SQL table to multiple smaller tables

Addressing the question behind the question:

  Maybe you can answer this. Is it possible to search a column within a table for a numerical value range of 1-2000 and then copy each full row into a different table?

If you already have a column by which you can "partition" the table, then you can do something like this:

SELECT *
 INTO dbo.newtable_0001_to_2000
 FROM dbo.existingtable
 WHERE column >= 1 AND column <= 2000;

SELECT *
 INTO dbo.newtable_2001_to_4000
 FROM dbo.existingtable
 WHERE column >= 2001 AND column <= 4000;

...

Just note that this will give you a predictable number of rows, but won't control the size of the rows, so depending on the data in each "partition" these tables will not likely be of equal sizes.


Use mysqldump to backup table with a new table name

You have to use sed to transform the table name

mysqldump -u user -p db_name test_table | sed 's/test_table/test_table_backup/g' > backup.sql

Give it a Try !!!


How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours?

Create a Master-Master setup as follows:

Create second master, MasterB
MasterB acts as slave to logTable
Create logTable_new as innodb
Run INSERT INTO logTable_new SELECT * FROM logTable (psuedocode) on MasterB, which sends the replication over to MasterA
When logTable_new on MasterA finishes syncing, swap out the tables


MS SQL: Can't connect to server with anything but 127.0.0.1 on the server machine

Are you running multiple instances?

Make sure that TCP connections are enabled.

Make sure a connection is allowed by your firewall, or that the firewall is disabled.

If you have trouble with either of these after making changes, try re-starting the DB and SQL Server Browser (or disable Browser).


Perhaps you should use --single-transaction
mysqldump --single-transaction -u root -p common_schema > common_schema_bkup.sql

Why ? According to the MySQL Documentation on mysqldump, you need the following rights:

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.
To reload a dump file, you must have the same privileges needed to create each of the dumped objects by issuing CREATE statements manually.

Please note the common_schema is comprised of one table and 27 views. You must have the necessary grants in order to be able to dump them, whether or not you dump with --single-transaction. This might include having the CREATE VIEW and SHOW VIEW grants.
UPDATE 2015-07-24 23:09 EST
I think the problem rests with mysqldump itself. Many metacommands are written around the dump of the definitions that could be interfering with rendering output that can logically represented. In some cases, a mysqldump of common_schema could not be reimported.
Your best chance would be to import common_schema from scratch. Right from the common_schema Documentation, it says on troubleshooting installation:

Since installation is merely an import act, you should only expect trouble if schema generation is unable to execute on your server.

Are you executing on a 5.0 MySQL server? This version is not supported.
Were errors reported during installation process?
What was common_schema's last message during install?
What is the output of SELECT * FROM common_schema.status?

Since unique metadata is not in common_schema, you should be able to reimport it rather that risk backing up with mysqldump with the possibility of not reloading it.


Error while exporting data to flat file from database using sqlplus in UNIX

ORA-08103 object no longer exists

means you were querying a segment that has since been (re)moved.  The most common examples of this are:

Someone truncated the table
Someone did 'alter table move'
Someone did 'exchange partition'

all of which move a segments contents to a new place on disk, hence the data you were scanning "no longer exists"


Age range on age range in WHERE to heavy - POSTGRES

One approach to improve readability would be to use a CTE to add a couple of calculated columns..  However, this approach could also be slow, depending on the size of the table..  But it would look something like this

WITH true_ages AS (
  SELECT
    <cols>,
    CASE WHEN age_high < age_low THEN age_high -- handle case of 'x and below'
         WHEN age_low = 99 and age_high = 99 THEN 0 -- handle case of 'all ages'
         ELSE age_low END as true_low,
    CASE WHEN age_low < age_high and age_high = 99 THEN 999 -- handle case of 'x and above'
         WHEN age_low = 99 and age_high = 99 THEN 999 -- handle case of 'all ages'
         ELSE age_high END as true_high
  FROM <table>
) SELECT
  <cols>
FROM true_ages
WHERE <target_age> BETWEEN true_low and true_high;

You could potentially add some WHERE clause in the CTE to reduce the result set initially, but that might add unnecessary complexity.


Deploying the stuff you need to move a job is pretty much just like packaging the stuff you need to move application code assuming that you correcly put all the new items and changes in source control. This includes your SSIS package, any object creation or alteration scripts such as tables, views, UDFs, stored procs, CLRS (Never ever create database objects using the GUI if you want to deploy later), any scripts to populate tables (such as lookup tables). You may need to number the items in the deplyment folder to ensure they are run in the correct order. Usually I write a deployment document as well because some of what we are deploying will go to differnt servers (Our ssis server is is differnt from our database server).

You can also script out the job, but you will need to review and change the script for the new environment. I often find it just as easy to set up the job on the other server manaully (but I have dba rights to all servers, if you do not, you will probaly need to this).


Can't connect using SqlManager for Oracle, but Sql+

After installing 32-bit Oracle client software on a 64-bit Windows
  operating system (OS) you receive one of the following errors:
  ORA-12154 / ORA-06413.

  Cause   64-bit Microsoft OS's install 32-bit applications into   C:\Program Files (x86)\... rather than the typical location of
  C:\Program Files\....

  This causes an existing networking bug to occur where the networking
  layer is unable to parse program locations that contain parenthesis in
  the path to the executable which is attempting to connect to Oracle.

  Solution   Find the location of the application that is generating the error.  Check the path to this location and see if it contains any
  parenthesis.  If so, you must relocate the application to a directory
  without any parenthesis in the path.

I just moved Sql Manager to 64-bit applications install path (I know it's not super "clean", but nevermind) and now it works correctly.

Found answer here: MSDN connecting to oracle on 64bit x64 machine


Dimension Modelling Senario Data Warehouse Design

The second version looks more realistic. Mobile devices and computers can change their users and this version allows to keep the history.


Why does changing the sort order return a different number of results? Is it a bug?

The two should be equivalent since they only differ in the ORDER BY, and this is last in the logical processing order of the clauses you use.

The physical processing order, however, can be anything the optimizer chooses. In this case I would guess (and it can only be a guess without execution plans for both queries) the DESC causes the optimizer to evaluate the COALESCE later in the plan than in the other query. This causes physical join operators to compare on NULL instead of -1. That comparison fails and so fewer rows are returned.

Anyhoo, you shouldn't get different row counts either way.


LDF and MDF file sizes for a dynamically created Database

This all depends on what you want to happen when the database hits 5GB. Do you want users to get errors and the database to become unusable full stop, or do you just want to be notified?

It also depends on whether you want to consider the log size separately from the actual data. The log size will fluctuate based on a variety of factors, including recovery model, frequency of full/log/diff backups, avg/peak size of transactions, duration between begin transaction and commit/rollback, etc.

Finally, it depends on whether you want to be have to manage this proactively. If for any of the above reason your log reaches 5GB but you've decided you don't want to stop the database from working because of log usage, you'll have to intervene if the max log size is set to 5GB but the data itself really hasn't grown...


POSTGRESQL 12: pg_basebackup isn't copying postgresql.conf and pg_hba.conf from master to standby

If data_directory in the master's postgresql.conf is set to some non-default value, then the conf files will not be in the "real" data directory and will not be copied by pg_basebackup.

If the person in charge of the master is not you, you should ask them for a copy by some other means.  Or you can just create your own.


Any better way out of MySQL InnoDB log "in the future"?

This was a pretty rare situation.  I hope to never end up there again, with an InnoDB "log sequence number is in the future!" error.  Because of my particular details, rebuilding/restoring my server's data was a last resort.  Some cheats in helping that were good ideas, but in the end, I decided to just keep improving my Perl script to play this silly game and churn through as many gigs/hour as I could.  What the heck, it's a good system stress test.

Remember: the goal is to increase a single counter ("log sequence number") which is stored somewhere in the headers of ib_logfile0 and ib_logfile1.  This is to fake out InnoDB so it will ignore an apparent time warp and get on with life.  But no one knows how to edit that number. Or if they do know, no one's talking.

Here's my final product.  YMMV, but using mysql's REPEAT function to generate the data internally is highly efficient.

 #!/usr/bin/perl
 use DBI;
 $table = shift || die;
 $dbh = DBI->connect("DBI:mysql:junk:host=localhost", "user", "pass"); #Edit "junk" (DB name), user, and pass to suit.
 $dbh->do("DROP TABLE IF EXISTS $table");
 $dbh->do("CREATE TABLE $table (str TEXT) ENGINE=INNODB");
 $sth = $dbh->prepare("INSERT INTO $table (str) VALUES (REPEAT(?,1000000))");
 foreach (1..50) {
    $sth->execute('0123456789');   # 10 MB
 }
 $dbh->do("DELETE FROM $table");

My suggested recipe:

Create a 'junk' database
Save the above perl script as junk.pl.
Run junk.pl data1, and junk.pl data2, and junk.pl data3, etc. all at once, for as many CPU cores as your database server has, to start.  Open multiple shells and wrap each run in a Bash loop: while true; do date; junk.pl dataX; done.

Watch your LSN grow, perhaps in another loop:

 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 124 3871092821
 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 124 4209892586
 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 125 85212387

The big number is an unsigned 32-bit INT which will wrap at 4GB, increasing the smaller number every time.  In this case above it just rolled from 124 to 125.  Your goal is hidden in the mysqld.log that sent you Googling for this ridiculous solution in the first place.  Once you cross that finish line, that's it!  Blow the horns!  Release the confetti!

  Sidebar: This uncovered an interesting bug in mysqld 5.0 w/REPEAT: if you go to 20 MB, it flips some internal counter and rolls over to ~ 96 KB.  No warning or error anywhere.  I wasn't about to waste time tracking that down.  10 MB works great.  If you do hit some other limit, it may complain.  I have various innodb buffers increased from default.  Season to taste.  As always, watch mysqld.log in one window.


SELECT NOT IN two foreign keys from other table

There are various ways to do it.

For NOT IN (not recommended because you will get unexpected results if your columns are nullable), you should have 2 subqueries as the a IN (subquery) requires that the subquery returns a single column:

SELECT tba_id FROM tableA
WHERE tba_id NOT IN
      ( SELECT tbb_tbAID1
        FROM tableB
        WHERE tbb_date > '$date2'
      )
  AND tba_id NOT IN
      ( SELECT tbb_tbAID2
        FROM tableB
        WHERE tbb_date > '$date2'
      ) ;

or a UNION:

SELECT tba_id FROM tableA
WHERE tba_id NOT IN
      ( SELECT tbb_tbAID1
        FROM tableB
        WHERE tbb_date > '$date2'
      UNION ALL
        SELECT tbb_tbAID2
        FROM tableB
        WHERE tbb_date > '$date2'
      ) ;

My favourite is NOT EXISTS and seems you got it almost right, in Try2.

SELECT tba_id
FROM tableA
WHERE NOT EXISTS                  -- notice the difference with your query
      ( SELECT 1
        FROM tableB
        WHERE (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
          AND tbb_date > '$date2'
      ) ;

Of course, you could use 2 subqueries as well:

SELECT tba_id
FROM tableA
WHERE NOT EXISTS
      ( SELECT 1
        FROM tableB
        WHERE tba_id = tbb_tbAID1
          AND tbb_date > '$date2'
      )
  AND NOT EXISTS
      ( SELECT 1
        FROM tableB
        WHERE tba_id = tbb_tbAID2
          AND tbb_date > '$date2'
      ) ;

You could also use LEFT JOIN / IS NULL check:

SELECT tba_id
FROM tableA
  LEFT JOIN tableB
    ON  (tba_id = tbb_tbAID1 OR tba_id = tbb_tbAID2)
    AND tbb_date > '$date2'
WHERE tbb_date IS NULL ;

or:

SELECT tba_id
FROM tableA
  LEFT JOIN tableB AS b1
    ON  tba_id = b1.tbb_tbAID1
    AND b1.tbb_date > '$date2'
  LEFT JOIN tableB AS b2
    ON  tba_id = b2.tbb_tbAID2
    AND b2.tbb_date > '$date2'
WHERE b1.tbb_tbAID1 IS NULL
  AND b2.tbb_tbAID2 IS NULL ;


Best way to move different desktop local PostgreSQL DB to single amazon RDS

Based on the replies you gave to my comments you must figure out how to resolve the duplicate keys issue. Also I do not think that master-slave replication is appropriate in this case.

I would, after making sure that there are no duplicate keys, use pg_dump to transfer your data into AmazonRDS one by one. Perform checks after every database that you do. If you have time and space then first do a test. During this test the 5 machines can remain active.

For master-slave you should check the Replication page of PostgreSQL. The problem remains the duplicate keys as mentioned there as:

Your users take a local copy of the database with them on laptops when they leave the office, make changes while they are away, and need to merge those with the main database when they return. Here you'd want an asynchronous, lazy replication approach, and will be forced to consider how to handle conflicts in cases where the same record has been modified both on the master server and on a local copy.

I do not think however that you can have 5 different 'versions' on the local desktops.


MS SQL Server CPU load goes up dramatically when turning on 2014 features by setting compatibility level

I would suspect the new cardinality estimator as a responsible for this behaviour, you can read up on it in the msdn article

It is clearly stated that the new cardinality estimator takes effect when you put the database in the new compatibility mode.

  Upgrade all existing databases to use the new cardinality estimator.
  To do this, use ALTER DATABASE Compatibility Level (Transact-SQL) to
  set the database compatibility level to 120 or higher.

There are trace flags that control this behavior.
If you want to use the new cardinality estimator when the database is in the old compatibility mode you can enable trace flag 2312
If you want to use the old cardinality estimator when the database is in the new compatibility mode you can enable trace flag 9481

If you can isolate the problematic query you could try to tune it so it performs well with the new cardinality estimator or if that doesn't work you can use the QUERYTRACEON keyword on that query only. That is also documented in the msdn link.

Also note that when, setting a trace flag you might need to use DBCC TRACEON (<traceflag>,-1) where the -1 sets it globally.


As far as a barber being connected to the appointment I'm with you guys on this. A foreign key [BarberID] in the appointments table links a barber to an appointment. The service being performed is also an attribute of that specific appointment. So the line from Performs should go to the appointment table. If anything, a foreign key relationship can exist between the barber table and the Services reference table, to create a list of services that a particular barber can perform (ex: not all barbers may be able to do highlights)

On a non-related note, I noticed that some of your tables are plural and some not. Try looking up some conventions for object naming and come up with something consistent, that would easily be followed by your future co-workers.


MS SQL servers in different countries linked for ETL via SP

I had to work around a similar issue where the two locations where 13,000 kilometers away and the internet connection on one of the locations was 1995 level of poor:

(Forget about merge replications and all other fancy methods).

So after all kinds of test including Azure and other cloud services I realized I was gonna have to go with something far less orthodox.

I created a new custom TCP/IP service (basically a silent console application that runs in the background on a separate thread than the users frontend) using C# that basically queries the database on either end and finds modified, deleted and new records every 15 minutes (using GUID to make sure records on both sides have same identifiers) it will then compress and encrypt this data and send it to the other location where a queue table with timestamps will determine whether or not the records are to be committed. If a record on the receiving instance has a newer timestamp that the record being brought in (accounting for a 4 hour difference) then they go into a conflict resolution routine.

Then some modifications to the users frontend where necessarily mainly so they're aware of data changing on one side or new records added to the database.

All of this was possible because they only needed 3 tables to be doing this cycle, I will not recommend it for drastically more complex scenarios.


Load into memory-optimized table gives insufficient memory error

Setting max memory to 2147483647MB is a really bad idea, as it could/would destabilize the operating system.

You've posted your SQL server is the following version:

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30

I would strongly recommend patching this server to CU4, because they contain many bug fixes that are specific to In-Memory.

If you are still having problems:

Is compression used for the disk-based table?
After creating a resource pool, and binding the database to it, did you OFFLINE/ONLINE the database to make the binding effective?


Slow MongoDB start time when data is replicated to a new machine

It looks like the initial slowness is because the EBS volume (which is restored from snapshot) is being initialized during the service startup.

https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-initialize.html


Load balancing between 2 servers using MySQL Router

Yes! You can have a router for basic Master-Slave Replication. But use the routing strategy as "first-available".

Though this works, you should be aware that your slave will be in read-write mode (if you allowed it to be) and all write operations performed on slave should be manually synced with master.


How do I get the types of columns for postgresql?

Based on the query you already have,

SELECT (SELECT data_type
               FROM information_schema.columns
               WHERE table_schema = 'public'
                     AND table_name = 'table_name'
                     AND column_name = 'name') "name",
       (SELECT data_type
               FROM information_schema.columns
               WHERE table_schema = 'public'
                     AND table_name = 'table_name'
                     AND column_name = 'id') "id";

would produce the output you want (but only a single row, which is enough per your comment).

SQL Fiddle


dbcc checktable will write in my dbcc page (x,9,3) for the field dbi_dbccLastKnownGood

According to Erin Stellato's blog, What Checks Update dbccLastKnownGood?:

  Breaking out the checks between the different commands does not update
  dbccLastKnownGood – running CHECKDB is the only thing that does

Erin also notes there that DBCC CHECKFILEGROUP and CHECKDB with the PHYSICAL_ONLY option will update dbccLastKnownGood.


Restoring multiple files to multiple database with pg_restore

pg_restore doesn't take multiple filenames, nor multiple databases (-d) end of story. So use a simple shell loop. I assume you're trying to

Clear out the old database
Load up a dump to the database with the same name

That loop will look like this

for dump in gl_election gl_world gl_tourism; do
  echo Clearing and reloading "$dump"
  pg_restore -h localhost -p 5432 -U postgres -d "$dump" -c "$dump"
done;


Database design for articles with localized prices

I would have tables for:

Supplier

Product

Inventory

Price

Currency

Sales

Country

I would follow tradional Entity Relationship Diagram and model. The more you normalise the design the more efficient it will be because it will be doing less IO. Sometimes you can take shortcuts which might save you a bit of time. But eventually it'll catch up with you and you'll wish you did it properly the first time.
A product could have many prices. Could be sold in many countries. Some countries could have multiple currencies. Prices change over time.
If you expect there will be more than 1 country/currency/price etc then design it to handle multiple countries/currencies/prices etc from the beggining.
So when you define a price you'd want to be able to define a price for this product, in this country, in this currency for this time frame.
Yes its possible to combine all of these things into a compound key, but experience has taught me you dont want keys to have any business meaning.
"Performance" is relative. RDBMs' are extremely good at crunching transactional data. With good design this model will scale up to corporate levels. Reporting and analytics might need a rethink.


Transform a number in symbols in a MySql SELECT

SELECT ID,
       REPEAT('*', IFNULL(category, 0)) AS SymbolCategory
    FROM ...

This assumes you want NULL to be equivalent to 0.


An alert for Log backup which is less than 2 hours

The below script will create a SQL Server Agent Job that runs hourly.  The job reports, via database mail, whenever it detects a database that hasn't had a log backup in the past 2 hours.  Since log backups don't apply to databases in the simple recovery model, those databases are excluded.  Also, the model database is excluded - you may want to remove that exclusion if you make frequent modifications to the model database (why would you, though).

The job is schedule to run starting at 6:00 am each day, and stop running at 10:00 pm.  You'll need to modify the @recipients and @profile_name variables in the first couple of lines of the script.

The job is set to notify the SQL Server Agent Operator named 'DBA', so you'll need to set that up in advance.  You can use dbo.sp_add_operator to add an operator via T-SQL.

USE [msdb]
GO
DECLARE @recipients varchar(max) = '<your_email_here>';
DECLARE @profile_name sysname = N'<database_mail_profile_here>';

DECLARE @cmd nvarchar(max) = N'DECLARE @msg nvarchar(max);
SET @msg = N'''';
;WITH src AS
(
SELECT bs.database_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = ''L'' --only log backups
    AND bs.backup_finish_date > DATEADD(MINUTE, -120, GETDATE())
)
, last_backup AS (
SELECT bs.database_name
    , bs.backup_start_date
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = ''L'' --only log backups
    AND bs.backup_finish_date <= DATEADD(MINUTE, -120, GETDATE())
)
SELECT @msg = @msg + CASE WHEN @msg = N'''' THEN N'''' ELSE N''

'' END
    + d.name + CASE WHEN lb.backup_start_date IS NOT NULL AND lb.backup_start_date <> N'''' THEN N'' (last log backup: '' + CONVERT(nvarchar(30), lb.backup_start_date, 120) + N'')'' ELSE N'''' END
FROM sys.databases d
    LEFT JOIN last_backup lb ON d.name = lb.database_name AND lb.rn = 1
WHERE d.state_desc = N''ONLINE''
    AND d.recovery_model_desc <> N''SIMPLE'' --simple recovery affords no log backups
    AND d.name <> CONVERT(sysname, N''model'')
    AND NOT EXISTS (
        SELECT 1
        FROM src
        WHERE src.database_name = d.name
        )
ORDER BY d.name;

IF @msg <> N''''
BEGIN
    SET @msg = N''

The following databases haven''''t had a log backup during the last 2 hour period:

'' + @msg + N''

    '';
    DECLARE @subject nvarchar(1000);
    SET @subject = ''Log Backup Notification from SQL Server: '' + @@SERVERNAME;

    EXEC msdb.dbo.sp_send_dbmail
          @profile_name = ''' + @profile_name + N'''
        , @recipients = ''' + @recipients + N'''
        , @subject = @subject
        , @body = @msg
        , @body_format = ''HTML'';
END
';

DECLARE @ReturnCode int;
SELECT @ReturnCode = 0;

BEGIN TRANSACTION;

BEGIN TRY
    IF NOT EXISTS (
        SELECT sc.name
        FROM msdb.dbo.syscategories sc
        WHERE sc.name = N'Log Notifications' AND category_class=1
        )
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
            , @type = N'LOCAL'
            , @name = N'Log Notifications'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred adding the Job Category', 1;
    END

    DECLARE @jobId binary(16);

    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysjobs sj
        WHERE sj.name = CONVERT(sysname, N'Log Backup Notifier')
        )
    BEGIN
        EXEC msdb.dbo.sp_delete_job @job_name = N'Log Backup Notifier'
            , @delete_history = 0
            , @delete_unused_schedule = 0;
    END

    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Log Backup Notifier'
        , @enabled = 1
        , @notify_level_eventlog = 3
        , @notify_level_email = 2
        , @notify_level_netsend = 0
        , @notify_level_page = 0
        , @delete_level = 0
        , @description = N'No description available.'
        , @category_name = N'Log Notifications'
        , @owner_login_name = N'sa'
        , @notify_email_operator_name = N'DBA'
        , @job_id = @jobId OUTPUT;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred adding the Job', 1;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
        , @step_name=N'Email DBAs if no log backup has occurred in 2 hours'
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @on_success_action = 1
        , @on_success_step_id = 0
        , @on_fail_action = 2
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority=0, @subsystem = N'TSQL'
        , @command = @cmd
        , @database_name = N'msdb'
        , @flags = 0;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurrred adding the Job Step', 1;

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
        , @start_step_id = 1;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred setting the job starting step.', 1;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
        , @server_name = N'(local)';

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred setting the job server to (local)', 1;

    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysschedules ss
        WHERE ss.name = N'Log Notification Schedule'
        )
    BEGIN
        EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'Log Notification Schedule';
    END
    DECLARE @start_date int;
    SET @start_date = CONVERT(int, CONVERT(varchar(8), GETDATE(), 112));
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
        , @name = N'Log Notification Schedule'
        , @enabled = 1
        , @freq_type = 4
        , @freq_interval = 1
        , @freq_subday_type = 8
        , @freq_subday_interval = 1
        , @freq_relative_interval = 0
        , @freq_recurrence_factor = 0
        , @active_start_date = @start_date
        , @active_end_date = 99991231
        , @active_start_time = 60000
        , @active_end_time = 215959;

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRANSACTION;
        THROW
    END
END CATCH
GO


How can I push data from my local MAMP database to my remote database?

There are a couple of ways to get your data from your local Dev to your remote Dev or Production environment. The first and simplest way is to just dump your db and upload it. You can also use a tool like phpMyAdmin to Sync your db but that would require you to install more software and understand basic networking. If you have a huge Dev database with lots of unused test data you can also dump and import just the db structure which will give you an empty db with no information (users, setting, whatever you are storing). I would highly recommend installing phpMyAdmin on your local Dev server to simplify your life, it makes any of these options extremely simple and pretty much 1 click tasks.


Finding the default isolation level in SQL Server 2017 and Azure SQL DB

It's referring to Azure SQL Database which uses RCSI by default.

Isolation Level SQL
Database default database wide setting is to
enable read committed snapshot isolation (RCSI) by having both the
READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options
set to ON, learn more about isolation levels here. You cannot
change the database default isolation level. However, you can control
the isolation level explicitly on a connection.

In on-premises SQL Server, the default isolation level is Read Committed.
Finding the "default" isolation level is a bit of a goose chase. It will depend on the database where a query is initiated from, and if any hints exist in the query to override a database level setting.
That said, you could query sys.databases to find out if RCSI or SI is enabled.
SELECT d.name,
       d.is_read_committed_snapshot_on,
       d.snapshot_isolation_state
FROM sys.databases AS d;

Note that the READ_COMMITTED_SNAPSHOT is not technically an isolation level. Rather, it is a database option that controls the implementation so that row versioning instead of locking is used to provide read consistency for sessions using the READ_COMMITTED isolation level.


Unable to get Cassandra in a Docker to talk to cluster

I think that the problem could be that you don't set the CASSANDRA_BROADCAST_ADDRESS parameter that is used by Cassandra when it talks with other nodes. By default it sets it to value of CASSANDRA_LISTEN_ADDRESS, but in  your case this address is address of Docker network, so other nodes won't able to reach your Docker instance.

You need to set CASSANDRA_BROADCAST_ADDRESS to IP address of your machine that hosts the Docker.


The example PowerShell command below will delete trace file target rollover files that haven't been written to for more than 24 hours. Change the trace file pattern to match your actual trace file target and schedule in a SQL Agent job PowerShell step for the daily purge.

Get-ChildItem  "C:\SqlTraceFiles\YourTraceName*.xel" | Where-Object -Property LastWriteTime -lt (Get-Date).AddDays(-1) | Remove-Item -ErrorAction Ignore

This command will not delete the active trace file since it's in use (ignoring the error). If the event volume is too low to cause a rollover at least once per day, consider restarting the trace daily to avoid older events accumulating in the same file.


SQL | How to sum in same table but referring to different cell values

Don't forget GROUP BY clause when using aggregate functions.
Your inner SUMs aren't aliased, so x.quantity won't work.

Try this :

SELECT x.productName, SUM(x.quantity) as quantity
FROM
(
    SELECT productName, SUM(quantity*10) as quantity
    FROM Sample
    WHERE description IN ('GT','GE')
    GROUP BY productName

    UNION ALL
    SELECT productName, SUM(quantity*20) as quantity
    FROM Sample
    WHERE description IN ('VD')
    GROUP BY productName
) AS x
GROUP BY x.productName


Oracle silent install on Redhat Linux (The Global database name was left blank.)

./runInstaller -silent responseFile=/home/oracle/db_install.rsp
  -showProgress DECLINE_SECURITY_UPDATES=TRUE

The above line is wronge. I changed it like the following and it works fine.

  ./runInstaller -silent -responseFile /home/oracle/db_install.rsp -showProgress


How to connect multiple users/schemas in Oracle 11g?

You need to grant the REFERENCES privilege on the reference table to user2.
(See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.


Does assigning variable in SELECT do RBAR operations?

It almost seems like for each value returned, that value is assigned to the variable, then each row in the result set is subsequently assigned, almost like a loop.

Yep.  That's what happens.  This is a side-effect of how variable assignment in SELECT queries were initially implemented.  But it was used enough that it can't really be changed, and even is documented:

  If a SELECT statement returns more than one row and the variable
  references a non-scalar expression, the variable is set to the value
  returned for the expression in the last row of the result set.

Variables (Transact-SQL)

and

  For backwards compatibility reasons, SQL Server provides support for
  assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most
  scope.

Ordering guarantees in SQL Server


Best Practices for Testing Application Dashboard Data

I would not use GetDate() for this fixed-period scenario as the end date for queries, because the reports are only meningful for specific periods.

A better approach is to use a specific run-date for each reporting period. That run-date is not the current date.

For example, let's say one of your KPIs is the sales amount for the first 6 months of 2013. Your system should use end of June as the end date not the current run-date regardless of when the report is run. If you do this, you'd not have to touch the data.


change character_set_connection utf8mb4 mysql

What client are you using?  You will need to specify that the connection is to be with utf8mb4.

What about the tables?  If you have existing tables, an ALTER ... CONVERT TO will be needed.


dbo.SORT temporary run storage: 148643705716736' What does this number means?

That is most likely the allocation_unit_id that was attempting to be allocated when it ran out of space. You can check by running the following query to see if tempdb is currently using similar IDs:

SELECT * FROM tempdb.sys.allocation_units;


Getting the error log location in an efficient way

SELECT SERVERPROPERTY('ErrorLogFileName')

Actually it is an (undocumented) serverproperty,  which appears to work on SQL2005+

Joe Sack has a post on it


PostgreSQL: Why is the schema owner not the database owner

A new database is created by cloning an existing one. If you don't specify which one to clone, PostgreSQL clones the hidden-by-default database template1. All objects in template1 are owned by user postgres.

So you're not the owner of any of the objects in the database at the time it's cloned from template0 or template1; you only own the database its self.

New objects are created with your ownership because you created them. Existing objects retain their existing ownership.

See \dn * in psql to see the namespaces defined, \dp *.* to see table/view/sequence privileges, \df+ * to see function definitions with owners, \dT * to see data types, etc. You'll see that in a new blank DB they're all owned by user postgres.


I would suggest using SQL Server Data Tools (SSDT), which is essentially the latest iteration of the Visual Studio Database Projects. I use this at places that do not already have a way to source control their databases since it easily integrates with TFS, which most Visual Studio shops use for source control.

A couple pros I have for using it are:

Source controlling database schema
Able to test changes locally before publishing to server
Easier workflow when changing project and generating change script to server
Peer reviews are simpler as the other person just needs to open the project
Table design has most items (indexes, constraints, foreign keys, primary keys, etc.) right in designer instead of opening multiple areas
Latest version has everything in one install (database schema, SSIS, SSRS, and SSAS).

A few cons for using it would be:

Change script may be as simple as you would like (sometimes a change creates a new table and copies the data into this table, which could take a long time depending on row count)
Generates change script using SQLCMD syntax so be aware of that
Default settings detect changes that may be irrelevant so sometimes best to not check for those (permissions, role memberships, and users are examples)
Referencing external sources may not be intuitive (i.e. table in another database)
This only supports SQL Server (the older Visual Studio Database Projects supported more than SQL Server).
No schema design tool

Having said all that, I still use it and recommend it. I generally try to let developers use just that tool instead of SQL Server Management Studio (SSMS) because I look at SSMS as a DBA tool and SSDT as a developer tool.


How can I alter the fill factor of an index outside of an ALTER INDEX command?

Check your DB Server (or instance) options, it will set every index based on the fill factor you specified.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', x --set the fill factor value here, such as 70 for 70%
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

edit:

Do note, it will not actually rebuild or alter existing indexes.  It will fulfill your request that all future index rebuilds will now have this fill factor, unless specified in the rebuild index command.  Anything specified in the rebuild command will override this option.


SQL Server 2008 R2 on production -- is it worth to upgrade to SP1/SP2

1.Upgrading the server to SP1 or SP2: will it be worth the hassle?
  I have been searching for something clear-cut on the benefits of SP1 over RTM, or SP2 over SP1 over RTM

Yes very much and believe me its not a hassle it will save you from lot of hassles. As already pointed you are running on unsupported version of SQL Server. Let me tell you personal experience, suppose you face any issue which comes eventually as a MS bug and you plan to ask MS to pay you for your loss you cannot because you are running unsupported version. The legal agrrements are little more complicated and I will not talk about it. Also even if you raise a case with MS for some support related activities MS engineere will straight away say its unsupported version first please apply SP2, YES not even SP1 SP1 is also not supported, then only they will proceed further, after you have applied SP2.

There was a critical bug fixed in SQL Server 2012 SP1.

Memory leak for SQLOPTIMIZER
Unexpected start for SQL Server.

List of bugs fixed in SQL Server 2008 R2 Sp1

List of bugs fixed in SQL Server 2008 R2 SP2

I guess above two reasons and links are enough. Are you waiting to face the issue and then apply SP2 ? Please dont. Also the option recompile bug was fixed in later CU and eventually in SP1.

  2.Should we decide to apply SP1/SP2, will there be critical hotfixes to apply afterwards?

As already pointed SQL Server 2008 R2 SP2 is not fully supported. As a general rule after launch of new service pack within one year previous SP becomes unsupported. Following is difference between mainstarem support and extended support taken from here

Think of Mainstream support as “normal”. In other words, mainstream support means Microsoft supports a product with its full offerings including paid incident support, hotfix support, security updates, etc.

When a product enters the Extended Support “phase”, the game changes:

1) We still provide security updates at no charge to all customers

2) You can still call CSS or create a case online per the normal support offerings (pay per incident, Premier, etc)

But…you cannot obtain a non-security hotfix from Microsoft for no charge. In order to obtain a non-security hotfix, you must purchase an Extended Hotfix Support Agreement. Extended Hotfix Support Agreements are available for Premier customers. Contact your Technical Account Manager for more information.

The extended support might involves more cost than normal support and is not available to everyone. If your firm has premier support tie up with MS then you can get extended support eaisly otherwise its costly and bit difficult. This is as per my region MS support/licensing is complex and you should speak to licesning expert to get more information about extended support.

MS says that CU should be only applied if user is facing bug which is fixed in that CU. If not Microsoft recommends not to apply it. SP's are more throughly tested and more reliable as compared to CU. So I would also say unless you face issue dont go for CU.If you want help in deploying SP please refer to This Link(its for SS 2012 but process/steps remains same)


I have resolved the issue as it was a bug in the connectorJ's version. As The project I am working on was already using both the buggy jar mysql-connector-java-5.0.8.jar and the jar version in which the issue is already resolved i.e. mysql-connector-java-5.1.13-bin.jar. After all the search when I removed the jar mysql-connector-java-5.0.8.jar my issues got resolved. All that was problematic was that the ConnectorJ/Driver was getting referred from the buggy jar. The bug id and url which refers to this issue is: http://bugs.mysql.com/bug.php?id=31053 .

Thanks for considerations.


why does mysqldump restore to a smaller size?

InnoDB stores records inside pages (by default each page size is equal to 16 KB). For different reasons you may have partially filled pages (for example, after you deleted some records). When you restore database using mysqldump your rebuild tables from scratch and receive less fragmented version of your database. You can use OPTIMIZE TABLE command to defragment table in your old database and release some space.


The IP Addresses in your DNSx_tmp variables need to be enclosed in single quotes in order to make them character strings (as they have been Declared).

The SET DNSx_tmp; variables need to be moved below all the DECLARE statements.

You also need to add a BEGIN clause above DECLARE c_user and the matching END clause before your final ENDclause.


SSRS 2016 report manager using m/d/y date parameter format in Chrome

I'm not sure if this will work for you but Chrome uses it own language configuration.

Go to:

Settings
  Show Advanced Settings
    Languages
      Language and Input Settings

And then select the desired language. (English UK)


Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)?

Getting the same row from different indexes

As David mentions in his answer, you can get the same row from multiple sessions if you happen to access that row via different indexes.

The UPDLOCK hint only applies to the specific access method. Having a nonclustered index row U locked does not prevent another query acquiring a U lock on a different index (including the clustered index, if any).

Running these two queries (with index hints) from different sessions results in the same row being returned:

-- Session 1
BEGIN TRANSACTION;

SELECT TOP (1) Skey, BsAcctId, Status
FROM dbo.Clearing WITH(READPAST, UPDLOCK, INDEX(2))
WHERE ([Status] = 'NEW')
ORDER BY Skey;

-- Session 2
BEGIN TRANSACTION;

SELECT TOP (1) Skey, BsAcctId, Status
FROM dbo.Clearing WITH(READPAST, UPDLOCK, INDEX(1))
WHERE ([Status] = 'NEW')
ORDER BY Skey;

After the failover, new execution plans will be compiled for incoming queries - so this could explain why you ended up with the new behavior after the failover.  As David also said, you could force the index to avoid this problem.

As a side note, you should also use a ROWLOCK hint, since READPAST can only skip locks taken at the row granularity.

Getting different rows in the same session due to concurrency

You also mentioned this:

  The problem is load related. With 4 worker processes running it did not happen. But with 10 workers it happens consistently.

So it sounds like the failover was not the only thing that changed - you also increased concurrency on the application side of things.

I tried loading up your table / index with some data:

INSERT INTO dbo.Clearing
    ([Status])
SELECT TOP 100
    'NEW'
FROM master.dbo.spt_values;

INSERT INTO dbo.Clearing
    ([Status])
SELECT TOP 10000
    'COMPLETE'
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

Then I loaded up SQL Query Stress with your query, set it to run on 10 threads at once, every 100 ms:

While that was running, I periodically ran the same query with EXEC sp_lock @spid1 = @my_spid; tacked on to the end in SSMS.  If I run the SELECT query in the same session multiple times (without rolling back), I can get multiple locks held by that session:

Which you can see using the %%lockres%% predicate:

SELECT * FROM dbo.Clearing WITH (NOLOCK, INDEX(2)) WHERE %%lockres%% = '(36aaaeef6267)';
SELECT * FROM dbo.Clearing WITH (NOLOCK, INDEX(2)) WHERE %%lockres%% = '(84d6be32a10d)';

Without much concurrency, you'll generally get the same row if you run that SELECT more than once in the session.  But with other queries taking and releasing locks all the time, it's easily possible to get different rows.  So make sure you are not depending on the SELECT returning the same ID twice (we don't have the whole context of your workload, so this is just speculation / FYI).

Unexpected locking behavior

It's can be unsafe to depend solely on specific locks to be taken.  Consider an optimization described in this blog post from Paul White (or another one like it): The Case of the Missing Shared Locks

The post outlines a situation where a row protected by an X lock can still be read by SELECT queries:

  SQL Server contains an optimization that allows it to avoid taking row-level shared (S) locks in the right circumstances. Specifically, it can skip shared locks if there is no risk of reading uncommitted data without them.

Related reading:

Using tables as Queues by Remus Rusanu
When READPAST Doesn’t Read Past by Erik Darling


Retrieve information about a recent copy only backup?

Try querying without the "top 100":

SELECT *
FROM msdb.dbo.backupset
WHERE name = "abc"

OR

SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ),*
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0

Reference: Where is database .bak file saved from SQL Server Management Studio?

As Avarkx said, add  NAME = 'abc Name' to your WITH so that it shows a name when you do a backup. Otherwise it shows NULL. Then run the select without the TOP 100.


How to control psql displaying rows with (END)?

The (END) is the mark by your pager (more or less).  Turn it off using

\pset pager off

inside psql.  I can imagine you had a ~/.psqlrc file earlier that had this line in it, or the default behaviour changed otherwise.  Just add the above command to this file to return to the desired behaviour.

Scroll down to 'Files' on the documentation page for more details about what can be done using this file.


SQL Server 2005 collation issue during installation

there is no collation "sql_latin1_general_as"
there is no collation "sql_latin1_general_ci_as"
collation chosen during install does depend on the control panel settings
The default collation for a clean install would be "SQL_Latin1_General_CP1_CI_AS" for US English:
The default for UK English is "Latin1_General_CI_AS"
SQL collations are legacy and should only be chosen for in-situ upgrades (IIRC)

Basically, choose "Latin1_General_CI_AS" (you have a choice) and make sure you read correctly before posting


rename a column without disrupting users

Rename the column, add a generated column with the old name.  If inserts or updates of the old column haven't been corrected yet, provide insert and update triggers to handle the new column.  When no logic refers to the old column anymore, drop it.


How can I remove a channel from replication slave?


My guess is the answer is NO. You won't make this any faster.

A database (at least all the ones I've worked with) doesn't treat text as you would with a Java StringBuilder, but rather like a Java String, which will suffer exactly the same problem (as would strings in most programming languages, except, maybe the V8 implementation of JavaScript). I wouldn't consider that a bug. It's a design decision. A database is not normally used in this fashion.

A StringBuilder has extra room for extra text, which is appended at the end of the already used space. When it actually runs out of space, it allocates a big chunk for further filling. A String is immutable and does not have extra room to add new text "at the end".

Perhaps what you should do is not modify your original row, and have instead a secondary related table where you store every string associated with myKey, together win an string_order column (could be an auto_increment, or a current timestamp) and, when needed, retrieve everything together with a GROUP_CONCAT of them. Your related table would just work as a kind of log, where you would be inserting the new "events" in order, a piece at a time.


Are indexes unnecessary for columnar database?

If you want to scan a subset of all data stored you need some kind of indexing structure. It does not have to be a B-tree. In a certain sense, partitioning or segment elimination is also a kind of indexing.

There is nothing intrinsically preventing a columnar database to be stored in a sorted (or roughly sorted way). This would allow seeks to happen.

That said, not all columnar databases provide the ability to scan a subset of the data stored selected by some predicate. This is an implementation issue, not a limitation of the columnar model.


Query timeout expired error on SQL Server via ASP page

This can be issue with both depending on how you look at it.  You are getting a 'Query timeout' and not a 'Connection timeout'.
You have 2 options.

Increase your query timeout setting.  Most of the time by default it
is 30 seconds. See this links for
ODBC and
SqlCommand.
Set up an Extended Event trace (If you are not comfortable, do a
server side  trace with proper filters) and find the query that is
timing out.  Work on improving the run time till it is below 'Query Timeout' setting.  There were times I was
able to capture the query with out trace and just by using sp_whoisactive.  I was watching
total run time.


How did this statement update 3 rows in Postgres?

It seems that random() is executed once per row and not once for all the statement. So each id value is checked against a different random value. You may get 0, 1, 2 or more, even all the rows of the table, although that possibility would be very small even with 10 rows.

If you want it to be executed once and thus get only one value and update only zero or one rows, you could use a CTE, which are known to be evaluated before the main query:

with rnd as
  ( select random() as random )               -- get just one value
select *
from keyword, rnd
where id = cast(rnd.random * 99999 as int) ;  -- and use it everywhere

and the same for the UPDATE:

with rnd as
  ( select random() as random )
update keyword
set revenue = random()*10                    -- different random value
from rnd
where id = cast(rnd.random * 99999 as int);


Insert into ... (...) (Select...) on duplicate key update

if you want to change the value with the new one, you can use:

ON DUPLICATE KEY UPDATE
    doctets = VALUES(doctets) ;

or if you want to add the new value to the existing one:

ON DUPLICATE KEY UPDATE
    doctets = doctets + VALUES(doctets) ;


You need a compound unique index.

Suppose your table is called photos. You can do this:

CREATE TABLE photos_new LIKE photos;
ALTER TABLE photos_new ADD UNIQUE INDEX pid_tid_index (pid,tid);
INSERT IGNORE INTO photos_new SELECT * FROM photos;
ALTER TABLE photos RENAME photos_old;
ALTER TABLE photos_new RENAME photos;

If it works out, then run

DROP TABLE photos_old;

Give it a Try !!!


Hi user1202278 (is that your real name?),

MariaDB does not perform any particular scheduled actions on its own.  There is almost certainly a cron job at work, unless there are users who stay up late every night!

It would be a good exercise to monitor your DB for a while and see how often and when heavy processes are running.  From there, you can narrow down the suspects.  If you haven't already, I would recommend that you invest a good monitoring tool.  I personally use Navicat Monitor for MySQL/MariaDB.  It provides all of the important stats on the Dashboard page and can track server metrics such as disk usage, I/O, CPU, RAM, etc.

It has a 14 day trial, which should be plenty of time to diagnose your issue.

Best of luck!


PostgreSQL doesn't currently support DB-level encryption. (Correct as of 9.4, at least).

You can use an encrypted file system, though the performance impact can be pretty serious for write-heavy systems, especially those doing random I/O. Your options depend on the operating system - Microsoft BitLocker, Linux's dm-crypt/LUKS/cryptsetup, OS X's FileVault 2, etc.

Alternately, look at PgCrypto for field-level encryption, but beware of key-disclosure issues in logging and pg_stat_activity.

See also:

Secure postgresql database encryption
How to use aes-encryption in PostgreSQL?
https://stackoverflow.com/q/12614523/398670
https://stackoverflow.com/q/21095026/398670
https://stackoverflow.com/q/8054503/398670


Delete the N oldest entries grouped by multiple columns

If I understand you requirements clearly, you need to delete rows that are grouped on the basis of char and category (leaving minimum latest N rows)

Updated: Temporary table workaround

This can be achieved by

Option 1:

create temporary table test_table_rows_temp SELECT * from test;

DELETE t1
FROM
    test  as t1
WHERE
    t1.id < (
            SELECT
                min( t2.id )
            FROM
                test_table_rows_temp as t2
            WHERE t2.category =  t1.category
                    AND t2.`char` =  t1.`char`
                    AND t2.id >  t1.ID
            HAVING count( t2.id ) >= 2
        )

I have created a temporary table to store the results of the test table, this helps to ease out the query

Option 2:

There's a workaround to avoid manually creating a temporary table by using a nested sub-query instead

DELETE t1
FROM
    test  as t1
WHERE
    t1.id < (
            SELECT
                min( id )
            FROM (
                SELECT
                    id,
                    category,
                    `char`
                FROM test
            ) AS t2
            WHERE t2.category =  t1.category
                    AND t2.`char` =  t1.`char`
                    AND t2.id >  t1.ID
            HAVING count( t2.id ) >= 2
        );

The nested sub-query internally too creates a temporary table :)

The 2 rows as mentioned above (ids 1, 4) are deleted

The query simply gets minimum - N + 1 id in the group of category, word (ordered by the latest id) and then checks if the current row id is less than the one retrieved (within the same group(s))

SQLFiddle

Hope this helps


thanks all for halp, especially sticky bit.

Here solution worked for me.

New table with id and name column with fulltext. I query with match against in boolean mode "query*". Works very well. No need for elastic.


Ubnutu/MySQL - Error "Unable to locate package innotop"

It seems to be in the file list for mysql-client-5.5.

sudo apt-get install mysql-client-5.5 should be all you need to do.

Note that the mysql client program is in a separate package, mysql-client-core-5.5, so it's possible to have mysql installed without any of the other useful tools like mysqladmin or mysqldump.


Instant File Initialization on Shared drive

Community Wiki answer generated from comments on the question, from Bob Klimes, Sean Gallardy, and Aaron Bertrand.

You'll need to enable Instant File Initialization (IFI) on the server holding the disk. Restart SQL Server for it to take effect.

You can test if IFI is working on the share by creating a database on the share with trace flags 3004 and 3605 turned on. Output is written to the SQL Server error log.

  If IFI is working, there will not be any entries for zeroing the mdf file.

IFI does appear to work with network shares, but for me the creation of a test database took 45 seconds on the share and less than 1 second locally. Even with IFI enabled, you might still have performance problems.


The pl/sql package DBMS_PARALLEL_EXECUTE can be used to parallelize pl/sql. Examples can be found in the manual. You can use the first example (Chunk by ROWID):

define a procedure

create process_chunk(start_id rowid, end_id in row_id)
...
begin
...
for rec in select column from table1 where rowid between start_id and end_id
  loop
  stored_procedure( rec.column );
  end loop;
...
end;
/

and then (following the example) write a job that does the following

...
begin
...
--create a task
DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
-- Chunk the table by ROWID in chunks of 100 rows
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', USER, 'TABLE1', true, 100);
-- execute with parallel level 10
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', 'call process_chunk(:start_id,:end_id)',
  DBMS_SQL.NATIVE,  parallel_level => 10);

--and implement code for error handling and removing the task according to the example.
...
end;


Physical Server or a Virtual Machine SQL Server?

If you are using SQL Server 2008 R2 RTM or older then there is a CMD Command "SYSTEMINFO" which has information about System model.
OR
If cmdshell is enabled on instance then you can execute it from SQL Server Management Studio.

DECLARE @result int
EXEC @result = xp_cmdshell 'SYSTEMINFO'

  System Manufacturer:       VMware, Inc.
  System Model: VMware Virtual Platform

New columns (virtual_machine_type, virtual_machine_type_desc) in DMV sys.dm_os_sys_info are introduced in SQL Server 2008 R2 Service Pack 1

http://blogs.msdn.com/b/sqlosteam/archive/2011/05/11/more-on-dmv-changes-in-sql-2008-r2-sp1.aspx


Problem generating restore scripts for multiple ola backups

I posted this in the comments section but I feel this should give you an answer that is simpler and cleaner.

https://paulbrewer.wordpress.com/sp_restoregene/

Simply install the script and run sp_Restoregene.  You can pass it @database and @stopat too.

This script will script all the restore scripts automagically for you.  Including FULLs, DIFFs and LOGs (in order).

This script has saved my bacon more times than I can count!


Call to sysmail_delete_mailitems_sp keeps deadlocking

So I didn't got any luck with the deadlock graph. Doesn't seem to appear in my SQL Logs. But, I did find out that another job has been scheduled to run at the same time than my job, and it was taking backups and sending e-mails at the same time that my job was trying to cleanup the mail history.
So changing the schedule of the other job got my problem solved!


SQL Server Query to EXCLUDE Friday and Saturday of the week & 4PM to 8PM all the days of the week

Assuming that DATEFIRST = 1 (Monday), you can use datepart(dayofweek) to avoid Friday and Saturday and for the time part, I've edited my previous answer due I think you said 8PM, I supposed 08:00:01 must be included in the final result.

declare @events table (Name varchar(20), ProcessStartTime datetime, Status varchar(20));
insert into @events values
('Name1', '2017-04-01 17:00:02', 'SUCCESS'), -- (Saturday)
('Name2', '2017-04-02 21:00:02', 'SUCCESS'), -- (Sunday)
('Name3', '2017-04-03 01:00:02', 'SUCCESS'), -- (Monday)
('Name4', '2017-04-04 05:00:02', 'SUCCESS'), -- (Tuesday)
('Name5', '2017-04-05 18:00:02', 'SUCCESS'), -- (Wednesday)
('Name6', '2017-04-05 15:00:02', 'SUCCESS'), -- (Wednesday)
('Name7', '2017-04-06 23:00:02', 'SUCCESS'), -- (Thursday)
('Name8', '2017-04-07 15:00:02', 'SUCCESS'); -- (Friday)

SET DATEFIRST 1

SELECT Name, ProcessStartTime, Status
FROM   @events
WHERE  datepart(DW, ProcessStartTime) NOT IN (5,6)
AND    (datepart(hh, ProcessStartTime) < 16
       OR
       ProcessStartTime > DATEADD(dd, DATEDIFF(dd, 0, ProcessStartTime), '08:00:00'))

GO

Name  | ProcessStartTime    | Status
:---- | :------------------ | :------
Name2 | 02/04/2017 21:00:02 | SUCCESS
Name3 | 03/04/2017 01:00:02 | SUCCESS
Name4 | 04/04/2017 05:00:02 | SUCCESS
Name5 | 05/04/2017 18:00:02 | SUCCESS
Name6 | 05/04/2017 15:00:02 | SUCCESS
Name7 | 06/04/2017 23:00:02 | SUCCESS

dbfiddle here


How can I find out if any cluster member is offline in mongodb cluster?

If you run the db.printSlaveReplicationInfo() or rs.status() as well as the db.printShardingStatus() commands you should be able to determine who can see who in the cluster and what state each member is in.


Saving the "view data options" in the "edit data" window in pgAdmin III

As people said in the comments, it is not possible. I didn't create a feature request for this as I feel it is going to be rejected (got some previous negative experience proposing features there), but if you want to try: https://redmine.postgresql.org/projects/pgadmin3


Google Cloud Spanner UNIQUE Index Consistency

First, there currently isn't an option to run Cloud Spanner across continents, even though Google's internal Spanner can. The answer however is still the same, just latencies on writes, etc are different in a Regional (Multi-zone) and Global (Multi-Continent) configuration.

The trick here is that Spanner does not insert into "the node", but instead tries to reach consensus across a majority of nodes. Since we always run an odd number of nodes, one of them will reach consensus first and block the other one. This transaction will be committed, then the other transaction will fail as the row already exists.

Eric Brewer gives a good overview of how consistency is achieved in this situations in this recent article: Spanner, TrueTime &
The CAP Theorem


Update Server JDK and JRE after Oracle Database Installation and creation

Oracle Database ships with its own JDK, it does not use the JDK you install on Linux.

So you can upgrade that without any impact.


If I understand correctly, you are getting count to satisfy your Pagination requirements. Generally, two separate queries are executed; one to get the 10 records, and another to get the total count.

The problem in your approach is that you are calculating the Count in a subquery inside the SELECT clause. For every row returned by the SELECT clause, is going to be executing the same count again and again. You can move the count calculation part to a Derived Table instead (Subquery inside the FROM clause), and CROSS JOIN it.

Another noteworthy thing is that MySQL is (unfortunately) generally inefficient when optimizing subqueries inside the IN(...) condition. You can convert this into EXISTS based Correlated Subquery instead (JOIN may result in Duplicates).

SELECT c.*,
       MATCH (c.name, c.keywords, c.description) AGAINST ('+searchKeyword*') as relevance,
       dt.totalResults
FROM customers c
JOIN customersPersonelData cpd ON cpd.customerID = c.customerID
CROSS JOIN
(
  SELECT COUNT(c1.customerID) AS totalResults
  FROM customers c1
  WHERE MATCH (c1.name, c1.keywords, c1.description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND EXISTS (SELECT 1 FROM customersPersonelData cpd1
                WHERE cpd1.customerID = c1.customerID)
) dt
WHERE
  MATCH (c.name, c.keywords, c.description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND EXISTS (SELECT 1 FROM customersPersonelData cpd
                WHERE cpd.customerID = c.customerID)
ORDER BY relevance DESC, c.name ASC
LIMIT 0,10

For good performance, you would need an index on customerID in customersPersonelData table. Also, a composite Full Text Index should exists on (name, keywords, description) columns in the customers table.

Now, if I were you, and this was a Pagination problem, I would look at this article to rethink how pagination should be done: https://mariadb.com/kb/en/library/pagination-optimization/


How to add duplicate records by basing on the count from another table (SQL)

You could JOIN the tables
eg
select T1.first_name, T1.last_name, T1.role
from table#1 T1
  join table#2 T2
    on T1.first_name = T2.first_name and T1.last_name = T2.last_name
;

-- result
+----------+---------+------+
|FIRST_NAME|LAST_NAME|ROLE  |
+----------+---------+------+
|John      |Smith    |Dev   |
|John      |Smith    |Dev   |
|Jane      |Doe      |Tester|
|Jane      |Doe      |Tester|
|Rock      |Lee      |Dev   |
+----------+---------+------+

DBfiddle here.


This would be a good application of an aggregate function and a case statement:

    ;WITH a AS -- CTE for your query
    (SELECT Household.Name,
            Account.AccountNumber,
            Account.Registration,
            FinancialInst.Name AS [Financial Institution],
            SUM(Asset.Value) AS Value,
            FinancialPlanner.LastName,
            PlanFirmSpecCode.SpecialCode,
            NFSvsDirect=CASE WHEN FinancialInst.Name ='National Financial Services' THEN 'NFS'
            ELSE 'Direct Business' END
FROM PlanFirmSpecCode
INNER JOIN FinPlanSpecCode
   ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID
RIGHT OUTER JOIN Household
INNER JOIN Account
   ON Household.HouseholdID = Account.HouseholdID
INNER JOIN Asset
   ON Account.AccountID = Asset.AccountID
INNER JOIN FinancialPlanner
   ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
LEFT OUTER JOIN FinancialInst
   ON Account.FinancialInstID = FinancialInst.FinancialInstID
INNER JOIN /*inner join not specified in question, assumed*/ FinPlanSpecCode
   ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE (Account.Closed <> 'Y')
  AND (Asset.Value > '0')
GROUP BY Household.Name,
            Account.AccountNumber,
            Account.Registration,
            FinancialInst.Name ,
            FinancialPlanner.LastName,
            PlanFirmSpecCode.SpecialCode,
            CASE WHEN FinancialInst.Name ='National Financial Services'THEN 'NFS' ELSE 'Direct Business' END

)

        SELECT SUM(CASE WHEN [Financial Institution] = 'National Financial Services' THEN Value ELSE 0 END) as [FirstColumn],
                   SUM(CASE WHEN [Financial Institution] <> 'National Financial Services' THEN Value ELSE 0 END) as [SecondColumn],
                   SUM(Value) as [ThirdColumn]
            From a

Please let me know if there's any need to clarify further.


My idea is not far from yours.

DECLARE @dtStart AS DATETIME ='20151028 10:00:00'
        ,@dtEnd AS DATETIME = '20151028 12:00:00'
        ,@iInterval AS INT = 30;  --30 min interval

WITH aCTE
AS(
    SELECT
        @dtStart AS StartDateTime,
        DATEADD(MINUTE,@iInterval,@dtStart) AS EndDateTime
    UNION ALL
    SELECT
        DATEADD(MINUTE,@iInterval,StartDateTime),
        DATEADD(MINUTE,@iInterval,EndDateTime)
    FROM aCTE
    WHERE
        DATEADD(MINUTE,@iInterval,EndDateTime) <= @dtEnd
)

SELECT
    -- 10:00:00 AM
    CONVERT(VARCHAR(10),StartDateTime,108)
    + ' ' + RIGHT(CONVERT(VARCHAR(30), StartDateTime, 9), 2)
    + ' - ' +
    -- 10:30:00 AM
    CONVERT(VARCHAR(10),EndDateTime,108)
    + ' ' + RIGHT(CONVERT(VARCHAR(30), EndDateTime, 9), 2) AS Result
FROM aCTE

And the output is:

Result
10:00:00 AM - 10:30:00 AM
10:30:00 AM - 11:00:00 AM
11:00:00 AM - 11:30:00 AM
11:30:00 AM - 12:00:00 PM