Is it a good idea to split an auditable table into two tables based on the update frequency? I'm currently designing a database schema for a small microservice which handles products.
The service contains a simple REST API which allows users to perform basic management of products and an another API which is used to perform operations which consume these products. The maximum amount of consumes per product is limited with an explicitly defined consumables count. When this limit is reached, the product cannot be consumed anymore. Each product typically contains about 1,000 - 1,000,000 consumables, while a single operation may take 1 - 10 consumables at a time.
The frequency of the management operations is quite low, but the rate of the consuming operations is very high.
At the moment, the schema puts all product information into a single table. This table also contains information which must be audited into a snapshot based history table on each INSERT, UPDATE and DELETE operation. This is currently done with DB triggers, which know how to add rows into the history table along with the additional audit information. Each consumable use also adds a new row into consumables table with some additional information related to how it was consumed.
Here's a simplified example about how these tables are currently organized.
------------------------
| product              |
------------------------
| + id                 |
| + name               |
| + is_enabled         |
| + consumable_limit   |
| + consumable_counter |
| ...10 omitted fields |
------------------------

------------------------
| product_history      |
------------------------
| + id                 |
| + product_id         |
| + time               |
| + user_id            |
| + history_type       |
| + name               |
| + is_enabled         |
| + consumable_limit   |
| + consumable_counter |
| ...10 omitted fields |
------------------------

------------------------
| operations           |
------------------------
| + id                 |
| + product_id         |
| + consumable_idx     |
| ...10 omitted fields |
------------------------

In the above diagram, I've omitted some fields to keep things more simple. The thing that keeps me wondering here is that the consumable_counter in the product table gets rapidly updated with consuming operations, which may further flood the product_history table. Would it be better move the consumable counter into an another table where it would not trigger the a full snapshot of the product table row each time the consumable_counter is being updated? Somehow, I feel like it could be ideal as the consumable_counter is the only column in the product table which is being updated with a high rate. A full snapshot on each counter increment somehow feels a bit overkill.
Edit
The consumable_counter acts as a counter of how many times the corresponding product has been used. When it reaches the consumable_limit, the product cannot be consumed any more.
Edit
Each time a product is being consumed, a new entry will be added into the operations table. Entries in this table also act as business logic transactions containing a state which will be managed during the operation execution flow.

I think your assessment is heading in the right direction. You do appear to be capturing too much information. Based  on your description, I think you are describing a consumption event and not product history.
My recommendation is to look at splitting out the consumption attributes from the PRODUCT_HISTORY table into a new table.  Without knowing the omitted columns, I am unable to provide a recommendation. Here are some thoughts:

if the "name" column is the product's name, that doesn't change with a consumption event and should remain in product history.

The consumption_limit might need to be in the product history and consumption event tables. You may need to know what the limit was when the consumption occurred and the limit may change over time, thus that fact should be recorded in the product history table.

A relationship between the consumption event and the operations table might be needed

Does a row in the operations table describe a collection of individual operations (and perhaps there is an operation table) or is it a singular operation?  That is an important detail on figuring out the relationship with consumption events. If it is singular, I recommend you rename the table.


ALTER TABLE with missing FROM-clause entry for table "inventory" ALTER TABLE inventory.subtitles
   ALTER COLUMN id SET DEFAULT (inventory.subtitle_subtitle_id_seq);

produces missing FROM-clause entry for table "inventory"

but even pgadmin 3 gives me the same syntax.

The problem is in the SET DEFAULT (inventory.subtitle_subtitle_id_seq). Postgres interprets this as "the column subtitle_subtitle_id_seq from the table inventory" which is clearly not your intention.

First of all, you can't get a value from a sequence with just the bare sequence name like that. Generally, you would use nextval('sequence_name').

You probably want something like:

ALTER TABLE inventory.subtitles
    ALTER COLUMN id SET DEFAULT nextval('inventory.subtitle_subtitle_id_seq');


Postgres: my "data/backup" directory is way too large In my postgres 9.3 data directory, there exists a subdir called "backup" that is 6.1G compared to my pg_xlog which is only 130M. I understand that pg_xlog is the WAL logs, so what is in the backup?

There are files there from a month ago so not sure why they're there, if I need them, and how I can prevent them from being created if not needed.

Any explanations?

Here's a du -sh * output of a typical PostgreSQL 9.3 data directory:

4.0K    PG_VERSION
59G     base
47M     global
20M     pg_clog
28K     pg_multixact
80K     pg_notify
4.0K    pg_serial
4.0K    pg_snapshots
4.0K    pg_stat
368K    pg_stat_tmp
168K    pg_subtrans
4.0K    pg_tblspc
4.0K    pg_twophase
1.1G    pg_xlog
4.0K    postmaster.opts
4.0K    postmaster.pid

Notice that there's no backup directory: a PG instance does not need or create such a directory. It had to be created by a person or a script that is not part of PostgreSQL itself.

Since you mention pg_xlog, if the files in this directory follow the naming convention of WAL files (24 numbers in hexadecimal in a 3x8 layout) found in pg_xlog, it's plausible that they're WAL files copied for continuous archiving. Check the archive_command in postgresql.conf.

In this case, the name backup for the target directory make sense, but its location doesn't. Such backups are useful for disaster recovery when the data directory has disappeared, so obviously the data directory itself is the last place where you want such backups.


Is there an Oracle rdbms monitoring API? We have about a thousand customers with our application installed out of which 500 have an oracle database.

Monitoring the database is only one of the requirements.

Installing Oracle Enterprise Manager will give me all the info I need for my databases but no information for the other important metrics (we got some synchronization going on through database link) and a number of process which should be up and running outside of the db.

So my question boils down to this: Is there an oracle rdbms API I could use to integrate the oracle metrics we need in our monitoring tool?

All of the internal database information is obtained from the system data dictionary.  Static metadata can be found in the data dictionary views (e.g. DBA_DB_LINKS for database links) or their underlying tables (obj$ etc.).  Runtime stats can be obtained through the dynamic system ('v$') views (e.g. V$DBLINK).  You may need to look at the documentation for the views to find out how to get the metrics you want.  A bit of google-fu may also turn up code snippets showing how to use the views.

Anything external to the database will have to be monitored through its own logging or diagnostic facilities.  Again, I can't really answer specifics without knowing what processes you actually need to monitor.

Enterprise Manager has a plug-in API which you can use to add external data feeds.  This page has links to the documentation describing how to do this.


How to confirm if the backups are going to tape in SQL Server 2005 and 2008 R2 We have many of our SQL Servers where we take the backup locally on the server as well as over the network to other server in case of disaster.

Also, we have a process set that server support team takes those backups on drives for a defined time out to tapes.

Recently we had an issue where server went down and didn't had the backups on its alternative server and so needed to recover them from tape. Initially even from tape that could not be located, something was a miss for sure but we got it finally.

So instead of being dependent on other team, is there a way in SQL may be via script we can get a report or build something that confirms backups are going successfully onto the tape and when?

Please advise, if it can be achieved. Thanks!

If you want to be sure that you're backups are going to tape, you need to periodically recall the tapes and restore the backups. We recall tapes once every three months to do this, it seems like a lot of effort but it is not overkill if you value having offsite backups.


One address table for many types of addresses I have an application where I have to store adresses of multiple types of location.

For example, my app requires addresses of people, hotels and event venues.

Should I create separate tables for different type of addresses having same fields? or Should I add an additional field telling what is the type of address?

Unless the attributes between the different types are totally different (which I doubt), go for the second option and store the address type in a column of the address table.


Is it better to have a wider single table or "child" tables based on business processes? The example I am working with is an invoice data model, where there may be around 30 columns by the time we're done designing. Each row of data would be unique to a single invoice.

Some of the columns are used by multiple parts of the application (invoice number, obviously, charge totals, etc.) but there are some columns that are only used by a single process.

