Best design for a tree-like structure database

I would create a separate table for history.  If it was me, I would use an auto-increment column for the primary key (historyID) , a column for the vial ID, and a column for a location ID.   I would think the vial ID would be foreign key to the nodes table. You could then query against a vial to see it's full history.

Since you don't list what your lookup tables are, I am going going to assume you don't have a history table yet.  I am also not sure you have a separate location table, and if you don't I would also use a location table that simply has a autoincrement primary key and a location description  the primary key of the location table would have foreign keys in both your node table and the history table.

Since you mention that the batch number is redundant, I would also add a separate batch table that uses auto-increment as the primary key (batchID) along with any columns that would be useful for a batch, (date of manufacture,other information unique to the batch, etc.)  The batchID would would then be a foreign key in your node table and although the batch ID might be repeated many times for different nodes, you might be able to remove other redundancies related to the batch in your node table.  Of course, since you don't list all the columns that are in your node table, it's difficult to give advice that is not based on some assumptions.  As I review my post, I would also put the batchID in the history table.  It would give you an easy way to trace the history of the parent batch.

Basically, you want to go through the process of normalizing your database.  By the time you normalize, your first main nodes table might end up being a very simple table that provides for the relationships between parent and children vials in a single table.  (Some might advocate for a separate table of parent and children vials, but since your manufacturing methods allow for multiple generations, I would keep it in a single table, and keep it simple.)

Sounds like an interesting project.

I hope this all makes sense and is helpful.

Although I have been involved with databases since the days when Access 97 was still current, this is my first post in dba.stackexchange.  I've been the accidental DBA for multiple projects at our company and I am finally finding some of the most commonly used resources for help and information on the web DBAs.


Need help trying to run a batch file from a SQL Agent job

The 'Open' button is for the convenience of importing a large script into the command window, which is not helpful with this type of job step as I explain below.  If you want it to run a batch file, you simply enter the path and filename, such as:

D:\scripts\DoSomething.bat

or

D:\executables\DoSomething.exe

And be sure to specify the type of command as "Operating System (CmdExec)"

Whatever account the step is set to run as will need permission to execute the batch file or process as well as access to any resources that are used in the script or process.

Also, a quirk of this type of job step is that it will only run one command.  So if you put the following in the command input box:

rem This script does something
D:\scripts\DoSomething.bat

It will run the first command and then stop, so the batch file will not be run.  So that is why the 'Open' button is not really helpful for CmdExec job steps.


Some RDBMS will allow you to design a table's data column as unique but not required and some will not. Supposing it was allowed then you would simply write some code to first query the table on the most commonly entered field and if not found, on the least commonly entered. Job done.

Otherwise, you may need to do something like creating a unique filtered index as mentioned in this Stack Overflow answer:
https://stackoverflow.com/questions/3570805/unique-non-required-column-in-sql-server-2008


ORACLE - Trying to return MAX or MIN based on a CASE statement

When you want to display aggregate results for each row without grouping, use the analytic versions of standard aggregate functions. For example:

SELECT
  CASE mod(ROLE_TYPE, 2)
    WHEN 1 THEN MAX(SALARY) OVER ()
    ELSE MIN(SALARY) OVER ()
  END
FROM HR;


This request is quite old, but as the accepted answer is wrong, I thought I'd add a correct one, so future readers don't get too confused.

A campain has landers and conversions. If we merely join all tables, we get for a campaign with two landers and three conversions 2 x 3 = 6 result rows. If we sum or count then, we'll get wrong results (the number of landers will be three-fold in the example and the conversion sum will be doubled).

There are mainly two ways to go about this:

Aggregate in subqueries in the select clause.

select
  id, name,
  (select count(*) from landers l where l.campaign_id = ca.id) as landers_count,
  (select sum(revenue) from conversions co where co.campaign_id = ca.id) as total_revenue
from campaigns ca
order by id;

Aggregate before joining.

select ca.id, ca.name, l.landers_count, co.total_revenue
from campaigns ca
left join
(
  select campaign_id, count(*) as landers_count
  from landers
  group by campaign_id
) l on l.campaign_id = ca.id
left join
(
  select campaign_id, sum(revenue) as total_revenue
  from conversions
  group by campaign_id
) co on co.campaign_id = ca.id
order by ca.id;

You can use COALESCE to get zeros instead of nulls in your result.


Why all the ( ) and long names?
Why mixed lowerecase camel case on table names?

Just basic cleanup

  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dbo.currentadwordsmaxbid.campaginid,
         dervtable.SKU,
         dervtable.Bid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU, campaginid, Bid
                          FROM   dbo.BidHistory AS ph1
                          WHERE  DTStamp = ( SELECT MAX(DTStamp) AS Expr1
                                             FROM   dbo.BidHistory AS ph2
                                             WHERE  ph1.campaginid = ph2.campaginid
                                             AND    ph1.SKU = ph2.SKU )
                        ) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU
                     AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
                     AND dbo.currentadwordsmaxbid.maxcpc <> dervtable.Bid

row_number()

  SELECT dbo.currentadwordsmaxbid.maxcpc - dervtable.Bid AS Expr1,
         dbo.currentadwordsmaxbid.webid,
         dbo.currentadwordsmaxbid.maxcpc,
         dbo.currentadwordsmaxbid.adgroupid,
         dbo.currentadwordsmaxbid.keywordid,
         dbo.currentadwordsmaxbid.campaginid,
         dervtable.SKU,
         dervtable.Bid
  FROM   dbo.currentadwordsmaxbid
         LEFT OUTER JOIN dbo.WebSkuLookup
                      ON dbo.currentadwordsmaxbid.webid = dbo.WebSkuLookup.webid
         LEFT OUTER JOIN (SELECT SKU, campaginid, Bid
                               , row_number over (partition by SKU, campaginid order by DTStamp desc) as rn
                          FROM   dbo.BidHistory AS ph1
                        ) AS dervtable
                      ON dbo.WebSkuLookup.sku = dervtable.SKU
                     AND dervtable.rn = 1
                     AND dbo.currentadwordsmaxbid.campaginid = dervtable.campaginid
                     AND dbo.currentadwordsmaxbid.maxcpc <> dervtable.Bid


A more efficient (and somewhat shorter) version of what Jon of All Trades mentioned in his answer could be something like this:

SELECT DISTINCT
CASE WHEN A < B THEN A ELSE B END AS value1,
CASE WHEN A < B THEN B ELSE A END AS value2
FROM T

Of course if you had to only check for tuples that contain 1, you would add something like WHERE A = 1 OR B = 1


Instead of doing a lookup like that for each row, you could join the AddressRole and Setting tables like this:

SELECT
    a.AddressID,
    IsPrincipal = Max(iif(s.SettingValue Is Not Null, 1, 0)),
    IsPickUp = Max(iif(ar.AddressRoleTypeID = 2, 1, 0)),
    IsSender = Max(iif(ar.AddressRoleTypeID = 3, 1, 0)),
    IsDelivery = Max(iif(ar.AddressRoleTypeID = 4, 1, 0)),
    IsReceiver = Max(iif(ar.AddressRoleTypeID = 5, 1, 0))
  FROM dbo.[Address] AS a
  Left Join dbo.Setting As s
    On a.AddressID = s.SettingValue
    And s.SettingDefinitionID = 3
    And s.ProfileID = 1
  Left Join dbo.AddressRole As ar
    On a.AddressID = ar.AddressID
  WHERE a.MFTID = '12345'
  Group By a.AddressID;

This should reduce the number of joins performed, and not change the cardinality of the resultset.

I added the MAX so that I could do a GROUP BY, since I fully expect that there might be more than one row in the AddressRole table for each row in the Address table. This way we maintain the cardinality as being the same as the Address table, but we can see if any of the rows in the AddressRole contained the value of AddressRoleTypeID that we were looking for.

The MAX and GROUP BY aren't needed if the subqueries of your original query without the TOP return a single row.


Can I get SSMS to show me the Actual query costs in the Execution plan pane?

I don't know of a way to do this in the plan from Management Studio, but this is one of the many things the free SentryOne Plan Explorer will do for you when you generate an actual plan from within the tool - it includes all the runtime metrics per statement.


Registry error while trying to install Oracle 11g on Win 2008 R2

From this link:

An old ORACLE_HOME had not been deinstalled/removed correctly and the following registry information still existed:

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Protid_0]
"Name"="HTTP"
"Host"="hostname"
"Port"="49155"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup]
"NumberOfInstalls"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\All Versions]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\All Versions\1]
"Version"="11.2.0.1.0"
"Home"="SOFTWARE\\ORACLE\\KEY_OraDb11g_home2"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\Current Version]
"Version"="11.2.0.1.0"
"Home"="SOFTWARE\\ORACLE\\KEY_OraDb11g_home2"

Please do the following,

Verify that:
the above registry keys exist in the Windows registry, and
they point to an old %ORACLE_HOME% which no longer exists on the server
If the registry keys do not exist or if they point to a valid %ORACLE_HOME% on the server, there is another cause for the error which needs to be investigated further. Do not perform step 2.
After verifying both of the above points, remove the above registry keys and perform the 11.2 installation again. The error should no longer occur.


Well for security reason you should not be letting anyone use the "sa" account. If you do then obviously changing that password will break whatever is using it. I try to set this password to a random password, rename it (only SQL 2005 and above allow this), and then disable it. I have not found anything so far that explicitly requires the "sa" account to function, my own admin account suffices.

With regards to the service account this should not cause any problems. Again as long as no other resource or service is using the same account.

