How to allow for ampersand in XMLtable()? Starting with this Q&A, note that while this works:

with data as (
  select 'Hallöle sloven&#264;ina Hallöle sloven&#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b

… this fails (note the addition of "AT&T and AT & T" to the string):

with data as (
  select 'Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b

with error message:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)
Error at line 2
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

How to fix?

First crude attempt(s):

replace('Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina', ' & ', ' and ')

While this works (at least for "AT & T", it would be desirable to preserve the ampersand in the final output…

The error message clearly told you to use &amp;.

with data as (
  select 'Hallöle AT&amp;T and AT &amp; T sloven&#264;ina Hallöle sloven&#273;ina' str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b
;

STR
-------------------------------------------------------------------------
STR2
--------------------------------------------------------------------------------
Hallöle AT&amp;T and AT &amp; T sloven&#264;ina Hallöle sloven&#273;ina
Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina

Update (will not work for other special characters such as &gt;):

with data as (
  select regexp_replace('Hallöle AT&T and AT & T sloven&#264;ina Hallöle sloven&#273;ina', '(&)([^#])', '\1amp;\2') str from dual
)
select a.str, b.str2
from data a, xmltable( '/'
  passing xmltype( '<dat>' || a.str || '</dat>' )
  columns
   str2 varchar2(4000) path '/dat'
) b
;

STR
-------------------------------------------------------------------------
STR2
--------------------------------------------------------------------------------
Hallöle AT&amp;T and AT &amp; T sloven&#264;ina Hallöle sloven&#273;ina
Hallöle AT&T and AT & T slovenĈina Hallöle slovenđina


What is the correct name for "relational role"? If I want to describe a column as one of the following:

Surrogate key
Natural key
Foreign key
Attribute
etc.

then what do I call this list? Relational roles? I don't think "column type" quite cuts it due to confusion with "data type".

I don't believe there is a word for this exact thing. This is because the RDBMS design does not force you to define your field as being of one role or another. A column can serve multiple roles simultaneously if you want it to. Although in practice it is almost universally better to seperate the keys from the data in your design, it is not actually required.


Improve Connectivity To MySQL Databases Using Multiple Ports I have a Ubuntu server with multiple websites, each website connects to its own MySQL database - each of my sites have a number of users accessing/updating the MySQL databases.

Currently all my websites are utilizing the the default MySQL port 3306.

I know I'm able to separate the connection of each website so that is uses its own dedicated port to connect to it's MySQL database.

By doing this will this improve the speed of SQL queries? I'm guessing it will... What are other peoples thoughts on this?

Running MySQL on different ports wont make any difference in the Speed of your queries.
What you could do is have a look at your Indexes. If you have a WHERE statement, make sure you have in the following order =, !=, in, < / >, like.

If you use 'where' and your 'like' is before '=' your query will run longer.


Backup and Restore Database Method I have a production database. I am using FULL recovery model. I am taking regular FULL backup every week. I am planning to take LOG backup every day. Now, if my database crashes or failed to load due to a reason then what can I do? Restore the database backup and restore all the log backups or only the last log backup. Should I automate the FULL backup and LOG backup using Jobs? Any help will be appreciated

If your database is in the full recovery model, you should immediately start taking log backups along with the full backups. For simple backup recovery model, you will not need log backups. The frequency depends on your required SLA (RTO & RPO), more on it here.

Second, you should definitely be automating your backup tasks - both full and log backups (if you are taking log backups). There are hundreds of reasons that can justify automating the tasks, my favorite one is I don't have to run/repeat the task again in a well planned automation.

In order to automate your backups using SQL Server Agent jobs, you can either write your won scripts, test it and implement to suit your environment, or pick up one of the freely available scripts and modify to suit your need. My favorite is Ola Hallengren's free sets of scripts, which he publishes and updates on his website. These scripts have been around for a while, and are well tested in various SQL Server environments.

Restore

Assuming your database is in full recovery mode, and you have taken your full backups and log backups, following are a simplified steps for database recovery.

Perform the tail-of-the-log backup to backup with NORECOVERY
option. This is the log generated since the last log backup was
executed.
Restore the most recent full backup with NORECOVERY option.
Restore all the log backups since last full backup beginning the
first log backup done after the full backup to the most recent log
backup with NORECOVERY option.
Restore the tail-of-the-log backup with NORECOVERY option.
Recover the database with RECOVERY option.

Note: If differential backup is part of your backup plan, you may have to restore the most recent differential backup before you restore the log backups, if the most recent differential backup was taken after the most recent full backup.

The database recovery could be more complex depending on the physical layout of your database files, and your recovery objectives etc. You can learn more about backup and recovery in SQL Server book online. Aslo, Paul Randal has a series of blog post on backup and recovery on his Accidental DBA series.


Purged more than 100,000 records in table but size of database has not reduced I am using SQL Server 2008 R2 version running on Windows Server 2008 R2 Standard. My database size is around 207MB. As it contained 100's of thousands of records in a table, I decided to keep only the first 10000 records and delete the remaining so as to minimise the size of the database.

I deleted the 90000 records from the database and I also rebuilt the indexes:

 DELETE FROM toptrends
 WHERE HandleID NOT IN (SELECT TOP 10000 HandleID
                        FROM toptrends
                        ORDER BY lastmodifieddatetime DESC)
 go

 ALTER INDEX ALL ON TopTrends
 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
          STATISTICS_NORECOMPUTE = ON);
 GO

and checked the size of the database and database_log files. The database_log file size has increased in size but the database file remained the same. I thought it should decrease

file size before deletion : around 207892
file size after deletion : around 207892(same)
size of database_log file 625MB (up from some 300MB)

Can't we reduce the size of the database by purging unwanted/old records from the table and rebuilding the indexes?

p.s : My database_log file has increased dramatically after purging the table and I don't want that too going large.

When you're faced with a database being too large, for whatever reason (growth is more than what you expected, you're running out of disk space, you run out of buffer pool space in memory) there's a few questions you probably want to ask yourself before taking action:

What happened to cause the database to be too large?
Where exactly am I seeing problems?
What steps can I take now to alleviate my immediate space concern?
What steps do I need to take to keep this from happening again in the future?

A lot of times, growth is to be expected if people are using the system that adds data to the database. This is, after all, the purpose of a database. However, understanding the root cause will be key in addressing the issue long-term, so doing that work up front will save time later. This may mean you need to take a look at use patterns using DMVs or other mechanisms.

The next thing you'll want to determine is where you're seeing the problem. If you've separated out your data from log drives you should be able to easily tell where you're seeing growth. If it's in the log file, figure out how you can better address the log file growth (more frequent log file backups?). If it's the database, your options become a bit more plentiful. If it's the Temp db, figure out what is causing temp db growth.

Once you determine what caused the data growth, you can take steps to determine how to immediately deal with the problem and address it long term. Deleting data out of a database can be a pretty drastic step if you are running with logging on (and you should be). For one thing, the log file will grow because it has to keep information about the deletes in there, until you're able to back up the log file. For another thing, as you noticed, SQL Server won't automatically adjust the size of the files when you remove data. From a long-term point of view, you can assume that if the database ended up a certain size through normal usage it will get there again... so spending time shrinking and growing the files would be wasteful.

To immediately address the issue there are a couple of things you can do:

If you are absolutely sure the database won't just grow again, you can shrink your database. You won't want to do this because the database is probably going to grow again, and it's not a long term solution that helps address the actual problem.
You can enable Page or Row compression to make the data smaller, if you aren't experiencing CPU pressure. The general trade off is that while Row and Page compression reduce size on disk (and therefore memory usage) it takes CPU cycles to compress. You also need to make sure that new objects added to the database are compressed.
You can attempt to limit the amount of data being entered into the database.

By not only pinpointing the cause but determining a course of action for the long term, you'll be better prepared for the future and may even save yourself a panic.


Finally got a hook of it by using MAX (CASE WHEN.. END) instead of IF

SELECT tbl_days.id,tbl_days.date_day,
       MAX(CASE WHEN tbl_bookings.slot_id =1 THEN 'Booked' ELSE 'Available' END) '07:00 to 08:00',
       MAX(CASE WHEN tbl_bookings.slot_id =2 THEN 'Booked' ELSE 'Available' END) '08:00 to 09:00',
       MAX(CASE WHEN tbl_bookings.slot_id =3 THEN 'Booked' ELSE 'Available' END) '09:00 to 10:00',
       MAX(CASE WHEN tbl_bookings.slot_id =4 THEN 'Booked' ELSE 'Available' END) '10:00 to 11:00'
FROM tbl_days
LEFT JOIN tbl_bookings
     ON tbl_bookings.day_date = tbl_days.id
LEFT JOIN tbl_slots
     ON tbl_slots.id = tbl_bookings.slot_id
LEFT JOIN tbl_rooms
     ON tbl_rooms.id = tbl_bookings.room_id
GROUP BY tbl_days.id;


Difference between DROP and TRUNCATE What does the TRUNCATE do differently to drop?

I believe it deletes all the data in the table but keeps the table name in the database, where as DROP deletes all data and the table. Is this correct ?

DELETE - DATA MANIPULATION LANGUAGE(DML)

The DELETE statement in any RDBMS is considered a DML statement.  Also known as CRUD (Create, Read, Update, Delete), this kind of statement is intended to manipulate data in a database without affecting the underlying structure of the objects.  What this means in practical term is:

A DELETE statement can be fine-tuned using a predicate via either WHERE or JOIN to delete some or all rows in a table.
A DELETE statement will be logged by the database and can be rolled back within a transaction if the statement fails.
Typically a DELETE will take row level locks on the data it is deleted, though this could escalate higher as necessary.
Because of the transactional overhead, DELETE can be "slow" (this is relative), but safer because it is fine-grained.

TRUNCATE - DATA DEFINITION LANGUAGE(DDL)

TRUCNATE is considered a DDL statement, meaning that it is intended to alter how objects are defined in a database.  Usually, DDL statements are CREATE, ALTER, or DROP, but TRUNCATE serves a particular purpose, that of "resettting" a table by removing all the rows.  The methods of this differ between the RDBMS engines and I would recommend looking at the specifics of MySQL. The practical implications of a TRUNCATE are:

TRUNCATE can not be fine grained.  If successful, it will remove all the rows from your table.
TRUNCATE is typically not logged.  This varies by RDBMS and I would suggest you looks more specifically at how MySQL handles it. (Hint, it varies by version.)
TRUNCATE requires a table metadata lock to execute.  How this is actually implemented can be specific to RDBMS, but essentially the TRUNCATE process must prevent other processes from mucking with the table in order to execute its DDL.
Because it is (typically) not logged and does not use predicates, a TRUNCATE will be faster than a DELETE, but less safe.

DROP TABLE - DATA DEFINITION LANGUAGE(DDL)

DROP TABLE goes further than a TRUNCATE in that it actually removes the table from the database completely.  This includes removing all associated objects, such as indexes and constraints.  If you dropped a table, not only would you remove all the data, but the structure as well.  This will usually be done when a table is no longer needed.  The primary concern is that since a DROP is DDL, you typically can't undo it.  Some RDBMS engines will allow you to wrap DDL within a transaction so you can roll it back, but this is not considered best practice and should be avoided.


Database failover/failback history I am a SQL DBA newbie, and I am trying to get the failover/failback history for one of our mirrored servers. I am able to get the the failover history from event viewer but do not know how to the get the failback history. Your help is greatly appreciated!

The following is a script I wrote for displaying the failover and failback history:

SELECT d.name, t3.time, (1 - t3.role) as status
FROM (
    SELECT t1.database_id, t1.time, t1.role
    FROM
        (
            SELECT RANK() OVER (ORDER BY database_id, time) AS rec, database_id, role, time
            FROM msdb.dbo.dbm_monitor_data dbmd
        ) as t1
        INNER JOIN
        (
            SELECT RANK() OVER (ORDER BY database_id, time) AS rec, database_id, role, time
            FROM msdb.dbo.dbm_monitor_data dbmd
        ) t2 ON T1.database_id = T2.database_id and t1.rec = (t2.rec - 1) AND t1.role <> t2.role
    ) t3
    INNER JOIN sys.databases d ON t3.database_id = d.database_id
ORDER BY d.name, t3.time

I believe Role = 0 is PRIMARY.


Double Transactional Replication (e.g. Two report servers) Is it a good plan to use SQL Server Transactional Replication from server one to server two then another from server two to server three?  Most of us DBAs here are advise against it.  The boss wants it.  He does not want to have 2 subscriptions from the production server.  Are two subscriptions from the production server a big hit?

Is it a good plan to use SQL Server Transactional Replication from
  server one to server two then another from server two to server three?
  Most of us DBAs here are advise against it.

I have done this before because that was the best design we could come with to meet business requirements.  Biggest issues with this design, if you are to make any changes to any publication/article you will first have to

Remove the subscription from server two to server three
Make changes in publication/article in server one
Reinitialize and resync between server one and server two
Once sync is completed, add back subscription from server two to server three
Reinitialize and resync between server two and server three (There is a way to avoid this step. See this question and answer)

  Are two subscriptions from the production server a big hit?

Depends on two things:

Location of the distribution database
Push vs Pull subscription

If you are using the same publication for all subscribers (two in your case) the workload on publisher database/server does not increase provided your distribution database is not in the same server as publisher databases. Log reader will read the publication database's transaction log once and add the replicated transactions in the distribution database. Distribution agent will read transactions from the distribution database and apply in the subscription databases.


Get related columns depending on a ID I have a table with a m:n (many to many) relationship. Basically, if I select an article_id, I want to return all the other articles that have the same tag_id as the selected article_id.

| article_id | tag_id |
|------------|--------|
| 1          | 2      |
| 1          | 11     |
| 2          | 5      |
| 4          | 6      |
| 4          | 11     |
| 12         | 9      |
| 14         | 9      |
| 14         | 15     |
| 14         | 17     |
| 18         | 1      |
| 20         | 8      |
| 41         | 13     |
| 49         | 9      |
| 50         | 12     |
| 60         | 18     |
| 75         | 9      |
| 75         | 10     |
| 75         | 11     |

So, if i select article_id 75 i need to output all of the articles that contains tag_id 9, 10 and 11

| article_id | tag_id |
|------------|--------|
| 1          | 11     |
| 4          | 11     |
| 12         | 9      |
| 14         | 9      |
| 49         | 9      |

I've created the following query and it looks like it works, but i want to make sure. Is there a better way?

SELECT aj2.article_id, aj2.tag_id
FROM article_tag_join aj
JOIN article_tag_join aj2 ON aj2.tag_id = aj.tag_id
WHERE aj.article_id = 75 AND aj2.article_id <> 75

You could change the query slightly so that the exclusion is done within the join:

SELECT DISTINCT aj2.article_id, aj2.tag_id
  FROM article_tag_join AS aj
  JOIN article_tag_join AS aj2
    ON aj.tag_id = aj2.tag_id
   AND aj.article_id <> aj2.article_id
 WHERE aj.article_id = 75;

This might offer better performance, but it is always worth comparing it against your existing query. If I have two queries that result in the same execution plan, I pick the one that is easiest to maintain.


Migrating master databases between clusters by detaching/attaching them As a migration strategy is it possible or feasible to detach a master database in a Microsoft SQL Server cluster and then attaching a different master database's files from another cluster?

The goal is to migrate a cluster from one server to a different one.

Both are SQL Server 2000.

SQLServer can't work without a master database at all as it stores essential configuration information in it (such as the table of databases in the system), so it wouldn't allow you to detach it. However, provided the SQLServers being the exact same version, you'll probably run into no problems if you stop the SQLServer services in both ends, copy the master database files from the one system to another (keep the old ones just in case), and start the services agan. It will be safer if neither server has any other databases in the system.


MySQL table creation insanely slow A simple table creation on one of my MySQL databases takes forever:

mysql> CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (16.58 sec)

The machine is quite idle:

01:21:26 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle
01:21:27 PM       all      0.50      0.00      0.21      0.00      0.00     99.29

Any ideas how to investigate this?

EDIT: Following DTest's advice, this is the execution profile:

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000044 |
| checking permissions | 0.000024 |
| creating table       | 8.668129 |
| After create         | 0.000014 |
| query end            | 0.000005 |
| freeing items        | 0.000028 |
| logging slow query   | 0.000004 |
| logging slow query   | 0.000206 |
| cleaning up          | 0.000006 |
+----------------------+----------+

I would turn on profiling to get an idea of what takes so long. An example using mysql's CLI:

SET profiling = 1;
CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
SET profiling = 1;

You should get a response something like this:

mysql> SHOW PROFILES;
| Query_ID | Duration   | Query |
+----------+------------+-------------------------------------------------------------+
|        1 | 0.00913800 | CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY) |
+----------+------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000071 |
| checking permissions | 0.000007 |
| Opening tables       | 0.001698 |
| System lock          | 0.000043 |
| creating table       | 0.007260 |
| After create         | 0.000004 |
| query end            | 0.000004 |
| closing tables       | 0.000015 |
| freeing items        | 0.000031 |
| logging slow query   | 0.000002 |
| cleaning up          | 0.000003 |
+----------------------+----------+
11 rows in set (0.00 sec)


