Why 't' and 'f' instead of TRUE and FALSE Why does PostgreSQL return 't' and 'f' instead of TRUE and FALSE?

The documentation itself advises us to use TRUE and FALSE when inserting boolean values or making comparisons, but returns a different set of values when selecting the value from the database.

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

Is there a reason for this behavior? Have the PostgreSQL commmunity ever tried to change that?

PostgreSQL does not return these instead of boolean values.  It is some clients (for example, psql and pgAdminIII) which represents TRUE with t and FALSE with f - try the same query in another client and you will see something else.  See, for example, what DBVisualizer gives you:

I guess the reason for showing t and f is simply sparing space in a command-line client that lacks horizontal scrolling abilities.

Note: I am in no way affiliated to DbVis Software.


Need help in restore some rows into mySQL database frm myd myi I am using one mySQL database for several sections of the website and one of the section is not working, when I go to the link of that section it gives the error "1146 Error @ Line 39: Table 'database_name.table_name' doesn't exist"

I logged into PHPmyAdmin and in the list of raws the "table_name" is missing, seems like someone has hacked into it and dropped it.

I have got the following files from the backup server :

table_name.frm
table_name.myd
table_name.myi

I opened all these files into notepad to see the data, frm and myi is understandable may be because they are structures but myd is understandable as it has data.

Now I want to restore the lost data through these 3 files but I don't know how.

In order to restore the MyISAM called table_name into database_name you must do the following

STEP 01) Run this query : show variables like 'datadir';

This will tell you what the base directory is for data. For this example, let's use the default (/var/lib/mysql)

STEP 02) Copy table_name.frm to /var/lib/mysql/database

STEP 03) Copy table_name.MYD to /var/lib/mysql/database

STEP 04) Copy table_name.MYI to /var/lib/mysql/database

STEP 05) chown mysql:mysql /var/lib/mysql/database/table_name.*

That's it. A mysql restart is not needed because the information_schema database is very sensitive and detects folder changes very quickly.

To make sure the restored table is known to mysql, do the following:

STEP 06) Run this query

SELECT * FROM information_schema.tables
WHERE table_schema = 'database_name'
AND table_name = 'table_name'\G

You should see information about the table size and change dates

STEP 07) Run these commands

use database_name
show tables;

You should see the restored table in the listing


Postgres strategy for UPDATE SET x = NULL It is not clear the strategy Postgres takes when columns are set to NULL:
UPDATE tbl SET
  col1 = NULL,
  col2 = NULL
WHERE created < current_date - INTERVAL '1 year';

Documentation https://www.postgresql.org/docs/current/mvcc.html is a bit lengthy and techy so I cannot reliably deduce:
if setting to NULL is performed in place or affected rows/pages are copied?
Looks like any UPDATE should create new row for MVCC semantic but what if setting to NULL is a special case?
For GDPR conformance I think to null every personal historical data and I try to understand implications of massive periodic UPDATE SET x = NULL. Should I think of VACUUM after that?

PostgreSQL never performs an UPDATE by modifying the existing data in place.  If you set columns to NULL, a new row version will be created just as with any other UPDATE, and the previous row versions will remain until VACUUM reclaims them.
But be warned that

VACUUM will only delete the old row version if there is no long running transaction that still might need old data.
VACUUM will not overwrite the data, so the old value will still be on disk until the space is reused.

Concerning the GDPR, the wording is:

The data subject shall have the right to obtain from the controller the erasure of personal data concerning him or her without undue delay and the controller shall have the obligation to erase personal data without undue delay

The term “erasure” is nowhere defined in that law, so it is subject to interpretation. My bet is that few enough people understand the inner workings of PostgreSQL well enough to contest that DELETE is erasure. And it would take a data forensics expert with advanced PostgreSQL knowledge to retrieve such data. Once VACUUM has run, it is nigh impossible to do that. If I were called to court as an expert witness, I would say that anybody who has run DELETE in the database has taken all possible steps to erase the data.
If you feel paranoid, schedule a regular VACUUM on the table in question, and make sure that you have no long running transactions. Any worry beyond that is silly.


Query to join two subsets of table data (get it faster) TABLE1:

+-----+----------------------+------+-------+
| id  |        value         |weight|  stat |
+-----+----------------------+------+-------+
|  5  | OB_4772FB7E65        |    5 |     1 |
|  6  | OB_A339546708        |    6 |     1 |
|  7  | OB_6CA632C6BC        |    7 |     1 |
|  8  | OB_58757E6E9C        |    8 |     1 |
|  9  | OB_8FF337BE76        |    9 |     1 |
|  10 | OB_CCA8FEC422        |   10 |     1 |
+-----+----------------------+------+-------+

TABLE2 (it's a translation table, where there is always an entry with lang_id=5 and there may or may not be a translation in other languages (here I show only one another lang_id=10). The key keycode here is the same as value in TABLE1) :

+---------------+-----------------+--------+
| id  | lang_id  |    keycode     | teksts |
+---------------+-----------------+--------+
| 940 |  5       | OB_4772FB7E65  | suga1  |
| 941 |  10      | OB_4772FB7E65  | spec1  |
| 946 |  5       | OB_A339546708  | suga2  |
| 947 |  10      | OB_A339546708  | spec2  |
| 949 |  5       | OB_6CA632C6BC  | suga3  |
| 955 |  5       | OB_CCA8FEC422  | suga4  |
| 956 |  10      | OB_CCA8FEC422  | spec4  |
+---------------+-----------------+--------+

The needed result (get all keycodes with stat=1 and their translation or an empty string if there is no translation. The results must be sorted first by weight and then by basis language):

+----+----------------+-----------+
| id |   keycode      | teksts_ln |
+----+----------------+-----------+
|  5 | OB_4772FB7E65  | spec1     |
|  6 | OB_A339546708  | spec2     |
|  7 | OB_6CA632C6BC  |           |
| 10 | OB_CCA8FEC422  | spec4     |
+----+----------------+-----------+

I have managed to write a mysql query, it works ok but it is slow. Maybe I have overdone and it can be simplified?

SELECT glv.id, glv.keycode, gln.teksts_ln FROM
(SELECT t1.id, t1.weight, t1.value AS keycode, t2.teksts AS teksts_lv FROM TABLE1 AS t1 LEFT JOIN TABLE2 AS t2 ON t1.value = t2.keycode AND t2.lang_id=5 WHERE t1.stat="1") AS glv
INNER JOIN
(SELECT t1.value AS keycode, t2.teksts AS teksts_ln FROM TABLE1 AS t1 LEFT JOIN TABLE2 AS t2 ON t1.value = t2.keycode AND t2.lang_id=10 WHERE t1.stat="1") AS gln
ON glv.keycode = gln.keycode ORDER BY glv.weight ASC, glv.teksts_lv ASC;

My main concern is how to make the query work faster.

A few things that can improve your query:

There is no need for the derived tables (subqueries). You can have a 4-table join in the FROM clause.
Once we do the above, it's obvious that you join table1 to itself on a column that seems to be UNIQUE: (ON glv.keycode = gln.keycode). We can remove this join, so the result is a 3-table join.

The query:

SELECT
    t1.id,
    t1.value  AS keycode,
    ln.teksts AS teksts_ln
FROM
    table1 AS t1
    LEFT JOIN table2 AS lv
      ON t1.value = lv.keycode AND lv.lang_id = 5
    LEFT JOIN table2 AS ln
      ON t1.value = ln.keycode AND ln.lang_id = 10
WHERE
    t1.stat = '1'
ORDER BY
    t1.weight ASC, lv.teksts ASC ;

After we have it working correctly (producing the same results), you can improve efficiency by adding indexes:

on table2 (lang_id, keycode, teksts)
on table1 (stat)


Backup a huge database 200gb+ i have a mysql database where i need to take a backup of the log database.

the problem is the log database i over 200gb and contains over 280 millions of rows, i need right now to remove a kind of data but before i can do it i need to take a backup.

i have trying mysqldump function but the problem is here its lock everything and block the website there running right now, and i have trying to do it over ssh tunnel the problems is.

1) the database its contain over 200gb data
2) the internet line its between 20-40mbit internet in download
3) the web server's its pretty slow and i have calculate its take over 4-5 hours thats mean the store is down in thats time.

are there a easy and smart method to take a remotely backup of so larges database before i remove something in the log table?

If your big tables are innodb, you can use --single-transaction to not lock the table.

Other possibility is Percona XtraBackup

Or pt-archiver (again Percona)


T-SQL How does CHANGETABLE Have a Parameter with the Literal Table Name I can pass the literal table name into the built in function CHANGETABLE. I was wondering how this is done and if it is possible to do in my own stored procedures/inline functions.

In addition to that. If it is possible to pass it to a literal constant first and then pass it to the function so I can get the table ID and not have to specify the table name in two places.

Thanks in advance.

This is just special syntax, nothing more. There should be a hint for you in the fact that you are not passing just the table name but also need to put a keyword, either VERSION or CHANGES, before it. However, even without a keyword it would still be just a syntactic issue.

That is because CHANGETABLES is a built-in Transact-SQL function, part of the language. Of course, one might argue that user-defined functions are, in a way, extensions to the language. Even so, there is pre-defined syntax for such extensions and it does not include all the variety of built-in functions' syntax. For instance, you still cannot put an OVER clause after a UDF, as in window functions, or use AS as a delimiter between arguments, as in the CAST function, or enforce the argument to be a single string literal rather than an expression, as in OPENQUERY or OPENROWSET. In short, built-in functions' syntax cannot serve as an example of what you can do in user-defined functions.

The takeaway of the above is, names still cannot be parameterised in Transact-SQL without resorting to dynamic SQL.