The only backup plan I can suggest is having the old password handy to reset the accounts back to in the event something breaks.


SELECT * into dbo.dblog

FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'C:\tranlog.trn',
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
GO

Then add indexing, etc to the table dbo.dblog.


Should I add a column or 1-1 table if property is usualy null

If one of the following conditions is true then yes, go ahead and create a separate table.

You are working in SQL 2005 or lower
Your new column is one of the following datatypes: Geography, Geometry, Image, NText, Text, Timestamp, or a user defined data type
No default value
No rules
Column isn't a computed column

If none of those are true then you could use a SPARSE column.

ALTER TABLE tablename ADD mysparsecolumn INT SPARSE NULL

Here is BOL on SPARSE columns.  Also you may want to look at COLUMN SETS.  A sparse column is a column that is optimized for NULL values and according to BOL takes up no space when the value is NULL.  There are tables in BOL that will give you an idea of the actual savings generated by using SPARSE columns.

All that being said if you want to use a separate table you certainly can.  The down sides are that you will lose some space over all depending on how large your primary key is (the minimum you will need to include in the new table along with the extra column) and it's libel to be a bit slower because of the extra join.


No, you do not need a quorum disk for availability groups or failover instances.  Microsoft Failover Clustering has supported the file share witness since Windows 2003 SP1.  The file share witness, in conjunction with majority node set, enables a cluster quorum to operate with no shared disk infrastructure.

Technically, the cluster can function with majority node set only, but the typical availability group configuration also uses at least one file share witness so that a node gain take ownership of quorum when one or more other nodes lose communication.

With a failover instance, a shared disk is required for the SQL Server program and data files.  A quorum disk would typically be configured for a failover instance because the shared disk infrastructure is already required.  However, it would not be required and the a majority node set / file share witness quorum could be used.

See Configure and manage quorum and  Clustering: What exactly is a File Share Witness and when should I use one? for more details.


Count missing values in each column of the table in PostgreSQL

You can convert the rows into JSON to dynamically generate one row for each column:
select colname,
       count(cols.value) as non_null_values,
       (select count(*) from the_table) - count(cols.value) as missing
from the_table t
  cross join jsonb_each(jsonb_strip_nulls(to_jsonb(t))) as cols(colname, value)
group by colname;

But this isn't going to be fast on large tables.


I don't really understand why you're trying to pivot anything, so maybe I'm missing your point. But try this:

select
  id, name
, regexp_replace(metadata, '.*"a":"([^"]+)".*', '\1') m_a
, regexp_replace(metadata, '.*"b":"([^"]+)".*', '\1') m_b
from foo;

Doesn't rely on the order of the JSON attributes to always be the same. It's not completely safe - doesn't handle quoted " in particular, but it does the job for your sample.


Selecting one variant out of different similar results

One option is to use window functions to select those books:

select book, format, contents
from (
  select book, format, contents,
         row_number() over (partition by book order by case when format = 'markdown' then 1 else 2 end) as rn,
         count(*) over (partition by book) as format_count
  from books
) t
where rn = 1
   or format_count = 1;

The window function row_number() in the inner query assigns a number to each format. markdown will get a 1 everything else will get a 2.

The outer select then gets those rows where there is a markdown available or where only a single format is available.

Another (faster) option is to use Postgres' distinct on operator:

select distinct on (book) book, format, contents
from books
order by book,
         case when format = 'markdown' then 1 else 2 end;

This again sorts on an expression that will give markdown a higher "priority" than html.

The first solution is portable as it is ANSI SQL. The second one is Postgres specific.

SQLFiddle example: http://sqlfiddle.com/#!15/7a027/3


MS SQL Server: Import Flat File with auto-increment ID

You cannot alter a column to be an IDENTITY column.

What you'll need to do is create a new column which is defined as an IDENTITY.

Then use an update statement to update the new column from the old one (with SET IDENTITY_INSERT ON before and OFF after the update).

Once this is done, drop the old column.

Fianlly, rename the new one to the old name.


Searching all database schemas - how to use wildcards?

You can try this:

SELECT table_schema, table_name, column_name, data_type, ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'activated_a%' ;

When you want to use wildcard, you have to use LIKE instead of '='


Normalization: 'zip code' as a primary key

The page you linked to is, quite simply, wrong.

It makes no sense for a ZIP code to be a primary key in an address table.  You would expect to have multiple addresses in the same ZIP code, as you said.  ZIP codes cross city, county, and state lines which makes it uniquely poor as a key (postal codes in other countries may not be as problematic but data modelers always have to worry about the worst case).  Plus, ZIP codes change over time for an address which is not something you want in your primary keys.

In general, given that addresses tend not to have anything that works as a natural primary key, your address table will need a synthetic primary key (address_id) that has no meaning and simply acts as a primary key.  Databases have different ways to generate synthetic primary keys, sequences and auto-incrementing columns are common approaches.


Deploy dacpac fails with compatibility set to 130

I had (have) this same problem.

When the dacpac generates the script, it queries the target database to see what objects are there. One or more of those queries went from sub second to much longer, when we moved to 2016. By updating the statistics of some system tables I was able to bring it back down to normal behavior.

update statistics sys.sysschobjs;
update statistics sys.sysobjvalues;
update statistics sys.sysidxstats;
update statistics sys.syssingleobjrefs;

So, I don’t know exactly what changed to make SQL Server run those queries slowly, but this is how we've solved the problem.


Where does SYSTIMESTAMP come from in multi-server Oracle RAC environment?

Up to 11g I know Oracle used the local OS time as the system time in a RAC environment, and I never heard that it changed so I'm comfortable saying it's still in place.

You would sync all the servers together typically using NTP or another solution to ensure the time sync.  Usually if your servers local time is out of sync even by 30 seconds or so, you will tend to see other issues such as Kerberos auth issues so it's unlikely they are far off from each other.


Oracle19C installation is stuck at executing datapatch in Windows 10 using dbca

Make sure that the user installing the software is in the local ora_dba group, and that os authentication is enabled in sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Also, it is a good idea to make sure that any anti-virus software doing active scanning is disabled during install/db create activity, and that when running it contains exceptions for all Oracle-related files and directories.


Share the database between vmware guest and host