For example, we have three columns that reflect control numbers (varchar(18)) used by external clients that we should track, but only the tracking process ever looks at these columns. The control numbers are usually one to one to the invoice, so there would only ever be one control number per invoice. There are also some invoices that will just not have a control number because they're older invoices being imported in from a system that didn't keep track of control numbers (they'll represent about 25% of the initial data). Even those older invoices may eventually get a control number (although most likely they're old enough that won't happen).

Does it make more sense to take these columns and make a separate table for them, from a data modeling perspective or a performance perspective, or, should we just leave them in the Invoices table? Answers for this specific example is appreciated, but I'm also curious about a more general answer, since we'll certainly come across this sort of scenario again.

Based on how I read this Wikipedia article, I do intend to mean vertical partitioning, at least in a logical sense (the two different tables would be on the same physical storage in this case).

This is part of an OLTP database.

Probably 75% of the rows will have a control number eventually.

I am a fan of separating items that truly have separate purposes (given that this is an OLTP system, as stated by the O.P.).

Please see my answer to the following DBA.SE question that covers this in more detail, and has links to more of my answers on this same topic showing various examples of implementing this:

Abstract classes in SQL Server. Are they even possible?

The general reasoning is this:

Performance is generally better when normalizing to separate tables. After all, we are working in a "Relational" Database Management System, right? Yes, there will be JOINs require by some consumers of this data, but less data per row = more rows per data page = more data loaded into memory per each read = more efficient I/O and memory usage.
Maintainability is better when normalizing out. It is easier to explain to new members of the team that data comes from related tables (explicitly denoted by the PK -> FK relationship(s)) than it is for them to remember all of the whacky rules for which columns pertain to which teams / consumers.
It is not difficult to adjust the data model if / when business rules change. Yes, things always change. However, it is fairly easy to move a column from a related table back into the main table (simplified here):

ALTER main table to ADD new column
UPDATE main table with values from related table
Adjust / create indexes, if new column should be indexed
ALTER procs to reference new location
ALTER related table to DROP column (might need to drop indexes that use this column beforehand)


SQL Insert with soft row limit in stored procedure I have a table ([Label]) which i would like to enforce a soft limit on the number of rows a company can insert (stored procedure code below)
If this stored procedure is being called frequently (e.g. a few times per second by different users), is it possible that it will insert a row over the limit due to concurrency issue? Should i use LOCK or TRANSACTION?

CREATE PROCEDURE [dbo].[admin_AddLabel]
@companyId INT,
@label NVARCHAR(50)
AS
DECLARE @limit INT = 10
DECLARE @count INT = (SELECT COUNT(1) FROM Label WHERE CompanyId = @companyId)
IF @count < @limit
BEGIN
INSERT INTO Label (Label, CompanyId)
VALUES (
  @label,
  @companyId
)
RETURN 0
END

DECLARE @count INT = (SELECT 1 FROM Label WHERE CompanyId = @companyId)

This makes little sense. It should be SELECT COUNT(...)

  is it possible that it will insert a row over the limit due to concurrency issue?

Yes. More than just one row. If N concurrent users will reach the count at the same point and count limit -1, then all N will proceed and insert thus you will exceed by N-1.

  Should i use LOCK or TRANSACTION?

It is possible, is very difficult to get right, and will severely impact performance. And will be easily defeated by inserts that do not go through this stored procedure (data always outlives the app), by updates that modify the CompanyId and so on and so forth.

BEGIN TRANSACTION
DECLARE @count INT = (
  SELECT COUNT(*)
    FROM Label WITH (UPDLOCK)
    WHERE CompanyId = @companyId)
IF @count < @limit
BEGIN
    INSERT INTO Label (Label, CompanyId)
    VALUES (@label, @companyId)
END
COMMIT

I would strongly advise against doing something like this. Revisit the requirements, a 'soft limit' is very hard to get right in a relational model.

I recommend you read also Enforcing Complex Constraints with Indexed Views, maybe you find something to inspire you.


What DML commands can be allowed / limited on a reporting server? I have a largish database (PostgreSQL 9.1) which I'm preparing to replicate using streaming replication. The 2nd server will be used for reporting and backup purposes. When building reports, it's often useful to build "temporary" tables. By using "temporary" I mean tables which are considered scratch, but which are not using CREATE TEMPORARY TABLE.

Can I force a user to create new tables only their separate schema? Will these schema changes have any impact on streaming replication? The master server only ever has changes in the public schema.

As far as I know, replicated database clusters are complete replicants of the original cluster (e.g. does not support replicating only a certain subset of databases/schemas etc ) and may only host read only transactions.


How can I design Address table with different types? I have a Address table and different address types like Personal and Corporate. I design a table but I think is breaking normalization rules. How can I improve this design?
My design:

Your design appears to be pretty good overall, but I would make the following recommendations:

You already store the client's First Name and Last Name in the clients table, so you can probably remove the client_address_personal_info table altogether. This will reduce redundant data and lead to increased performance and easier management and reporting.
You can probably normalize the name of the client_address_corporate_info table to something similar as your clients table, e.g. corporations
Similarly to the first point, you should either remove the phone_number field from the clients table or the client_addresses table so it's only stored in one place. (Your choice which table you want to keep it in, I've seen it done both ways.)
One other general recommendation (though this is tangentially related to normalization) is you should size your varchar fields to something that is capable of fitting all potential future values. While obviously we can't predict the future, my point is generally leave enough space for larger values that are logically possible. VARCHAR(1) vs VARCHAR(1000) isn't going to affect performance or disk space is any measurably different way. So while most First Names or Business Names can fit inside a VARCHAR(16) field, I'm sure there's names out there that are 25+ characters long. My general starting point on something like that is VARCHAR(50) (and only in rare cases do I use a smaller sized VARCHAR).


Inconsistencies with ADD CONSTRAINT index_name in MySQL I created this example table

CREATE DATABASE foobar;
USE foobar;
CREATE TABLE foos (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  bar_id INT(11) DEFAULT NULL
);
CREATE TABLE bars (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL
);

Then ran this query

ALTER TABLE foos ADD CONSTRAINT constraint_name FOREIGN KEY foreign_key_name(bar_id) REFERENCES bars(id);

Some mysql server will return this on show create table foos

CREATE TABLE `foos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `bar_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `constraint_name` (`bar_id`),
  CONSTRAINT `constraint_name` FOREIGN KEY (`bar_id`) REFERENCES `bars` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then some other ones will return this on show create table foos

CREATE TABLE `foos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `bar_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `constraint_name` (`bar_id`),
  CONSTRAINT `foreign_key_name` FOREIGN KEY (`bar_id`) REFERENCES `bars` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Notice the differences in the index_name, it should be foreign_key_name.

At first I thought it was a problem between MySQL 5.5 vs 5.6, but that does not seem to be the case.

Does anyone know why sometimes the index_name is ignored?

See if these sound close.  (Taken from Changelogs)

----- not yet released 5.7.6 Milestone 16 & 2015-02-02 5.6.23 -- Bugs Fixed -- InnoDB -----