Set in mySQL a predefined order for SELECT queries without using the ORDER BY clause I have a MySQL database and users access it through an old legacy software (OLS) the company bought years ago. OLS connects to MySQL through MySQL ODBC driver. There is no way to change OLS in recent future. The OLS is really slow when browsing records, so the idea is to provide OLS with records in the order users expect them i.e. the last created record, first. I have an auto incremental field, so when I order by desc the records, I get what I need. The problem is that OLS does not allow to manipulate query: the only thing it does is to ask for the whole table (I suppose it internally uses the query "SELECT * FROM xyz;").

I learned here that in SQL there is no "predefined" order if there is no order by clause.

So my question is: how can I tell MySQL "if you get a SELECT statement without ORDER BY clause, then ORDER the table desc using Auto ID field"?

If I can not force MySQL, but I have to act on the ODBC driver, that's is still ok for me.

Oh, by the way, sorry for any error i could have had, is my first question and I tried to stick to the rules.

Like you already found out, there is no (predefined) order in a result set unless you specify it in the ORDER BY clause.

Your only option would be, to rename the table and create a view on it.

RENAME TABLE your_table TO another_table_name;
CREATE VIEW your_table AS
SELECT * FROM another_table_name ORDER BY id DESC;

Test of course heavily if you run into problems, but I assume there shouldn't be any problems since the view would even be updatable and insertable like it says in this manual entry:

  For a view to be updatable, there must be a one-to-one relationship
  between the rows in the view and the rows in the underlying table.
  There are also certain other constructs that make a view nonupdatable.
  To be more specific, a view is not updatable if it contains any of the
  following:

  Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
  DISTINCT
  GROUP BY
  HAVING
  UNION or UNION ALL
  Subquery in the select list
  Certain joins (see additional join discussion later in this section)
  Reference to nonupdatable view in the FROM clause
  Subquery in the WHERE clause that refers to a table in the FROM clause
  Refers only to literal values (in this case, there is no underlying table to update)
  ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)
  Multiple references to any column of a base table


mongodb: replica-set processing reads on the primary Running a replica-set with 1 primary and 2 secondaries, I wonder why the READs are also done on the primary. Shouldn't the primary mainly do the WRITEs and the secondary can share the READ-load?!

I am using the PHP driver for the mongodb and sending the details for replica set. The PHP is running from the secondaries and in the script the intitial connection goes to the secondary itself which is localhost there.

The php documentation says, that the driver will find the primary and secondaries on its own as soon as it connects to any member of the replica-set, no matter if primary or secondary.

After checking the load and logs, I found out that currently my poor primary has to deal with all the READs and the slaves are bored.

I'm not sure what documentation you've read so apologies if I'm repeating anything here.

To distribute reads to secondary nodes, most drivers allow you to set a readPreference value for the current session. Clients set read preference on a per-connection basis. With slaveOk, the driver should will always send queries to the secondaries, if they're available.

Distributing reads to secondaries requires the use of
ReplicaSetConnection with ReadPreference.SECONDARY.

See “rs.slaveOk()” for more information and this link.

In the mongo shell, to enable secondary reads, issue the following command :

rs.slaveOk()

The PHP documentation for it is here but I'm guessing that may be the documentation you're referring to.

As a FYI, here's an old discussion about it on the MongoDB Google Group.

If you're still having issues, I'd recommend using the MongoDB Google Group and providing some further information such as the version of MongoDB you're using, the version of the PHP driver, your log files, rs.conf() and rs.status().

As a FYI, you have to be careful with read scaling as sending too many reads to the secondaries can often result in the secondaries lagging the primary and becoming stale, thus requiring a full resync.


"Stored Procedures Invoked/sec" counter always increasing We've implemented some feature using service broker and deployed recently on production. I am observing that "Stored Procedures Invoked/sec" counter is always increasing. However, I can see that other counters like Tasks Running etc are stable. Why Stored Procedures Invoked/sec not stick to a range?

There are several different types of performance counters, five of which are used by SQL Server (at least in terms of what you will find in the sys.dm_os_performance_counters DMV). You can see this via the following query:

SELECT DISTINCT [cntr_type]
FROM sys.dm_os_performance_counters
ORDER BY [cntr_type];

Returns:

65792
272696576
537003264
1073874176
1073939712

Those values mean:

Noncomputational Counter Types

65792 == PERF_COUNTER_LARGE_RAWCOUNT == "Raw counter value that does not require calculations, and represents one sample which is the last observed value only."

Counter Algorithm Counter Types

272696576 == PERF_COUNTER_BULK_COUNT == "Average number of operations completed during each second of the sample interval."
1073874176 == PERF_AVERAGE_BULK == "Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type."

Base Counter Types

1073939712 == PERF_LARGE_RAW_BASE == "Base value found in the calculation of PERF_RAW_FRACTION"

Basic Algorithm Counter Types

537003264 == PERF_LARGE_RAW_FRACTION == "Ratio of a subset to its set as a percentage. This counter type displays the current percentage only, not an average over time."

You can find more info in the following blog post: Interpreting the counter values from sys.dm_os_performance_counters

The Stored Procedures Invoked/sec counters in the SQLServer:Broker Activation category are of type 272696576 (i.e. "PERF_COUNTER_BULK_COUNT"). These values are cumulative. You need to capture a value into a variable, wait for N seconds (i.e. WAITFOR DELAY '00:00:30'; -- 30 seconds), capture a second value, and then divide the difference between the values by N (i.e. SELECT  (@var2 - @var1) / 30.0;). For example:

DECLARE @Sample1 BIGINT,
        @Sample2 BIGINT,
        @Time1 DATETIME,
        @Time2 DATETIME;

SELECT @Sample1 = [cntr_value],
       @Time1 = GETDATE()
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Databases' -- SQLServer:Broker Activation
AND   [counter_name] = N'Transactions/sec' -- Stored Procedures Invoked/sec
AND   [instance_name] = N'_Total';

WAITFOR DELAY '00:00:30.000'; -- 30 second pause/sleep

SELECT @Sample2 = [cntr_value],
       @Time2 = GETDATE()
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Databases' -- SQLServer:Broker Activation
AND   [counter_name] = N'Transactions/sec' -- Stored Procedures Invoked/sec
AND   [instance_name] = N'_Total';

SELECT (@Sample2 - @Sample1) AS [Difference],
       (DATEDIFF(MILLISECOND, @Time1, @Time2) / 1000.0) AS [Seconds],
       (@Sample2 - @Sample1) / (DATEDIFF(MILLISECOND, @Time1, @Time2) / 1000.0);


Problem generating restore scripts for multiple ola backups I am using Ola for maintenance solution. I have 300 databases I want restore those databases  using scripts rather than going through one by one.  I prepare the following script to generate restore script.