You have to comment out the single line in the `my.cnf:

 bind_address = 127.0.0.1

By default mysql server listen only on the localhost for security reason. When that line is commented, mysql server become listening on the all available interfaces because default value is bind_address = *. Now you can connect to the mysql from the host OS by IP-address of the VM.

To restrict guest access to the single database/schema you have to perform the next query:

GRANT ALL PRIVILEGES ON database.*
   TO 'guestuser'@'1.2.3.4'
   IDENTIFIED BY 'somepassword';

where 1.2.3.4 is an IP-address of your host OS.

If you have dynamically assigned IPs on your LAN then you have to allow connections from the whole subnet your IPs are belongs to:

GRANT ALL PRIVILEGES ON database.*
   TO 'guestuser'@'192.168.%'
   IDENTIFIED BY 'somepassword';

where 192.168.% means 192.168.0.0-192.168.255.255


replication database vs mirroring database

Replication:

Its mainly for Reporting and database synchronization
Secondary database is online and searchable. Basically you can run queries against your secondary database and treat it like a normal database while protecting the performance of your primary database.

There are different forms of replication

Transactional - Reads transactions to your secondary DB(s) as they occur on your Primary with a slight delay

Snapshot - Takes dumps at scheduled times of the changes recorded in your logs since the last sync, and inserts them at a scheduled time

Merge - The term says a lot for itself. It will merge content between databases so you can update data in both DB's. You might use this if you have two identical databases from day 1, and you have App 1 talking to DB 1, and App 2 talking to DB 2 for load balancing purposes or something. At the end of the day you might want to merge the transactions that occurred during the day to make sure you have two full lists of transactions in each DB.

Mirroring :

Its mainly for disaster recover solution which now is deprecated and replaced with AlwaysOn AG. Secondary database is in an offline mode while mirroring is in place. This is to ensure integrity in the secondary database in the event of a failover being required. Nothing can write to the secondary database, so you know it will exactly match your primary.
Can replicate schema changes. What this means is if you add a new index, or a new table, it will automatically be mirrored over. This is not the case with replication, it will only transfer across data level changes.
There are many configurations you can put together for replication, or mirroring, and you'd have to identify exactly what setup would suit your needs, but the above is a good baseline as to the best practices. Note you could possibly have snapshots of the mirrored database for reading purposes but that comes with its caveat of additional disk space resources and management, data integrity, and more. Generally you would not use this for your reporting solution.

Just gearing away from database mirrroring, if you are on SQL 2012 or later, can you consider AlwaysOn Availability Groups. It works just like database mirroring but allows the secondary replicas to be readable.


Removing Instances and SQL Server Express edition and going to Standard/Developer/Enterprise

(Local) without an instance name is the instance named MSSQLSERVER (the "SQL Server (MSSQLSERVER)" service).

If your only goal here is to get SSMS and SQL Profiler from the normal edition and not the express edition of SSMS that's easy, just install the tools from the Developer edition DVD / ISO.

Launch the installer, get through to the list of services.  Select only the management studio options (there are two, one under the other) and finish the wizard.  This will give you the tools and leave your instances alone.


When designing a new database, it's important to know before hand how it will be used.  Of course, it's difficult to have perfect knowledge of exactly how your database will be used before hand, because you may be prototyping, your business needs may change over time, etc.

I've found that a good rule of thumb for designing a relational database for a transaction processing system is to assume that each tangible thing your system tracks gets its own table.

Consider the following ERD for your situation:

In your case you have VENUEs that each have one or more STAGEs.  Each stage has many SEATs.  However, note that you want to keep track of PRICEs for seats, so for the sake of sanity in maintaining pricing data, your seats are arranged into SECTIONs.  The price is set at the section level instead of at the individual seat level.  The price also depends on the show time (EVENT) because you might have different prices for different days of the week or different times of day (week day matinee is cheaper than Saturday evening, for example).

You have multiple show times, but there is probably going to be many things common to an event with multiple show times, so you want an EVENT table and a child SHOW table.  This lets you keep show and event (show time) information in third normal form (3NF) which is always a good place to start with a transactional system database.

Assuming that you also want to keep track of who is actually in each seat, you need to have TICKETs and CUSTOMERs in your database as well.

To address your specific question about how to track seats, as rows or as columns, I strongly suggest you track seats as rows.  Relational database management systems like MySQL are built to easily handle rows of data.  If you handle seats as columns, you would probably need a different seat table for each stage, since the seats will be different for each one.  That would make your queries very awkward and complex.  If you are going to use a relational database for your data, then use it the relational way and put things in rows and facts about things in columns.  Your code will be simpler and your life will be better.


Is there a way to limit bandwidth of a backup/restore from a network location?

If backups/restores are the only activities occurring between the SQL Server and your offsite Data Center, you could setup Firewall Quality of Service rules to limit transfer speeds.  The total amount of data you transfer can only be reduced by lowering the amount of data you send (such as only sending every other backup).

I will highly suggest that you adjust where your backups are created, though.  You should backup your databases locally (preferably to another server within your local data center or to a separate backup drive locally on the server itself if you don't have other options available), and then configure a job to copy the backups (or every other backup) off site for long-term disaster recovery purposes.  Not only could this help reduce your bandwidth issue, but more importantly, having at least one backup locally will eliminate the possibility of a network outage or some other external factor from preventing you from recovering the database in the event of an emergency.  Backing up a database directly to an external data center is a risky proposition as you can generally count on some sort of network issue every now and again.  I've seen far more network related outages than disk or server outages, though my experience may not be reflective of everyone's.


How to find a session with a specific command

select sid, serial# from v$session where sql_id = (select sql_id from v$sql where sql_text = 'your SQL text');


Behaviour of performance counter "Checkpoint Pages/sec" under load

Checkpoints, like most things in SQL Server, are a big topic.  A good place to start is this Microsoft Docs page: Database Checkpoints (SQL Server)

To respond to your main question though, check out this specific quote from that page:

  The time interval between automatic checkpoints can be highly variable. A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

So "checkpoint pages / sec" will vary based on a lot of factors:

the type of checkpoints you're using (automatic or indirect)
if using automatic checkpoints, the value of the "recovery interval" server-level setting
if using indirect checkpoints, the value of the TARGET_RECOVERY_TIME database-level setting
disk latency

  Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 50 milliseconds.

the transactional throughput currently happening on the database

All that to say: seeing peaks and valleys in the number of pages being flushed to disk by checkpoint operations is completely normal.

If the purpose of the performance testing you mentioned is to normalize (as much as possible) the number of pages flushed to disk per second, consider reading the documentation page I linked to above and adjusting those settings.


SQL Server 2000 DTS package, filter rows using stored procedure returned date

In SQL Server 2000, you cannot use the results of a stored proc directly in another query. However, you can insert them into a #temp table and then join to that. But you must create the temp table structure first, you can't use select into.


Use FOR XML PATH

Sample from TSQL – Concatenate Rows using FOR XML PATH() - sqlandme.com

SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        — Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') — Select it as XML
                        ), 1, 1, '' )
                        — This is done to remove the first character (,)
                        — from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

Without FOR XML PATH

With FOR XML PATH


#1036 - Table 'pma__recent' is read only in phpMyAdmin / xampp / Windows

This a draft of my ongoing attempts to solve the issue.

The first steps I suggest are:

Stop the MySQL server
Backup the whole database folder mysql/data
Start the server again
Check the MySQL databases individually for possible errors: mysqlcheck -u root -p some_database_name


Map a Login to a User that was Created Without Mapping to a Login

By selecting SQL user without login within SSMS' create user dialog, this is the T-SQL that is generated (with no other options besides the user name supplied):

USE [AdventureWorks2012]
GO
CREATE USER [User1] WITHOUT LOGIN
GO

If you were to create a login and attempt to alter the user with the new login, like the following attempt:

create login TestLogin1
with password = 'p@$$w0rd';
go

use AdventureWorks2012;
go

alter user User1
with login = TestLogin1;
go

You will get the following error:

  Msg 33016, Level 16, State 1, Line 8
  The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

Because of that, I don't think you can link up a login to this type of user without recreating it.  It should be relatively painless, as you could just script out the user, but change the parameters from WITHOUT LOGIN to whatever login you want it to be mapped to.

EDIT: As pointed out by Aaron below, ensure that you're taking note of permissions and role memberships before making any changes.  You'll want to ensure that you can get back to the permissions state you currently are in, if that's the desired behavior.


Mongo can't determine key ranges for sharding if you use increments.
Here's an example:

You have a collection with keys 10, 20, 30, 40, 50, 60.

If mongo have to create two shards, it may assume the keys range as: [10,30] and [31,60] (or similar).

By continuing to write bigger keys, they will always go to the second range.

Mongo will adjust the ranges, but it can never know what the next key will be, and it will always put it into the last range.

Try to use some key with good distribution.

More details here: https://docs.mongodb.com/manual/core/sharding-shard-key/#monotonically-changing-shard-keys


How to implement UNIX like ACL for table rows

I'd strongly consider using PostgreSQL, as it supports Row Security (as well as Column Security), and is free and well made

You should also consider using real database users, but you don't have to. For example, you could set the current user into a temp table upon sign-in, and reference that username in your RLS policy


Why adding FOREIGN KEY constraint does not lock the table

A guess:

Even though the table is effectively locked, SHOW OPEN TABLES is oblivious to it.

LOCK TABLES is a very old DDL statement in MySQL.  It was probably a kludge to let users manually simulate transactions.  At about the same time (version 3?) SHOW OPEN TABLES was added.

Once InnoDB was added, it became clear that LOCK TABLES was no longer needed; InnoDB does row-level locking for DML and transactions.

There are a few DDL statements that effectively "lock" a table even in InnoDB, but they probably come through a different part of the code.  That is (remember, I am guessing), LOCK TABLES and SHOW OPEN TABLE know about each other, but ALTER runs in a different circle.

Feel free to complain at bugs.mysql.com .

I checked 122 systems; according to Com_show_open_tables, only 11 used SHOW OPEN TABLES since startup.


How do I analyse a set of SQL Profiler-generated rolling trace files?

The following will insert the entire set of files into a table of your choosing. Then you can query. Please do this on a dev instance.

SELECT *
INTO [Database].[schema].[table]
FROM ::fn_trace_gettable('\path\to\tracefile1.trc', default)


Why do PostgreSQL arrays have different starting indices for 1D and 2D arrays?

You don't have a two-dimensional array, you have an one dimensional array of points.  That is not the same thing, even if they do have some similarities.  I think a "point" is a type implemented as a fixed length array type, as discussed in the docs

  For historical reasons (i.e., this is clearly wrong but it's far too late to change it), subscripting of fixed-length array types starts from zero, rather than from one as for variable-length arrays.


Add fields to view from select (transpose fields)

For the simple case you present, build an array from your sorted values and unnest one by one in the SELECT list

SELECT arr[1] AS field1
     , arr[2] AS field2
     , arr[3] AS field3
FROM  (SELECT ARRAY(SELECT value FROM test ORDER BY field) AS arr) sub;

Obviously, this is not dynamic, but only works for given fields. You did not actually ask for "dynamic" - except that you added the tag dynamic-sql.

For more complex operations use crosstab() from the additional module tablefunc. Detailed instructions:

PostgreSQL Crosstab Query

Still not "dynamic". A completely dynamic function is hardly possible since SQL demands to know the return type at the time of the function call. If the return type is dynamic, you need two steps:

build your query dynamically
execute it.

But there may be something for you here:

Dynamic alternative to pivot with CASE and GROUP BY


How do I use window functions ("over") together with crosstab in Postgres?

In addition to what Colin already cleared up:

SELECT * FROM crosstab(
   $$SELECT (applied_date AT TIME ZONE 'America/New_York')::date AS d
         ,applied_class
         ,sum(count(id)) OVER (PARTITION BY applied_class
                         ORDER BY (applied_date AT TIME ZONE 'America/New_York')::date)
   FROM   application_app
   WHERE  applied_class LIKE '%L13'
   GROUP  BY 1, 2
   ORDER  BY 1, 2$$

  ,$$VALUES ('BOSFAL13'::text), ('NYCFAL13'), ('CHIFAL13'), ('MULTIPLECITIESFALL13')$$)

AS ct ("Day" date, "Boston" int, "New York" int, "Chicago" int, "Any City" int);

Major points

The extracted day depends on the time zone setting of the current session. Your sample displays timestamp with time zone literals, which indicates you may be operating in multiple distinct time zones. To get the date for a particular time zone, you have to declare that, or the current setting will be applied - which is easily overlooked and therefore a loaded footgun: You would get different numbers, depending on where you run the query!
Details:

Ignoring timezones altogether in Rails and PostgreSQL
Accounting for DST in Postgres, when selecting scheduled items

Safe syntax would be:

to_char(applied_date AT TIME ZONE 'America/New_York', 'MM-DD')  -- your time zone

There is another trap. If you GROUP BY day and month only, you can easily aggregate dates from multiple years by accident. I use the full date in my example.
Plus, skipping the key word AS is fine for table aliases but not for column aliases. So:

(applied_date AT TIME ZONE 'America/New_York')::date AS d

See:

SELECT Query merge / join two tables in PostgreSQL

You don't need date_trunc() here. to_char() can work with the original timestamp directly.

to_char(applied_date, 'MM-DD')

You do not need a subquery. Window functions can be computed OVER aggregate functions. sum(count(id)) OVER ... in my query. More explanation in this related answer on SO:

Get the distinct sum of a joined table column

You'd have to test which is faster. I don't expect much of a difference.
It's more efficient to invert your use of dollar-quotes and plain quotes.
It's sufficient to declare the type in the first row of a VALUES expression.


You could do something along the following lines:

select
    s1.grade,
    s1.firstname as s1_fname,
    s2.firstname as s2_fname,
    s3.firstname as s3_fname,
    s4.firstname as s4_fname
from #grades as s1
-- Self join the table for second student
left outer join grades as s2
    on s1.grade = s2.grade
    and s2.student_num = 2
-- Self join the table for third student
left outer join grades as s3
    on s1.grade = s3.grade
    and s3.student_num = 3
-- Self join the table for forth student
left outer join grades as s4
    on s1.grade = s4.grade
    and s4.student_num = 4
where s1.student_num = 1
order by s1.grade;

This joins the table to itself once for each set of columns (s1, s2, etc.). You could write some dynamic SQL to generate this based on the maximum student_num or just run select max(student_num) and make sure you have enough columns to catch all the values. (If you don't you'll need to add another join like the others).

Edit: This assumes that the grade and student_num columns combine to create a unique key. If you have duplicates (e.g. two students with grade 1, student_num 1), then you'll get duplicate rows in your results.


How do you cope with Database Server Inventory Management

I don't have direct experience with this task but know how folks get around this problem. :-)

You setup some monitoring program to look out for all SQL Server instances on the network and anytime something comes up that is NOT in your radar, you can catch them right away. There are lot of options and here are couple of them. I am pretty there might be others or many variants of these two.

http://www.straightpathsql.com/archives/2009/02/what-sql-instances-are-installed-on-my-network/

http://andrekamman.com/find-sql-servers-on-your-network-with-powershell

That said, I have to tell you that if someone brings an instance without notifying the DBA or production support team then this should be handled politically. Get your boss (in confidence) to talk to their boss and avoid future recurrence of the problem. GL.


What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

Because like any other optimization, it doesn't fit every workload.

Galera can get overwhelmed by a high rate of transactions, or when transactions update many rows. It can also make your applications experience delays on COMMIT as the cluster is synced up.

Galera doesn't update other nodes synchronously, either. It just transmits worksets synchronously. In this way, it's a bit like standard replication in semi-synchronous mode. Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT. And even a chance of getting a deadlock on SELECT, which seems counter-intuitive.

Galera is brilliant, but not a one-size-fits-all technology. There are still good reasons to use asynchronous replication.


Should you OPTIMIZE FOR default values in a dynamic search query?

I would dynamically construct the query such that each combination of search values results in a different plan.

If you are using a stored procedure to do this, it might look something like this.

Do this in the tempdb:

USE tempdb;

Create a demo table:

CREATE TABLE dbo.DatabaseList
(
    ServerName sysname NOT NULL,
    DatabaseName sysname NOT NULL,
    CreateDate datetime NOT NULL,
    CONSTRAINT PK_DatabaseList PRIMARY KEY CLUSTERED
    (
        ServerName ASC,
        DatabaseName ASC,
        CreateDate ASC
    )
) ON [PRIMARY];

Create a stored proc:

IF OBJECT_ID(N'dbo.SearchDatabaseList', N'P') IS NOT NULL
DROP PROCEDURE dbo.SearchDatabaseList;
GO
CREATE PROCEDURE dbo.SearchDatabaseList
(
    @ServerName sysname = NULL
    , @DatabaseName sysname = NULL
    , @CreateDate datetime = NULL
    , @Debug bit = 0
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @where nvarchar(max);
    DECLARE @connector nvarchar(max);
    DECLARE @statement nvarchar(max);
    DECLARE @vars nvarchar(max);

    SET @vars = N'
    @ServerName sysname
    , @DatabaseName sysname
    , @CreateDate datetime
    ';

    SET @statement = N'SELECT *
FROM dbo.DatabaseList dl
';

    SET @connector = '';
    IF @ServerName IS NOT NULL
        OR @DatabaseName IS NOT NULL
        OR @CreateDate IS NOT NULL
    BEGIN
        SET @where = N'WHERE ';
    END
    ELSE
    BEGIN
        SET @where = N'';
    END
    IF @ServerName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + N'ServerName LIKE @ServerName';
        SET @connector = N'
    AND '
    END
    IF @DatabaseName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + N'DatabaseName LIKE @DatabaseName';
        SET @connector = N'
    AND '
    END
    IF @CreateDate IS NOT NULL
    BEGIN
        SET @where = @where + @connector + N'CreateDate >= @CreateDate';
        SET @connector = N'
    AND '
    END
    SET @statement = @statement + @where;
    IF @Debug = 1
    BEGIN
        PRINT @statement
        PRINT N'';
    END
    ELSE
    BEGIN
        EXEC sys.sp_executesql @statement, @vars
            , @DatabaseName = @DatabaseName
            , @ServerName = @ServerName
            , @CreateDate = @CreateDate;
    END
END
GO

Demo searches using the stored proc:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer';

Running the above generates this query (running with @Debug = 0 will actually execute the query):

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName

more examples...

EXEC dbo.SearchDatabaseList @Debug = 1, @DatabaseName = N'SomeDatabase';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE DatabaseName LIKE @DatabaseName

Just by create date:

EXEC dbo.SearchDatabaseList @Debug = 1, @CreateDate = N'2017-06-19T00:00:00';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE CreateDate >= @CreateDate

Search server and database:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer'
    , @DatabaseName = N'SomeDatabase';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName
    AND DatabaseName LIKE @DatabaseName

Search server, database, and create-date:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer'
    , @DatabaseName = N'SomeDatabase'
    , @CreateDate = N'2017-06-19T00:00:00';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName
    AND DatabaseName LIKE @DatabaseName
    AND CreateDate >= @CreateDate

This results in clean target queries that only contain the relevant where clauses, thereby allowing the SQL Server Query Optimizer to pick a plan that is relevant to the query, and generate multiple plans that can also be cached.

This approach could easily be replicated in C#, or whatever client you're using to submit the query.


What is an industry standard method for verifying openquery connection is open prior to running queries?

For Linked Servers in SQL Server ...

Run a SELECT @@SERVERNAME or some such in a stored procedure that contains a BEGIN TRY/CATCH block.

The stored procedure limits the scope in case it fails and for re-use
The TRY/CATCH is to suppress any error to the calling query
The stored proc uses an output parameter


maybe your account is being locked out... are you using it someplace else? Did you check in your DC for events for this account? You could use a SQL Server login instead of a domain account.


Best way to copy MS SQL (2000) database files to an external drive

Per the specific requirements of getting copies of the .mdf and .ldf files, you can simply stop the SQL Server service, copy the files, then start the SQL Server service.

The way to do it without downtime would be to take full backups, but then the other party would be required to restore the backups instead of just copying files.  This would be the preferred method in almost all cases, but if the other party doesn't want to manually restore 90 databases or take the time to automate the restoration, the file copy method is understandable.


Cant initialize MySQL. "mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended"

The error says:

  The system tablespace must be writable

Please check the permission of the data directory of MySQL


How do I generate a tnsnames.ora file to connect to an Oracle Database?

You can easily create a tnsnames.ora [text] file.  It should be in $ORACLE_HOME/network/admin/ and should look something like this:

 ORATST=
 (description=
   (address_list=
     (address = (protocol = TCP)(host = fu.bar)(port = 1521))
   )
 (connect_data =
   (service_name=oratst)
 )
)


But after 2-3 days of continuous work, the consumed memory increases ...

Databases don't generally consume memory by themselves.  Even Oracle.  :-)

They do so on behalf of client Applications.

  ... the number of sessions increases significantly to 450+.

Sounds like those client Applications are opening connections and not closing them.

Have those Application(s) changed recently?

  As far as I know, the database session should be thrown out by itself

[By default] The database has no knowledge of which sessions are eligible to be killed off and so will leave them connected for as long as they "want" to be.  Whilst it is possible to configure Oracle to kill off idle sessions after a period of time, this would only be a "Band-Aid"; you have a underlying [Application] problem that needs to be resolved.

  I do not know how the database is configured. I did not install.

Then ask yourself (very honestly): are you the Right Person to be dealing with this problem?  Do you have enough information to even look at it?

I get the impression (apologies if I'm completely wrong) that you are an "Accidental DBA".  If that's the case, then I would suggest you need help from a real one, preferably the one that installed this database in the first place.

If you can, go back and ask them for assistance.

If you can't, then escalate this issue to your Management.  Switching your database "off and on again" every other day is not a viable, long-term solution to your problem - and it's not just your problem, either; it's a problem for your entire organisation and not one that you can "solve" in isolation.  You need input from the developers of those client Applications.


How to get Kerberos working against a named SQL Server instance in Power BI Report Server?

After hours of troubleshooting, I've got this working. I had to allow the PBI service account to pass credentials to the destination SQL Server(s) on an EXPLICIT port, even when connecting to a default instance on 1433. If anyone has any input, feel free to comment, but here's what worked for me:

Open Active Directory Users and Computers
Right-click the service account running Power BI Report Service, and click "Properties"
Click the "Delegation" tab, then "Add", then "Users or Computers"
Enter the service account of the DESTINATION SQL Server instance and click "OK"
Click the MSSQLSVC Service that shows a port, whether it's the default 1433 or another port. If you have multiple named instances, add the entry for each port number.
Click OK, then OK again.
Restart the Power BI Report Server MACHINE, not just the service.
Open a Power BI (.pbix) file that has uses DirectQuery against a different SQL Server instance.
Credentials should now be passing from you (the client), to Power BI Report Server, and then on to any destination server(s).


Corrective

If instead of generating one random value we generate S random values
where S is the sample size, then order all of the random generated
values, it will be sampling with replacement. (I don't know if I am right.)

You are wrong in two points.

Your original query does not generate "one random value". random() is a volatile function that is called for every row. Then all rows are sorted by the result, that's why this is hugely inefficient for big tables.
Accordingly, the approach "if we generate S random values ..." is going nowhere. You have to "granulate" random rows to matching "row numbers" - either existing IDs or surrogate numbers. That's what I am going to demonstrate.

I know you said:

At this point I don't mind about the performance of the query.

So @ypercube's answer is correct as usual. But my heart is bleeding. There are much faster ways.
Assuming the worst case that you don't know anything about the table.
Attach a sequential number to each row in arbitrary order, then you can select random rows reliably, multiple times if you want. The table is scanned once, which is still expensive for huge tables and small samples, but much better than scanning n times:
WITH t AS (SELECT *, row_number() OVER () AS rn FROM tbl)
SELECT * FROM (
    SELECT trunc(random() * (SELECT max(rn) FROM t))::int + 1 AS rn
    FROM   generate_series(1, 10) g
    ) r
JOIN   t USING (rn);

Each row has the same chance to be selected any number of times.
If you have an ID column with few or no gaps there are much faster options for big tables:

Best way to select random rows PostgreSQL


Maximum number of databases for single instance of PostgreSQL 9

I haven't tried it myself, but there are others around who have.
Here you can see that even 10,000 databases run without problem on a single instance.  You can even find some pratical aspects on ServerFault as well.

Since your databases are quite small, you will not run into any sort of file number limitation of the host OS.  The only problem I can think of is that when all these databases will be accessed concurrently, handling all those connections will be tricky.

And, as a last note: you are very welcome on this site.  We hope you will remain with us for a long time.


Throttling SQL Server database cpu usage

Edited for clarity and consistency:

By the database itself? No. However, you can through the connection (what you should be trying to manage anyways) called the Resource Governor.

CPU
MEMORY
Physical IO, depending on the version.

There are a number of Concepts which the Docs explain in greater detail:

Resource Pool

a group of default, internal (cannot be changed), and custom made groups that have a set value on the amount of CPU, Memory, and Physical IO that a session may consume.

Workload Group

a class of session requests that have  similar characteristics. These are mapped to  The appropriate resource pool and are set at the time of connection settings. Meaning the default database connect is used not the first USE statement or any statement afterwards.

The governor must be activated Or configured.  A governor specific function is run against the connection's  default settings  and map to a workload group.

.  This workload is attached to a specific resource pool where the limitations are enforced on all subsequent sessions that occur out of the connection.

This function is the most important part and you should keep it simple.

For more reading, visit the Docs:

Main Page -Resource Governor

Resource governor Resource pool

Research governor Workload group

Resource governor classifier function  | Docs Microsoft


Correlated subquery and join: still the same exectution plan?

The two queries are logically identical and do produce the same plan. The simplification phase of the Query Optimizer handles this.

They're identical because of the constraints that are on the tables - foreign keys, uniqueness, nullability...


Are there any schema design changes that can take a design from 4NF into a different 4NF design?

I dont think so except for the fact that there is a  5NF, which describes a design where your joins are only on the candidate keys.

Many "4NF" designs meet this criteria, but not all, and it is definitely something you can change a 4NF "into" to be be more normalized.


What are the performance implications of creating an index in MySQL?

Since the creation of an index is DDL, it must always incur a lock on all moving parts of the table. How does the locking work ?

MyISAM

For the table mydb.mytable whose datadir is /var/lib/mysql, there are file handles on the following files:

/var/lib/mysql/mydb/mytable.frm (Table Definition)
/var/lib/mysql/mydb/mytable.MYD (MyISAM Data)
/var/lib/mysql/mydb/mytable.MYI (MyISAM Indexes)

By means of a full table lock, all INSERTs, UPDATEs, and DELETEs are held in abeyance until the completion of the index creation.

InnoDB

For the table mydb.mytable whose datadir is /var/lib/mysql, there are file handles on the following files:

innodb_file_per_table disabled

/var/lib/mysql/mydb/mytable.frm (Table Definition)
/var/lib/mysql/ibdata1 (Data and Index Pages for mytable)

innodb_file_per_table enabled

/var/lib/mysql/mydb/mytable.frm (Table Definition)
/var/lib/mysql/mydb/mytable.ibd (InnoDB Data and Index Pages)

By means of a full table lock as well as the Clustered Index, all INSERTs, UPDATEs, and DELETEs are held in abeyance until the completion of the index creation. There are also too many moving parts to the lifecycle

Can I setup a temp table for index creation?

One could attempt to use an external table as follows:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ADD INDEX (some_column);
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;
DROP TABLE mydb.mytable_old;

This does not work under two conditions

CONDITION #1 : If there are any INSERTs, UPDATEs, or DELETEs during the first 3 steps, the RENAME steps will have the incoming data disappear.
CONDITION #2 : If the table has foreign key constraints, CREATE TABLE mydb.mytable_new LIKE mydb.mytable; will not create those constraints in the temp table because

a table with foreign key constraints can only have one parent at a time
two tables cannot have identical constraints with the same parent

While there is a tool that can bypass these limitations and allow INSERTs, UPDATEs, or DELETEs (called pt-online-schema-change), the simplest approach for a creating an index is to just bite the bullet and let mysqld lock the table.


I believe Percona already has that option built-in.
Inside /etc/init.d/mysql, somewhere I saw something like
# numactl --interleave=all

When I discovered this, every time I installed Oracle's MySQL, I added that command to /etc/init.d/mysql.
If you want to be sure, just run this:
grep -n "interleave" < /etc/init.d/mysql

and you will see which line that appears.
I have discussed this before

Jul 22, 2013 : MySQL 5.5 High CPU Usage
Mar 06, 2013 : mysqld_safe version different than mysqld?
Dec 30, 2012 : Mysql slowly uses memory until it starts to use swap

Based on the link you posted, you should be able to activate those features starting from Percona Server 5.5.28. My posts mentioning numactl were evidently from earlier versions of Percona Server.
In light of all this, the same thing applies to flush-caches.
UPDATE 2013-08-28 09:54 EDT
If you have Percona Server 5.5.30 and there are no options, go install Percona Server 5.5.28 or 5.5.29 on a Dev Server and test it out there. Check /etc/init.d/mysql as follows:
# grep -n "numa" /etc/init.d/mysql

If you find it, then Percona must have missed putting that option on 5.5.30 (which would be very surprising). As the quick fix, take the mysqld_safe line from 5.5.28/29 and hardcode it into the mysqld_safe invocation in 5.5.30.
At the very least, take the line you said works:
mysqld_safe --datadir=/mnt/data/mysql --pid-file=/mnt/data/mysql/db3.improve.pid --numa-interleave --flush-caches

and imbed that into /etc/init.d/mysql for 5.5.30 where mysqld_safe is called.
I hope that human error (Percona missing this one (highly unlikely)) is the reason from this problem and not something much deeper.


So the IIS on this server is intended to be externally accessible from your internal data network but the SQL Server instance on this same server is not meant to allow for external connections except for only through the web interfaces of your applications, correct?

If so, then is not adding an explicit rule in the Windows Firewall on this server to allow only the private IP address ranges to connect via local network on SQL Server ports not acceptable? I assume you trust your internal IP addresses on this port and don't suspect those as the authentication attempts with malicious intent?

Allow the [localhost IP] IIS apps to authenticate to SQL (may need to check site bindings, etc. and/or setup internal DNS pointers too).

On your external side, you should only be allowing the port 80 or 443 to port forward/NAT to the IIS server on those ports only and not all ports too it I would think -- stop at corporate firewall too I guess and only allow the web app ports to go to that server from the external interface with your firewall appliance proxies, etc.


Can I have full backups on multiple disks?

Since you are not familiar with the T-SQL BACKUP DATABASE command, I thought I'd add some details about that.

You probably want to run something along the lines of the following statement through the Windows Task Scheduler service, since you don't have access to SQL Server Agent (I see from your other questions, you use SQL Server Express).

BACKUP DATABASE [xyz]
TO DISK = 'C:\somepath\mybackupfile.bak'
MIRROR TO DISK = 'D:\somepath\myotherbackupfile.bak'
WITH FORMAT
    , INIT
    , SKIP
    , STATS = 1;

You might want to strongly consider having the MIRROR TO clause point to some location that is not on your local machine, since if you lose your local machine completely, you may not be able to access either backup file.  Specifying a MIRROR TO clause requires you to specify the FORMAT keyword in the WITH clause the first time you run that backup statement.

You can use the name of a Windows Share, such as \\SomeServer\SQLBackups\MyBackupFile.bak as long as the security permissions on the share allow the Windows Scheduler service access.

The WITH FORMAT, INIT part tells SQL Server to overwrite any existing backups that may be in the backup files. You could change this to WITH NOINIT once you've completed the first mirrored backup if you want multiple backups (i.e. backups from different points in time) saved in those files.  NOSKIP tells SQL Server to not check for backup expiration, among other things.   STATS = 1 will display the output in 1 percent increments.  You can change this number to anything you like.  I use 1 for very large databases since it gives some indication of progress.

To have this run through the Windows Task Scheduler service, you'll need to save that command (once you have tested it in SQL Server Management Studio) to a file on your disk; let's call it C:\somefolder\BackupMyDB.sql.  You'll then want to add the following command to the Windows Scheduler:

<path to sqlcmd>sqlcmd -S localhost -E -i C:\somefolder\BackupMyDB.sql

You'll want to have that task "run as" you.

Once you've done all that, you want to very seriously consider attempting to restore the backup onto another machine so you understand how to do that.  Having backups is only one part of a disaster recovery plan; the arguably more important part is testing that plan.

The restore process would use a command something like:

RESTORE DATABASE [xyz]
FROM DISK = 'D:\somepath\myotherbackupfile.bak'
WITH RECOVERY
    , STATS = 1;

Be warned, running RESTORE DATABASE on the machine where your current database resides can overwrite the current database without confirmation or warning, so please ensure you carefully evaluate the name of the database, [xyz] in my example, and the other options you use.  (This actually won't overwrite the existing database unless you add the REPLACE keyword to the WITH clause - I just want to emphasize being careful.)


There is no need to take a FULL backup before and after running DBCC CHECKDB. It is recommended that your FULL backup go before the DBCC CHECKDB so that you know exactly which FULL backups don't contain database corruption if DBCC CHECKDB ever fails due to corruption. The FULL backup before the last successful DBCC CHECKDB is one without corruption.

To recover from corruption, you can use the FULL+DIFF+LOG chain or FULL+LOG chain. Pick the FULL backup from before the last successful DBCC CHECKDB. If there was a DIFF before the last successful DBCC CHECKDB, then you can use that too. But a DIFF after the last successful DBCC CHECKDB could have database corruption, depends when the DIFF occurred and when the corruption occurred.

LOG backups do not contain data pages, so they do not have the corrupted data pages in them. Thus, you can recover with no data loss if you have the entire LOG chain and the tail of the log after the non-corrupted FULL or non-corrupted FULL+non-corrupted DIFF.


How to connect to Oracle 11g using "Navicat for Oracle"?

You need to change port 1158 to 1521 in your connection properties.

Oracle's listener for database connections runs on port 1521 by default.

Port 1158 is the web listener for Database Control, a webgui for managing the database.

In addition, the servicename should be something like "database.domain.com", or you can try using SID and specifying ORCL.


Since you're using SQL 2012, you can leverage SQL Server Auditing, which was introduced with SQL 2008.  SQL Server Auditing can be used to track a multitude of actions within SQL Server, include database object permission GRANT/REVOKE/DENY actions.  You'll be able to see the security principal that executed the statement, when the statement was run, and what that exact statement was.  To create your audit, you will need to perform the following:

Create an Audit

The Audit defines the over-arching logging for all the audits.  To create an Audit, you can use the object explorer to browse under the instance to Security->Audits.  Right click on Audits and select "New Audit".  Fill out the next dialog (the important bit here is where you store the audit file, the rest you can use defaults on).  After you create it, you will need to right click on the Audit in the object browser and enable it.

Create an Audit Specification

The Specification defines what action you want to track, using any combination of actions/groups to define the Specification.  There are many different actions, both on the server and database level, that can be tracked.  To create a Specification for database permission changes, browse under the database you wish to track in to Security->Database Audit Specifications.  Right click and fill out the dialog.  Select the Audit you created above to write to.  Under Audit Action Type, select SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP from the drop down.  This action allows you to track permissions changes on schema objects.  Once you have this, click 'Ok' to create the specification.  Then, in the object browser, enable it.

Viewing the audit

Once this is all complete, the audit is running and will capture and GRANT/REVOKE/DENY actions in that database.  To view the audit logs, browse to the audit under the instance in Security->Audits.  Right click on the audit and select 'View Audit Logs' and you will be able to see all the audit actions that have occurred in your database since you started the auditing.

As an alternative, your auditing can be enabled using T-SQL with the following commands:

--Create Audit
USE [master];

CREATE SERVER AUDIT [PermissionTracking]
TO FILE
(   FILEPATH = N'C:\DBData'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);

--Enable Audit
ALTER SERVER AUDIT [PermissionTracking] WITH (STATE=ON);

--Create Database Audit Specification
USE [foo];

CREATE DATABASE AUDIT SPECIFICATION [FooDBPermissions]
FOR SERVER AUDIT [PermissionTracking]
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE=ON);


mariadb server optimization for a db of 6GB size

This is really bad:

query_cache_size = 4G

It should not be more than about 50M

How much RAM do you have; this should not be more than about 1%:

join_buffer_size = 2G

This is not bad, but most people do just fine with 8M; let's compromise at 16M...

innodb_log_buffer_size         = 512M

The main setting that you should tune is innodb_buffer_pool_size; it should be up to about 70% of RAM (so, about 40G), but does not need to be much bigger than the 6GB that you have in data+index (so 8G is fine for now).

If this VM is dedicated to MySQL, you are paying for more RAM than you need.

If you are chewing up the CPU(s), then look to the queries, indexes, and schema.  Let's see the slowest query and SHOW CREATE TABLE.


Select column names whose entries are not null

Let's pick a sample table on my machine:

mysql> show create table weisci_jaws_staging2.users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL DEFAULT '',
  `passwd` varchar(32) NOT NULL DEFAULT '',
  `user_type` tinyint(4) DEFAULT '2',
  `recovery_key` varchar(48) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `timezone` varchar(5) DEFAULT NULL,
  `language` varchar(5) DEFAULT NULL,
  `theme` varchar(24) DEFAULT NULL,
  `editor` varchar(24) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  `updatetime` datetime DEFAULT NULL,
  `change_passwd` tinyint(1) NOT NULL DEFAULT '1',
  `never_expire` tinyint(1) NOT NULL DEFAULT '1',
  `bad_passwd_count` smallint(6) DEFAULT '0',
  `last_access` bigint(20) DEFAULT '0',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_username_idx` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=160 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> select count(1) from weisci_jaws_staging2.users;