An ALTER TABLE ( http://dev.mysql.com/doc/refman/5.6/en/alter-table.html ) operation that changed the name of a foreign key column resulted in a failure when reloading the foreign key constraint. The previous column name remained in the data dictionary cache instead of being evicted. (Bug #20031243)

----- 2014-02-10 MariaDB 10.0.8 -- -- -----

MDEV-4439 ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS does not work if constraint name is not used. Patches for server and the Innodb engine. Server is fixed so it does nothing if no indexes left to alter. Innodb parser is fixed so it looks for the IF [NOT] EXISTS option in a string. Another change is that it uses the index name for the internal dictionary. Prior to that it only used the CONSTRAINT name for it.

----- 2009-07-13 5.1.37 -- Bugs Fixed -- -----

Renaming a column that appeared in a foreign key definition did not update the foreign key definition with the new column name. (Bug #21704)

----- 2008-01-29 5.1.23 -- Bugs Fixed -- -----

User-supplied names foreign key names might not be set to the right key, leading to foreign keys with no name. (Bug #30747)


Oracle Installation Account Is there any scenario where it is the best choice to make the Oracle installation/service account (oracle) an LDAP/AD account versus a local-only account?

More generally, is creating the Oracle account locally the best practice? I don't see this stated explicitly in their installation documentation.

I worked with an insurance company that used active directory on all of their Redhat Linux servers so that their DBA's could log in with their own credentials. The DBA's would then SUDO to Oracle which did not have a password. Hence, the upper management could track when DBA's logged into each server. Using AD can work well in Linux the manage authentication. It also makes for fewer passwords that the DBA's need to remember.


Is it possible to restore a database in a mirroring configuration? Or will I have to break the mirror, restore the database, then rebuild the mirror?

We're in the process of upgrading our SharePoint servers.  We already have two new  servers in place and they are currently mirrored.  If possible, I'd like to leave the servers in a mirrored state to save time during our maintenance window.

Database Mirroring must be removed to perform the restore. Break the mirror, do the restore and then setup mirroring again.


Variables in Oracle Triggers Context

Every few weeks the database guy at work backs up the production database and then several weeks later imports it into the development database. I don't know why it takes so long between backup and restore, but anything I do in the database during that time period is lost unless I explicitly tell him to save it. Not having every little change memorized has at least once caused a data loss issue in one of my applications. So my plan is to create a table and populate it with information about the changes made to the database so I have an answer next time he asks me "what changes have you made to the database since September 5th?"

Question

I'm trying to compose this trigger to log structural changes to a table that I will query with a cron job and backup the relevant tables as needed. I don't care about data, just structural changes.

How do I access the variables needed to populate the table in this statement?

CREATE OR REPLACE TRIGGER DB_CHANGES_T
AFTER ALTER OR CREATE OR DROP OR GRANT OR RENAME OR TRUNCATE ON DATABASE
BEGIN

  insert into db_change_log (
    event_type, -- 'create', 'alter', 'drop', 'grant', 'rename', or 'truncate'
    affected_entity, -- the name of the table/view/package/etc that was affected
    entity_type, -- 'table', 'view', 'index', 'package', 'procedure', 'function', etc, etc
    event_time
  ) values (?, ? , ?, sysdate);

END;

It really sounds like you have an XY Problem.

Your Business Requirement can be summed up with this statement:

  I don't care about data, just structural changes.

I'm sure the Business Requirement to capture and manage Schema deltas is part of DevOps.  There are solutions that exist for this part of the entire DevOps process.  (eg Liquibase)  I suggest you don't reinvent the wheel.

Oracle Tools for Schema Deltas

The latest version of Oracle SQLcl (v19.2.1 - $0) has incorporated Liquibase.  This allows you to create schema deltas (and save the result in a Code Repository)

The third example in the documentation appears to be a good starting point for your needs.

capture base Schema
capture changes
(example applies base Schema to new Schema)
apply changes to base Schema

Make sure you keep the deltas in a Code Repository.

More Info?

There are a lot of articles that discuss how automate schema changes...especially with the context of DevOps.  Feel free to google for more information.


How to update information of the data of column in the table matching the certain text? I have a condition where i need to update the only matched case of the data in the table.
For an example
I have a #test table

Sample data are

Firstname Lastname
ABCDEF    GHIJKL
BFGABCF   FGYUTY
CDFSERABC VVVVVV

I need an update query which match the 'ABC' in the #test table and update that 'ABC' with 'XYZ'.

Resulting data must be

Firstname Lastname
XYZDEF    GHIJKL
BFGXYZF   FGYUTY
CDFSERXYZ VVVVVV

Its easy : REPLACE (string_expression,string_pattern,string_replacement)

CREATE TABLE FirstLastName
    ([Firstname] varchar(9), [Lastname] varchar(6))
;

INSERT INTO FirstLastName
    ([Firstname], [Lastname])
VALUES
    ('ABCDEF', 'GHIJKL'),
    ('BFGABCF', 'FGYUTY'),
    ('CDFSERABC', 'VVVVVV')
;

select * from FirstLastName

--- now update the table

update FirstLastName
set Firstname = replace (Firstname,'ABC', 'XYZ')
-- optional (else it will do an upadte to entire table) !!
--where somecondition

SQLFIDDLE HERE


SQL server 2016 restore impact of mirroring configuration? We have Windows Server 2016 and on it SQL server 2016 standard edition is installed. Another windows server 2016 machine has mirroring of SQL server configured.

Lets call Primary SQL server as A and Mirrored SQL server as B.

ON A we will delete DB and restore from some existing backup.

My question is do I need to recreate mirroring configuration between A & B?

You cannot just delete or drop db when mirroring is configured. You have to remove mirroring alter database dbname set partner off.

Then drop database on primary.

Then restore from the desired backup.

Then reset up mirroring - take full and log backup and restore it to server B with NO_RECOVERY.

Remember : Mirroring is deprecated technology. So you should either use basic availability group or if you are on Enterprise edition then go with AlwaysON.


Select column names whose entries are not null I would like to have a list of those columns of a table that have at least one not-NULL data entries in them.

In other words, I would like to get the column names for which the following returns at least one entry:

SELECT DISTINCT column_name FROM table WHERE column_name IS NOT NULL

I tried the following:

SELECT column_name
FROM information_schema.columns
WHERE table_name = "table_name"
AND EXISTS (
    SELECT DISTINCT column_name FROM table_name WHERE column_name IS NOT NULL
)

But this also returns the column names where all the entries are NULL.

So how do I get only those columns with non-NULL entries?

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.


Multiple transactional publication: pros and cons I have two database servers:

 1. DB1
 2. DB2

Both are SQL Server 2012. DB1 - is a production server. DB2 - is a server which is used only for reporting. There is a transactional replication, which replicate data between DB1 (publisher) and DB2 (subscriber and distributor).

Recently database size has grown and if I need to add or delete any object it takes too much time to make a snapshot, send it to Distributor and apply all changes to Subscriber. Now it takes 3+ hours which is not reasonable for me.

So I wonder if I could configure multiple publications - one for the biggest tables which are not changed to often and one for all other objects.

Does it make sense?
What do you think about any cons of this solution?
Maybe you could provide any other solutions for this case?

Thank you.

Make sure both of these settings for your publication is set to False which is the default.  It these are set to FALSE and you DO NOT reinitialize your subscription snapshot should only be generated for new articles.

If you are changing any portieres of the article you have to reinitialize and recreate full snapshot.

  [ @immediate_sync=] 'immediate_synchronization' Specifies if the
  synchronization files for the publication are created each time the
  Snapshot Agent runs. immediate_synchronization is nvarchar(5), with a
  default of FALSE. If true, the synchronization files are created or
  re-created each time the Snapshot Agent runs. Subscribers are able to
  get the synchronization files immediately if the Snapshot Agent has
  completed before the subscription is created. New subscriptions get
  the newest synchronization files generated by the most recent
  execution of the Snapshot Agent. independent_agent must be true for
  immediate_synchronization to be true. If false, the synchronization
  files are created only if there are new subscriptions. You must call
  sp_addsubscription for each subscription when you incrementally add a
  new article to an existing publication. Subscribers cannot receive the
  synchronization files after the subscription until the Snapshot Agents
  are started and completed.

  [ @allow_anonymous=] 'allow_anonymous' Specifies if anonymous
  subscriptions can be created for the given publication.
  allow_anonymous is nvarchar(5), with a default of FALSE. If true,
  immediate_synchronization must also be set to true. If false,
  anonymous subscriptions are not allowed on the publication.

My personal preference is to group articles into multiple publications. That way even you have to reinitialize subscriptions and generate new snapshot you can run multiple jobs at the same time. You will need to make a choice if you want shared or individual (per publication) distribution agents.


How to safely recreate a MS SQL table that has other objects depending on it? I have a table in A Microsoft SQL 2012 database, called Courses.  It was "upconverted" from an originally SQL 2000 database, upconverted to SQL 2008 database, and then upconverted again to 2012 (You can't directly upconvert from 2000 to 2012).

A result of this is that we have some tables that have the old text/ntext datatype, and I want to change the datatype to varchar(max) or nvarchar(max).  For reasons I don't quite understand, I can't simply change the datatype of the ntext columns to nvarchar(max), because whenever I try to do that in Table Design view, I get this error:

Additionally, If I try to simply right click the Courses table in Management studio and Delete it, I see that there are quite a few other database objects dependent on Courses, and upon attempted delete, I get an error message saying I can't do so, because Courses is referenced by another Foreign Key constraint from another object.

So, essentially what I want to do is recreate a table, that has other objects depending on it, and the only thing different about the recreation is that I want to change the text/ntext columns in it to varchar(max) and nvarchar(max) datatypes.  Everything currently "depending" on the 'old' Courses table, needs to then "depend" on the 'new' recreated Courses table.  What's the easiest way to go about doing so in SQL 2012?

The simple way around this is to change some options in Management Studio.

Go to Tools, Options, Designers, Table and Database Designers. Uncheck "Prevent saving changes that require table re-creation".

Generally speaking, Management Studio will properly handle dependencies when you modify a table in a way that requires it to be recreated (it will do assorted tricks with temp tables behind the scenes, which you can see if you tell it to generate a change script instead of actually making the changes). However, this may involve modifications/changes to related tables or foreign keys. In other words, don't do this on a live system if you can help it, and make sure you've got backups before you do it.


Mysql slow.log user specific I use a slow.log logging on my MySQL server, to catch bottlenecks in my script, but at the same time, i use phpmyadmin on this server. My script and pma has different MySQL user accounts and now, when i analyzing the slow.log file, i see a lot of spam from pma queries, is it possible, to configure the MySQL to logging a slow queries only from specific users?

You can try to analyse your slow log with the Percona tool pt-query-digest and use the option :

–filter ‘($event->{user} || “”) =~ m/john/’

Max.


MySQL Master  Slave configuration Sorry for the wonky title if it got you confused.

I want to know if a server can be a slave and master at the same time. Our problem is that we have lots of mobile units that need to be synced to the master but they only need 6 out of the 100s of tables on the master. All the extra tables serve no purpose on the slave except for delaying synchronization and adding data costs.

Desired Configuration:

Server A: Master to Server B.

Server B: Slave to Server A, Master to Server C.

Server C(n): Slave to Server B.

OR

Server A: masterSchema, slaveSchema.

slaveSchema is a sync'd subset of masterSchema.

Server B(n): Slave to Server A.slaveSchema

If the answer is no would anyone have any alternate solutions to propose. We're trying to avoid having to sync the different schemas/databases manually as that can get real ugly real fast. We have a lot of critical live data flying around so both performance and integrity are of concern.

Actually both configurations are possible.

Remarks to your ideas

For configuration 1, just set up the first Master->Slave replication. See that you should activate --log-slave-updates on the slave so that the slave creates an appropriate binary log for the propagation of the next slave in the chain. Then set up the new slave with the previous slave as a master. This should be straight forward.

For configuration 2 you can use --replicate-do-table (or --replicate-wild-do-table) or --replicate-ignore-table options on the slave. This alters the behaviour of the slave executing received binary logs. Another way is to use --binlog-do-db or --binlog-ignore-db on the master. This alters the logged binary statements on the master directly. There are some pitfalls thought when using. You might want to read the manual yourself (basically it is unsafe to use the --replicate...--db or --binlog...--db options, because they may have unintended side effects).

These problems are all mentioned in this blog post of Percona too. Since there are no --binlog-do-table options, one does not have the chance to safely restrict the written statements on the master. In this setting the whole binary log would need to be transferred to all n slaves.

Another solution

They do propose a different solution for cases when there should become n slaves propagated, but only with a partial binary log. Setting up a replication chain like you did in configuration 1, but setting the storage engine to all tables on the first slave to BLACKHOLE. BLACKHOLE is just /dev/null, so all written data will be immediately lost. This allows to replicate everything from the first master to the first slave. Then the replication rules from configuration 2 (--replicate-ignore-table) are applied on the slave. All other clients are slaves from this "filter-slave". So the first slave is just responsible for filtering the binary log and propagating this log to (maybe) a lot of slaves. Thus the full binary log must be transferred only once, and not to all of the n slaves.

Maybe the last configuration is something you want to take into account.


Prematurely exiting a while loop in T-SQL and viewing the output I let a while loop run over the weekend with a CURRENT_TIMESTAMP condition on it, problem is I accidentally set the condition to 2020-08-06 instead of 2020-06-08 (oops). Is there any way to manually exit the loop and view the results so far? I don't think it's possible for me to change the system time in order for the loop to exist as the SQL server isn't run locally.

Success will depend a lot on where the results are stored. If they're written to a normal table then you can simply query that table. If there's a transaction open which would block new queries use the NOLOCK hint. If you're using snapshot isolation I don't know how you can get at the open transaction's versioned data.

If all the results are in temporary tables you'll have a harder time. Those tables are scoped to the session so accessing them from a different session will be tricky. You may be able to look in TempDB's metadata to find the table's full name and query that explicitly. I don't know if that will work, I've never tried. (I tried; it doesn't work.)

If the code is running through SSMS you can stop execution without ending the session. This will leave the temp tables in tact and available to the session. Cancel using the menu Query -> Cancel Executing Query (mapped to the red square in the toolbar). If there's an open transaction that transaction will remain open. You will have to manually commit it or roll it back.

Cancellation is achieved by sending an "attention" message to SQL Server. Other clients may or may not support this. From a scripted environment (e.g. Powershell, Python), if the query was submitted asynchronously it may be possible to call the "Cancel" method (or equivalent) on the original connection or query object.

With sufficiently high access rights you could look at the on-disk structures. Paul White has written up a method here. Depending on how much has been written it may take as long to transcribe the dumps from DBCC PAGE as it would to re-run the query.

If results are in variables you could connect a debugger to the SQL Server process, pause its execution and go hunting. Good luck with that. If there are other users on the system, or background system activities, it may be difficult to distinguish your desired values from all the others present. Certainly, it is not documented or supported in any way. Do NOT try this on a shared production instance.

Finally, if you're using SSMS have a look in the messages tab. You never know, whoever wrote the script may have placed PRINT statements to track progress.

Ending the loop is easy. Find the SPID from the SSMS tab or DMVs (sys.dm_exec_sessions et al) then issue KILL session_id. Unfortunately it will end the script entirely, loosing local variables and temp tables, and disconnect the targeted session. Any open transaction will have to roll back, which will take roughly as long as the transaction has been open, give or take.


Control file creation failed I lost all the control files and I don't have backup for it. Control file creation failed

SQL> CREATE CONTROLFILE SET DATABASE "UAT02" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/uat02/redo01.log' SIZE 100M,
GROUP 2 '/opt/oracle/oradata/uat02/redo02.log' SIZE 100M,
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/uat02/system01.dbf' size 400m',
'/opt/oracle/oradata/uat02/sysaux01.dbf' SIZE 300m,
CHARACTER SET US7ASCII
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15
CREATE CONTROLFILE REUSE DATABASE "UAT02" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/opt/oracle/oradata/uat02/system01.dbf
size 400m'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
System01.dbf is there

[oracle@localhost uat02]$ ls -ltr

total 1034612
-rw-r----- 1 oracle oinstall 104858112 Sep 27 07:41 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Sep 27 11:48 undo01.dbf
-rw-r----- 1 oracle oinstall 419438592 Sep 27 11:48 system01.dbf
-rw-r----- 1 oracle oinstall 314580992 Sep 27 11:48 sysaux01.dbf
-rw-r----- 1 oracle oinstall 104858112 Sep 27 11:48 redo01.log
-rw-r----- 1 oracle oinstall 9748480 Sep 28 23:39 control01.ctl

Any suggestion?

Your single quote is in the wrong place in the datafile definition:

DATAFILE
'/opt/oracle/oradata/uat02/system01.dbf' size 400m,
'/opt/oracle/oradata/uat02/sysaux01.dbf' SIZE 300m,


PGPool for Postgres load balancing, where to sit it So I've been doing a stack of reading. I'm still not 100% clear of the best way to go about it.

Let's take a simple setup.
3 nodes to server UWSGI apps (in random), 3 nodes for PGSQL, 1 nominal master and 2 read only slaves - it's not setup yet but that's the goal. 80% of the requests are read only anyway for most apps.

Now what really bugs me is that PGPool is usually illustrated as a single machine. If we are going for high availability as the primary goal with load balancing as a highly desirable side effect, introducing the need for a PGPool in the middle is introducing a singular point of failure as well as perhaps an otherwise unecesaey node.

Trying to ascertain what to do I imagine that really the app servers could be PGpools as they're the first one to notice if a DB goes down, but I've heard multiple PGPools are
Complicated.

I mean what I really want is the built in replication ability with the ability to execute a failover and mark the new master etc, I know PGPool does it. I'd like it to be decentralised if possible - can people please point me in the right direction.?

Direction:
I would definitly recommand this step by step tutorial that may point you to the right direction. This is the one I used when setting up similar config. You can adapt it to your needs eyes wide open.

Multiple PgPool:
The tutorial seems long (and it is: 6 parts!) but the part related to setting up multiple Pgpool is less than half one of 6 parts (see part 5 and 6). Even if I could consider setting up pgpool as a pain, objectively, this part is not the worth (consisting in setting up ip arping and watchdog). If you have already been through the rest for configuring one pgpool and stream or wal replication that will not be a problem.

Decentralized or not:
This may be only based opinion, but if I had to setup pgpool redondancy (I did not), I would prefer placing pgpools along postgresql servers in order to simplify my setup and make the whole thing accessible from outside "as if" it was a simple pgsql server. App and database layers stay good at what they are supposed to do and could stay technically "agnostic" to ascendant/descendant layer.


Unpivot columns based on select It is possible to do Unpivot operation, where column names are done via select, without using dynamic SQL ?

I tried this, but it fails.

SELECT *
FROM [dbo].[Report]
UNPIVOT
(
  Value
  FOR WeekName IN
  (
      SELECT COLUMN_NAME FROM information_schema.columns c
      WHERE c.TABLE_NAME = 'Report'
      AND ORDINAL_POSITION > 1
      ORDER BY ORDINAL_POSITION
  )
) u

UNPIVOT is not the best way to unpivot a table, CROSS APPLY with VALUES is a far better method.

Sorry, but I don't think you can get away from Dynamic SQL for this one.

This code will dynamically unpivot an entire table.

DECLARE @tablename VARCHAR(255) = 'Employee'
DECLARE @schemaname  VARCHAR(255) = 'HumanResources'
DECLARE @qrystr NVARCHAR(4000) = ''

;with cte
    AS
    (
    SELECT
        c.column_id
        ,c.name
    FROM
        sys.objects o
            INNER JOIN sys.columns c
                ON
                o.object_id = c.object_id
    WHERE
        o.name = @tablename
        AND
        SCHEMA_NAME(o.schema_id) = @schemaname
    )
SELECT
@qrystr =
'SELECT A.[' + (SELECT name FROM cte WHERE column_id = 1) + ']
    ,C.Name
    ,C.Val
FROM
    [' + @schemaname + '].[' + @tablename + '] A

    CROSS APPLY (VALUES ' +
    STUFF((SELECT ',' + '(CAST(['+name + '] AS VARCHAR(255)),''' + name + ''')' FROM cte WHERE column_id > 1 ORDER BY column_id FOR XML PATH ('')), 1, 1, '')
    + ' ) C(Val,Name)'

EXEC (@qrystr)


Table-name.column-name in PostgreSQL? I would like to use the following syntaxis in my PL/pgSQL code:

INSERT INTO table (table.column1, table.column2) VALUES (1, 2);

But somehow it doesn't work. Does anybody know if such a notation it is prohibited or I do something wrong?

Firstly, if you really want to use the name table for your table then you will need to enclose it in quotes:

create table table (column1 integer, column2 integer);
ERROR:  syntax error at or near "table"

create table "table" (column1 integer, column2 integer);
CREATE TABLE

but assuming your table name is something different (which I highly recommended), the error returned indicates that the parser is interpreting my_table as a column name:

create table my_table (column1 integer, column2 integer);
CREATE TABLE

insert into my_table (my_table.column1, my_table.column2) values (1, 2);
ERROR:  column "my_table" of relation "my_table" does not exist

This interpretation is sensible, as column1 may be a composite type, for example:

create type my_type as (column1 integer, column2 integer);
create table my_table (col my_type);
insert into my_table (col.column1, col.column2) values (1, 2);
select * from my_table;

  col
-------
 (1,2)
(1 row)

You probably just want the following as already suggested by DrColossos:

insert into table (column1, column2) values (1, 2);


pt-kill - how to securely allow access to mysql server when running as service I know how to run pt-kill as systemd service:

[Unit]
Description = pt-kill myUser
After=syslog.target mysql.service
Requires=mysql.service
[Service]
Type = simple
PIDFile = /var/run/ptkill.pid
ExecStart = /usr/bin/pt-kill -uxxx -pxxx --daemonize --pid=/var/run/ptkill.pid --log=/var/log/ptkill.log --match-user myUser --match-state "Sending data" --busy-time 640 --kill-query --print
Restart=on-abort
[Install]
WantedBy=multi-user.target

However, this way another system user can see the mysql root password via ps -e -o pid,unit,cmd:

7786 ptkill.service perl /usr/bin/pt-kill -uxxx -pxxx --daemonize --pid=/var/run/ptkill.pid --log=/var/log

I tried adding User=root to the [Service]-section, because root user has protected /root/.my.cnf where passwordless access to mysql is configured. Sadly this doesn't work (I got no permission to access mysql without specifying user... when starting ptkill.service, I think the protected .my.cnf does not get loaded when starting the ptkill.service).
I also tried adding parameters "-umyUser -p`cat /path/to/my/password`" which does not work either.

How to securely run pt-kill as a system service pt-kill and how to securely let it access the mysql server?

I hope you are not one of my workmates :-) He had to setup a very similar configuration than yours a few weeks ago- you can check our packages if that helps you: Source deb, puppetization.
I told him to use socket-based authentication, it is the same method we use for non-human accounts that only need local-only permissions (icinga, prometheus). You only need to enable the plugin and tell the account with the same name as the account the service is going to run as to use it, so no password is required. Because there is no password, it cannot be stolen, so a compromise of the host would not escalate to a worse issue. Also less things that are private to handle. Unless there is a MySQL bug, the account is as secure as your OS account. You have to create a non-root account for both MySQL (auth_socket will provent password-based authentication and will lock it to localhost only) and the OS (system user with locked login), or otherwise it would defeat the point of it.
If for some reason you cannot use the above, you can still use password-based authentication, but set it on its own configuration file only readable by the user (non-root) that you start the service as.
The point is always to provide the least amount of permissions you can to a service. Root usage not only give the maximum amount, it also overlaps some OS (logging, limits, etc.) and mysql limits (max_connections) you don't want to, even if it effectively has a lots of permissions to kill other processes.


Postgresql how to return nested type in a function I'm trying to return nested type with a function, but always end up with some error.
With example below I get:
select mytest();
ERROR:  malformed record literal: "test3"
DETAIL:  Missing left parenthesis.

create type nestedtype as (test1 text);
create type maintype as (test2 text, other nestedtype);

CREATE OR REPLACE FUNCTION mytest() returns maintype as $$
  SELECT 'test' AS test2,
  ((select 'test3' as test1)::nestedtype) AS other
$$ LANGUAGE sql STABLE;

What am I doing wrong?
How can I return nested type used together with aliases?
In the end I need to return multiple fields from nested type, so it's not possible just switch to text, just in case such suggestion would come up.

To create an instance of a type, you need a row constructor
So to create a value of nestedtype you need row('test1')::nestedtype. To create a value of maintype you essentially do the same, row(...)::maintype but for the second value you need again pass a proper row type:
row('test2 value', row('test1 value')::nestedtype)::maintype

This expression can be used in your function:
CREATE OR REPLACE FUNCTION mytest()
  returns maintype
as
$$
  select row('test2 value', row('test1 value')::nestedtype)::maintype;
$$
LANGUAGE sql STABLE;


SSIS Get max value in column and create new column? I'm importing two data sources. The problem is that one data source has a date, the other does not. Everything in the data flow has the same date and is inserted as one transaction into the same destination table.

Source A: [Calendar Date], [Item Number], [Units Sold]

Source B: [Item Number], [Inventory Units]

Final Output: [Item Number],[Calendar Date],[Units Sold],[Inventory Units]

Source A & B always refer to the same date. Therefore I want MAX([Calendar Date]) applied to every row in the final output. I just can't figure out how to do that in SSIS

Update: Possible Solution?

So what I did was add a temp id of 1 to every row following the merge of A&B. I then split the data, grouped by the MAX(DATE) which in this case is the only date. Then I joined back both sides on 1=1 thus giving each row the single value of MAX(DATE). Is this the recommended approach?

I took a different approach and well, mine's going to be a bit easier to maintain. ;)

I have created a Variable named SourceDate. I'm going to run a query against my Source A table and derived the maximum date in there. Due to data type quirkiness, even though I used a date datatype, SSIS 2008 freaks out and thinks I'm changing data type so I explicitly cast it to datetime

My control flow looks like the following.

SQL Derive MAX date

I return a single result set and assign into my variable ordinal position 0. This gives me the thing I need to ensure I'm only pulling current data out of table A and since it's the same for all values, it'll used after the merge join.

SELECT
    CAST(MAX(SA.CalendarDate) AS datetime) AS CalendarDate
FROM
    dbo.SourceA AS SA;

In my data flow, I'm going to use two OLE DB Sources. If this were all on the same source, I'd have you craft the appropriate query there and be done with it but this approach allows you to use two different servers and/or RDBMSes.

Source Query A

Since I like to make things complex, I assumed that Source A could have many days worth of data in there but we only wanted the most recent. Therefore, I'm going to use a parameter of ? and map that to my Variable @[User::SourceDate]

SELECT
    SA.ItemNumber AS ItemNumberA
,   SA.UnitsSold
FROM
    dbo.SourceA AS SA
WHERE
    SA.CalendarDate = ?
ORDER BY
    SA.ItemNumber ASC;

Source Query B

SELECT
    SB.ItemNumber AS ItemNumberB
,   SB.InventoryUnits
FROM
    dbo.SourceB AS SB
ORDER BY
    SB.ItemNumber ASC;

The important thing is that for a Merge Join to work, you must have sorted data. Sort in your database. It has indexes which might mean the data is already sorted for you. But, when you do that, you have to tell SSIS that this data is sorted. You do this by going to the Advanced tab after right clicking on the OLE DB Source component. You mark the Output as Sorted and then you need to identify the SortKey position. Since I'm sorting on ItemNumber I will put a value of 1 there.

In my source queries, note that I'm renaming ItemNumber. After the join, I don't know if my Left, right or Both sources had an ItemNumber.

I merge both data sets together based on the ItemNumber matching and use a type of Full Outer Join

I then add in Derived columns.

The first adds my Variable @[User::SourceDate] as a column called CalendarDate.

The second derived column helps me render a single column called ItemNumber I use an expression to pick the first non-null version of my two columns ItemNumberA and ItemNumberB

ISNULL([ItemNumberA])? [ItemNumberB] : [ItemNumberA]

My Data Flow ends up looking like

Database setup

I spun up a little database with some data to demo against. Nothing fancy here.

CREATE TABLE dbo.SourceA
(
    CalendarDate date NOT NULL
,   ItemNumber int NOT NULL
,   UnitsSold int NOT NULL
);

CREATE TABLE dbo.SourceB
(
    ItemNumber int NOT NULL
,   InventoryUnits int NOT NULL
);

-- add 3 values, 2 for today, one for yesterday
INSERT INTO
    dbo.SourceA
(
    CalendarDate
,   ItemNumber
,   UnitsSold
)
VALUES
    (CURRENT_TIMESTAMP,100,10)
,   (CURRENT_TIMESTAMP,200,20)
,   (DATEADD(d, -1, CURRENT_TIMESTAMP),100,10);

-- Add inventory. Something for items sold + something not sold
INSERT INTO dbo.SourceB
(
    ItemNumber
,   InventoryUnits
)
VALUES
    (100, 30)
,   (200, 10)
,   (300, 7);

Biml

Biml, the Business Intelligence Markup Language, describes the platform for business intelligence. Here, we're going to use it to describe the ETL. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that addresses a host of shortcomings with it. Specifically, we're going to use the ability to transform a Biml file describing ETL into an SSIS package. This has the added benefit of providing you a mechanism for being able to generate exactly the solution I'm describing versus clicking through many tedious dialogue boxes.

So, install BIDS Helper if you haven't already. Run my table create scripts. Fix the Connection String to point to wherever you created the tables and Whoosh, you have my solution. Adapt for your actual table and servers.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection ConnectionString="Provider=SQLNCLI11;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=tempdb" Name="CM_OLE" />
    </Connections>
    <Packages>
        <Package Name="dba_77666" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="DateTime" Name="SourceDate">2014-01-01</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL Derive MAX date" ResultSet="SingleRow">
                    <DirectInput>
SELECT
    CAST(MAX(SA.CalendarDate) AS datetime) AS CalendarDate
FROM
    dbo.SourceA AS SA;
                    </DirectInput>
                    <Results>
                        <Result Name="0" VariableName="User.SourceDate" />
                    </Results>
                </ExecuteSQL>
                <Dataflow Name="DFT Make Data">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC SourceA">
                            <DirectInput>
SELECT
    SA.ItemNumber AS ItemNumberA
,   SA.UnitsSold
FROM
    dbo.SourceA AS SA
WHERE
    SA.CalendarDate = ?
ORDER BY
    SA.ItemNumber ASC;

                            </DirectInput>
                            <Columns>
                                <Column SourceColumn="ItemNumberA" SortKeyPosition="1"></Column>
                            </Columns>
                            <Parameters>
                                <Parameter Name="0" VariableName="User.SourceDate" />
                            </Parameters>
                        </OleDbSource>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC SourceB">
                            <DirectInput>
SELECT
    SB.ItemNumber AS ItemNumberB
,   SB.InventoryUnits
FROM
    dbo.SourceB AS SB
ORDER BY
    SB.ItemNumber ASC;

                            </DirectInput>
                            <Columns>
                                <Column SourceColumn="ItemNumberB" SortKeyPosition="1"></Column>
                            </Columns>
                        </OleDbSource>

                        <MergeJoin JoinType="FullOuterJoin" Name="MJ Mash all">
                            <LeftInputPath OutputPathName="OLE_SRC SourceA.Output" />
                            <RightInputPath OutputPathName="OLE_SRC SourceB.Output" />
                            <JoinKeys>
                                <JoinKey LeftColumn="ItemNumberA" RightColumn="ItemNumberB"></JoinKey>
                            </JoinKeys>
                        </MergeJoin>

                        <DerivedColumns Name="DER Add Calendar Date">
                            <Columns>
                                <Column DataType="Date" Name="CalendarDate">@[User::SourceDate]</Column>
                            </Columns>
                        </DerivedColumns>

                        <!--
                        We have two ItemNumbers, One of them is populated, possibly both.
                        Create a single column that has the ItemNumber
                        -->
                        <DerivedColumns Name="DER Unified ItemNumber">
                            <Columns>
                                <Column DataType="Int32" Name="ItemNumber">ISNULL([ItemNumberA])? [ItemNumberB] : [ItemNumberA]</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                        Perform any NULL handlings here
                        -->

                        <DerivedColumns Name="DER Fix outstanding nulls">

                        </DerivedColumns>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Update

With the updated information that this will be coming from a file source, the package will change in 3 places.

The first will "SQL Derive MAX date". Instead of using an Execute SQL Task, you will need to use another Data Flow and wire the Source to an Aggregate transformation. Find the MAX date and then use the ... variable sampling thing to assign 1 row into our Variable (or use a Script Component, which is what I'd do).

If the source file that has a date could hold a mix of older and newer data, then you'd need to add a Conditional Split transformation after the data source A to filter out anything older than Max datetime derived in addendum 1 in "DFT Make Data".

Finally, you will need to add a Sort transform immediately after both of your data sources/Conditional Split in the "DFT Make Data" step.

1-2-3, solution holds true. Too lazy to update the Biml as I don't know your file definitions.

Future reader note, this does force you to reprocess one of your source files. If it's a few hundred/thousand/million rows, depending on your IO subsystem, that may not be a concern. If you have a slow IO sub, then you'll need to investigate alternate mechanism of


Does autovaccum wake up in slave? I cannot find the answer to question what I wrote in the title. Postgres automatically wake vacuum by autovacuum, but when it's slave does slave wake up autovacuum process?

No.  Vacuum only runs on the master.  The results of vacuuming are WAL logged and so replicated through to the slave via the normal WAL replay mechanism.


What is the most efficient way to alter a column definition in a table with millions of rows I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple

alter table Table1 ALTER COLUMN Column1 XML NULL

but it takes forever. So here are my questions:

Why does it take so long to apply the alter?
Is there a better way to do it?

Would it be faster to:

Create a new table with the correct definition for Column1
INSERT INTO <NewTable> SELECT * FROM <OriginalTable>;
Rename OriginalTable to OriginalTable_old; Rename NewTable to OriginalTable
Validate and Drop OriginalTable_old

The advantage here is that you do not hold a lock on the Original table for the duration of the operation. The table should only be locked during the rename phase. (It assumes that SQL Server supports an object level rename.)


Partial restore of Progress DB I have progress .bup files for my databases The environment I am restoring too has less space and because I have no control over this environment I wish to make the databases smaller.

To do this I need to exclude certain tables from the restore process. (Alternatively I want to specify which tables to restore.)

The list for excluding is much smaller so would make more sense to specify what I wish to exclude certain tables, rather than a list of what must be  included.

The Progress backup is a block-level image of the database & bi file, so there's no way to only restore certain tables. You'll need to

restore the db on a machine with enough space for the old db and the smaller db,
create an empty version of the db,
dump the required tables from the restored db,
load that table data into the new db file,
then copy the smaller db to your target machine


column value is not increment after 10 I tried below 2 codes.... showing same error

1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1

SELECT MAX(columnname ) AS max FROM tablename

I am trying to increment value of column.(++string) It worked properly till 10. After 10 it is not increment.

then i checked manually, then i get result 9.

  Result Got - 1 2 3 4 5 6 7 8 9 10 10 10 10

  Result Required - 1 2 3 4 5 6 7 8 9 10 11 12

I think database count 10 as 1. i dont know why?

In future i want change number to string like... pro1, pro2 like that..so that i dont want to change datatype

I think you have your column data-type on non numeric format. if your data type is CHAR (or VARCHAR) type it would select 9 as your max number.

If you change your datatype for the column to INT, your problem should be resolved.


MySQL schema recovery Is there a chance to recover overwritten schema or table in a schema?
I have accidentally overwritten schema with the back-up from other source.

You may find this useful. It's TwinDB's data recovery kit. According to the site:

TwinDB data recovery toolkit is a set of tools that operate with InnoDB tablespaces at low level.
The toolkit allows to recover data:

After table was dropped
If InnoDB tablespace is corrupt so badly so MySQL doesn’t start with innodb_force_recovery
If file system with InnoDB data is corrupt
If InnoDB files were accidentally deleted
After records were deleted from table

It's from by Aleksandr Kuzminsky, a former Percona employee - Percona are big hitters in the MySQL ecosphere. The source code for the toolkit is available here.


how to clean some character from row in mysql? How to remove some part of rows from column in mysql? I have a column like this

my desire output is like this

Fairly easy with a couple of string functions ... change test to the name of your table of course.

update `test` set bfi_name = substr(bfi_name,instr(bfi_name,' ')+1)


SQL Server 2012 Installation Error on Windows 2008 Server I am facing an error while trying to install SQL Server 2012 EXPRESS ADVANCED EDITION(32bit) on Windows Server 2008 Enterprise(64bit).

After passing Rule Check, at install updates section, error occurs about update could not be downloaded. On clicking next, following error in dialog box occurs.

SQL Server Setup has encountered the following error:

Setup encountered a failure while running job UpdateResult.

Error code 0x876E0003.

How can I solve this?

Please include the option "UpdateEnabled=False" in the defaultsetup.ini file which can be found in Media**X64\defaultsetup.ini**.
This works like a charm. Please let us know how this goes.


One SQL Server RDS instance plus one Ubuntu instance cost money on my initial free trial year Why do I get charged when I only have one micro instance running as Ubuntu and another one a RDS. Does the extra RDS count as a separate instance? My RDS is Microsoft SQL Server exchange which should be free.

I wonder if there is any place where I can get started with node and a database that isn't as annoying as aws. Their documentation is horrible. It feels like the medical system. I never know what will cost me until I get the bill and then it's always surprising.

Yes technically both are considered separate instances.  However you get 750 hours free for each as they are billed as separate services with the caveat that instance sizes have to be a t2 micro.  Since RDS is a managed service you won’t see the running instance in your ec2 console even though it’s an instance running in your account.

Some things to look at on your bill as they might be the cause of the charges:

Are you using more than 30gb for EBS volumes attached to your ec2 instance (You can have any combination of gp2 or hdd EBS volumes, they just need to be <= 30gb)
Did you allocate more than 20gb for your RDS instance
Have you been stopping and starting your ec2 instance multiple times an hour (Each time you stop then start your instance the clock starts over on the running costs. Restarts keep the clock running)
Multiple snapshots of the EBS/RDS volumes


How to concatenate arrays with user-defined types in pgsql? Background

I am using pgsql to build database tables, and have historically defined the tables in static arrays (eg ARRAY[ROW(a,b,...)::user_defined_type,...]). With the upgrade to PostgreSQL 11.2, I’d like to manipulate dynamic arrays (specifically, to generate new arrays with a number of elements determined at runtime).

The Problem

When trying to build the arrays, I have tried to use array_append, array_cat, and || (for both array-to-array and array-to-element concatenation), as documented in §9.17. While it works with built-in types, it does not work with the user-defined types (created using CREATE TYPE). The error is:

No [function | operator] matches the given name and argument types. You might need to add explicit type casts.

However, since the function definitions of the aforementioned array functions use anyarray and anyelement as their parameters, I’m not certain why the array functions do not operate on the user-defined types (note that the error does show that an array of user-defined types appears where an anyarray parameter expected and the properly- cast user-defined type appears where an anyelement should appear).

Example

DB Fiddle here

Other Notes

I have tried to ask the PostgreSQL mail list, but the e-mail server doesn’t seem to like my e-mail address.

System

PostgreSQL v11.2

Fedora 29

array_append() works just fine if you use the correct syntax. Your fiddle doesn't work because you used “ instead of " and ‘ instead of '. The language for a function is an identifier and shouldn't be quoted to begin with.

String constants need to be enclosed in single quotes, not double quotes. The expression ROW("a","b")::udt would result in " column "a" does not exist" because "a" is an identifier. You need to use ROW('a','b')::udt to initialize a value of type udt

So the correct function would be:

CREATE OR REPLACE FUNCTION abc() RETURNS text AS $BODY$
DECLARE
  x udt;
  y udt[];
BEGIN
  x := ROW('a','b')::udt;
  return array_append(y,x);
END
$BODY$ LANGUAGE plpgsql
VOLATILE;

And select abc() returns:

abc
---------
{"(a,b)"}

Working fiddle


How can I get Performance Statistics to work in MySQL Workbench? I have a complex query that is performing in subsecond time on my laptop, while the same query takes 70 seconds on my production server under no load against an identical schema.

The EXPLAIN plan is similar enough to not warrant such a massive increase in execution time, and identical in terms of the number of rows returned at each step.

This query is going against a query only database, so it's not a result of not having analyzed table statistics either.

I installed MySQL Workbench which seems to be a really nice tool, especially considering the fact that it's free. However, this section in the explorer interface for my connection that is completely grayed out:

PERFORMANCE
  Dashboard
  Performance Reports
  Performance Schema Setup

I did add the following line to my /etc/my.cnf file and restart my server:

[mysqld]
performance_schema

And I followed the instructions in the MySQL Performance Schema Quick Start Guide and verified that this feature is indeed enabled.

I kicked off a job last night in order to have some statistics collected, but this morning only find:

Performance_schema_files_instances_lost    89804

while every other performance_schema-related variable is zero.

The error log is blank, so I have so idea how to find out how to make this work.

I am running MySQL 5.5.21, and MySQL Workbench 6.1 on Mac OSX Mavericks.

The MySQL Workbench Performance requires the connected database to be version 5.6+. Although 5.5 has most of the capability for it, the MySQL team didn't believe it was mature enough to allow it


Unable to delete SQL Server 2008 R2 login I had created a SQL Server login some days back. Now I need to delete the same. So I went into SQL Server Management Studio => Security => Logins and selected the login that I want to delete. It displays an informational message which says

  Deleting server logins does not delete the database users associated
  with the logins. To complete the process, delete the users in each
  database. It may be necessary to first transfer the ownership of
  schemas to new users.

Ignoring this message, when I confirm the delete process I am getting a Fatal Error which says

  Drop failed for Login 'iis_login'.  (Microsoft.SqlServer.Smo)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  This login is the owner of 1 job(s). You must delete or reassign these
  jobs before the login can be dropped. (Microsoft SQL Server, Error:
  15170)

How do I delete the login when there are jobs (according to the message above) owned by the login.

regards,
Nirvan.

If you have many jobs and do not know which job is owned by this login, this query will tell you the name of jobs owned by them:

SELECT j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN sys.syslogins AS l ON j.owner_sid = l.sid
WHERE l.name = 'loginYouWantToDelete'

Once you have found the job, you can delete it using: sp_delete_job: (From MSDN)

EXEC msdb.dbo.sp_delete_job
    @job_name = N'JobToDelete';


Confirmation on query optimization and performance understanding (seek and scan) I am following along with this video on SQL Server query optimization and I would like to check my understanding of the results I am seeing.

I am using SQL Server 2012 with the AdventureWorks 2012 database.

Testing the first three examples

Example one:

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE CAST(CONVERT(VARCHAR(10), Customer.ModifiedDate, 101) AS DATETIME) = '20081013'

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE Customer.ModifiedDate >= '20081013' AND Customer.ModifiedDate < '20081014'
GO

-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Example two:

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE DATEADD(YEAR, 6, Customer.ModifiedDate) < '20131231'

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE Customer.ModifiedDate < DATEADD(YEAR, -6, '20131231')
GO

-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Example three:

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE YEAR(Customer.ModifiedDate) = 2008

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE Customer.ModifiedDate >= '20081101' AND Customer.ModifiedDate < '20090101'
GO

-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Unlike the video, there is no difference between the number of logical reads between any of the queries as is. But, after playing around with the criteria and comparing results my reasoning for not seeing a difference comes down to

'In all the queries, all the records meet the criteria and so all of them have
 the same number of logical reads. When there is a predicate that excludes records
 from the result then you will see an improvement in the number of logical reads.
 This is due to SQL Server being able to utilize an index seek and thus only read
 the records that meat the criteria. If the indexed column is used in an expression
 than this will prevent SQL Server from being able to use an index seek.'

And this idea is also why I always see an improvement with the fourth example

SELECT COUNT(ProductId)
FROM Sales.SalesOrderDetail
WHERE ProductID=710

SELECT COUNT(ProductId)
FROM Sales.SalesOrderDetail
WHERE ProductID*2=1420
GO

-- Table 'SalesOrderDetail'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 276, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The first query in this example utilizes a seek but the second query in this has to scan and thus read more (all?) of the index.

Questions:

Is my understanding and explanation of what is happening correct?
Are there any occasions when an indexed column can be involved in expression and still have its index 'seeked'?
Does not having a clustered index on a table have any effect on the ability to use a seek?

To answer your questions:

Since you say 'all the records meet the criteria' I would advise doing tests on a larger set of rows where some rows are outside the criteria.
There are some circumstances where using an expression against an indexed field will result in the index being seeked, such as WHERE [MyField] LIKE 'SomeString%'.  However, using an expression directly in the field will result in the query not being sargable, and will induce an index scan.  For instance, WHERE LEFT([MyField], 1) = 'T'.
Not having a clustered index on a table (meaning the table is a heap) does not affect the ability for a particular SQL query to use a seek operation on an index.  Not having a clustered index on a table has its own set of issues that is way beyond the scope of this answer.  Check this blog post for more info on heaps vs clustered indexes.


Maximum Attachment Size in database mail Good Afternoon,

I am trying to send an attachment via database mail.

declare     @mail_recipients  VARCHAR(4000)  = 'abc@123.com'
          , @mail_subject     VARCHAR(500)   = 'subject'
          , @mail_body        VARCHAR(4000)  = ''
          , @mail_attachment  VARCHAR(500)   = ''
          , @sql_query        VARCHAR(4000)  = ''

..... Code to populate the query

EXEC msdb..sp_send_dbmail   @profile_name                 = {our profile name}
                          , @recipients                   = @mail_recipients
                          , @subject                      = @mail_subject
                          , @body                         = @mail_body
                          , @body_format                  = 'HTML'
                          , @query_result_header          = 1
                          , @query                        = @sql_query
                          , @attach_query_result_as_file  = 1
                          , @query_attachment_filename    = @mail_attachment
                          , @query_result_separator       = ','

I am getting the following error.

  Msg 22050, Level 16, State 1, Line 0
  File attachment or query results size exceeds allowable value of 10000000 bytes.

When I run this in SSMS, and SAVE-AS csv file, the file is only 9077 KB.  What would database mail be doing to make it think the file is so much bigger?

You will have to change the allowed Attachment file size by going to the following location:

Go to SQL Server Management Studio -->  Management -->  Database Mail

This will fire up the Configuration Wizard.

Click Next --> Select View or change system parameters --> click Next.

Now change the Maximum File Size (Bytes) property and click Next, then click Finish to save your changes.


Import data to existing schema from dmp file ORA-39083: Object type ALTER_PACKAGE_SPEC failed to create with error:
ORA-01775: looping chain of synonyms
Failing sql is:

ALTER PACKAGE "XZB"."P_IATA"   COMPILE SPECIFICATION     PLSQL_OPTIMIZE_LEVEL=  2    PLSQL_CODE_TYPE=  INTERPRETED
PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE' REUSE
SETTINGS TIMESTAMP '2015-07-24 05:12:12'

I get above error when I try to import schema which is already existed.

Check the documentation of the current database release

Oracle Database Error Messages
12c Release 2 (12.2)
E49883-13

In the documentation of the current release you will find only

  ORA-01775: looping chain of synonyms

This is not helpfull (The 11.2 documentation does not contain the message at all). In this case check the
documentation of the 9i release.

Oracle9i Database Error Messages
Release 2 (9.2)
Part Number A96525-01

It  may contain outdated but nevertheless useful information:

  ORA-01775 looping chain of synonyms

  Cause: Through a series of CREATE synonym statements, a synonym was defined
      that referred to itself. For example, the following definitions are circular:

CREATE SYNONYM s1 for s2
CREATE SYNONYM s2 for s3
CREATE SYNONYM s3 for s1

  Action: Change one synonym definition so that it applies to a base table or view and retry the operation.

So check if the package specification of XZB.P_IATA contains such a synonym.


Is it possible to have a temporary, on-the-fly PostgreSQL table which only ever lives in RAM? I know that PostgreSQL automatically uses RAM only for tables that are small enough. But I'm not just talking about the data inside the table, but about the entire table itself.
Basically, I've (poorly) re-implemented various database functionality in PHP arrays, for example when I fetch data from an API and want to sort it or "massage" it before displaying it in my control panel. In such a situation, it makes no sense (at least to me) to have an actual database table around which is only ever used for this, with temporary data. It would be much better if I could on-the-fly create a table which I fill up in RAM, sort and then fetch records from with normal PG SQL queries.
Is this a thing? It feels stupid that I have array structures and various functions that try to mimic SQL's "SORT BY".
Naturally, I'm not talking about executing CREATE TABLE, add the data to it, and then return it again to PHP, and then DROP TABLE. That would be ridiculously bad for performance.
If there is no way to do this, I'll just accept it, but it's something which I've often thought would make perfect sense.

You might be missing the obvious candidate: a TEMPORARY TABLE.
It lives in memory if enough RAM has been allocated with the temp_buffers setting. Only the current session can see it, no WAL is written for it (which is the main reason why it's faster) and it is dropped at the end of the session automatically.
If it must live past the current session, consider an UNLOGGED TABLE instead.
Be aware that temp tables are not covered by autovacuum. See:

Are regular VACUUM ANALYZE still recommended under 9.1?

But there are still entries in regular system catalogs, so it's not 100 % "in RAM".
Details in the manual for CREATE TABLE.


Does an empty entity make any sense in an entity-relationship diagram? I have an entity that has nothing else than a surrogate key as a primary key. I use it as an element made of a group of other elements
For example :

  TABLE SchoolClass (classNumber INTEGER PRIMARY KEY)

  TABLE Students (SudentName TEXT PRIMARY KEY, studentClass INTEGER
  FOREIGN KEY)

Does it make sense ? The School Class entity does not store any information, but it's still used to regroup all the students in the same class.

This is fine as long as all you need is an index for the class, though it may be very difficult to troubleshoot exactly what is meant by a particular record in either future develompent or by a user for any reason at all.

If you want to be able to define anything about the class, say a name or other defining attribute, you'd just want to add this to the table as well.

For example:

TABLE SchoolClass (classNumber INTEGER PRIMARY KEY, classDescription TEXT)


How implement a cancel attribute in a table? Which is the best way to implement a canceled attribute in a database table, provided that less than say 3 % of the rows are canceled.

For canceled data I want to store the additional information

by whom
when
why

In about 95 % of the queries I only want to select the non canceled rows.

Further I want to be able implement unique constraints to some columns for the not canceled columns.

What are the pros and cons of using NULL in some column as indicator for not canceled ?

I'm not sure I like NULL for lack of being cancelled. I think I'd rather have an Active flag that is 1 by default, and set to 0 when something is cancelled. Now your checks are simply WHERE active = 1 or WHERE active = 0, instead of dealing with all the OR IS NULL or OR IS NOT NULL checks, and a row can be inactive for other reasons other than "canceled" should that model ever mature into other reasons. You can use filtered indexes to make the queries of one type or the other slightly more efficient if you are using SQL Server 2008+. NULL implies unknown, and in the case where you only have two states, "not cancelled" is not unknown.

As for the who, why, when? Perhaps that is a separate table altogether, and can act like an audit log (including if entities are marked as cancelled, then made active again, then cancelled again, etc). It would just be something like:

CREATE TABLE dbo.<something>AuditLog
(
    key INT NOT NULL FOREIGN KEY REFERENCES dbo.<something>(key),
    action NVARCHAR(32) NOT NULL,
      -- perhaps a CHECK here or a foreign key to an
      -- action code table, e.g. 'made active, made inactive'
    who NVARCHAR(64) NOT NULL,
    when DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    why NVARCHAR(MAX)
);