SELECT
   'RESTORE DATABASE [' + name + ']'  +'
    FROM DISK = ''C:\Backup3\PC1800\'+name+'\FULL\PC1800_'+name+'_FULL_20160504_162534.bak''' + '
    WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10
    GO'
FROM sys.databases

It generates restore scripts for all  databases .

For example for database AdventureWorks2008

RESTORE DATABASE [AdventureWorks2008]FROM DISK =
          'C:\Backup3\PC1800\AdventureWorks2008\FULL\PC1800_AdventureWorks2008_FULL_20160504_162534.bak'
        WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10

GO

Problem:

The problem it is generating _162534 for all databases. But the second part of the backup file name is different from database to database.

Is there  a way I can change the script so it will generate the right number from the backup file not 162534 for all databases?

Exp : PC1800_AdventureWorks2008R2_FULL_20160504_162538

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

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

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

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

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


Why is a SELECT grant needed for an UPDATE statement on a table? (Error 1143) myuser@'%' has UPDATE and INSERT grants on table1.

I tried to run this UPDATE:

UPDATE table1 SET column1 = 'blahblah' WHERE id = 123;

I got this error:

  SELECT command denied to user 'myuser'@'localhost' for column 'id' in table 'table1'

... and error code 1143.

Of course, I understand that the particular UPDATE I was trying to run needs to pick just the particular row I want, probably using the equivalent of a SELECT statement "under the hood" so to speak, to find the row identified by id = 123, but it doesn't seem right that I should need the SELECT grant when I'm not doing an explicit SELECT.

Can someone help me understand the logic that calls for a SELECT grant for this very common sort of UPDATE?

Whilst I can't find anything official to back this up (I will keep trying) I guess a user with UPDATE but not SELECT permissions could brute force their way into knowing the values in the table without having SELECT permissions

lets say the user runs the following query

UPDATE MyTable SET MyCol = 'Something' WHERE Id = 1

and it returns that it successfully updated a row

and then the user runs

UPDATE MyTable SET MyCol = 'Something' WHERE Id = 2

and so on (they could write a loop using front end or database code to try a range of Ids quickly)

This tells the user which values for Id exist in the table without them having SELECT permissions (and this example could be applied to other columns in the table)


What ISN'T included in SQL Server snapshot replication? When creating a publication in SQL Server 2012 I get the options to include articles of type:

tables
views
stored procedures
UDFs

Since this list didn't include user-defined types, I was expecting those NOT to be replicated. But happily they were! (i.e. user-defined table types were replicated)

So question: What CANNOT be replicated in SQL Server? Also, why WERE the types replicated? Do they count as 'tables'?

I know this might not be a full answer. But this is too long to put in a comment.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/publish-data-and-database-objects

Publishing User-Defined Types and Alias Data Types

  Columns that use user-defined types or alias data types are replicated
  to Subscribers like other columns. The CREATE TYPEstatement for each
  replicated type is executed at the Subscriber before the table is
  created. In the case of user-defined types, the associated assembly is
  also copied to each Subscriber. Changes to user-defined types and
  alias data types are not replicated to Subscribers.

  If a type is defined in a database, but it is not referenced in any
  columns when a publication is created, the type is not copied to
  Subscribers. If you subsequently create a column of that type in the
  database and want to replicate it, you must first manually copy the
  type (and the associated assembly for a user-defined type) to each
  Subscriber.

What CANNOT be replicated in SQL Server?

Anything not listed here cannot be replicated.


how to execute unix commands within nzsql? I'm converting a script from Teradata to Netezza, and I'm stuck at the portion where BTEQ uses the functionality of the OS to check if a file contains any data. If it does, it is deleted. I want to perform the same function in nzsql.

Can anyone please help?

The documentation for nzsql states that to shell-out you just have to use blackslash pling, followed by the command:

\! command

If you need to know how to test for file size in a shell script, you can use something similar to:

if [[ -f $file ]] && [[ -s $file ]]; then
  echo "File exists and is greater than 0 bytes" ;
  rm -f $file
fi

-f checks that it exists, -s checks if it's greater than 0 bytes.


"ERROR: timestamp out of range" converting epoch stored as bigint to a timestamp I'm getting ERROR:  timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table):

select to_timestamp(1527012834506374);
ERROR:  timestamp out of range: "1.52701e+15"

Other approaches to conversion don't work either:

select 1527012834506374::abstime::timestamp;
ERROR:  cannot cast type bigint to abstime

select 1527012834506374::integer::abstime::timestamp;
ERROR:  integer out of range

It's a valid epoch; https://www.epochconverter.com/ tells me 1527012834506374 is equivalent to 2018-05-22 06:13:54.506 UTC

How can I do the conversion using SQL in Postgres?

When I paste the value 1527012834506374 into  https://www.epochconverter.com/ I see the warning:

  Assuming that this timestamp is in microseconds (1/1,000,000 second):

Postgres' to_timestamp() assumes an epoch with seconds, not microseconds, so you need to use:

select to_timestamp(1527012834506374 / 1000000.0)


How to list files in oracle backupset I've backupset created with RMAN, and I want to see what this set contains. How to do that?

Try "List Backup", or perhaps "list backup summary"

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmreprt.htm#BRADV89586

and

http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta027.htm#RCMRF132


Permission denied to create "pg_catalog.tablename" during pg_dump targetting another machine I am trying to dump some tables from a 9.5 beta 2 to a 9.4.4 server. The form of the command I am using is the fairly standard:

pg_dump -t table dbname | psql -h hostname -d dbname

I am using the Postgres user, which I realize is probably not ideal, but as these are both dev data processing boxes that only I use and it is relevant to the error which follows. Initially, I got the error

  ERROR:  unrecognized configuration parameter "row_security"

which is expected as this feature is new in 9.5, and, yes, I realize that it is not advisable to pg_dump between different Postgres versions, but, sadly unavoidable, given that I have hit a very obscure bug, and need to move a large amount of static data once.

So, using this hideous hack to remove the row_security error, and turning on stop on first error:

pg_dump -t tablemame dbname |sed 's/SET row_security = off;//'|
psql -v ON_ERROR_STOP=1 -h hostname -d dbname

I now get:

  ERROR:  permission denied to create "pg_catalog.tablename"
  DETAIL:  System catalog modifications are currently disallowed

While it may be undesirable to use the Postgres user/role, my understanding is that there should be no permission issues of this nature. I am open to any suggestions bearing in mind that this is a one off operation and that these are data processing dev boxes not connected to any live services, so expediency preferred over ideal corporate data access policies. Having said that, it would be good to understand how to do this properly and avoid this in future.

This turns out to be due to a somewhat obscure error message which happens when the schema you are dumping from with pg_dump does not exist on the target database/machine. I will leave this question, in the hope it might help someone else.


How can I make this nested query more efficient? I have 3 tables: Room, Conference, and Participant.  Room has many Conferences, and Conference has many Participants.  I need my query to display the fields from Room, as well as the number of associated Conferences it has, and the sum of the number of associated Participants each Conference has.  Here's a cut-down version of the SELECT query I wrote to get this info; first, I just selected the room ID:

SELECT TOP(1000)
  rm.[Id]
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

This was very fast as SQL Server was able to just pull the data from Room and pretty much ignore the subquery (see Trial 1 - Trial 4 in image below).  Then I added in the ConferenceName field from the subquery, as well as a count of the number of conferences per room:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName])
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

This slowed down the query quite a bit, by a factor of about 100 (see Trial 5 - Trial 7 in image below).  I then added in the participant count from the subquery, meaning there were 2 levels of aggregate functions being used:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

This further slowed down the query by a factor of about 4 (see Trial 8 - Trial 10 in image below).  Here's the client statistics with data on the 10 trials:

Here's the query plan of the slow query: https://www.brentozar.com/pastetheplan/?id=SJpyeec5Q

Is there a way I can make this kind of query - where I calculate an aggregate of a subquery's aggregate - more efficient?

I mocked up data by looking at the row counts in your tables, giving them an even data distribution, and making guesses about the schema:

DROP TABLE IF EXISTS [Room];

CREATE TABLE [Room] (
    [Id] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);

INSERT INTO [Room] WITH (TABLOCK)
SELECT TOP (3088) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 200)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS [Conference];

CREATE TABLE [Conference] (
    [Id] BIGINT NOT NULL,
    [Name] VARCHAR(30) NOT NULL,
    [RoomId] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);

INSERT INTO [Conference] WITH (TABLOCK)
SELECT RN
, 'MY FAVORITE MEETING ROOM'
, 1 + RN % 3088
, REPLICATE('Z', 200)
FROM
(
    SELECT TOP (97413) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS [Participant];

CREATE TABLE [Participant] (
    [Id] BIGINT NOT NULL,
    [ConferenceId] BIGINT NOT NULL,
    FILLER VARCHAR(200) NOT NULL,
    PRIMARY KEY ([Id])
);

INSERT INTO [Participant] WITH (TABLOCK)
SELECT RN
, 1 + RN % 97413
, REPLICATE('Z', 200)
FROM
(
    SELECT TOP (235323) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

CREATE INDEX NCI_Part ON [Participant] ([ConferenceId]) INCLUDE (Id);

The most important assumption that I made about the schema is that the Id column is the primary key of the [Conference] table. This seemed reasonable given the query plan and the index names involved.

On my machine I get the same query plan as you, but my starting query only takes 163 ms of CPU. I assume the differences come down to differences in hardware, data distribution, and the fact that I'm not returning data to the client.

The first thing that jumped out to me is the unnecessary GROUP BY in your confData derived table. Id is the primary key of the table so you don't need all of the aggregates. With the right indexes (which you already have for this particular case), subqueries aren't necessarily a bad thing. Rewriting what you have to remove the GROUP BY:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    conf.[Name] AS [ConferenceName],
    conf.[RoomId] AS [RoomId],
    (
        SELECT COUNT(part.[Id])
        FROM [Participant] part
        WHERE part.[ConferenceId] = conf.[Id]
    ) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

This results in the stream aggregate getting pushed down further into the plan:

The uploaded plan takes 113 ms of CPU. The same operators are present, but some of them process fewer rows which saves time. You may be able to make this query more efficient by defining a covering index on the [Conference] with Id as the index key. This may seem like an odd thing to do, but your clustered index scan takes 10% of the overall query time and likely includes columns that you don't need.

If you want to make the query faster you could also consider an indexed view. Why perform the aggregation every time when you can define a simple indexed view to do it for you?

CREATE VIEW IndexedViewOnParticipant WITH SCHEMABINDING
AS
SELECT [ConferenceId], COUNT_BIG([Id]) CntId, COUNT_BIG(*) Cnt
FROM dbo.[Participant]
GROUP BY [ConferenceId];

GO

CREATE UNIQUE CLUSTERED INDEX CI ON IndexedViewOnParticipant ([ConferenceId]);

This will result in a little more space and a little bit of overhead when doing DML on the table. Overall I'd say that it's a good use case for an indexed view. Rewriting the query again:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    conf.[Name] AS [ConferenceName],
    conf.[RoomId] AS [RoomId],
    (
        SELECT CntId
        FROM IndexedViewOnParticipant part WITH (NOEXPAND)
        WHERE part.[ConferenceId] = conf.[Id]
    ) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

SQL Server agrees with my assessment that it's a good idea and CPU time falls to 78 ms.

On my machine I was able to make the query even faster, but this is starting to get into optimizations that are somewhat risky because it may require a LOOP JOIN hint. That hint may not be a good idea as your query or the data in the table changes. It also may not be a good fit for your hardware. The idea behind this approach is to create a suitable index on [Conference] and to take full advantage of the TOP with a plan that only does nested loops. Here is the index that I added:

CREATE INDEX NCI_Conf ON [Conference] ([RoomId]) INCLUDE ([Name]);

Running the same query as before with a LOOP JOIN hint gave me the following plan:

That query only took 58 ms of CPU time. It's worth mentioning that I noticed requesting the actual plan adds quite a bit of relative overhead at this stage. All of the other possible optimizations that come to mind to me aren't safe for production so I'll stop here.

As a final thought, do you really want to return 1000 arbitrary rows and the minimum conference name? Is that information useful to your end users?


Why can't I alter a security policy conditionally? I have the following rather simple statement in SQL Server Mgmt Studio 2016 CTP 3.2:

if exists(select null from sys.security_policies where name = 'OrganisationSecurityPolicy')
begin
    alter security policy [security].OrganisationSecurityPolicy
    with (state = off)
end

A security policy with that name does indeed not exist so nothing should happen. However, I get an error:

Msg 33268, Level 16, State 2, Line 3
Cannot find the object "security.OrganisationSecurityPolicy" because it does not exist or you do not have permissions.

Is there something special about security policies that makes this impossible? If I replace the alter security policy with an alter table for a non-existing table, the statement simply isn't executed.

Statements go through three processing steps - parsing, binding and execution, in that order. This batch is failing at binding since it cannot find an object to match the typed words. The work-around is to use dynamic SQL.

See a related question here.


Best approach to design a database model for this situation? Clients and records We need to store the following information for each client:

Three types of record per file, for example (obviously, with real data):

HDR2007022399999900000000000Soft/XX        0.0  XXX
XXX000000000000000               200702200000000000000000000           999999999               000000000100001
XXX003001958208255               200702200000000000000000000               999999999               000000000100001
XXX003001958208255               200702200000000000000000000               999999999               000000000100001
TRA200702222222222222222222XXX             0.0  XXX

In the name of the file is the client code which is used to identify each client.

The line starting with "HDR" is the Header.
Those starting with "XXX" are the Body and finally "TRA" is the footer.

Although there must be presented always together, at a first glance I decided to store them in three different tables, as they're different types of records (Don't know even if this is the best way)

I thought of two different models (can't post images):

With hierarchy: http://i.stack.imgur.com/ThHWn.png

I'm not sure if I will need a supertype field in the "Record" table in this case.

The other:
Without hierarchy: http://i.stack.imgur.com/FiJDf.png

Maybe I can remove idClient and just identify with the clientCode, as it is unique, but I have read is always good to have an auto incremental id.

I think the hierarchical approach is more correct, but not sure of that.

Also, with a hierarchical model it will be more difficult to retrieve all data, right?

The parameters I will have at first, to make queries are the dateReceived and clientCode. So if I want to retrieve all records for a client, I should use an INNER JOIN to join all subtypes, right?

Well, sorry for all questions.

Thank you very much in advance

You should organize the data according to its cardinality.

If you have one header and one footer per client, then these columns can be part of your client table.  Since there are clearly multiple body records per client, these should be kept in another table.

If you can have multiple records per client, then your "with hierarchy" model is not bad, although it could be simplified to collapse the Header and Footer into Record since there is only one header and one footer per record.

Your "without hierarchy" model is less desirable.  If you have multiple records per client it won't tell you which headers/bodies/footers go together.  If you have only one record per client, then having separate tables for header and footer is overkill for the same reason as I noted above for the "with hierarchy" model.

This is what I would recommend, depending on how many records each customer can have:


How to UPDATE from JOIN with GROUP BY In a SELECT query of

SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0

How can I turn this query to UPDATE as

UPDATE b.number = counted

UPDATE table2 AS b1, ( SELECT b.id, MIN(IFNULL(a.views, 0)) AS counted
                       FROM table1 a
                       JOIN table2 b ON a.id = b.id
                       GROUP BY id
                       HAVING counted > 0 ) AS b2
SET b1.number = b2.counted
WHERE b1.id = b2.id


Adding logging statements at start and end of all stored procedures I would like to capture stored procedure execution details which would require altering existing procedures to perform an INSERT at the start and an UPDATE at the end.

I recall finding a blog article some time ago that demonstrated adding statements to the start/end of stored procedures but cannot find it.

How could I make these changes programmatically either in C# or T-SQL ensuring the original stored procedure definition works as expected?

Edit: As a minimum I want to capture the user executing, the stored procedure name, start and end times. Parameters and stored procedure definition are optional.

I would advise against programmatically modifying all the stored procedures in a database like that.  Trying to ensure no edge-case causes a modification to be made erroneously will prove quite difficult.

Instead of modifying all the stored procedures, why not use Extended Events to capture the information you're interested in.  This will

Not impact performance nearly as much
Allow you to enable/disable monitoring as required, without modifying any stored procedures.
Not affect execution plans for the stored procedures.

SQLTips has a good example of several ways to capture stored procedure execution history without modifying the stored procedures.


How to convert rows into columns in sql server using pivot or with out pivot How to convert rows into columns in sql server?

This is my query output:

I need like..

ServiceName             CLO          IC             AC
---------------------   ---------    -----------    -----------
HeatExchanged           27933.562    133217.4152    146385.4261
MTD                     74.07        57.82          55.17
PerfTube_PressureDrop   3.83 / 0     1.21 / 0       2.63 / 0
Design_TubeOD           0.75         0.625          0.625
Design_NoTubes          7            41             32

Here ServiceName is dynamic value.

I tried:

select QuoteServiceID,HeatExchanged, MTD, PerfTube_PressureDrop,
     Design_TubeOD, Design_NoTubes
from
(
  select QuoteServiceID, HeatExchanged
  from Quotes_Output_Thermal
) d
pivot
(
  max(QuoteServiceID)
  for HeatExchanged in (QuoteServiceID,HeatExchanged, MTD,
       PerfTube_PressureDrop, Design_TubeOD, Design_NoTubes)
) piv;

Here are the DDL commands

CREATE TABLE Quotes_Output_Thermal
(
    ServiceName nvarchar(50) NULL,
    HeatExchanged nvarchar(50) NULL,
    MTD nvarchar(50) NULL,
    PerfTube_PressureDrop nvarchar (50) NULL,
    Design_TubeOD nvarchar (50) NULL,
    Design_NoTubes int NULL
);

INSERT dbo.Quotes_Output_Thermal
(
  ServiceName, HeatExchanged, MTD, PerfTube_PressureDrop,
  Design_TubeOD, Design_NoTubes
)
values('CLO','27933.562',  '74.07', '3.83 / 0', '0.75',  7),
      ('IC', '133217.4152','57.82', '1.21 / 0 ','0.625',41),
      ('AC', '146385.4261','55.17', '2.63 / 0', '0.625',32);

Help me, how can I achieve this?

You need to UNPIVOT and then PIVOT.

;WITH x AS
(
  SELECT ServiceName, num, sn
  FROM (
    SELECT ServiceName,
      HeatExchanged         = CONVERT(varchar(32), HeatExchanged),
      MTD                   = CONVERT(varchar(32), MTD),
      PerfTube_PressureDrop = CONVERT(varchar(32), PerfTube_PressureDrop),
      Design_TubeOD         = CONVERT(varchar(32), Design_TubeOD),
      Design_NoTubes        = CONVERT(varchar(32), Design_NoTubes)
    FROM dbo.Quotes_Output_Thermal
  ) AS d
  UNPIVOT (num FOR sn IN
    (HeatExchanged, MTD, PerfTube_PressureDrop, Design_TubeOD, Design_NoTubes)
  ) AS unp
)
SELECT ServiceName = sn, CLO, IC, AC FROM x
PIVOT (MAX(num) FOR ServiceName IN (CLO,IC,AC)) AS p;

Though wanting a different set of values called "ServiceName" in the output might be confusing for consumers of the query.

Couple of caveats:

I have no idea where QuoteServiceID fits in from your question.
I assumed that this is all of the data. You have to explicitly name the columns twice, and the ServiceName values once. If you have 40 more service names and a bunch of additional measurement columns, you can use dynamic SQL to build those commands - but that is an order of magnitude more complicated.
There is no straightforward way to get the ordering you showed in the question - the (un)pivoting will yield seemingly arbitrary results that don't match the physical ordering of the columns in the table. You can use a CASE expression or charindex against a hard-coded list, but you'd have to manually specify the columns a 3rd time.


Consistency errors for a SharePoint database When I check my database (SQL Server 2008 R2, Windows Server 2008 R2, SharePoint 2007 DB) with the command:

DBCC CheckDB

I get 12 consistency errors (see below).

Is the only way to fix it with repair_allow_data_loss?

Is there also a way to get the filename and delete the file in my SharePoint?

Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data): Page (1:5718) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data), page (1:5718). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data). The off-row data node at page (1:5718), slot 0, text ID 410976256 is referenced by page (1:5682), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data): Page (1:62999) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data), page (1:62999). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data). The off-row data node at page (1:62999), slot 0, text ID 1788739584 is referenced by page (1:62231), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data): Page (1:72779) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data), page (1:72779). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057594044940288 (type LOB data). The off-row data node at page (1:72779), slot 0, text ID 1898315776 is referenced by page (1:72782), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057599818924032 (type In-row data): Errors found in off-row data with ID 1898315776 owned by data record identified by RID = (1:72370:23)
Msg 8929, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057599818924032 (type In-row data): Errors found in off-row data with ID 410976256 owned by data record identified by RID = (1:72375:20)
Msg 8929, Level 16, State 1, Line 1
Object ID 293576084, index ID 1, partition ID 72057599643549696, alloc unit ID 72057599818924032 (type In-row data): Errors found in off-row data with ID 1788739584 owned by data record identified by RID = (1:72448:16)
CHECKDB found 0 allocation errors and 12 consistency errors in table 'AllDocStreams' (object ID 293576084).
CHECKDB found 0 allocation errors and 12 consistency errors in database 'DatabaseName'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DatabaseName).

Restore from your backup if possible.

"Allow data loss" is really a terrible option.

This excellent article will help you:

http://www.sql-server-pro.com/dbcc-checkdb.html


Postgres - should I keep the log file on the fastest drive I wonder your recommendations / experience re database log file (pg_log) placement
We are debating internally if it is worth to keep that on fastest (and expensive) SSD volume, or can be kept on magnetic drives without hitting performance.
Have separate volume dedicated only for this - Id be more than happy to reallocate that to slower HDDs and use this space for DB volume
We have verbose logging (writing almost every single query into log) on and producing 100-200M  log file daily
Engine is PG 12.6 working on Linux.

With the logging_collector enabled, writes are sequential so you can keep log_directory on almost any media.
Assuming average size of a log message is 5 KiB, with a HDD that can do sequential writes at 100 MiB/s you can log 20k messages/second.
So in theory - if you need to you emit tons of logs - you could put them on a separate, cheaper storage.
At the same time, remember that when the log volume fails or is overloaded, it can effectively stop the server (as noted in documentation linked above).


Modulo operator with nonintegers - unexpected results MySQL 5.5.32, Oracle 11g

MySQL returns strange results for queries involving mod() with noninteger moduli. Why is this?

I have a column defined thus:

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| col    | double(15,6) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

I have selected a known value from that column to use for testing - 5850. If I query the table, testing that column for whole numbers, I get these results:

mysql> SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,1) != 0;
Empty set (1.07 sec)

This is expected. But this is not:

mysql> SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,.1) != 0;
+-----------+-----------+
| thing_id  | name      |
+-----------+-----------+
|   4444444 | some      |
|   4444445 | names     |
...
|  55555555 | go        |
|  55555556 | here      |
+-----------+-----------+
416 rows in set (1.07 sec)

The same results from substituting col MOD .1 and col % .1 so it has to do with the computation not the semantics.

Compare this with the behavior of Oracle (using SQL Developer if that matters), where the type of col is NUMBER:

SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,.1) != 0;
thing_id    name
[NO RECORDS]

The precision on the MySQL column seems to make this difference, but I don't understand how. Conceptually, shouldn't integer_value % .1 = 0?

I will start with a link: What Every Programmer Should Know About Floating-Point Arithmetic.
In short, float-point arithmetic types, like the float and double mysql types should never be used for precise arithmetic. And your col mod 0.1 is trying to do exactly that, a precise arithmetic check. It is trying to find if the value in col is an exact multiple of 0.1. And it fails miserably, as it is obvious from your results.
The solution is to use a fixed type, like decimal(m, n) to do these types of checks.
As for why you got different results in Oracle, it's because you used NUMBER which is implemented differently (with decimal precision). The equivalents in Oracle of float and double are BINARY_FLOAT and BINARY_DOUBLE and use binary precision. See the notes in Oracle docs: Overview of Numeric Datatypes:

Oracle Database provides two numeric datatypes exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. They support all of the basic functionality provided by the NUMBER datatype. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.
Note:
BINARY_DOUBLE and BINARY_FLOAT implement most of the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985 (IEEE754).

If you insist to use double (I see no reason why), a few more tests with the mod operator/function in mysql, reveals that:
When you use integers for the arguments, say you do x mod K, the result is always an integer between 0 and K-1. The expected mathematical behaviour.
When you use floats/doubles, say you do x mod K, the result is any number from 0.0 to K included. (as your tests which show 5850.0 mod 0.1 to result in 0.1). I haven't done millions of tests, so there may be cases where the result can even be larger than K! Floating-point arithmetic can give weird results.
So, a workaround (which may not always work!) would be to use this condition:
( mod(col, 0.1) <> 0.0  AND  mod(col, 0.1) <> 0.1 )

To be honest, I feel sad I even made the workaround suggestion. Please read again the What Every Programmer Should Know About Floating-Point Arithmetic and don't use the workaround.
Use fixed-precision types for precision arithmetic operations.


What steps are required to move data from a custom schema in one database to the default schema in new database Lets assume for the sake of argument that I have the following tables in a single SQL Server database.

mySchema.Users
mySchema.Products
--
secondSchema.Users
secondSchema.Contracts
secondSchema.ContractItems
--
oldSchema.People
oldSchema.Table1
oldSchema.Table2
oldSchema.Table3
oldSchema.Table4
--
and so on and so forth

Each "schema" is fully independent of the others. The only reason they are in a single database, is due to hosting and cost limitations. In a perfect world these would have been in separate databases to begin with.

What things will I need to do when (and if ever) I need to move secondSchema into a new database, all by itself because its usage goes up and it now requires being housed in its own database.

I have reviewed Decision criteria on when to use a non-dbo schema vs a new Database, and as I've stated above, there is no relational or transactional justification for them being in the same database. They are in there for cost reasons.

You can script out that portion of the database and then run a CREATE DATABASE with the scripted schema and data from your original database.

When you Generate Scripts..., you will have the ability to select specific objects as opposed to the whole database.  You can select the desired schema, and all of the objects that the schema owns.  This will generate a script containing your schema and schema objects.  A bit of a manual operation, but probably the easiest method.

In the generated script, you can alter the schema name to be what the new database's schema should be called, but realize that the object definitions also need to reflect the new schema's name.


Chain replication M1 (5.1) -> S1 (5.5) -> S2 (5.6) to limit downtime for an upgrade 5.1 to 5.6 I have to upgrade a production server from 5.1 to 5.6 version. The favourite option would be to dump the database from 5.1 and import on 5.6 but this operation has been estimated to 2 hours on my environment which will have an impact for customers (downtime during this maintenance).

I'm trying to find a solution to limit the downtime. A replication from 5.1 to 5.6 is not possible (only 1 major release between master and slave) so I wonder if the solution to setup a replication as M1 (5.1) -> S1 (5.5) -> S2 (5.6) is viable and has been already tested/validated by someone.

Thanks for your help.

I have a caution to share with you in your setup.

Using MIXED for all 3 servers would be OK, but I would prefer you use STATEMENT during this migration time. Why ??? On Jan 02, 2015 I wrote the answer to this post : mysql replication master 5.5 slave 5.1 error on create database

I discussed how the format of binlog events can change between versions.

In that post, I gave an example that my former manager (at the NY Times) discovered. There was a setup of MySQL 5.5 master with MySQL 5.6 slave. Replication would break very quickly and repeatedly. My then manager found an odd trace dump in the error log with the phrase unpack:

/usr/sbin/mysqld(_Z10unpack_rowPK14Relay_log_infoP5TABLEjPKhPK9st_bitmapPS5_PmS5_+0x43f)[0x898b7f]
/usr/sbin/mysqld(_ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x100)[0x873c50]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x852)[0x877c32]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x74)[0x87fb54]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x263)[0x8aefd3]

Please note the Z10unpack_rowPK14Relay_log_info

What was the issue ??? It turned out that DATETIME fields would break replication. How ??? MySQL 5.6 started supporting microseconds in DATETIME values. MySQL 5.5 did not. So, when replication threads attempted to unpack the binlog event from the relay log, the format for DATETIME was wrong in the eyes of MySQL 5.6.

My manager's workaround ??? Switch to STATEMENT for binlog_format, use statement-based replication. Everything went fine from there.

You said you are using MIXED and that's OK. I would just make sure you get away from the 5.1 and 5.5 server as fast as you can (Do the Migration in Haste).

Such a setup is fine. A current colleague at my current employer did MySQL 5.1 to 5.7 in stages. No issues (I assumed he used MIXED or STATEMENT).

You should try this setup in DEV/STG and make sure DATETIME value don't keel over replication.


Try

SELECT
   DATE_FORMAT(registDate, '%m-%Y') AS month,
   COUNT(name) AS register,
   SUM(!ISNULL(visited)) AS visited,
   SUM(ISNULL(visited)) AS not_visited
FROM mytable
GROUP BY DATE_FORMAT(registDate, '%m-%Y');

No need to create another column.


MongoDB SSL peer certificate validation failed: unable to get issuer certificate I have enabled SSL on MongoDB with optional SSL connection: preferred.

I have used lets encrypt in order to obtain SSL certificates. SSLs works as expected and in mongod.log file I can see that:

2018-06-02T06:46:26.664+0000 I NETWORK  [listener] connection accepted from MY_SERVER_IP:45442 #2953818 (121 connections now open)
2018-06-02T06:46:26.664+0000 I NETWORK  [conn2953818] SSL mode is set to 'preferred' and connection 2953818 to MY_SERVER_IP:45442 is not using SSL.

Now on the same host I try to connect to mongo using mongo client command like below:

mongo --ssl --host mongo.example.com --sslPEMKeyFile /etc/ssl/mongo.pem --sslCAFile /etc/ssl/ca.pem

when I use SSL mongo gives error:

MongoDB shell version v3.6.2
connecting to: mongodb://mongo.example.com:27017/
2018-06-02T06:48:34.156+0000 E NETWORK  [thread1] SSL peer certificate validation failed: unable to get issuer certificate
2018-06-02T06:48:34.164+0000 E QUERY    [thread1] Error: socket exception [CONNECT_ERROR] for SSL peer certificate validation failed: unable to get issuer certificate :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed

Why it reports that unable to get issuer certificate? Is there something in between that I have missed? Could someone shed some light on this?

EDIT1:

I tried the below command too and got the error network error:

root@example2:~# mongo --ssl --host='mongo.domain.com' --port=27017
MongoDB shell version: 3.2.11
connecting to: mongo.domain.com:27017/test
2018-06-02T08:31:13.501+0000 E QUERY    [thread1] Error: network error while attempting to run command 'isMaster' on host 'mongo.domain.com:27017'  :
connect@src/mongo/shell/mongo.js:231:14
@(connect):1:6

exception: connect failed

Now getting error network error while attempting to run command 'isMaster' on host.

In MongoDB server log it reports that:

Error receiving request from client: SSLHandshakeFailed: SSLHandshakeFailed. Ending connection from 127.0.0.1:32793 (connection id: 358957)

By removing CAFile: /etc/ssl/ca.pem option from mongoDB config file, problem gone away.


Regarding Sql server 2014 express edition I need to know if adding another data file in a database (let say MYDB) in express edition will have another 10 GB size or 10 GB will be the total size of all the data files we add in a database.  Actually I am using SQL Server 2014 Express edition and we know that DB size is limited to 10 GB and thus I have to archive the database once it reached to 10 Gb which I don't want.

My purpose is to ask this question is that once the database size reached to 10 GB, can adding another data file help me to avoid archiving the database and continue with the same database?

The size limit on Express Edition is for the database size, not the data file size.

You can have ten 1GB files, or one 10GB file.

If your data is larger than 10GB, you'll need to move some data to a different database.

Can you get around this?

Sure.

Say your database had one table that was 5GB, and the sum size of every other table was 5GB, putting you at the size limit.

Create a second database, and move your 5GB table into that database.
Drop the original table in the original database.
Create a synonym in the original database, with the name of the original table, and point it at the new table in the new database.
Existing code will "just work" and you'll have expanded your capacity beyond 10GB.


Which TLogs are causing large block change in SAN snap? The environment is SQL Server 2014 Enterprise Edition, NetApp storage managed by CommVault Simpana. We have 100 dbs setup on an instance, all TLogs are on the same Volume.

The SAN Admin has setup TLog backups 4 times a day with a full snap of the TLog backup volume once a night.  The retention period is 14 days so at any time the TLog backup volume has 14 snaps of the TLogs.

Recently the TLog backup volume came close to running out of space because the full snaps were increasing at a rapid pace. We attributed this to end-of-year data processing, causing a lot of block change. This up-tick wasn't expected to last and it didn't.

The SAN Admin said he has no way of knowing which TLog files were responsible for the increase in block change and in turn the larger snaps.

I know there is a disconnect between files and blocks, but I would think you could connect the dots through Simpana, NetApp or PowerShell to find out which TLogs were causing the large block change.

Any suggestions would be appreciated.

Thanks

Good news! You don't have to query Simpana, NetApp, or PowerShell to figure out where the log file sizes have been changing.

Just query the msdb.dbo.backupset table - it tracks database name, backup date/time, and backup size. You can trend this over time. I like charting this with Excel or SSRS to show volume trends, and then I can drill down into detail to see exactly which backup is causing it.

The below query displays full backup sizes in a pivot - the 0 column is current month, -1 is last month, -2 is two months ago, etc. Note that I'm filtering for file_type = D - that's full backups. You can switch out different filters for differential or log backups if you like.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE @startDate DATETIME;
    SET @startDate = GETDATE();
    SELECT PVT.DatabaseName ,
        PVT.[0] ,
        PVT.[-1] ,
        PVT.[-2] ,
        PVT.[-3] ,
        PVT.[-4] ,
        PVT.[-5] ,
        PVT.[-6] ,
        PVT.[-7] ,
        PVT.[-8] ,
        PVT.[-9] ,
        PVT.[-10] ,
        PVT.[-11] ,
        PVT.[-12]
    FROM ( SELECT BS.database_name AS DatabaseName ,
                DATEDIFF(mm, @startDate, BS.backup_start_date) AS MonthsAgo ,
                CONVERT(NUMERIC(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
            FROM msdb.dbo.backupset AS BS
                INNER JOIN msdb.dbo.backupfile AS BF
                    ON BS.backup_set_id = BF.backup_set_id
            WHERE NOT BS.database_name IN ( 'master', 'msdb', 'model', 'tempdb' )
                AND BF.[file_type] = 'D'
                AND BS.backup_start_date BETWEEN DATEADD(yy, -1, @startDate)
                                         AND     @startDate
            GROUP BY BS.database_name ,
                DATEDIFF(mm, @startDate, BS.backup_start_date)
         ) AS BCKSTAT PIVOT ( SUM(BCKSTAT.AvgSizeMB) FOR BCKSTAT.MonthsAgo IN ( [0], [-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11], [-12] ) ) AS PVT
    ORDER BY PVT.DatabaseName;
GO


mysql on duplicate key update the entire row this is a jsp code

 PreparedStatement pstmt =con.prepareStatement("select ssn,name,gender,bod,address,profession,phone from contacts ");
        ResultSet rss = pstmt.executeQuery();

        while(rss.next()){
            stmt = conn.createStatement();
String sql1 = "INSERT INTO contacts (id,ssn,name,gender,dob,address,profession,phone) VALUES (default,'" +rss.getString(1) + "','"+rss.getString(2) + "','"+rss.getString(3)+"','" +rss.getString(4) + "','"+rss.getString(5) +"','"+rss.getString(6)+"','"+rss.getString(7)+"' on duplicate key update ssn=VALUES("+rss.getString(1)+")";
            stmt.executeUpdate(sql1);
            out.println("
");
            out.println("");        out.println(rss.getString(1));out.println("	");
            out.println("");        out.println(rss.getString(2));out.println("	");
            out.println("");        out.println(rss.getString(3));out.println("	");
            out.println("");        out.println(rss.getString(4));out.println("	");
            out.println("");        out.println(rss.getString(5));out.println("	");
            out.println("");        out.println(rss.getString(6));out.println("	");
            out.println("");        out.println(rss.getString(7));out.println("	");
out.println("
");
                            }

out put:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on duplicate key update ssn=VALUES(1)' at line 1

note: Server version: 5.7.10-log MySQL Community Server

You used:

INSERT INTO contacts
    (id, ssn, name, gender, dob, address, profession, phone)
VALUES
    (default, 1, 2, 3, 4, 5, 6, 7)
ON DUPLICATE KEY UPDATE
    ssn = VALUES(1) ;

where 1, 2, 3, .. are the values passed form the application (check your method by the way, seems like it's vulnerable to SQL injection)

The error you get is because VALUES(1) is not valid syntax. The last line should be either:

    ssn = 1 ;               -- the value , repeated

or:

    ssn = VALUES(ssn) ;     -- reference to the value that was
                            -- to be inserted to column "ssn"


Change in bind_ip value is not reflecting I have mongo installed on Centos instance in AWS and I am connecting to the mongoDB server from my system. To connect from remote system we need to change bind_ip value to 0.0.0.0 in  /etc/mongod.conf file and have done the same thing. Still I am not able to connect to the mongoDB from my system. When I checked weather the changed bind_ip  is reflecting or not using  netstat -a command the result is

tcp  0  0   localhost:27017  0.0.0.0:*    LISTEN

but, to connect from remote system it should be

tcp  0  0   0.0.0.0:27017  0.0.0.0:*    LISTEN

So the change in bind_ip value is not reflecting. Can anyone help on this?

tcp  0  0   localhost:27017  0.0.0.0:*    LISTEN

As per @Jay Gordon blog documented here This output means that users are only permitted to access our MongoDB instance on port 27017 via IP 127.0.0.1.

Starting in MongoDB 3.6, mongos bind to localhost by default. See  Default Bind to Localhost.

The hostnames and/or IP addresses and/or full Unix domain socket paths on which mongos should listen for client connections. You may attach mongos to any interface. To bind to multiple addresses, enter a list of comma-separated values.

EXAMPLE

localhost,/tmp/mongod.sock

  Note: When possible, use a logical DNS hostname instead of an ip address, particularly when configuring replica set members or sharded
  cluster members. The use of logical DNS hostnames avoids configuration
  changes due to ip address changes.

To bind to all IPv4 addresses, enter 0.0.0.0.

To bind to all IPv4 and IPv6 addresses, enter ::,0.0.0.0 or alternatively, use the net.bindIpAll setting.

Enable Access Control and Enforce Authentication

Enable access control and specify the authentication mechanism. You can use the default MongoDB authentication mechanism or an existing external framework. Authentication requires that all clients and servers provide valid credentials before they can connect to the system. In clustered deployments, enable authentication for each MongoDB server.

See Authentication and  Enable Auth.

WARNING:
Before you bind to other ip addresses, consider enabling access control and other security measures listed in Security Checklist to prevent unauthorized access.

If you would like to make this available to other systems on your network, you'll want to bind the local IP associated with the private network. To determine network interface configuration easily, we can just run an ifconfig from the command line:

For Example

To determine network interface configuration easily, we can just run an ifconfig from the command line:

ubuntu@ip-172-16-0-211:~$ ifconfig
eth0      Link encap:Ethernet  HWaddr 0e:5e:76:83:49:3e
          inet addr:172.16.0.211  Bcast:172.16.0.255  Mask:255.255.255.0
          inet6 addr: fe80::c5e:76ff:fe83:493e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
          RX packets:65521 errors:0 dropped:0 overruns:0 frame:0
          TX packets:7358 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:94354063 (94.3 MB)  TX bytes:611646 (611.6 KB)

We have the IP we want to make part of the list of acceptable network addresses we can listen from. I will open the /etc/mongodb.conf file and edit it to reflect the additional network IP:

The file's changes will be:

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1,172.16.0.211

After modifying bindIP under "net" from just 127.0.0.1 to include private IP address 172.16.0.211, we should be able to restart and see it listening from netstat on both now:

ubuntu@ip-172-16-0-211:~$ sudo service mongod stop
ubuntu@ip-172-16-0-211:~$ sudo service mongod start
ubuntu@ip-172-16-0-211:~$ sudo netstat -plnt | egrep mongod
tcp        0      0 172.16.0.211:27017      0.0.0.0:*               LISTEN      2892/mongod
tcp        0      0 127.0.0.1:27017         0.0.0.0:*               LISTEN      2892/mongod

For further your ref here  and here


Are lots of columns or joins better in Django I am writing a simple Django app that stores and displays user objects with lots of attributes.

The user may have up to three hundred attributes.

These attributes however, can be grouped off.

For example, we can have one group for "Favourite Dish" and the attributes under that are cost, flavour, cuisine etc. and another group for "Favourite Dog" and under that are cost, flavour, breed etc.

I know that in normal circumstances, you would compartmentalise the data and store each group of attributes in its own table, however this app will display and query all of the data, most of the time.

For example: the main page of this app will have a search box and users should be able to search any of the attributes in the entire model. This would involve a lot of joins if done the "usual" way, but would involve less joins if I just had a table of three hundred columns.

I have considered using NoSQL but there is other data that fits a relational schema much better.

What is the best practice for this?

Thanks :)

I'm not sure how to phrase this, but this type of application isn't what Django is made for.

Django is made for simple CRUD based websites, with an admin interface that comes for free, but I don't think trying to shoehorn a search engine in an MVC framework is going to work very well. (however there may be extensions by now, my Django experience is old and rusty)

Creating 300 columns, resulting in 300 ORs is impossible to index for, and neither is 30 tables with 10 columns each joined together, if all columns have to be search-able.

The only reasonable approach I can think of is going for an EAV pattern (for which there is an extension: django-eav), but that is going to create other issues in my opinion.

I also believe you are misunderstanding how Django is supposed to work, but you are supposed to define your data model in your model files and be able to sync it to any db. Since you are asking about a NoSQL database I suspect you are going to have to write custom code anyway.

Depending on how much code you would have to write to shoe-horn your requirements in a framework that was never intended to do this, you might as well just write a custom application.

I think, depending on your requirements (which aren't necessarily all that clear) you should go for a full text index search or any indexing solution since searching 300 columns, or EAV values, whatever you pick as a solution to match your chosen framework, is still going to be a pain.

For an obviously data driven requirement the data should dictate the platform, not the other way around.

You are trying to desperately make a framework do something it wasn't designed for.


Sql Anywhere 11: Restoring incremental backup failure We want to create remote incremental backups after a full backup.  This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible with SQL Anywhere network servers.

We are doing a full backup as follows:

dbbackup -y -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"
    c:\backuppath\full

This makes a backup of the database and log files and can be restored as expected.  For incremental backups I've tried both live and incremental transaction logs with a renaming scheme if there are multiple incremental backups:

dbbackup -y -t -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"
    c:\backuppath\inc

dbbackup -y -l -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"
    c:\backuppath\live

However, on applying the transaction logs on restore I always receive an error when applying the transaction logs to the database:

  10092: Unable to find table definition for table referenced in transaction log

The transaction log restore command is:

dbeng11 "c:\dbpath\dbname.db" -a "c:\backuppath\dbname.log"

The error doesn't specify what table it can't find but this is a controlled test and no tables are being created or dropped.  I insert a few rows then kick off an incremental backup before attempting to restore.

Does anyone know the correct way to do incremental backup and restore on Sql Anywhere 11?

UPDATE: Thinking it may be related to the complexity of the target database I made a new blank database and network service.  Then added one table with two columns and inserted a few rows.  Made a full backup, then inserted and deleted a few more rows and committed transactions, then made an incremental backup.  This also failed with the same error when attempting to apply the incremental backups of transaction logs after restoring the full backup ...

Edit:

You can follow this link to see the same question with slightly more feedback on SA: http://sqlanywhere-forum.sybase.com/questions/4760/restoring-incrementallive-backup-failure

Summarizing the information linked to in the post above....

The command you have used is for a live backup but what you want is an incremental backup.  The approach you want to use (per this blog) uses somewhat different switches.  You want to use the -n -t -x switches, and the -o to specify a log file.  So the command ends up something like:

dbbackup -n -t -x -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" -o "c:\backup\backup_log.txt"


could not read symbolic link error while executing pg_start_backup function in Postgresql DB In order to take data folder backup in postgres 9.6 database executed the order of functions for backup,
while executing pg_start_backup function produced warning :  could not read symbolic link for pg_tblspace Invalid argument
test=# checkpoint;
       CHECKPOINT

test=# select pg_switch_xlog();
 pg_switch_xlog
----------------
 5/DF000238
(1 row)
**
test=# select pg_start_backup(to_char(now(), 'yyyy-mm-dd hh24:mi:ss'));
WARNING:  could not read symbolic link "pg_tblspc/oth_data_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_index": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/tbs_temp_default": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/oth_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_ids": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/tbs_default": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/top_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/rpt_data_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_h_index": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/rpt_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_h": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/top_data_tbs": Invalid argument
 pg_start_backup
-----------------
 5/E0000028
(1 row)

Somebody who was not the PostgreSQL operating system user (normally named postgres) messed with your data directory.
This directory should only contain symbolic links whose names are numbers (the object IDs of tablespaces), and those symbolic links (and the directories that they point to) must belong to postgres.
Put the person who created these files on the pillory (but that won't solve your problem).
One should never manually mess with the PostgreSQL data directory, so the safe way to recover would be to create a new, clean PostgreSQL cluster with initdb, use pg_dumpall to export the data from the one that was messed with and import the data into the new cluster. Then delete the cluster that was messed up.
If you know what you are doing, you might also be able to recover by deleting the offending files – but who knows what else was done to this data directory.


Is there a way to log each and every executing query and its corresponding result in MySQL? I have a requirement, where I want to log each and every executing query and it's corresponding results set to a log file in mysql. Is there any way I can achieve this?

Ex:-

Query: select * country_name from countries_tbl

Result: USA, China, Japan

For having all queries in a log file you need to activate general log file by adding this into the configuration file of mysql below section [mysqld] :

general_log
general_log_file=/path/to/the/general_log_file

Next if you want to have output of specific queries in a file you need to add the following instruction at the end of query :
into outfile /tmp/output_query

for example :
select version() into outfile /tmp/output_query

If you want to have output in a file from an entire MySQL client session write this in mysql client :

tee /tmp/output

That command tells MySQL to log both the input and output of your current MySQL login session to a file named /tmp/my.out.

As far as now there is no other way to have queries + output in a same log file, but maybe someone with much knowledge can confirm this.


How should I Schedule SQL Server 2008 queries to run every month? I need to schedule some queries to run at the beginning of every month (for monthly reports) and I read that creating a JOB in sql server 2008 was the way to go. Unfortunately, I don't have the rights/privileges to use SQL Server Agent at this time. Is there another way of scheduling a few queries to run the first day of every month?

I am running Sql Server 2008 R2 on Windows Server 2008 R2 Standard operating system.

SQL Jobs is indeed the right way about it.

If you don't have the permissions, talk to your manager/DBA in order to set up the job.

If this is not possible, you can use the task scheduler in conjunction with sqlcmd to execute queries on the SQL Server.

Another alternative is to write a windows service that will execute these queries, but in terms of scheduling I would trust the task scheduler over custom code.


Oracle 11.1.0 Autobackup going to wrong location I am running Oracle 11.1.0 on Win Server 2008. My autobackups used to go to flash_recovery_area\myDB\autobackup\*yyyy_mm_dd*. Then I added a prefix to the file name in front of %F and they started going to C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\ so I removed the prefix but they still don't go to the flash recovery area. I realize I could specify the directory but I would prefer if it went back to putting each backup in its own folder with the date. I didn't make any other changes, any ideas what I am doing wrong? My RMAN config output is:

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name SPATIAL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\SNCFSPATIAL.ORA'; # default

Figured out the problem. Simply changing the Autobackup Format back to the default '%F' is not viewed as the default. In order to truly have the default value you must use the clear command.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

RMAN behaivor returned to normal. Thanks to all who viewed my question!


Postgresql csv to db translate foreign keys I'm trying to import data from a csv to a postgresql database.

the db table looks like this:

CREATE TABLE texts(
text_id serial PRIMARY KEY,
topic_id integer REFERENCES topics,
format_id integer REFERENCES formats,
text text
);

Content:
    1;1;1;sample text

CREATE TABLE topics(
topic_id serial PRIMARY KEY,
topic varchar(50) NOT NULL
);

Content:
    1;comedy

CREATE TABLE formats(
format_id serial PRIMARY KEY,
format varchar(50) NOT NULL
);

Content:
    1;A5

and an csv sheet which looks like this:

topic;format;text
comedy;A5;asdlakjsdlahsdasd
love;A6;laksdasdkhjasf

Now i want to insert the content of the csv.
How can I translate the csv contents like "format" to the format_ids in the formats table ?

Any help is much appreciated.

UPDATE:

Creating a tmp table and inserting the csv is easy.

create table tmp (
    id serial PRIMARY KEY,
    topic_id integer,
    topic varchar(255),
    format_id integer,
    format varchar(255),
    text text
);

copy tmp(topic, format, text) FROM 'test.csv' DELIMITER ';' CSV HEADER;

But how do I get the right ID's now to insert them into the text table?

I don't see where you are referencing the content of your CSV anywhere in what you posted.. (at least before any future edits). You should be using the COPY command.

http://www.postgresql.org/docs/current/static/sql-copy.html

EDIT

Since Topic and Format tables appear to be lookup tables for referential integrity, the correct course of action would be to insert the values into those tables FIRST .. and then insert into the other table using a SELECT.. Answer below:

INSERT INTO topics (topic) SELECT DISTINCT topic FROM tmp;
INSERT INTO formats (format) SELECT DISTINCT format FROM tmp;
INSERT INTO texts (topic_id, format_id, text)
  SELECT b.topic_id, c.format_id, a.text
  FROM tmp a
  JOIN topics b ON a.topic = b.topic
  JOIN formats c ON a.format = c.format;

Don't forget to clean up when you're done.

DROP TABLE tmp;


When the number of users increases, the site is slowed down I have a database with 25 M record in 42 tables(all tables is Myisam). When the number of users increases, the site is slowed down . pleas help me .

Server Config: Linux , Centos
Ram : 30 GB
CPU : 20 Core

my.cnf parameters:

[client] port = 3306 socket = /var/lib/mysql/mysql.sock

[mysqld] port = 3306
socket = /var/lib/mysql/mysql.sock local-infile=0
 skip-locking skip-innodb
 wait_timeout=50
 max_connections=550
query_cache_type=1
 query_cache_limit=1M
interactive_timeout=50
connect_timeout=10
max_allowed_packet = 128M
join_buffer=1M
table_open_cache = 1024
sort_buffer_size = 30M
read_buffer_size = 30M
read_rnd_buffer_size = 8M
key_buffer_size = 6G
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 246M
myisam_max_sort_file_size = 4G
myisam_repair_threads = 1
myisam_recover thread_cache_size = 16
query_cache_size = 960M
 max_connect_errors=10
record_buffer=1M
table_cache=2048
thread_concurrency = 40
log-bin=mysql-bin
 server-id = 1

[safe_mysqld] err-log=/var/log/mysqld.log open_files_limit=65535 [mysqldump] quick max_allowed_packet = 128M

[mysql] no-auto-rehash

[myisamchk] key_buffer_size = 512M sort_buffer_size = 256M read_buffer = 16M write_buffer = 16M

[mysqlhotcopy] interactive-timeout

MyISAM.  Say no more.  The "table locks" are probably what causes the performance to decline as you get more 'users'.

Some tips on the conversion to InnoDB:  http://mysql.rjweb.org/doc.php/myisam2innodb


Why is this query so slow? I have a quiz site with approx 2000 questions. The function in question worked great when I wasn't tracking that many users, but now there are 300,000 question that users have seen. The function only checks if a user has seen a question and returns questions that they have not seen.

SELECT q.id
FROM questions q
WHERE
      q.chapter_id = ?
      && (q.module = 1 || q.module = ?)
      && q.question_type != 4
      && q.id NOT IN(
          SELECT u.question_id
          FROM user_quiz_questions u
          WHERE user_id = ? && chapter_id = ?
      )
ORDER BY RAND()
LIMIT ?

Just to elaborate on the code a little:

questions is the table where all the questions are.
user_quiz_questions is the table that tracks the users previous answers.
id is the id for the question
module checks if it in the users study group
question_type makes sure it is an active question.

Any help on this issue would be great. Thanks.

A few places I would expect to start.  Please look at query plans but my guess is that this is a pretty simple case here of antijoins performing poorly (yes, I have been bitten by something similar even on PostgreSQL so I don't think this is unique to MySQL though InnoDB has some design decisions which make me think this could be particularly bad there).

In general, one of the basic problems  with NOT IN is that most db's I have seen end up doing this as a nested loop check using one means or another.

The first thing you should do is rewrite this as an outer join (for example a left join):

SELECT q.id
FROM questions q
LEFT JOIN user_quiz_questions u
          ON (u.question_id = q.id
              && user_id = ? && chapter_id = ?)
WHERE
      q.chapter_id = ?
      && (q.module = 1 || q.module = ?)
      && q.question_type != 4
      && u.question_id IS NULL
ORDER BY RAND()
LIMIT ?

The planner has a lot more freedom for how to address this and left joins are far more mature in MySQL than are subselects making me suspect that this will perform much better.

Edit:  As has been pointed out to me, this may be a misreading of your question.  Are you saying you have 2000 questions or 300000 seen questions plus 2000 questions that haven't been seen yet?  These are big differences and you are likely to need an index on join fields anyway if you don't have one.


Aggregate function to return any (first) encountered value How do I tell PostgreSQL to return first encountered value instead of an aggregate column?

                          Table "public.cache"
  Column   |  Type   | Modifiers | Storage | Stats target | Description
-----------+---------+-----------+---------+--------------+-------------
 user_id   | integer |           | plain   |              |
 object_id | integer |           | plain   |              |
 data      | integer |           | plain   |              |
 data2     | boolean |           | plain   |              |
Indexes:
    "cache_object_id_user_id_key" UNIQUE CONSTRAINT, btree (object_id, user_id)
    "cache_user_id_object_id_key" UNIQUE CONSTRAINT, btree (user_id, object_id)
Has OIDs: no

Query grouping by object_id and data2 will make hash aggregate, what I want to avoid.

SELECT object_id, data2 FROM cache GROUP BY object_id, data2;

I found bool_or() but it will scan all values in bad case.

SELECT object_id, bool_or(data2) FROM cache GROUP BY object_id;

Moreover, there is no such function for any datatype. What I want to do is to get any of the values from data2 column so that engine don't have to iterate all rows.

What about data column which is integer?

There are various ways to do this, with different performance, depending on the distribution of data (number of distinct object_id values, etc).

The easiest query to write - but not necessary the most efficient, is of course using an aggregate, MIN() or MAX():

SELECT object_id, MIN(data2) AS data2
FROM cache
GROUP BY object_id ;

If you have an index on (object_id, data2) this will not be too bad in recent versions of Postgres that can use index-only-scan for the execution plan.

Another way would be using DISTINCT ON syntax. The same index as above would help:

SELECT DISTINCT ON (object_id)
    object_id, data2
FROM cache
ORDER BY object_id ;

If, compared to the table size, there is a small number of object_id values, a different approach would be much more efficient. Provided you also have another tables (say objects) that has object_id as its primary key:

SELECT o.object_id, c.data2
FROM objects AS o
  CROSS JOIN LATERAL
     ( SELECT data2
       FROM cache AS c
       WHERE c.object_id = o.object_id
       ORDER BY c.data2
       LIMIT 1
     ) AS c ;

The same index would be needed. The ORDER BY is not required but with the index in place, it won't hurt efficiency. If you don't have an objects table then that part would have to be replaced with:

---
FROM ( SELECT DISTINCT object_id FROM cache) AS o
  CROSS JOIN LATERAL
---

But you'd lose some efficiency, especially in older versions. In that case, you could replace this subquery with a complicated recursive query that traverses the object_id index efficiently. See the Posgres docs for more details: Loose Index Scan.

Read also these great answers by Erwin in the related questions:

How do I efficiently get “the most recent corresponding row”?.
Optimize GROUP BY query to retrieve latest record per user
Best performance in sampling repeated value from a grouped column

And last but not least, the main reason for the problems is this:

  I have denormalized data and I want to avoid ... (I know that all values in that group are equal booleans).

Normalizing the table would lead to much more efficient queries.


How can I get the row causing a conversion failure in SQL Server? I have an update statement that is failing with this,

Msg 242, Level 16, State 3, Line 63
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

I understand the error.  I want to know which row I am trying to convert is failing.  If I have 1200 rows, how can I know which one is causing the problem without looking through it by hand?  Is there a way to get more detailed ouput?

I ended up doing the answer to this one,

https://stackoverflow.com/questions/1094483/sql-server-find-out-what-row-caused-the-tsql-to-fail-ssis

But it does not seem right.

This will help you figure out what value is causing your error. The example below shows a table with a NULL, a word, and a time stamp. You can see by running a case when statement that analyzes the value to essentially see if it's a date or could be a date and prints the value that is not a date. The CTE wrap up gives us a clean cut view into what specific values are not dates, so you can edit that value.

As Erik said, you can do a try_convert to get the update to take. The approach below will show you which value is not converting.

DECLARE @table TABLE (test VARCHAR(255))

INSERT  INTO @table
        (test)
VALUES
        ('cat'  -- test - varchar(255)
            )

INSERT  INTO @table
        (test)
VALUES
        (NULL)

INSERT  INTO @table
        (test)
VALUES
        ('2017-03-04');
WITH    results
            AS (
                SELECT
                    ISNULL((CASE    WHEN ISDATE(test) = 0 THEN test
                                    ELSE 'not error'
                            END) , 'not error') AS test
                FROM
                    @table
                )
    SELECT
        *
    FROM
        results
    WHERE
        test != 'not error'


Why wouldn't I put the single preferred option into its own table, instead of having a 'Preferred' column? In similar spirit to How to implement a 'default' flag that can only be set on a single row I would like to specify that, for any given list of possible names for a business, only one of the names is considered 'canonical'.

Two appealing options for representing this would include:

a single table, with a Preferred column (a boolean flag) and a 'filtered unique index' on BusinessId, Preferred(when true) to ensure that for any given business, only one of the names could be preferred, but all others could be unpreferred

BusinessNames

BusinessID | Name          | Preferred
--------------------------------------
1          | Sony Pictures | 1
1          | Oh no! Inc    | 0
1          | Facepalm Co   | 0

; OR
the plainer version of that same table (BusinessID, Name) and a peer table for the 'preferred names' (also BusinessID, Name, but unique only on BusinessID)

BusinessNames                       PreferredBusinessNames

BusinessID | Name                   BusinessID | Name
--------------------------          --------------------------
1          | Sony Pictures          1          | Sony Pictures
1          | Oh no! Inc
1          | Facepalm Co

I lean towards option 2, which seems cleaner to me.  Never mind that BusinessNames could reference a surrogate primary key in a Names table for possible space savings.  (I am going on an anti-surrogate-key binge right now to see where I end up).

I am aware of some implications for my app, such as:

To insert/update/delete a name, I have to touch 2 tables.
To do a query where I want to return only a single row per business and 'the name' of that business, I would simply involve PreferredBusinessNames, and leave BusinessNames alone.

I think I can live with those implications, but am I shooting myself in the foot by this sensible extreme normalization'?  Am I creating a monster, or will I be pleasantly surprised?  I've never seen anyone else do it that way, and surely there is a reason?

It depends.  There are cases whether one design or the other are satisfactory.

If you are doing a lot of queries only on the preferred or default entry, then the second option seems better.

If, however, you are normally doing lots of queries for all the entries, regardless of whether they are preferred/default or not, the first option is a little easier to set up and manage.

And if most of your queries want to retrieve all the entries except the preferred/default one,  you'll find the first option a lot easier.  I can't think of such a case off the top of my head.

And, in most cases, these tables are so tiny that performance considerations will be secondary.


Best way to transfer a few tables from reserve server to production We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is production server. Another is reserve server.

Currently there is a transactional replication between two databases on these servers where production server is Publisher and Distributor and reserve server is Subscriber.

On production server every night runs some tasks which inserts to (or updates) about of 5 tables one of them is about 6 Gb.

We would like to move this tasks to reserve server and create on one another database with the same scheme. Then when tasks upload data to another database we need to synchronize one with production database on production server.

One moment : For supporting actual data we must to include another database on reserve in replication as Subscriber from production server.

As one way we consider linked server:

Truncate table on production (in one table there is FKs, so we can't)
Insert data from another database

Edit: How about linked server and merge functionality?

How do you think are there the better way?

I think linked server and MERGE would be the best way to do it.  Set it to run at the appropriate interval and go for it.  Take care with the merge statement so that it properly detects when to do an update or not.  But since they are on the same subnet, it should perform adequately.

But linked servers have their own share of risks as well, make sure you set them up correctly (pass through security, etc.).

If you already have SSIS configured in your environment, that may be the more manageable approach.


can i have password for my mysql table is there a way i can set password on a table i am creating in mysql. i am working with rather sensitive data , and i have to use mysql on a server on which i am obviously not the admin. so people using mysql on that server can pretty much see the tables other users are creating. is there a way i can make a table which requires a password to access the particular table, if not can anyone suggest another alternative. admin has not given rights to create a new database.

There is no such way to access a table created by you using additional username/password for a user once he logged in. One can control access by a normal MySQL login. The username used for that can be configured to have only access to the objects you want by setting up privileges. So the username decides what is allowed and what is not.