+----------+
| count(1) |
+----------+
|      117 |
+----------+
1 row in set (0.00 sec)

mysql>

With this table, here are two questions:

Which columns are nullable ?
Which columns are not nullable ?

This query will find out for you:

select is_nullable,GROUP_CONCAT(column_name) column_list
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and   table_name = 'users'
group by is_nullable;

Check out the result of that query for the table:

mysql> select is_nullable,GROUP_CONCAT(column_name) column_list
    -> from information_schema.columns
    -> where table_schema = 'weisci_jaws_staging2'
    -> and   table_name = 'users'
    -> group by is_nullable;
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| is_nullable | column_list                                                                                |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| NO          | id,never_expire,change_passwd,enabled,username,passwd                                                                              |
| YES         | recovery_key,last_access,bad_passwd_count,updatetime,createtime,last_login,editor,user_type,language,timezone,url,email,name,theme |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql>

OK, we have two lists. What do we learn from this?

If you get two lists, no need to inspect the actual table because the table has non-NULL columns by definition.
If you get one list, then

If you get only is_nullable='NO', no need to inspect the actual table because the table has non-NULL columns by definition.
If you get only is_nullable='YES', the actual table would be a little brittle. There would be no PRIMARY KEY, you poor, tormented soul !!! NOW, you have to resort to reading every row from the actual table.