Is there a way to truncate table that has foreign a key? Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?

No, you either need to delete and re-create the keys, or wait for the delete and re-seed. Disabling the foreign key temporarily might make the delete faster, but it still won't allow a truncate.

ALTER TABLE [dbo].[tablename] NOCHECK CONSTRAINT ALL;

-- delete, reseed, etc.

ALTER TABLE [dbo].[tablename] WITH CHECK CHECK CONSTRAINT ALL;

Very easy to automate this by building dynamic SQL from the metadata tables, depending on exactly which table(s) you need to target. The above is just a sample to demonstrate how it is done for a single table. For example, this will do so for each table that is the target of a foreign key and has an IDENTITY column:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET NOCOUNT ON;';

;WITH s(t) AS
 (
   SELECT
     QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id))
     + '.' + QUOTENAME(OBJECT_NAME(referenced_object_id))
  FROM sys.foreign_keys AS k
  WHERE EXISTS
  (
    SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = k.referenced_object_id
  )
  GROUP BY referenced_object_id
)
SELECT @sql = @sql + N'
  ALTER TABLE ' + t + ' NOCHECK CONSTRAINT ALL;
  DELETE ' + t + ';
  DBCC CHECKIDENT(''' + t + ''', RESEED, 0) WITH NO_INFOMSGS;
  ALTER TABLE ' + t + 'WITH CHECK CHECK CONSTRAINT ALL;'
FROM s;

PRINT @sql;
-- EXEC sp_executesql @sql;

It may be that the output gets truncated, but this is just a limitation of PRINT (8K) - the actual command is complete.

PS this assumes SQL Server 2005 or better. Always useful to specify your version as part of your question (usually with a tag).


Pivot rows into multiple columns I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:

╔══════════╦══════════╗
║ PersonID ║ OptionID ║
╠══════════╬══════════╣
║        1 ║ A        ║
║        1 ║ B        ║
║        2 ║ C        ║
║        3 ║ B        ║
║        4 ║ A        ║
║        4 ║ C        ║
╚══════════╩══════════╝

I need to pivot that data so the results are:

╔══════════╦═════════╦══════════╦══════════╗
║ PersonID ║ OptionA ║ Option B ║ Option C ║
╠══════════╬═════════╬══════════╬══════════╣
║        1 ║       1 ║        1 ║          ║
║        2 ║         ║          ║        1 ║
║        3 ║         ║        1 ║          ║
║        4 ║       1 ║          ║        1 ║
╚══════════╩═════════╩══════════╩══════════╝

Any suggestions?

There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.

Aggregate /Case version:

select personid,
  max(case when optionid = 'A' then 1 else 0 end) OptionA,
  max(case when optionid = 'B' then 1 else 0 end) OptionB,
  max(case when optionid = 'C' then 1 else 0 end) OptionC
from PersonOptions
group by personid
order by personid;

See SQL Fiddle with Demo

Static Pivot:

select *
from
(
  select personid, optionid
  from PersonOptions
) src
pivot
(
  count(optionid)
  for optionid in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv
order by personid

See SQL Fiddle with Demo

Dynamic Version:

The two versions above work great if you have a known number of values, but if your values are unknown, then you will want to implement dynamic sql and in Oracle you can use a procedure:

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select personid ';

    begin
        for x in (select distinct OptionID from PersonOptions order by 1)
        loop
            sql_query := sql_query ||
                ' , min(case when OptionID = '''||x.OptionID||''' then 1 else null end) as Option_'||x.OptionID;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from PersonOptions group by personid order by personid';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then you return the results, you will use:

variable x refcursor
exec dynamic_pivot_po(:x)
print x

The results are the same with all versions:

| PERSONID | OPTIONA | OPTIONB | OPTIONC |
------------------------------------------
|        1 |       1 |       1 |       0 |
|        2 |       0 |       0 |       1 |
|        3 |       0 |       1 |       0 |
|        4 |       1 |       0 |       1 |


Multiple charsets and Collations for a Multinational Database After decades of business speaking with companies that adhere to the general Latin1 Collation, my company is facing the issue of storing information in a different charset and collation: Greek.
So, it's time to start thinking a sort of redesign of our dbs.

Given that my installation is a MS SQLServer 2008 R2, what are the best methods, or general accepted guide lines, to do such a thing?
Multiple tables? Multiple Dbs with different settings?

I'm not a DBA, I'm only asking to have a starting point to mumble upon.

Thank you very much for reading and to all who will care to reply.

If the data is the same (same tables/columns) and the only change is that now there is the possibility of more languages being used, I'd say go for UTF-8. Force it as the default charset on all the table definitions (new ones at least), and set it in your config to be used for client connections that don't specify a charset.

You have not specified what DB type you are using (MySQL, SQL Server…etc) so I am trying to be as general as can be :)


Configure MariaDB PAM authentication, and enabling remote login from Toad I am currently trying to integrate Linux services into my Windows environment and have installed MariaDB with PAM authentication following the guidance from MariaDB.
I have tested successfully on the box (using putty) that I can authenticate using my AD credentials, however when I try to login via Toad I get the following error:
Client does not support authentication protocol requested by server; consider upgrading MariaDB client

I have tried adding the line pam-use-cleartext-plugin to the conf file and restarting the server but no joy.
Does anyone have any suggestions on how to resolve such that remote logins with Toad (and other clients) using AD credentials works?
Update
I have downloaded HeidiSQL and confirmed that its use of libmysql.dll means that it can use the PAM authentication.  It looks like toad for MySQL utilises myodbc.dll

I have downloaded HeidiSQL and confirmed that its use of libmysql.dll means that it can use the PAM authentication. It looks like toad for MySQL utilises myodbc.dll which cannot handle PAM authentication


Mongodb incremental backups I was given the task to set up incremental backups for MongoDB replicaset, as start point, of course, I googled it and could not find anything on MongoDB docs, I did find however this question on Stack Overflow, which encouraged to develop my own solution as didn't find Tayra very active.
I read about oplog and realized it was very easy to develop something to replay the log, but it turns out that I didn't have to as mongorestore does that for me.
Now I have a working solution with bash scripts and it was quite easy, that's the reason I am asking here if there is any flaw in my logic, or maybe something that will bite me in the future.
Below how I implemented that:
Full backup procedure

lock writes on a secondary member db.fsyncLock()

Take snapshot

Record last position from oplog
db.oplog.rs.find().sort({$natural:-1}).limit(1).next().ts

Unlock writes db.fsyncUnlock()

Incremental backup procedure

lock writes on a secondary member

Dump oplog from the recorded oplog position on full (or latest incremental ) backup:
mongodump --host <secondary> -d local -c oplog.rs -o /mnt/mongo-test_backup/1
    --query '{ "ts" : { $gt :  Timestamp(1437725201, 50) } }'

Record latest oplog position (same way as for full backups)

Unlock writes

Full backup restore procedure

stop all instances of mongod
copy snapshot to data dir of the box which will be the primary, but make sure to exclude all local* and mongod.lock  this restore technique is called reconfigure by breaking mirror
Start primary
reconfigure replicaset
start secondaries without any data, let them perform the initial sync. Or copy the data from the new primary with fresh local database

Restore incremental backup
When we created incremental backup it stored it like this:
/mnt/mongo-test_backup/1/local/oplog.rs.bson
/mnt/mongo-test_backup/1/local/oplog.rs.metadata.json

We're instered on oplog.rs.bson but we will have to rename it, so here are the steps:

change directory to the backup: cd /mnt/mongo-test_backup/1/local

delete the json file rm *.json

rename the bson file mv oplog.rs.bson oplog.bson

restore it:
 mongorestore -h <primary> --port <port> --oplogReplay /mnt/mongo-test_backup/1/local

I have it all scripted, I may commit it on GitHub later.
The question is if there is any flaw in the logic? I am a bit suspicious as the procedure is quite straight forward and still I couldn't find it documented anywhere.

To answer your question. No! There is no fail on your logic and it should work without problems. However, if LVM snapshots can be used, it's better way to do backups.


Access Oracle GTT table data from another session? I have using Oracle GTT tables with condition ON COMMIT DELETE ROWS.

Now i know GTT tables data is only session specific. 1 session cannot see the GTT data of other session in General.

But is their any way to access/read GTT table that belongs to another session?

No that is not possible, only session accessing the GTT can see it. IF you wish, you can use a physical table and model it as if it s a GTT. It isn't difficult, but isn't very efficient either.


How can I interpret the results of these DMVs to help me evaluate our partitioning strategy? Version: SQL Server 2008 R2 Enterprise Edtn. (10.50.4000)

In attempt to evaluate our partitioning strategy, I wrote this query to get the access methods against indexes on partitions (in the broadest sense of the term, though I'm eliminating heaps). As I narrow my focus to partitioned tables, I believe I need to be looking at range_scan_count and singleton_lookup_count but am having a hard time conceptualizing.

SELECT
    t.name AS table_name,
    i.name AS index_name,
    ios.partition_number,
    leaf_insert_count,
    leaf_delete_count,
    leaf_update_count,
    leaf_ghost_count,
    range_scan_count,
    singleton_lookup_count,
    page_latch_wait_count ,
    page_latch_wait_in_ms,
    row_lock_count ,
    page_lock_count,
    row_lock_wait_in_ms ,
    page_lock_wait_in_ms,
    page_io_latch_wait_count ,
    page_io_latch_wait_in_ms
FROM sys.dm_db_partition_stats ps
    JOIN sys.tables t
        ON ps.object_id = t.object_id
    JOIN sys.schemas s
        ON t.schema_id = s.schema_id
    JOIN sys.indexes i
        ON t.object_id = i.object_id
    AND ps.index_id = i.index_id
OUTER APPLY sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
WHERE
    ps.object_id = ios.object_id
    AND ps.index_id = ios.index_id
    AND ps.partition_number = ios.partition_number
    and ps.index_id = ios.index_id
    and ps.partition_number = ios.partition_number
    and s.name <> 'sys'
    and ps.index_id <> 0 ;

Relevant output (given the gap in SO's formatting of tables, this is a sample of the first 9 columns from the query above with last two columns being range_scan_count and singleton_lookup_count, respectively):

╔════════╦═════════════════╦════╦═══╦═══╦═══╦═══╦════════╦══════════╗
║ datetb ║ idx_datetb_col  ║  1 ║ 0 ║ 0 ║ 0 ║ 0 ║ 205740 ║  3486408 ║
║ datetb ║ idx_datetb_col  ║  2 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  1079649 ║
║ datetb ║ idx_datetb_col  ║  3 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  1174547 ║
║ datetb ║ idx_datetb_col  ║  4 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  2952991 ║
║ datetb ║ idx_datetb_col  ║  5 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3974886 ║
║ datetb ║ idx_datetb_col  ║  6 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  2931450 ║
║ datetb ║ idx_datetb_col  ║  7 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3316960 ║
║ datetb ║ idx_datetb_col  ║  8 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3393439 ║
║ datetb ║ idx_datetb_col  ║  9 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3735495 ║
║ datetb ║ idx_datetb_col  ║ 10 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  4803804 ║
║ datetb ║ idx_datetb_col  ║ 11 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  7655091 ║
║ datetb ║ idx_datetb_col  ║ 12 ║ 1 ║ 0 ║ 0 ║ 0 ║ 174326 ║ 47377226 ║
╚════════╩═════════════════╩════╩═══╩═══╩═══╩═══╩════════╩══════════╝

I see a few different possibility but I need some direction on how to think about this (of course I'm couching this in "may" because I know that "it depends", but I'm also looking for conceptual comprehension):

Similar values for all partitions of range_scan_count may indicate that we're not getting good partition elimination because we're scanning all partitions roughly the same number of times.
Varying values for all partitions of singleton_lookup_count accompanied with significantly lower values for range_scan_count may indicate good frequent partition elimination because we're scanning less than we're seeking.
?

Those are my thoughts so far. I was hoping to have someone weigh in on how I could use this, or another set of information, to determine which tables would most likely benefit from dropping partitioning in favor of indexes altogether.

EDIT

Here's a clipped DDL:

CREATE TABLE [dbo].[date_table](
    [date_id] [int] NOT NULL,
    [calendar_date] [datetime] NULL,
    [valdate] [datetime] NULL,
        CONSTRAINT [PK_datedb] PRIMARY KEY CLUSTERED
        (
            [date_id] ASC
        ) ON [partschm]([date_id]);

CREATE UNIQUE NONCLUSTERED INDEX [idx_datetb_col] ON [dbo].[date_table]
(
    [calendar_date] DESC,
    [date_id] ASC
) ON [partschm]([date_id])
GO

Rather than looking at index utilization, I'd look at the plan cache to find your queries with the highest amounts of logical reads. Usually when I'm dealing with partitioning, I find just a handful of queries that are dominating reads - like 50-80% of the servers' reads overall. Check those queries to see if they're successfully doing partition elimination.

If they aren't doing partition elimination, but you think they should (based on your partition scheme), then work with the query writers to get partition elimination.

If they aren't eliminating partitions, and they can't (because of the way the query is written or the partition is designed), then it's time to start asking tough questions.

If the biggest logical read queries don't have anything to do with your partitioned table, then move on and focus on those other queries instead.


Multi event Trigger syntax I have a db2 LUW 10.5 database.

I want to create a trigger that saves a copy of a record into an audit table whenever, a record gets updated and inserted.

I came up with follwing syntax:

CREATE TRIGGER TR_TRANSACTION_MESSAGE_CREATE_HIST
AFTER INSERT OR UPDATE ON TRANSACTION_MESSAGE REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO ....
END@

I get following error message when creating the trigger:

  An unexpected token "OR UPDATE" was found following "".  Expected
  tokens may include:  "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53

However, if I create it as an insert only trigger i can create the trigger succeesful. The same is true when creating an update only trigger.

Any idea what is wrong with the syntax?

According to the manual, "If multiple events are specified, the triggered action must be a compound SQL (compiled) statement (SQLSTATE 42601)".

BEGIN ATOMIC indicates an inlined compound SQL statement. You'll have to replace it with BEGIN to make it compiled.

It might be worth noting the difference between the inlined and compound SQL in case of a trigger. An inlined compound statement becomes part of the triggering statement execution plan, while the compiled compound statement is invoked much like a stored procedure would be, with the associated overhead. Subsequently, triggers with inlined statements might perform a bit better, which may become noticeable on a heavily loaded system.


Synchronising databases / database technologies I need some advice before we engage in some costly development work

We have two SQL Server database servers on the same network. One runs an in house database and the other a proprietary database that we bought in.

We want a couple of tables between the two to be synchronised (customer details / contact details)

Now if I were doing this I would create a trigger on a table in database A that when updated calls a stored procedure that updates a table in database B. And vice versa to keep the records in synch with an appropriate flag that activates the trigger.

The creators of the proprietary database that we need to interface with have proposed creating a network service that picks up changes in our database and extracts the data back to their own. And the transfer can only be one way by their service because of 'record locking'. Without making any assumptions is this really the best way to do things?

Thanks

NB both databases are running on SQL SERVER 2005

I strongly recommend they look at transactional replication. I recommend against triggers simply because they rely on the network connection and the other server being up. There are potentially some other solutions depending on whether you care about replicating the whole database or whether you can afford to have a delay in synchronization.

If you only need to update the secondary system once a day, then you could set up very simple log shipping. You simply restore a copy of the full database over top of the existing copy once a night. This will kick out active users.

For more flexibility (e.g. to only include certain tables, and to minimize disruption to uses) you could write your own methodology, which I've used in a few solutions. First, create two additional schemas in the destination database:

CREATE SCHEMA hold AUTHORIZATION dbo;
GO
CREATE SCHEMA cache AUTHORIZATION dbo;
GO

Then create a copy of each dbo table in the cache schema:

CREATE TABLE cache.TableName
(
  Columns...
);
GO

Don't forget indexes but be mindful of foreign keys if there are any in the database that point at this table.

Now however often you think this data needs to be refreshed, you can run a job that does the following - puts a fresh copy of the source table data into the "hidden" copy of the table:

TRUNCATE TABLE cache.TableName;

INSERT cache.TableName(columns) EXEC LinkedServer.database.dbo.StoredProcedure;

Where the remote Stored Procedure just does a standard SELECT columns FROM dbo.TableName; on the source server. Doing it this way allows you to keep the query logic, isolation level and other factors on the source and out of the distributed query scaffolding.

Now, the magic sauce, we can use a fast, metadata operation that should result in minimal blocking and disruption to end users. This is basically playing switcheroo with the active data.

BEGIN TRANSACTION;
  ALTER SCHEMA hold  TRANSFER dbo.TableName;
  ALTER SCHEMA dbo   TRANSFER cache.TableName;
  ALTER SCHEMA cache TRANSFER hold.TableName;
COMMIT TRANSACTION;

After this you can optionally truncate the cache copy, however I always kept it until the next switcheroo for troubleshooting purposes (and easy rollback).

The nice thing about this one is that it is very easy to run this as frequently or infrequently as you like, and since 99% of the work is being done against tables the application and users aren't looking at, there is very little impact to end users.


Docker-to-docker Mysql connection I have a 2 docker setup - one container with a MysqlDB on it and another with a web server. Both are on the same machine.

The db container has it 3306 port exposed though the hosts 3306 port (argument -p 3306:3306). This container is reachable (I can log into the mysql database) both through the host machine and outside, but for some reason, the web server container fails to connect to it.

I have ssh on the container with the web server. When I run:

nmap -p 3306 host.machine.ip.address

on it, I get:

PORT     STATE    SERVICE
3306/tcp filtered mysql

and when I actually try to log in I get:

mysql -h host.machine.ip.address -u root -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'host.machine.ip.address' (113)

Can somebody point me to where the issue might be?

First of all are the containers visible to one another i.e. can you ping the mysql box from the webserver?

If not it is a visibility issue, try creating a docker network to put the two containers within.

First create a docker network:

docker network create lamp-network

You can see this by the following command:

docker network ls

Now start your two containers with the additional flag:

--net lamp-network

Now try pinging and seeing if you have connection, if so then MySQL should work, although depending on setup you may have to comment out the bind-address from the config.


Updating nested rows I'm trying with this code:

DELIMITER //
CREATE TRIGGER myTrigger AFTER UPDATE ON cityies
FOR EACH ROW
BEGIN
 if NEW.visitors <=> OLD.visitors THEN
set visit_time = now()
END IF;
END;//
DELIMITER ;

I'm getting this error message:

  unknown system variable visit_time

Note: visit_time has DATETIME type.

There are two problems

PROBLEM #1

The visit_time needs to be prefixed with NEW.

PROBLEM #2

It needs to be a BEFORE UPDATE, not AFTER UPDATE because you cannot change values in an AFTER trigger.

Your code should read

DELIMITER //
CREATE TRIGGER myTrigger BEFORE UPDATE ON cityies
FOR EACH ROW
BEGIN
    IF NEW.visitors <> OLD.visitors THEN
        SET NEW.visit_time = now();
    END IF;
END
//
DELIMITER ;


Creating a table from variable names Is it possible to create a table from a variable name? For example I have the following stored procedure that I'm trying to write but it will not compile past with the create statement.

CREATE DEFINER = 'sabretooth'@'%' PROCEDURE `sp_archive_sabretooth`(
    IN vhost INTEGER(11),
    IN record_age DATETIME,
    IN delete_records BOOLEAN
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i_exist BOOLEAN DEFAULT FALSE;
    DECLARE table_to_create, backup_table_name, database_name VARCHAR(255);
    DECLARE backup_tables_cursor CURSOR FOR
            SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE 'vhost%'
                             OR
                             INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid')
                         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'sabretooth%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    INSERT INTO sabretooth.debug (message) VALUES (CONCAT('Initializing sp_archive_sabretooth; vhost = ', vhost, '; record age = ', record_age, '; delete_records = ', delete_records));

    SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
      INTO database_name
      FROM INFORMATION_SCHEMA.SCHEMATA
     WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'sabretooth%'
       AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'sabretooth_archive';

      OPEN backup_tables_cursor;

    create_tables_loop: LOOP
        FETCH backup_tables_cursor INTO table_to_create;
        IF done THEN
            LEAVE create_tables_loop;
        END IF;
        CREATE DATABASE IF NOT EXISTS sabretooth_archive;

        SELECT CONCAT(database_name, '.', table_to_create,'_archive') INTO backup_table_name;

        INSERT INTO sabretooth.debug (message) VALUES (CONCAT('I am here 38 backup_table_name = ',backup_table_name));

        CREATE TABLE CONCAT('sabretooth_archive.',table_to_create) LIKE CONCAT(database_name,'.',table_to_create);
    END LOOP;
END

What you are looking for is a concept called dynamic sql. To do this, you are going to have to use prepared statements. Lucky for you, MySQL permits prepared statements in stored procedures, but not stored functions or triggers.

Also, lucky for you, MySQL permits prepared statements on CREATE TABLE commands.

So you are going to have to prepare the CREATE TABLE something like this:

SET @SQL = CONCAT('CREATE TABLE ',CONCAT('sabretooth_archive.',table_to_create),' LIKE ',CONCAT(database_name,'.',table_to_create));

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


PSQL incomplete backup : how to debug I've been doing backups of my database " screen ".

after connecting to PSQL and typing \l+

I'm getting (among other things):

       Name       | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description
------------------+-------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------
           screen | admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 36 GB   | pg_default |

The size of my database being around 36GB.

Now i usually make regular backups doing :

pg_dumb screen > screenbackup.bak

And the size of the output was always pretty consistent with the size of my database.

But today i got a backup of only 8gb and that seems really odd to me.

After restoring into a temporary db and performing a few query there indeed seems to be a few missing data.

The size of the restored data was 10GB..

Thankfully I never dropped the original in the first place but there always seems to be a problem. I can't get pg_dump or pg_dumpall to backup the complete DB. The size is inconsistent when in my experience it's often at least the same size of the db or Bigger. not 4 time smaller..

Do you have any idea where to go from there to know where the problem is coming from?

Edit: maybe it's important: I'm on PSQL 9.4.4.1 and trying to do the backup so I can update Postgres in addition to just saving my data.

Did you get any error messages on dump, or on restore?  If not, then the most likely explanation is that nothing is wrong.  There is no tight coupling between the size of the database and the size of the dump.  For example, if you deleted a bunch of data and did not do a "Vacuum full", then the database itself is unlikely to shrink, but the dump (and the restore of it) will be smaller.


Changed max_allowed_packet and still receiving 'Packet Too Large' error I use mysqldump to create a flat file for backup purposes.  I have used this file to recreate the database on an alternate server.  I ran the import process through ssh on the command line and I received multiple Packet too Large errors.

I restarted mysql with a much larger max_allowed_packet (i.e.- 1000M) and still received the error.  I even attempted setting the max_allowed_packet in the import file, still received the error.

Is there a way to ensure the max_allowed_packet is set and/or use mysqldump that will create a file that does not cause this problem?

For reference:

the uncompressed mysqldump file is ~2GB

database type is INNODB

The first I thought about was what max_allowed_packet actually controls. Here is what I found:

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Given that definition of max_allowed_packet, I then discovered something else from ServerFault: innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones

Keeping these two things in mind, I would increase innodb_log_file_size in /etc/my.cnf to the max size allowed for it, 2047M. This of course requires the following

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

This will accommodate any big blobs you may have in your data.


The mapped logins are removed when database is restored Whenever I restore a database all my logins associated with that database are removed. How can I avoid this ?

Logins reside in master database and users reside in non system databases. Logins does not come with user database backup.  There is an exception for contained database, in which case there is no login mapped to user. Everything you need is contained in the user database as mentioned by @Nic and @Sepupic in comment section.

Read this for more details:

Basic SQL Server Security concepts – logins, users, and principals by Laurentiu Cristofor

Follow the process described here to copy logins to destination server.

How to transfer logins and passwords between instances of SQL Server

You can also use PowerShell function to copy logins.

COPY-DBALOGIN


SQL Server - Uniqueness Of The Transaction ID For auditing purposes, I need to get the unique transaction id from inside the current transaction. I will log the mapping between this transaction id and the application specific session id in a table. Then when looking at the transaction logs, I can find out which application session is responsible for a change in a row.

But it seems that transaction id is not unique for the database and is reset to 0 after a server restart. Querying sys.dm_tran_current_transaction proves so.

So, how can I get such a unique id for a transaction in the transaction logs? Or should I use SQL server audit? Is there a way to query the audit id from inside the transaction?

So, how can I get such a unique id for a transaction in the transaction logs?

Stop trying to use the transaction log for these types of situations. Take any notion you have of using the log for data mining and defenestrate it.

  Or should I use SQL server audit? Is there a way to query the audit id from inside the transaction?

Judging by your other questions, I don't think SQL Audit would work for you. Most likely you're going to need to change your application and build in auditing to gather what you're looking for due to the complex requirements you are looking for.

The application could be changed to generate its' own transaction id with the id of the web user, etc., and log that to an auditing area. That's, however, up to the application but would get you want you want. Attempting to continue to mine transaction log data will at best cause you throughput issues and at worse crash/suspect your database.


MongoDB problems recovering a member of the replica set I have a sharded database with 2 replica sets (RS1 and RS2) each one of the RSs with 2 servers. I had a problem yesterday with one member of the RS2, the mongod instance crashed throwing an error. After that I tried to recover the member making it sync with the other member of the replica set (it took a long time to finish the sync) and then I'm getting the same error again:

Tue May  7 12:37:57.023 [rsSync]   Fatal Assertion 16233
0xdcf361 0xd8f0d3 0xc03b0f 0xc21811 0xc218ad 0xc21b7c 0xe17cb9 0x7f57205f2851 0x7f571f99811d
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0xdcf361]
 /usr/bin/mongod(_ZN5mongo13fassertFailedEi+0xa3) [0xd8f0d3]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl17syncDoInitialSyncEv+0x6f) [0xc03b0f]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl11_syncThreadEv+0x71) [0xc21811]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl10syncThreadEv+0x2d) [0xc218ad]
 /usr/bin/mongod(_ZN5mongo15startSyncThreadEv+0x6c) [0xc21b7c]
 /usr/bin/mongod() [0xe17cb9]
 /lib64/libpthread.so.0(+0x7851) [0x7f57205f2851]
 /lib64/libc.so.6(clone+0x6d) [0x7f571f99811d]
Tue May  7 12:37:57.155 [rsSync]

***aborting after fassert() failure

Tue May  7 12:37:57.155 Got signal: 6 (Aborted).

Tue May  7 12:37:57.159 Backtrace:
0xdcf361 0x6cf729 0x7f571f8e2920 0x7f571f8e28a5 0x7f571f8e4085 0xd8f10e 0xc03b0f 0xc21811 0xc218ad 0xc21b7c 0xe17cb9 0x7f57205f2851 0x7f571f99811d
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0xdcf361]
 /usr/bin/mongod(_ZN5mongo10abruptQuitEi+0x399) [0x6cf729]
 /lib64/libc.so.6(+0x32920) [0x7f571f8e2920]
 /lib64/libc.so.6(gsignal+0x35) [0x7f571f8e28a5]
 /lib64/libc.so.6(abort+0x175) [0x7f571f8e4085]
 /usr/bin/mongod(_ZN5mongo13fassertFailedEi+0xde) [0xd8f10e]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl17syncDoInitialSyncEv+0x6f) [0xc03b0f]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl11_syncThreadEv+0x71) [0xc21811]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl10syncThreadEv+0x2d) [0xc218ad]
 /usr/bin/mongod(_ZN5mongo15startSyncThreadEv+0x6c) [0xc21b7c]
 /usr/bin/mongod() [0xe17cb9]
 /lib64/libpthread.so.0(+0x7851) [0x7f57205f2851]
 /lib64/libc.so.6(clone+0x6d) [0x7f571f99811d]

Any idea of why this may be happening? How can I make this server sync and work? My last surviving server is now running as secondary, is there a way to make it primary for a while to get the data out of it?

Thanks in advance!

You currently have a member running in secondary, because it cannot form a majority.  This is why you should always have an odd number of nodes in a replica set (one can be an arbiter) and I would recommend adding a third node as soon as possible once you get things back to normal.

In terms of how to get the second node up and running, do the following:

Shut down the remaining Secondary server (not strictly necessary, since it is read only as a Secondary, but safer)
Now, copy the whole data directory (everything in dbpath) over to the other host
Restart both nodes

One of the advantages to replica sets (over classic master/slave) is that they are intended to be functionally identical to each other, so you can simply use the data from one "good" node to seed any other "bad" node.


Convert int to RGB or hex color I'm working with a database where colors are stored as integers. For some unknown reason, they are negative integers actually.

Now, I need to use them in the CSS background-color property, which accepts color names, RGB colors or hexadecimal colors. The problem is I don't know how to convert them correctly to get an acceptable string value.

I tried converting -65280 with CONVERT(VARBINARY(8), abs(S.Color)) and I'm obtaining 0x0000FF00. What I need as output, however, is either #00ff00 or rgb(0,255,0). How can I achieve that?

Eithers of these 3 should work:

SELECT color
    , HEX_1 = '#'
        + CONVERT(varchar(6),
            CAST(ABS(color) as varbinary(1))
            + CAST(ABS(color/256) as varbinary(1))
            + CAST(ABS(color/256/256) as varbinary(1))
        , 2)
    , HEX_2 = '#'+
        +CONVERT(varchar(2), CAST(ABS(color) as varbinary(1)), 2)
        +CONVERT(varchar(2), CAST(ABS(color/256) as varbinary(1)), 2)
        +CONVERT(varchar(2), CAST(ABS(color/256/256) as varbinary(1)), 2)
    , RGB = 'rgb('
        + CAST(ABS(color)%256 as varchar(3)) + ','
        + CAST(ABS(color/256)%256 as varchar(3)) + ','
        + CAST(ABS(color/256/256)%256 as varchar(3)) + ')'
FROM (
    values
        (-65280)
        , (-65535)
        , (-460293)
        , (-13606962)
        , (-3678732)
) as colors(color)

Output:

color       | HEX_1     | HEX_2     | RGB
-65280      | #00FF00   | #00FF00   | rgb(0,255,0)
-65535      | #FFFF00   | #FFFF00   | rgb(255,255,0)
-460293     | #050607   | #050607   | rgb(5,6,7)
-13606962   | #32A0CF   | #32A0CF   | rgb(50,160,207)
-3678732    | #0C2238   | #0C2238   | rgb(12,34,56)

The CONVERT with style 2 requires SQL Server 2008 or later.

The expression could be used in a computed column definition, or inline table-valued function.


Why is full text index falling behind? I have a full-text index set up on a table; the table is under fairly constant load, receiving ~50k inserts and ~35k deletes daily, with rather short (up to 5 minutes) gaps between inserts.
The index is configured for automatic change tracking, and is usually able to process all documents within seconds from insertion. However, yesterday monitoring alerted that the index was not updated for more than 4 hours.
After receiving that alert, I checked the following:

No new entries were appearing in full text log. The last entry was informational - full-text auto population completed.
Index population status (as reported by sys.dm_fts_index_population) was stuck on "Has stopped processing".
A single session running command "FT BATCH CMPLETE" was using up one entire CPU core. That session's last_request_start_time was within seconds of the last entry in full text log. Other than that the CPUs were idle.
Two (out of 30) fragments had status 6 / Being used for merge input and ready for query (as reported by sys.fulltext_index_fragments); the size of these fragments was close to 60GB each.

After roughly 10 hours, indexing resumed just as mysteriously as it stopped.

Am I right in thinking that indexing was paused because of the merge? If not, what else can I check in order to get a better diagnosis?

The issue occurs roughly twice a month on different servers. As far as I can tell, the index is not reorganized or rebuilt on a schedule. I'm looking for a solution that would allow me to either avoid indexing pauses completely, or schedule them during maintenance downtime. The server is SQL Server 2012 Enterprise.

There seems to be a few queries you can try to get more information out of what is happening. The below query that was pulled from Stackoverflow takes advantage of FULLTEXTCATALOGPROPERTY.

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'FTS_Demo_Catalog'

SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs AS cat

However, In this article, microsoft mentions

  it is usually a better option to check the corresponding
  PopulateStatus property at the table level,
  TableFullTextPopulateStatus in the OBJECTPROPERTYEX system function.
  This and other new full-text properties in OBJECTPROPERTYEX provide
  more granular information about full-text indexing tables.

To this, I would use the following script:

DECLARE @TableId VARCHAR(MAX)
SET @TableId = OBJECT_ID('gGastroversion')

Select CASE OBJECTPROPERTYEX ( @TableId , 'TableFullTextPopulateStatus' )
            WHEN 0 THEN 'Idle'
            WHEN 1 THEN 'Full Population In Progress'
            WHEN 2 THEN 'Full population is in progress.'
            WHEN 3 THEN 'Propagation of tracked changes is in progress.'
            WHEN 4 THEN 'Background update index is in progress, such as autochange tracking.'
            WHEN 5 THEN 'Full-text indexing is throttled or paused.'
            WHEN 6 THEN 'An error has occurred. Examine the crawl log for details'
        END

Notice, that if you have number 6 as a result it will tell you to check the crawl log. Troubleshooting this information can be found here.


Need database of categories for products and services Need to develop a database schema that associates products and services into industry-standard categories.  I know that both the schema and data are out there, yet I'm not finding it.

There are databases that list products (and contain millions of records), but I'm not finding a good database that lists corresponding categories.  For example, individual cell phones might be in a "cell phone" category which, in turn might be in the "consumer electronics" category.  What would be good sources of these types of categories?

Classification is a really tricky thing -- a company that specializes in cell phones might have many categories of cell phones, while ones that don't might stop at a broader category.  Even within a given company, they might have multiple ways of looking at things, and so have multiple classification schemes / taxonomies / thesauri / ontologies / knowledge organization systems in use.

Just like any database, you have to know what sort of questions you're going to ask of your classification scheme, and from there, you can design what's going to fit it best.

A few registries / repositories / lists that I know about :

Taxonomy Warehouse
TaxoBank
JISC Directory of Metadata Vocabularies
TemaTres Vocabulary Server
Controlled Vocabulary Examples

You might also check to see if whatever organizations there are for your industry have any standard classification scheme / thesauri / ontologies / whatever.

Update : I don't know if the Global Product Classification might help or not.


Speed of MySQL query for the most recent 800 entries in chronological order I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24x7). My column names are:

ip_address varchar(16),
status varchar(7),
timestamp datetime

Been running it for several weeks and it has grown (10,581,421+ rows). I run a query to return the most recent 800 results on a specific IP address:

SELECT *
FROM (
    SELECT Device_ip, Status, timestamp
    FROM ping_results
    where Device_ip = '192.168.1.1'
    order by timestamp desc
    LIMIT 800
) SUB ORDER BY timestamp asc;

It takes 10+ seconds to return the results. Is there anything I can do differently to speed this up?

You could break out of that subquery like this:

SELECT Device_ip, Status, timestamp
    FROM ping_results
    WHERE Device_ip = '192.168.1.1'
    AND timestamp > DATE_SUB(NOW(), INTERVAL 800 MINUTE )
    ORDER BY timestamp ASC

That should simplify the query plan a bit, and you're only doing one ORDER BY instead of two.

As everyone else also mentioned, indexes are a good idea for improving performance, and will probably do more than manipulating the query.


Handling multiple primary keys I have multiple tables, most of them have integer primary key. Is it a bad practice If I put all tables to identity(1,1) ?

I have thinking about this to be able to distinguish:

identity(1000,1) for the users table
identity(100000,1) for the employees
identity(20000,1) for leave_request
for leave_given_id (in leave_given table), It's not going to be automatic increment but I'll take the leave_request_id and add 2 to the left. (or 200000). In other words, I will have 220001 in leave_given_id If leave_request_id is 20001

and so on..

Does it make sense to do that?

TLDR; firstly no it doesn't make sense to do that, secondly you can do that and the application wont care / notice either way

The main thing that you are fighting with confusing is human interaction, So as far as any application is concerned you should have the user_id, employee_id and a leave_request_id, any application when coded correctly will be able to identify the differences,

Looking at it, would it not be easier to have a person table (I'm presuming here that users are external to your employee list.

From here you could have a person and all their details in that table, with the identity field on it, and then if they are a user that entry is added into that table, if they are an employee they are entered into that table, (if both then both tables) and finally if an employee leaves they can be added into that table.

Now you have one id across all of your tables which makes joining them together a lot easier if you need to look at any users who are employees who have left you have a single ID across all tables, it also means that from a human perspective the ID you know what it all is in relation to because its all the same.


How can I use a default value in a Select query in PostgreSQL? I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?

E.g. something like this:

SELECT MAX(post_id) AS max_id DEFAULT 0 FROM my_table WHERE org_id = 3

And if there is no rows with org_id = 3 in the table I want to return 0.

SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3

or

SELECT case count(*) when 0 then 0 else MAX(post_id) end AS max_id
FROM my_table
WHERE org_id = 3;

if you want max(post_id) to be null when there is 1 row but post_id is null

dbfiddle


What is the Query to display the failed sql jobs What is the Query to display the failed sql jobs,so that I can get all the failed job information with one query

I don't think you will really get "all" the job information with one query since jobs can be configured to go to output files. Output files can at times get more information than is reported or written to the msdb tables.

However, the view that is found by looking at the job history via SSMS can be pulled with this query to return only failed jobs (e.g. if a job has 2 steps and the second failed this query will return both steps):

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0


Set Maximum Server Memory Higher Than What is Available to OS I have a server that is being repurposed into a SQL Server.  It came with more physical memory than the 32GB that can be seen by my OS (Windows Server 2008 R2).

Management would like me to configure the Maximum server memory used by SQL (SQL Server 2008 R2 Service Pack 3) to be higher than what is seen by the OS.  I can change it in the properties of SQL Server via SQL Server Management Studio.  Will SQL Server be able to utilize the additional RAM?  Or will it exhaust all memory made available to the OS?

Presumably you have the "Standard" edition of Windows Server 2008 R2.

You'll need to move to the Enterprise Edition (or DataCenter) of Window Server 2008 R2 to utilize more than 32GB, regardless of the "max server memory" setting in SQL Server.

Windows memory limitations can be seen on MSDN here.

Keep in mind, SQL Server also has memory limitations by edition.  SQL Server 2008 R2 Standard Edition limits memory to a maximum of 64GB.


Explain an OVER clause I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character.

If I am reading the query correctly, effectively, 3 CTEs are being used to prepare a table of 1 column containing 10,000 rows, each with the value '0'.

A fourth CTE is defined as follows:

cteTally(n) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
    FROM E4
)

Subsequently, this CTE is joined to a table containing a column with the strings of interest, with the following select:

SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))

That is, row number n, then the nth character in LastName, then the ascii value of that character.

My questions relate to the over clause in the CTE above.

Essentially, what exactly is it doing?

If we are querying row_number from 10,000 identical rows, why do we need an order by clause at all? Why is the order by put into an over clause rather than as an order by clause for the select statement - especially as the over clause isn't even specifying any partition? (I presume this means the window over which row_number operates is the full 10,000 rows?) And what does it mean to order by select null?

ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause.
If you try to write it without the ORDER BY you will get a syntax error.

SELECT ROW_NUMBER() OVER()
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 4112, Level 15, State 1, Line 1
-- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

The trick with the subquery was discovered by someone who blogged about it, as a performance optimization.
SQL Server always performs a sort operation since constants are not allowed for the ORDER BY clause:

SELECT ROW_NUMBER() OVER(ORDER BY NULL)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5309, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions
-- do not support constants as ORDER BY clause expressions.

And neither are integers which are treated as indices:

SELECT ROW_NUMBER() OVER(ORDER BY 1)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5308, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions
-- do not support integer indices as ORDER BY clause expressions.

Turns out that due to some glitch in the code, you can circumvent this limitation by using a subquery, which for some reason is allowed, and eliminates the sort operator.

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);

You can use any constant in the sub query, the NULL is probably a reminiscent of the habit of using SELECT NULL within EXISTS predicates, which in the early history of SQL Server had a performance impact as opposed to * or any other column expression, as the optimizer wasn't smart enough to ignore it.

HTH

UPDATE
@Erik-Darling commented that you can also circumvent it by using a computed expressions:

You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);


Table not getting created under dbo schema When creating tables in SSMS, I've realized that if you execute the following statement:

CREATE TABLE [tableName];

the table will be created under your own schema (and not dbo).  So in order to create it under the dbo schema, you'll need to explicitly say so, like this:

CREATE TABLE [dbo].[tableName];

Does anyone know of a way (eg. server-wide setting) so that when a table is created, the [dbo] part doesn't need to be specified?

The schema that will be used when schema is omitted will be the default schema of the database user.  Therefore, to have dbo schema have the created table without specifying, you'd have to set that database user's default schema to dbo.

Server-wide setting?  I don't think so.  But you could write a query to convert all database users in a particular database to their default schema changed to dbo if that's what you so choose.


What exactly does the last_value() window function do? While testing my answer to SQL - Getting data from first and last row of each group I noticed something strange.  The first_value and last_value window functions appear to behave differently.

As expected, first_value(col1) over (order by col2) finds the value of col1 from the row with the minimum value for col2.  But last_value(col1) over (order by col2) seems to find the value for the current row.  last_value seems unrelated to the group or partition it's operating on.

For the query below:

id  Session ID  bal
0   00000002    100
1   00000002    120
2   00000002    140
3   00000001    900
4   00000001    800
5   00000001    500

The expression:

last_value(bal) over (partition by [Session ID] order by id) as lv

Returns something different than:

first_value(bal) over (partition by [Session ID] order by id DESC) as fv_desc

Here's the result (note that the value of lv changes within one group):

lv       fv_desc
500      500
800      500
900      500
140      140
120      140
100      140

Example at SQL Fiddle with some extra columns.  Reversing the order by or omitting partition by doesn't seem to impact what last_value() returns.

If I read the MSDN page correctly, it suggests that last_value() should work as the opposite of first_value(), which is different from what I observe during testing.

Does last_value() do what it is supposed to do?  Why does last_value() allow you to specify a partition and/or order by clause when it doesn't seem to use either?

This happens because default window frame is range between unbounded preceding and current row, so the last_value() never looks beyond current row unless you change the frame.

From MSDN:

  If ORDER BY is not specified entire partition is used for a window
  frame. This applies only to functions that do not require ORDER BY
  clause. If ROWS/RANGE is not specified but ORDER BY is specified,
  RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for
  window frame. This applies only to functions that have can accept
  optional ROWS/RANGE specification. For example, ranking functions
  cannot accept ROWS/RANGE, therefore this window frame is not applied
  even though ORDER BY is present and ROWS/RANGE is not.

Since last_value() has an optional order by clause, its default window frame ends at the current row.  So with the default frame, no matter which partition or ordering you choose, last_value() returns the value from the current row.


sh.status() error processing query: ns=config.mongosTree if I run sh.status in mongos I get the error:

assert: command failed: {
        "ok" : 0,
        "errmsg" : "error processing query: ns=config.mongosTree: ping $gt new Date(1532712420740)\nSort: {}\nProj: {}\n No query solutions",
        "code" : 2
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
printShardingStatus@src/mongo/shell/utils_sh.js:611:13
sh.status@src/mongo/shell/utils_sh.js:78:5
@(shell):1:1

2018-07-27T11:28:05.644-0600 E QUERY    [thread1] Error: command failed: {
        "ok" : 0,
        "errmsg" : "error processing query: ns=config.mongosTree: ping $gt new Date(1532712420740)\nSort: {}\nProj: {}\n No query solutions",
        "code" : 2
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
printShardingStatus@src/mongo/shell/utils_sh.js:611:13
sh.status@src/mongo/shell/utils_sh.js:78:5

I reinstalled all mongo-enterprise packages but still the error persist, all the shards are working correctly, since I can perform find from mongos.

thanks

As I am able to see that you are getting the exit error code : 2

As MongoDB documented here The error code :2  signifies The specified options are in error or are incompatible with other options.

As per MongoDB documentation here The sh.status(), When run on a mongos instance, prints a formatted report of the sharding configuration and the information regarding existing chunks in a sharded cluster. The default behavior suppresses the detailed chunk information if the total number of chunks is greater than or equal to 20.

For further your ref here and here


Get multiple columns from a select subquery SELECT
   *,
   p.name AS name,
   p.image,
   p.price,
   (
       SELECT ps.price
       FROM product_special ps
       WHERE p.id = ps.id
         AND ps.date < NOW()
       ORDER BY ps.priority ASC, LIMIT 1
   ) AS special_price,
   (
       SELECT ps.date
       FROM product_special ps
       WHERE p.id = ps.id
         AND ps.date < NOW()
       ORDER BY ps.priority ASC, LIMIT 1
   ) AS date
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id)

As you can see I'm repeating the same subquery just to get another column out. I'm wondering is there a better way of doing this?

id is the primary key in both tables. I've no problem making product_special.priority unique if that can help.

Assuming combination product_special.id, product_special.priority is unique

 SELECT p.*, special_price,special_date
 FROM product p
 LEFT JOIN
 (
     SELECT ps.id, ps.price as special_price, ps.`date` as special_date
     FROM product_special ps
     INNER JOIN
     (
       SELECT id, MIN(priority) as min_priority
       FROM product_special
       GROUP BY id
     ) ps2
     ON (ps2.id = ps.id)
 )a ON (a.id=p.id)


Can Postgres raise warnings if joins are inconsistant with foreign keys? Like most relational databases, PostgreSQL provides foreign keys as a means of providing referential integrity

CREATE TABLE job (
  id serial NOT NULL PRIMARY KEY
)

CREATE TABLE jobtuning (
  id serial NOT NULL PRIMARY KEY,
  job_id integer REFERENCES job (id)
)

CREATE TABLE jobstep (
  id serial NOT NULL PRIMARY KEY,
  jobtuning_id integer REFERENCES jobtuning (id)
)

In a case such as this when an intermediate join table is used it becomes very easy to write a query which appears to work, but is wrong:

JOIN jobstep ON jobtuning_id=jobtuning.job_id

It should be:

JOIN jobstep js ON js.jobtuning_id=jobtuning.id

One of the strengths of SQL is the ability to form arbitrary relationships, but is it possible to ask Postgres to issue a warning when a join is written that is inconsistent with the way foreign keys or other constraints are defined?

No.  It is up to the developer/DBA to write sensible joins, in this regard PostgreSQL is just a slave that does what its master commands.

In any case, defining the boundary between joins that make sense and those that don't is not trivial.  If that column is there because of a denormalization, for example, then it perfectly makes sense in spite of a nonexistent foreign key.  You can always decrease the possibility for accidental misjoins by not naming the primary key column just id but jobstep_id, for example.

Also, when should it emit the warning?  When you hit it with a 1000 line SQL script, containing a hundred statements, out of which 20 contain 'inconsistent' joins?

All in all, this is rather a code review problem than anything else.


Pgbouncer server parameter I installed and configured pgbouncer 1.5.4 on Ubuntu 14.04 LTS and I have a hard time interpreting log messages.

Basically, I have seen many errors:

2015-12-10 18:09:16.835 30488 ERROR varcache_apply failed: ERROR: invalid value for parameter "TimeZone": "localtime"

I have googled and some articles told me the root cause is coming from timezone or encoding and date style.

I tried to implement this on pgbouncer.ini:

db-order= host=xxx.xxx.xxx.xxx port=4326 user=xxxx password=xxxx timezone='Asia/Jakarta' datestyle=ISO client_encoding=UNICODE

I then tried to reload the config but still no luck and I am still seeing the same error.

Is the config need to be restarted rather than reloading?

Timezone is a startup variable, you need to restart the pgbouncer service. pgbouncer.github.io/config.html

Wiki answer originally a comment by 3manuek


subquery inside a case statement I have a huge view -the view gathers information of players.
In a specific part the view assigns agents for players, those players are selected from table 'S' and are being assigned using a case statement.
I want to assign specific agents according to a aggregated value called total_deposit in table T.
so basically i want to say case make the agent name Andy when users from table S have max(total_deposit)within the conditions below (from table T) that is the code i tried:

        CASE
        WHEN s.Username = gs.username THEN gs.agent
    when exists (select t.username from temptest t
            where t.username = s.username
            group by t.username
             having max(t.total_deposit) >= 5000 or
                (    max(t.total_deposit) >= 1000 AND max(t.total_deposit) < 5000 )or
                      ( max(t.total_deposit) >= 500 AND max(t.total_deposit) < 1000) or
                      ( max(t.total_deposit) >= 250 AND max(t.total_deposit) < 500  )
            ) and s.Country in('Austria', 'Luxemburg', 'Switzerland', 'Lichtenstein')

                         then 'Andy'
else 'Joy'

It didn't work because if exists needs to follow with an expression..
how can i do this? thanks

If I understood you correctly, all you need is to calculate max(total_deposit) and join it to your main table, it can be done by using CTE or, if you wish, a subquery, like this:

declare @s table (username varchar(100));
insert into @s values ('peter'),('ann'), ('paul'), ('john');

declare @t table(username varchar(100), total_deposit int);
insert into @t
values  ('peter', 1000),('peter', 200),
        ('ann', 100),  ('ann', 150),
        ('paul', 300);

with tot_dep as
(select username, max(total_deposit) as max_d
 from @t
 group by username)

select s.username,
       case when t.max_d >= 500 then 'Andy'
            else 'Joy'
       end as agent
from @s s left join tot_dep t
        on s.username = t.username;

or (that is the same)

declare @s table (username varchar(100));
insert into @s values ('peter'),('ann'), ('paul'), ('john');

declare @t table(username varchar(100), total_deposit int);
insert into @t
values  ('peter', 1000),('peter', 200),
        ('ann', 100),  ('ann', 150),
        ('paul', 300);

select s.username,
       case when t.max_d >= 500 then 'Andy'
            else 'Joy'
       end as agent
from @s s left join  (select username, max(total_deposit) as max_d
                     from @t
                     group by username) t
        on s.username = t.username;


T-SQL Merge not updating in explicit transaction We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the inventory.gtin table but the update statement below it works and data is updated in the import.file_data table when the stored procedure is called in a batch job. But, when the stored procedure is executed manually, the merge works fine and updates/inserts data. Is this a bug in the merge statement?

DECLARE @targetVendorCode nvarchar(20)
DECLARE gtin_cursor CURSOR FOR

        SELECT DISTINCT aVendorCode
        FROM #fileData fd

    OPEN gtin_cursor
    FETCH NEXT FROM gtin_cursor INTO @targetVendorCode
    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRANSACTION;

    MERGE [inventory].[gtin] AS target
    USING (SELECT * FROM #fileData WHERE ngtin IS NOT NULL AND aVendorCode = @targetVendorCode ) AS source
    ON (target.[ngtin] = source.[ngtin])
    WHEN MATCHED AND ( ( source.createDateTime > COALESCE(target.fileCreateDateTime,'04/02/1982') ) OR source.[status] = 'override' ) THEN
        UPDATE SET
        [skuId] = source.[skuId],
        [lastUpdateFileId] = source.[fileId],
        [vendorCode] = COALESCE(source.[aVendorCode],source.[vendorCode]),
        [lastUpdateDateTime] = source.[lastUpdateDateTime],
        [fileCreateDateTime]= source.createDateTime
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (
                [skuId],
                [lastUpdateFileId],
                [vendorCode],
                [lastUpdateDateTime],
                [fileCreateDateTime]
                )
        VALUES (
                source.[skuId],
                source.[fileId],
                COALESCE(source.[aVendorCode],source.[vendorCode]),
                source.[lastUpdateDateTime],
                source.createDateTime
                )
            ;
        SELECT @action = '[{"type":"distribute","actionDateTime":"' + CONVERT(VARCHAR(19), GETUTCDATE(), 120) + '","insertDateTime":"' + CONVERT(VARCHAR(19), GETUTCDATE(), 120) + '","referenceId":' + TRY_CAST(@fileId AS NVARCHAR(20)) + '}]'

        UPDATE fd SET [status] = 'processed',[modifiedDateTime] = GETUTCDATE(),[modifiedUserName] = SYSTEM_USER FROM [import].[file_data] fd WHERE fileId = @fileId

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
    END

    FETCH NEXT FROM gtin_cursor INTO @targetVendorCode
    END

    CLOSE gtin_cursor
    DEALLOCATE gtin_cursor

You MERGE is probably failing due to permissions issues to access the [gtin] tabale. , and then proceeding to the UPDATE statement.

These won't solve access issues, but you won't get funky results:

Add TRY and CATCH blocks as appropriate for your needs (inside the cursor to skip just the offending item, or outside to fail the whole thing along with a ROLLBACK).

Or just add SET XACT_ABORT ON at the top to fail whole thing when there is an error, rolling back if in the transaction.


How do I get the current and next greater value in one select? I have a InnoDB table 'idtimes' (MySQL 5.0.22-log)
with columns

`id` int(11) NOT NULL,
`time` int(20) NOT NULL, [...]

with a compound unique key

UNIQUE KEY `id_time` (`id`,`time`)

so there can be multiple timestamps per id and multiple ids per timestamp.

I'm trying to set up a query where I get all entries plus the next greater time for each entry, if it exists, so it should return e.g.:

+-----+------------+------------+
| id  | time       | nexttime   |
+-----+------------+------------+
| 155 | 1300000000 | 1311111111 |
| 155 | 1311111111 | 1322222222 |
| 155 | 1322222222 |       NULL |
| 156 | 1312345678 | 1318765432 |
| 156 | 1318765432 |       NULL |
+-----+------------+------------+

Right now I am so far:

SELECT l.id, l.time, r.time FROM
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id
    WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;

but of course this returns all rows with r.time > l.time and not only the first one...

I guess I'll need a subselect like

SELECT outer.id, outer.time,
    (SELECT time FROM idtimes WHERE id = outer.id AND time > outer.time
        ORDER BY time ASC LIMIT 1)
    FROM idtimes AS outer ORDER BY outer.id ASC, outer.time ASC;

but I don't know how to refer to the current time (I know the above is not valid SQL).

How do I do this with a single query (and I'd prefer not to use @variables that depend on stepping though the table one row at a time and remembering the last value)?

Doing a JOIN is one thing you might need.

SELECT l.id, l.time, r.time FROM
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id

I suppose the outer join is deliberate, and you want to be getting nulls.  More on that later.

WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;

You only want the r. row that has the lowest (MIN) time that is higher than the l.time.  That is the place where you need subquerying.

WHERE r.time = (SELECT MIN(time) FROM idtimes r2 where r2.id = l.id AND r2.time > l.time)

Now to the nulls.  If "there is no next-higher time", then the SELECT MIN() will evaluate to null (or worse), and that itself never compares equal to anything, so your WHERE clause will never be satisfied, and the "highest time" for each ID, could never appear in the result set.

You solve it by eliminating your JOIN, and moving the scalar subquery into the SELECT list :

SELECT id, time,
    (SELECT MIN(time) FROM idtimes sub
        WHERE sub.id = main.id AND sub.time > main.time) as nxttime
  FROM idtimes AS main


SQL: TSQL to Find invalid views we have a Tuesday/Thursday Maint window for one application we support. On Tuesdays we modify one facility and thursday if there are no errors we roll all the changes out to the other facilities.  The application is 3ed party, and at times makes changes to the databases which render views invalid on our "reporting" server for the application.

Is there a way I can roll through all the views on my report server (There are about 70 of them) with TSQL to find the Binding Errors after Tuesdays Depolyment?

Errors I would like to find are:

Executed as user: <>. Statement(s) could not be prepared. [SQLSTATE 42000] (Error 8180)  Could not use view or function 'PAT.dbo.Reorders' because of binding errors. [SQLSTATE 42000] (Error 4413)  All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. [SQLSTATE 42000] (Error 205).  The step failed.

Is there a way to do this programatically, or do I just have to keep right clicking each view and trying to see if it comes back with data?

Thanks,

Something like the following should be good enough to get you started.  It should be quite lightweight as well.

DECLARE @Name       NVARCHAR( MAX ),
        @SQL        NVARCHAR( MAX );

DECLARE @t_BindingErrors TABLE
(
    ViewName        NVARCHAR( MAX ),
    ErrorMessage    NVARCHAR( MAX )
);

DECLARE c CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT  '[' + ss.name + '].[' + so.NAME + ']'
    FROM    sys.schemas ss
    INNER JOIN sys.objects so
        ON  ss.schema_id = so.schema_id
    WHERE   so.type = 'V';
OPEN    c;
FETCH NEXT FROM c
INTO    @Name;

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
    SET @SQL = '
        DECLARE @i  INTEGER;
        SELECT  TOP ( 0 ) @i = 1
        FROM    ' + @Name + ';';
    BEGIN TRY
        EXECUTE dbo.sp_executesql @statement = @SQL;
    END TRY BEGIN CATCH
        INSERT INTO @t_BindingErrors
        VALUES ( @Name, ERROR_MESSAGE() );
    END CATCH;

    FETCH NEXT FROM c
    INTO    @Name;
END;

CLOSE   c;
DEALLOCATE c;

SELECT  *
FROM    @t_BindingErrors;


Fetching Data from joining table for any one from condition satisfied I have two table called Activity And Claim. Activity Table contains Activity id and all other Activity Details. Claim Table contains all claim related information for the Activity. One activity have multiple claims and activity id is the foreign key for claim table.

In claim table i am maintaining different status(Approved, Deleted, rejected, Submitted) for each claim.

Currently i am facing one issue for fetching data from activity table if any one of the claim present in claim table and any one of the claim status not in Approved, rejected, submitted.

For an example if an Activity have two different claim and one claim is approved status and another claim in deleted status. So in my sql query should returns empty(because one claim already approved for activity). In another example if the activity have only one claim and status is deleted. This cause my query should fetch the Activity Details(because the activity have only one claim and it's on deleted status).

The query which i am currently using is below

SELECT *
    FROM myactivities_4
    LEFT JOIN claims2 ON claims2.activityid = myactivities_4.activityid
    and claim_status NOT IN ('Submitted', 'Approved','Rejected')

Could any one help me to resolve this issue

Try it:

SELECT *
FROM myactivities_4
LEFT JOIN claims2 ON claims2.activityid = myactivities_4.activityid
WHERE myactivities_4.activityid NOT IN (
    SELECT activityid
    FROM claims2
    WHERE claim_status NOT IN ('Submitted', 'Approved','Rejected')
)


BCNF decomposition excercise I have a relation R(A,B,C,G,H,I) with the functional dependencies (FDs for brevity) F = {A→B; A→C; C,G→H; C,G→I; B→H}. I want to decompose it into BCNF.

So, we have AG as candidate key. I consider the first FD A→B. I see that A is not a super key, so I determine its closure as ABCH and decompose it as R1(A,B,C,H) and R2(A,G,I).

Now, R1 satisfies the FD A→B, A→C, but violates B→H, so I decompose it further as R3(B,H)  and R4(A,B,C).

Now, my question is: How do I fit satisfy the other FD C,G→I and C,G→H in R2, as R2 does not contain C or H?

In the relation R2(A, G, I), since A, G is a candidate key of the original relation, a minimal cover of the dependencies is A, G -> I (there are no other non-trivial dependencies that hold in it).

Then, what happened to the dependencies C,G->I and C,G->H ? Well, they are lost in this decomposition (and if you try decomposing starting with dependencies different from A->B you can find that these or other dependencies are lost as well). From the practical point of view, this means that the constraints expressed by the lost dependencies cannot be enforced in the decomposed database.

On the other hand, it is a well known fact that the classical decomposition algorithms for  BCNF is not guaranteed to preserve the dependencies, differently from the synthesis algorithm for 3NF.


how can I count by week, month, and YTD with postgres? under Postgres 9.3 I am trying to run a query to summarize some data from table a:

| Entry date-Date | Customer ID-charvar | received-Boolean | Processed-Boolean | finalized-Boolean | billed-Date |

I would like use count(case when) to count all the records by the current week, month, then all of time into table b:

|report date | Total Received Weekly | Received Monthly | Received all of time | so on...

I am having a difficult time wrapping my head around how to write that query.  I have thought about extracting the week day and month number to determine my records to sum for each length of time but then how would I make it repeatable without having to change the dates so I could automate it to run weekly through either PgAgent or CRON?

Too long for a comment now: I don't speak PGSQL so try adapting the following for your date extraction functions:

SELECT
     MAX(CurrentWeekCount) As CurrentWeekCount
    ,MAX(CurrentMonthCount) As CurrentMonthCount
    ,MAX(AllCount) As AllCount
FROM
(

  count all the records by the current week

    SELECT
     COUNT(CustomerID) As CurrentWeekCount
    ,NULL As CurrentMonthCount
    ,NULL As AllCount
    FROM YourTable
    WHERE DATEPART(week, YourDateColumn) = DATEPART(week, GETDATE())

    UNION ALL

  month

SELECT
     NULL As CurrentWeekCount
    ,COUNT(CustomerID) As CurrentMonthCount
    ,NULL As AllCount
    FROM YourTable
    WHERE DATEPART(month, YourDateColumn) = DATEPART(month, GETDATE())

    UNION ALL

  all of time

    SELECT
     NULL As CurrentWeekCount
    ,NULL As CurrentMonthCount
    ,COUNT(CustomerID) As AllCount
    FROM YourTable
) x