If you are looking for just the non-null columns, then this would be your desired query:

select GROUP_CONCAT(column_name) nonnull_columns
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and   table_name = 'users'
and   is_nullable = 'NO';

Here is the output of this query:

mysql> select GROUP_CONCAT(column_name) nonnull_columns
    -> from information_schema.columns
    -> where table_schema = 'weisci_jaws_staging2'
    -> and   table_name = 'users'
    -> and   is_nullable = 'NO';
+-------------------------------------------------------+
| nonnull_columns                                       |
+-------------------------------------------------------+
| id,username,passwd,change_passwd,never_expire,enabled |
+-------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Removing the GROUP_CONCAT, you get this:

mysql> select column_name nonnull_column
    -> from information_schema.columns
    -> where table_schema = 'weisci_jaws_staging2'
    -> and   table_name = 'users'
    -> and   is_nullable = 'NO';
+----------------+
| nonnull_column |
+----------------+
| id             |
| username       |
| passwd         |
| change_passwd  |
| never_expire   |
| enabled        |
+----------------+
6 rows in set (0.01 sec)

mysql>

Give it a Try !!!

NOTE : Please notice that I do not need to read the actual table's data content. That's far more efficient than reading the entire table.

UPDATE 2012-11-15 13:40 EDT

The code from @sensware's answer gives NULL columns. The original question asked for non-NULL columns. I augmented the code to test just my table:

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
SELECT CONCAT(
         'SELECT * FROM ('
       ,  GROUP_CONCAT(
            CONCAT(
              'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
            , 'IF('
            ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
            ,   'NULL,'
            ,    QUOTE(COLUMN_NAME)
            , ') AS `column` '
            , 'FROM `',
            REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
            REPLACE(TABLE_NAME, '`', '``'), '`'
            )
            SEPARATOR ' UNION ALL '
         )
       , ') t WHERE `column` IS NOT NULL'
       )
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'weisci_jaws_staging2'
AND    TABLE_NAME = 'users';
SELECT @sql\G
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is the output:

mysql> SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(
    ->          'SELECT * FROM ('
    ->        ,  GROUP_CONCAT(
    ->             CONCAT(
    ->               'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
    ->             , 'IF('
    ->             ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
    ->             ,   'NULL,'
    ->             ,    QUOTE(COLUMN_NAME)
    ->             , ') AS `column` '
    ->             , 'FROM `',
    ->             REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
    ->             REPLACE(TABLE_NAME, '`', '``'), '`'
    ->             )
    ->             SEPARATOR ' UNION ALL '
    ->          )
    ->        , ') t WHERE `column` IS NOT NULL'
    ->        )
    -> INTO   @sql
    -> FROM   INFORMATION_SCHEMA.COLUMNS
    -> WHERE  TABLE_SCHEMA = 'weisci_jaws_staging2'
    -> AND    TABLE_NAME = 'users';
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT * FROM (SELECT 'users' AS `table`,IF(COUNT(`id`),NULL,'id') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`username`),NULL,'username') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`passwd`),NULL,'passwd') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`user_type`),NULL,'user_type') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`recovery_key`),NULL,'recovery_key') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`name`),NULL,'name') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`email`),NULL,'email') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`url`),NULL,'url') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`timezone`),NULL,'timezone') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`language`),NULL,'language') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`theme`),NULL,'theme') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`editor`),NULL,'editor') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_login`),NULL,'last_login') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`createtime`),NULL,'createtime') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`updatetime`),NULL,'updatetime') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`change_passwd`),NULL,'change_passwd') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`never_expire`),NULL,'never_expire') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`bad_passwd_count`),NULL,'bad_passwd_count') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_access`),NULL,'last_access') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`enabled`),NULL,'enabled') AS `column` FROM `weisci_jaws_staging2`.`users`) t WHERE `column` IS NOT NULL
1 row in set (0.00 sec)

mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> EXECUTE stmt;
+-------+--------+
| table | column |
+-------+--------+
| users | theme  |
+-------+--------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

This give NULL columns. The original question asked for non-NULL columns. I'll change the code to generated non-NULL. I'll do that by flipping the order of the IF..COUNT:

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
SELECT CONCAT(
         'SELECT * FROM ('
       ,  GROUP_CONCAT(
            CONCAT(
              'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
            , 'IF('
            ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
            ,    QUOTE(COLUMN_NAME)
            ,   ',NULL'
            , ') AS `column` '
            , 'FROM `',
            REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
            REPLACE(TABLE_NAME, '`', '``'), '`'
            )
            SEPARATOR ' UNION ALL '
         )
       , ') t WHERE `column` IS NOT NULL'
       )
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'weisci_jaws_staging2'
AND    TABLE_NAME = 'users';
SELECT @sql\G
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Let's run it now...

mysql> SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(
    ->          'SELECT * FROM ('
    ->        ,  GROUP_CONCAT(
    ->             CONCAT(
    ->               'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
    ->             , 'IF('
    ->             ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
    ->             ,    QUOTE(COLUMN_NAME)
    ->             ,   ',NULL'
    ->             , ') AS `column` '
    ->             , 'FROM `',
    ->             REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
    ->             REPLACE(TABLE_NAME, '`', '``'), '`'
    ->             )
    ->             SEPARATOR ' UNION ALL '
    ->          )
    ->        , ') t WHERE `column` IS NOT NULL'
    ->        )
    -> INTO   @sql
    -> FROM   INFORMATION_SCHEMA.COLUMNS
    -> WHERE  TABLE_SCHEMA = 'weisci_jaws_staging2'
    -> AND    TABLE_NAME = 'users';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT * FROM (SELECT 'users' AS `table`,IF(COUNT(`id`),'id',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`username`),'username',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`passwd`),'passwd',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`user_type`),'user_type',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`recovery_key`),'recovery_key',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`name`),'name',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`email`),'email',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`url`),'url',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`timezone`),'timezone',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`language`),'language',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`theme`),'theme',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`editor`),'editor',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_login`),'last_login',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`createtime`),'createtime',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`updatetime`),'updatetime',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`change_passwd`),'change_passwd',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`never_expire`),'never_expire',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`bad_passwd_count`),'bad_passwd_count',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_access`),'last_access',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`enabled`),'enabled',NULL) AS `column` FROM `weisci_jaws_staging2`.`users`) t WHERE `column` IS NOT NULL
1 row in set (0.00 sec)

mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
+-------+------------------+
| table | column           |
+-------+------------------+
| users | id               |
| users | username         |
| users | passwd           |
| users | user_type        |
| users | recovery_key     |
| users | name             |
| users | email            |
| users | url              |
| users | timezone         |
| users | language         |
| users | editor           |
| users | last_login       |
| users | createtime       |
| users | updatetime       |
| users | change_passwd    |
| users | never_expire     |
| users | bad_passwd_count |
| users | last_access      |
| users | enabled          |
+-------+------------------+
19 rows in set (0.01 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

OK it works now. There is a problem still present. The query requires reading the entire table. My test table only has 117 rows and 20 columns. What about bigger tables with millions of rows or dozens of columns? I am not going to speculate because I know that the code would be orders of magnitude worse.

That's why I recommend my answer

select GROUP_CONCAT(column_name) nonnull_columns
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and   table_name = 'users'
and   is_nullable = 'NO';

or

select column_name nonnull_column
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and   table_name = 'users'
and   is_nullable = 'NO';

because the actual data content does not have to be inspected.

The augmented code I made should only be used on a table where all column allow NULL values, which is extremely rare.


MySQL : How do I partition based on Month of date

From here, below is an example that range partitions by YEAR - change as appropriate.

Here is the part of the manual that explains how to alter tables and add partitions. You should be able to take it from here.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE);


You're confusing some technologies here, unless I'm missing something or misinterpreted.  Log shipping is FAR different than Availability Groups.

Your drives on the secondary server do not have to be the exact same size, they simply need to have enough room for the DBs.  That being said, it's usually a good idea to size things the same across environments that are being replicated.  What if your primary DB grows and you have enough room on the primary but not enough on the secondary?  That will take your replication down and cause things to break.


A single transaction log file has both a physical size (that you see on disk), and it's also broken down within the physical file into logical sections called virtual log files (VLFs).

Both auto-growth and auto-shrink operate on the physical transaction log file.

Transaction log truncation (also called "log clearing") operates on the logical sections of the transaction log (VLFs), and does not affect the physical file size. This part is frequently the subject of confusion.

A log file must always grow to accomodate a large transaction; turning off auto-shrink will leave the log file with its maximum needed size, instead of physically decreasing its size.

If you don't have large transactions, it will be safe to disable auto-shrink; the log files will not grow without bound like would happen if the database was in FULL or BULK_LOGGED and you weren't taking transaction log backups.

This behaviour is the same for SQL Server 2005+.


Sorry for the confusion. I missed an option in the article that decides how DELETEs are treated.

The option is called "delete_tracking" and it's set to 0. It is documented here: http://msdn.microsoft.com/it-it/library/ms174329.aspx


Tables not displaying after creating database by script. Do I need to insert?

In Object Explorer, drill down to your database, and then to Tables.

Right-click Tables and click Refresh.

If your newly created tables do not show up, perhaps you created the tables under a different database by mistake. This might happen if you were missing a USE YourDatabase in which case they would likely end up in the default database for your login (often master)

Also, the following will show all of the tables in a database. Run it for your database. If they do not show up, SQL Server did not create the tables (at all/in the desired location).

use [YOUR_DATABASE]
GO
select * from sys.tables
GO


Query works manually, not from stored procedure - Safe update error

Right From the MySQL 8.0 Docs

It is possible for UPDATE and DELETE statements to produce an error in safe-updates mode even with a key specified in the WHERE clause, if the optimizer decides not to use the index on the key column:

Range access on the index cannot be used if memory usage exceeds that permitted by the range_optimizer_max_mem_size system variable. The optimizer then falls back to a table scan. See Limiting Memory Use for Range Optimization.

If key comparisons require type conversion, the index may not be used (see Section 8.3.1, “How MySQL Uses Indexes”). Suppose that an indexed string column c1 is compared to a numeric value using WHERE c1 = 2222. For such comparisons, the string value is converted to a number and the operands are compared numerically (see Section 12.3, “Type Conversion in Expression Evaluation”), preventing use of the index. If safe-updates mode is enabled, an error occurs.

As of MySQL 8.0.13, safe-updates mode also includes these behaviors:

EXPLAIN with UPDATE and DELETE statements does not produce safe-updates errors. This enables use of EXPLAIN plus SHOW WARNINGS to see why an index is not used, which can be helpful in cases such as when a range_optimizer_max_mem_size violation or type conversion occurs and the optimizer does not use an index even though a key column was specified in the WHERE clause.

When a safe-updates error occurs, the error message includes the first diagnostic that was produced, to provide information about the reason for failure. For example, the message may indicate that the range_optimizer_max_mem_size value was exceeded or type conversion occurred, either of which can preclude use of an index.

For multiple-table deletes and updates, an error is produced with safe updates enabled only if any target table uses a table scan.

Based on the these things, you need to run this
select @@global.sql_safe_updates,@@session.sql_safe_updates;

If your output shows 1 for @@global.sql_safe_updates,

mysqld process was started up with sql_safe_updates or i-am-a-dummy.
Otherwise, it was not started that way.

If your output shows 0 for @@session.sql_safe_updates, that means your client is setting it

The client will automatically have @@session.sql_safe_updates set to whatever is in @@global.sql_safe_updates
If the client has @@session.sql_safe_updates set to something different from @@global.sql_safe_updates, then the client is personally setting it.

The fact that you can execute it by hand shows that the client program you are using to connect allows you to run the query. You may need to review the setting in MySQL Workbench to see if --safe-updates is being imposed on stored procedure. Other than these things, please check out suggestions from StackOverflow on how to tweek safe-updates off and on in the Workbench.


import .db files/tables into sqlite database

For each database file (other.db) that must export tables to main.db:

sqlite3 other.db .dump | sqlite3 main.db

Or if you want to check the generated commands:

sqlite3 other.db .dump > log
sqlite3 main.db < log

Another method: (not so good as it does not import the primary key and column types)

First open the main database:

sqlite3 main.db

Then for each database containing tables to be imported:

ATTACH DATABASE 'other.db' AS other;
CREATE TABLE tablename AS SELECT * FROM other.tablename;
DETACH DATABASE other;


SQL Server returns "Arithmetic overflow error converting expression to data type int."

For values larger than the INT max (2,147,483,647), you'll want to use COUNT_BIG(*).

SELECT COUNT_BIG(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

If it's happening in the SUM, you need to convert Amount to a BIGINT.

SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;


Any potential issues/ DoS risks with frequent connections/ queries to a SQL Server db?

Depending on how the server handles this, it might cause a potential problem. One technology that SQL Server implements to handle this kind of situation is "Connection pooling". With Connection Pooling, when the process closes its connection, SQL Server does not immediately remove the connection object from its memory. This way, if the process requests a new connection, SQL server can reuse the connection object instead of instantiating a new one. This process can be watched by monitoring the performance counters with perfmon. Here is a link to a MSDN blog for more information: http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/10/08/connection-pooling-for-the-sql-server-dba.aspx


I wouldn't recommend messing with the system stored procs.  You could wrap the sp_setapprole call in a stored proc and have your app call that proc instead.  The wrapper proc would have the password hard-coded and use EXECUTE AS to impersonate a user who can use sp_setapprole.  Then you could limit access to the wrapper proc.  Another advantage to this approach is that the password doesn't go out across the network, nor is it included in your .exe file.

EDIT:

My mistake, you can't wrap the call directly.  But you can wrap a modified version of the T-SQL that it runs.  Looking at the version in my 2008r2 server, I see that the proc calls SETUSER intead of EXECUTE AS.  Sad to see deprecated code in system stored procs.  For yours, you may want to use EXECUTE AS instead.

You could also grant rights on whatever your app needs to function to a SQL user who can't login, give certain users rights to impersonate that user, and have your app EXECUTE AS the login-less user.  This route still requires cookie handling to set the context back.  In fact, it's very similar to a customized version of sp_setapprole, since both require some sort of impersonation.

Or, you could also create a database role and grant the role to authorized users.  This seems like it might be more like what you want.  Application roles are designed for app-defined rights.  It seems like you really want user-defined rights.


Easily show rows that are different among multiple tables

The wanted query is not trivial at all but it would be a bit easier to write if there was one table and not one per year. So let's create a VIEW:

CREATE VIEW states AS
    SELECT stateID, propertyID, owner, area, timestampGot, 2010 AS year
    FROM states_2010
    UNION ALL
    ...
    SELECT stateID, propertyID, owner, area, timestampGot, 2017
    FROM states_2017
    UNION ALL
    SELECT stateID, propertyID, owner, area, timestampGot, 2018
    FROM states_2018
    ... ;

Then, I assume that you want to get data from the earliest and the latest date per product. So we can use one of the (many) ways to get a greatest-n-per-group result (applied twice, one for the earliest date and another for the latest):

select
    p.propertyid,
    s.owner,
    s.area,
    s.timestampgot,
    s.stateid
from properties as p
  left join states as s                    -- earliest timestamp
  on  s.propertyid = p.propertyid          -- for this property
  and (s.year, s.timestampgot) =
      ( select si.year, si.timestampgot
        from states as si
        where si.propertyid = p.propertyid
        order by si.year, si.timestampgot
        limit 1
      )
union all
select
    p.propertyid,
    s.owner,
    s.area,
    s.timestampgot,
    s.stateid
from properties as p
  left join states as s                    -- latest timestamp
  on  s.propertyid = p.propertyid
  and (s.year, s.timestampgot) =
      ( select si.year, si.timestampgot
        from states as si
        where si.propertyid = p.propertyid
        order by si.year desc, si.timestampgot desc
        limit 1
      ) ;


Can I use a rule to insert aggregate data into a new table?

Trigger Transition Tables

First you should be using TRIGGER and not rules when you can. Second, what you want is possible with version 10's, REFERENCING

  The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time. This option is only allowed for an AFTER trigger that is not a constraint trigger; also, if the trigger is an UPDATE trigger, it must not specify a column_name list. OLD TABLE may only be specified once, and only for a trigger that can fire on UPDATE or DELETE; it creates a transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT; it creates a transition relation containing the after-images of all rows updated or inserted by the statement.

CREATE TRIGGER AFTER INSERT
  ON tblfoo
  REFERENCING NEW TABLE as new
  FOR EACH STATEMENT
  EXECUTE PROCEDURE whatever();

Now whatever has access to new. You can select the avg() and put it wherever you want.

See also,

What is a “transition table" in Postgres?


Mongodb rs.add() new member in STARTUP state

I figured out what is wrong with my replica set clusters. You need to provide an argument to rs.initiate(), which is the configuration document. If you don't specify the document, it will read the configuration from the /etc/mongod.conf which will has no configuration whatsoever for the replica set. The configuration for rs.initiate() looks something like this:

rs.initiate(
   {
      _id: "myReplSet",
      version: 1,
      members: [
         { _id: 0, host : "mongodb0.example.net:27017" },
         { _id: 1, host : "mongodb1.example.net:27017" },
         { _id: 2, host : "mongodb2.example.net:27017" }
      ]
   }
)

But if you do not know before hand how many replica sets you want to add to the cluster it is ok, just specify the first one and the subsequent ones will be added without any problems:

rs.initiate(
   {
      _id: "myReplSet",
      version: 1,
      members: [
         { _id: 0, host : "mongodb0.example.net:27017" }
      ]
   }
)


Why does Casting this string as a decimal fail?

This has been a long-standing issue with MySQL with people reporting this very issue as a bug since 2011. I have found that the problem is almost completely dependent on the collation being used within the REGEXP_SUBSTR() function.
For instance, if you cast the result of REGEXP_SUBSTR() as a CHAR(100), your decimals remain intact:
mysql> SELECT CAST(CAST(REGEXP_SUBSTR('Cost (-$14.18)', '[0-9.]+') AS CHAR(100)) AS DECIMAL(8,2)) AS result;

result
-----
14.18

The result returned by REGEXP_SUBSTR() used a UTF-16 character set before MySQL 8.0.17. Versions after this supposedly use the same character set as configured by the client (See bug #94203 reported by Rick James), but this does not appear accurate. My SQL client is configured to use UTF-8 everywhere. Running your initial query in my client produces the exact same results as you shared in the question.
However, if I CONVERT( ... USING 'UTF8'):
SELECT CAST(CONVERT(REGEXP_SUBSTR('Cost (-$14.18)', '[0-9.]+') USING 'UTF8') AS DECIMAL(8,2)) AS result;

result
-----
14.18

Surprise, surprise. A correct number.
Generally in this situation I do the very same thing that you did for cost_3; I multiply the returned value by 1, then cast it to the desired type. You can save a step by casting as FLOAT, but this sometimes has precision implications.
It is not a great answer, but it is one that can be used across multiple versions of MySQL.


MYSQL optimization questions about values on server

On settings:
innodb_force_recovery - don't set this. Its dangerous to have this as default.
query_cache_limit=20M
query_cache_type=1
Disable these. They are a concurrency problem.
flush flush_time=60 - I've never seen these set. Sounds like a workaround for MyISAM being default.
default-storage-engine=MyISAM don't set MyISAM being a default. It has horrible concurrency problems. Change existing tables to innodb.
On status variables warnings:

created tmp disk tables    345.7K

handler read rnd 165M

handler read rnd next 45G

select full join 450k
All these indicate poorly indexed queries. Enable your slow query log and start asking questions on how to index your tables better.

innodb buffer pool reads 140k

Your innodb_buffer_pool_size isn't big enough. Especially after you change all your tables to InnoDB. Start at several G and increase until the innodb_buffer_pool_reads is significantly (<<1%) less than innodb_buffer_pool_read_requests.

opened tables 2k

Might not be that bad. Check the number of active tables and if > 2k increase table_open_cache.

Qcache lowmenprunes   50k

Won't matter when query cache is disabled.

sort merge passes : 55

When indexing is improved maybe this won't be needed.  If you do a lot of large table sorts maybe sort_buffer_size should be increased, but with correct indexing there is often no need to sort results.


what exactly are you trying to accomplish? do you want to have all db on another machine?

then you have to use these export and import it

use mongodump to export all you database (you can use mongodump -d your_db) http://docs.mongodb.org/manual/reference/program/mongodump/
use mongorestore to import it http://docs.mongodb.org/v2.2/reference/mongorestore/

you can also use --collection , -c for exporting/importing individual collections