texts: mysql empty mode is this valid for ALL mysql versions

texts:
I read http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_mode for mysql 5.6 and for Permitted Values (>= 5.6.6) it makes no mention of the mode of '' being valid.

Is '' not a valid mode anymore? It appears to work; but my hosting provider said it is NOT a valid mode and shouldn't be used. Is there a document that you can link me to that says a mode of '' is valid for mysql 5.0 --> 5.7? I have tested it on 5.5, 5.6, and 5.7 and it works; but on my cloud database server with a well known company setting it to '' causes the server to NOT start correctly.


texts:
I've had a look around and a play and can't what I need to work so wondered if you could help?

I have four rows in a table and I wish to count the number of items across all four. So to make it clearer the table looks like this:

Table: 'faults'
Row 1: affected_device_1
Row 2: affected_device_2
Row 3: affected_device_3
Row 4: affected_device_4

Each row can be populated with an entry. I wish to collate and put into a report the top five "affected devices" so that someone higher up can look at it and make a decision. So in plain English the result would return something like:

The top five affected devices are:

X
XX
XXX
XXXX
XXXXX

The SQL code to create the table is as follows:

CREATE TABLE `faults` (
  `fault_id`int(6) NOT NULL AUTO_INCREMENT,
  `affected_device_1` varchar(50) NOT NULL,
  `affected_device_2` varchar(50) NOT NULL,
  `affected_device_3` varchar(50) NOT NULL,
  `affected_device_4` varchar(50) NOT NULL,
  PRIMARY KEY (`fault_id`))

Then I insert a bunch of data into each of the four columns:

INSERT INTO `faults`
(
    `fault_id`, `affected_device_1`, `affected_device_2`,
    `affected_device_3`, `affected_device_4`
)
VALUES
(1, 'device1-name', 'device2-name', 'device3-name', 'device4-name'),
(1, 'device3-name', 'device1-name', 'device2-name', 'device4-name'),
(1, 'device4-name', 'device3-name', 'device1-name', 'device2-name'),

Each 'fault' can have up to four 'affected devices' associated with it. I want to then count what are the top 'affected devices' across all four columns. Which devices occurs the most and is therefore most problematic.

Sorry if I haven't made it any clearer. The problem I suspect is that the design is rubbish so please bear with me.


texts: Best option for a database used by a translation app

texts:
I'm about to start working on an app which will include words and their translations in various languages.

What is my best option for a database?

Queries will be made instantly. So if someone selects "Russian" as a second language and starts typing "Please pass the salt", each of those words will have to be looked up in Russian. And maybe just listing each word won't be the exact translation of the phrase which will lead to another query which will maybe look up the phrase based on the words in the sentence.

So I can use MySql, or any other non sql database Firebase, Mongo, CouchDb ... etc. But I don't know if not using a relational database is my best option.

Can I do something like this which seems ok but the database will get huge when considering all languages:

{
  "english": {
    "word1": {
      "russian": "word1inrussian",
      "spanish": "word1inspanish",
      "korean": "word1inkorean"
    },
    "a phrase": {
      "russian": "phrase in russian",
      "spanish": "phrase in spanish",
      "korean": "phrase in korean"
    }
  }
}


texts: Why these characters are all equal in SQL Server?

texts:
I just don't get it. See this SQL query:

select nchar(65217) -- ﻁ
select nchar(65218) -- ﻂ
select nchar(65219) -- ﻃ
select nchar(65220) -- ﻄ
if nchar(65217) = nchar(65218)
    print 'equal'
if nchar(65217) = nchar(65219)
    print 'equal'
if nchar(65217) = nchar(65220)
    print 'equal'

Based on transitive relation, it means that SQL Server considers them all to be the same character.

However, in other environments, say for example C#, they're not the same.

What I'm confused about is:

How string comparison works in SQL Server
Why comparison doesn't behave the same on one machine, and one platform, but different environments
These 4 characters represent one human-understandable character. Why they are so abundant in Unicode character map?

This of course results in tremendous problems, because I'm working on a text-processing application and data comes almost from everywhere and I need to normalize text before processing it.

If I know the reason of difference, I might find a solution to handle it.
Thank you.


texts: How to convert Extended Event action mdmget_TimeStampUTC to datetime?

texts:
SQL Server Extended Events can include an action of mdmtargetpkg.mdmget_TimeStampUTC. It returns a value such as 132085458320550473. How can that be converted to an actual date?

It doesn't appear to be a valid unix timestamp, even using microseconds.

Maybe it's the TimeStamp is actually RowVersion issue-- but this explicitly has "UTC" in it, implying it's a real time.

@@VERSION:

Microsoft SQL Azure (RTM) - 12.0.2000.8
    Jul  3 2019 10:02:53
    Copyright (C) 2019 Microsoft Corporation


texts: SQL 2014 SP2 CU1 - AlwaysOn Cluster trouble synching to one node

texts:
We have recently move CPQ to SQL2014 AlwaysON cluster
we are seeing some serious problems with the synchronization from The Primary to one of the secondary servers (there are 2 secondary servers)
It seems to be fine for a while but then we start getting messages that Data Movement suspended on one or more databases

We have discovered by running this script we can see the amount of  locks that are being held and what session-id is running holding them

SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY count (*) DESC;
GO

What we have found is that the session-id is holding a large number of locks (98535 at the moment)

We then found you can run this script

SELECT
[request_session_id] AS [SPID],
[resource_type] AS [LockType],
DB_NAME ([resource_database_id]) AS [DB],
[resource_description] AS [Resource],
[resource_associated_entity_id] AS [ResourceID],
[request_mode] AS [Mode],
[request_status] AS [Status]
FROM sys.dm_tran_locks
WHERE [request_session_id] < 0;
GO

and it will show you what SPID -3 is holding  and all the locks are on one database (500+GB) with locktype of KEY or PAGE etc

Its not long after all the other database in same Availability Group and other AGs start failing.

I don't know how to release  the locks except to stop and remove database from AG but that takes downtime which is a luxury.  Any help or guidance is appreciated


texts: MYSL: How to keep duplicates if they have different X?

texts:
This is my query

SELECT * FROM(
    SELECT orderid, duration, is_renew, bundle_id, service_id, object_name, pkg_id_fk, disconnect_time, DATE_ADD(paid_on, INTERVAL duration HOUR) AS expires
    FROM wtb_orders WHERE username= :username AND is_paid=1 AND is_revoked=0
    HAVING (expires + INTERVAL :retry_rate DAY) >= NOW()
    ORDER BY expires DESC LIMIT 300)
    as tmp GROUP BY
    tmp.pkg_id_fk,
    tmp.bundle_id,
    tmp.service_id

I want to:

Keep duplicate pkg_id_fk if the other duplicate has a different duration
Keep duplicate bundle_id if the other duplicate has a different duration
Keep duplicate service_id if the other duplicate has a different duration

Everything else about the query is correct and I don't want to change anything else. It's just that group_by remove duplicates and I don't know how to keep certain duplicates from being removed.

if you need to see the DB schema, here, you just need to look at wtb_orders


texts: Difference between AlwaysOn with 'WSFC' as the Cluster Type and 'AlwaysOn Failover Clustering'?

texts:
Per what I understood:
Clustering is used for HA, where you can failover automatically, and it should be fast since the DB files are in a shared disk, so it would just be “reconnecting” the disk to another node.
Now, in AlwaysOn there is no shared disk, you have different boxes…
My doubt came when I was checking how to configure AlwaysOn and it says:  "synchronizes data at the database level, but health monitoring and quorum are provided by a Windows cluster.”
In AlwaysOn configuration I see 'Windows Server Failover Cluster':

How this works?!
In my mind clustering and AlwaysOn are two completely different technologies…
If AlwaysOn is “powered” by clustering, then what’s the difference between AlwaysOn and clustering? As far as I know is just the granularity of ‘db level’ in AlwaysOn that clustering lacks…


texts:
Trying to get a handle on upgrade implications. When I try and install the new MSOLEDBSQL driver, Windows says:

  A lower version of this product has been detected on your system.
  Would you like to upgrade...

I am presuming that refers to the existing SQLOLEDB driver, and that updating will break my ability to use all of the applications that use PROVIDER=SQLOLEDB.1?
I'd like to use the new driver for the current project and moving forward, but wasn't planning to upgrade every application (and every user's installed driver, etc) just yet.

Is the new driver backward compatible without updating all the
connection strings?
Can I roll back the old driver anytime? (I didn't see a download for
it anywhere since it's deprecated.)


texts:
Sorry for the wall of text, but I don't know what's relevant and what's not.

I have MySQL a MySQL database that it is being replicated from our production environment to our development/test environment so that they get real-time updates for the DEV side of the web application. The problem is that DEV wouldn't really be DEV if they couldn't change stuff and submit it. It's one-way so and changes to DEV don't replicate back to prod, but this creates an issue where if they create new entries in the DEV database, they stay local to DEV but have to potential to have their AUTO_INCREMENT id numbers clash should the numbers be re-used in PROD (which won't see the same value for the next available index number since it's blind to what's going on in DEV).

I thought I could resolve this clashing by setting auto_increment_offset at the GLOBAL level so that new database entries get ID's in the 30,000+ range whereas PROD stays below 30,000.

I set the variable on the slave, created the table on the master, filling it with values 1-4 on the master, which get replicated to the slave, but subsequent attempts to INSERT data on the slave, have it pick up where the master DB left off.

mysql> describe test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 30000 |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
+----+
5 rows in set (0.00 sec)

mysql> delete from test where id=6;
Query OK, 1 row affected (0.00 sec)

I've searched online and can't really find anything. It seems auto_increment_offset was probably created for this sort of situation but it doesn't seem to be having the effect I would anticipate (that the slave generates ID's that are a lot higher than what's in the Master). Is this saved table-level somewhere?

Any level of help would be appreciated.


texts:
There's a database called FOO in an Oracle XE 10g server that's due to be decommissioned (old.example.com). I've been asked to migrate it to some other Oracle XE 11g server (new.example.com). Both servers are on the same LAN. I don't know anything about the database (it isn't a project of mine). There isn't a DBA in the room. My random Google searches suggest it's a terribly complicated task. I can use SQL Developer or SQL*Plus.

I've figured out that using impdp over a network link might be a reasonably straightforward method. But docs take most stuff for granted and I can't make it work.

So far:

I've assigned EXP_FULL_DATABASE to user FOO in source server (role DATAPUMP_EXP_FULL_DATABASE does not seem to exist in 10g).
I've created a temporary user called tmp_imp in target server with CREATE DATABASE LINK and DATAPUMP_IMP_FULL_DATABASE roles.
I'm stuck in the create database link part. I've logged into new.example.com as tmp_imp and tried all possible combinations of the CREATE DATABASE LINK statement. They either trigger ORA-00933: SQL command not properly ended or are accepted but produce a non-working link (ORA-39001: invalid argument value, ORA-39200: Link name "OLD_FOO" is invalid., ORA-12154: TNS:could not resolve the connect identifier specified).

This is one of my attempts:

CREATE DATABASE LINK OLD_FOO
CONNECT TO FOO IDENTIFIED BY 'pa$$w0rD'
USING '//old.example.com:1521/xe';

What am I doing wrong? Is there a simpler tool to migrate the database?


texts: Postgres transparent tiering of older data to second server

texts:
Assume we have a large database - say 10 terabytes.  One table, which could be partitioned by time. So it has years of data, but only recent data needs to be queried performantly.   I'd like to host old data, on a less powerful machine, with lots of storage.  But for that to be transparent to the client.

With Postgres, is it possible to:

partition the table declaratively, by month (say)
have the current month's partition as defined as on the powerful server.  The clients always query this server.
use the foreign data wrapper (on an inherited table?) to set the older months to be served by the less powerful machine
use streaming replication to keep the less powerful machine up to date. It technically has all the data, but the partition setup means that recent data is not served by it.

If not, is this scenario typically covered with other Postgres features?


texts: Possible to delete a backup db file while it is being restored?

texts:
I currently a have a db backup, which is very big. So big that I can't restore my db on the same drive.

Is it possible to delete the backup while it is being restored? Such that the new db takes up the space which the backup file was taking?

My backup is currently on our prod server, and there is a link between our test and prod environments.  Could I somehow expose that? Is it possible to restore my db over the net?


texts: MSSQL - Should I mention column max length?

texts:
I am a bit confused from what I read so far regarding to this issue.
In my database I usually write the max length of a column when I create a table or add a column.
Because I need to support other languages than English, some of my columns are nvarchar type,
the problem occurs when a programmer is not aware he can insert to a nvarchar column half of the length mentioned and this is causes sometimes errors.
My question is what would happen if I will stop writing the max length of the columns, what would be the actual size of the content, any performance affect?
We are validating the content is the client and server side so if I will have a column without max length it won't be more than what we defined in the server side so I am not worry about this.


texts: Mysql multiple check and multiple update queries

texts:
i have question about checking and updating.
The problem is that i create a very basic mysql select and update , and
two times in day i get file where php reads it. In that file have more than 14k-60k records. Database checks if that id exists then make update for 2 columns but if that record doesnt exist then insert.

foreach($product_list as $id => $var) {if(mysql_num_rows(mysql_query("SELECT product_id FROM products WHERE product_id = $id")) == 0) { // insert}else{//update}}

problem is that, that script is taking very much resources and execute very long.
Is it possible to speed up this process or somehow make it in few queries? I understand that there is going more than 60k queries in this code.
P.s this is simple example with mysql but in real code im using PDO but system is like the same as this code.


texts: Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why?

texts:
I want to generate "unguessable" IDs, rather than /record/1, /record/2, /record/3 etc.

YouTube's style of using base 64 allows for 16.7 million IDs to be stored in just 4 characters (64 * 64 * 64 * 64).  This would be more than enough for my needs.

My only pondering is whether I should be storing them as ints in the database and converting them every time a request comes in (or when I need to generate a url), or storing them as the string that the user will see.  And why?

A couple of follow-up questions:

1) rather than generating random integers and converting them to the base64 string, should I use auto-increment numbers with a salt to randomise the alphabet, so I can get apparently "random" identifiers from IDs of 1 2 3 4 5 6 7 etc.  And why?

2) for table relationships should I use whatever I've generated as my PK/FK, or should that be a separate  column and just a simple auto-increment int, for some reason?


texts: PostgreSQL: Migrate Tablespace from Instance/Cluster A to another Instance/Cluster B (different Instance-/Clustername)

texts:
I'm looking for a solution to directly migrate (move) a PGS-10.x Tablespace from one Instance (let me give it the name) "Tea" to another Instance "Coffee".
You may ask why I do not just use pg_dump to solve this?
It simply takes to much time. I'm talking about databases around 5TB in size. So taking a dump, moving it across network and importing it, this will take up to several days.
I know that it's possible to relink Tablespaces to new location on same Instance. But that simply doesn't work with different instances.
The tool pg_upgrade seems to to have a build in function to move Tabelspaces from one instance to another. But I'm not able to extract this funcion (weak in programming skill).
So the question is: What's necessary to modify within the "Coffee" instance (system tables, files, symlinks) to use the "Tea" Tablespaces.
Thank you in advance for your time and knowlege.


texts: Can oracle dbca template be used cross versions

texts:
If you have a dbca template to create a database for version 12.1, will it also work for creating a database on 12.2 binaries?

Will it also work for creating a database on 11g binaries?


texts: Sybase ASE doing showplan for all requests

texts:
We have a strange situation where we are seeing showplan information in packets returned from a Sybase ASE 12.5 database server to a JDBC client. ie using tcpdump we see showplan output being sent back. We are trying to avoid this as the database is performing badly and it can't help having to generate detailed plan information.

We can't see any configuration settings client-side which would make this the case - is there some setting server-side which forces this option to be on?


texts: SQL Server 2008 transaction log growing since SP3 Installed

texts:
On our production db (~117gb) we quite a few tables being replicated.  Historically our maintenance plan has kept the transaction log fairly small.  Since we installed SP3,  the transaction log is growing pretty quickly (currently ~213gb).

We made sure the install was successful by checking the registry entries,  but not too sure what else to check.  Searching hasn't found any similar problems with SP3 as the culprit.

Not too sure how to look at the transaction log either since DBCC LOG results are cryptic at best.

This is output from version:

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Sep 21 2011 22:45:45
Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit)
   on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Full Recovery Mode = true
Backing up transaction log = true on a daily basis.

This is a production database that has had the transaction log maintained to a small size for many years without a problem... until SP3 came along.

I ran the DBCC OpenTran and found this:

Oldest active transaction: SPID (server process ID): 9s UID (user ID) : -1 Name : tran_sp_MScreate_peer_tables LSN : (365893:25399:1) Start time : Nov 12 2011 8:26:10:927AM SID : 0x01 Replicated Transaction Information: Oldest distributed LSN : (366831:664081:8) Oldest non-distributed LSN : (0:0:0)

Quite a few other people have reported this same long running process after a service pack install... hmmmm.

Not too sure what to do about it. Might consider pausing or killing my replication jobs and see if it goes away. All indications are that since it is a '9s' SPID, the system owns it and it can't be killed.

thoughts on how to proceed?


texts:
I have a PL/SQL web application,got the entire DB dump from remote DB.
Its very old application, running on oracle 9i.
I basically want to get the application up and running on my laptop,but i am not able to figure out how.
Can somebody help me with this?


texts: Is it "legal" to CREATE and DROP #SomeTable more than once?

texts:
I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:

CREATE TABLE #WidgetSetting
(
    WidgetID bigint not null,
    Name nvarchar(100) not null,
    Value nvarchar(max) not null,
    CreateDate datetime not null
)

INSERT VALUES

MERGE TABLES

DROP TABLE #WidgetSetting

But now SSMS is complaining that the object already exists by the next time the CREATE TABLE fires. What gives?

I think it's obvious I'm going to have to declare the table once at the beginning of the script, truncate instead of drop, but it's frustrating, naturally, to not be able to just drop the table and use the same name again.


texts: "Access is denied." for xp_cmdshell for network share

texts:
On a SQL 2014 SP 1 instance a SQL Agent job  I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied."

The command is EXEC master..xp_cmdshell 'DIR "\\backups\marshall\master" /A:-D /S /B'

The command will work for a local folder.EXEC master..xp_cmdshell 'DIR "C:\Temp" /A:-D /S /B'

Running EXEC master..xp_cmdshell 'whoami' returns the Windows account that is used for the SQL Engine and SQL Agent. This account has Full Control for the backup folder. I've deleted and re-added permissions for this account. I can manually add and delete files from this folder.

I will be changing the process to no longer use xp_cmdshell, but can't do that today. Any ideas on what is going wrong?


texts:
Question for you DBAs and such: How do you go about learning the skills to become a DBA without having the 'on hands' or on-the-job training?

My experience in DB work has been messing with MySQL via MyPHPAdmin or something similar. Not doing a ton of in-depth work.

Classes? SQL books?


texts: how to deal with a SSIS 2005 validation error in a script component

texts:
Within a script component, a strange error occurs.

the (vb.net) code leads to build succeeded
as mentioned, build done

BUT and that's not really awesome  (and, yes, design script was clicked):

  Error 1   Validation error. Load Observations: Script Load into
  MeasuringParameters & Observations [194]: The script component is
  configured to pre-compile the script, but binary code is not found.
  Please visit the IDE in Script Component Editor by clicking Design
  Script button to cause binary code to be
  generated.    LoadTransMeas.dtsx  0   0

any hints how to resolve this issue?


texts: What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table?

texts:
Given a SQL Server table with

a large number of rows
no columns with large-value data types
multiple indexes
more allocated space than available for the largest possible transaction log size
a single-column primary key with clustered index (optional consideration for this question)
an average record size of 1k (optional consideration for this question)

and an update statement which

needs to be run against every row
sets a value on a non-indexed column (optional consideration for this question)

What techniques can be employed to reduce the peak disk space consuption (including data files, log file and tempdb - if applicable) required to do this update?

For purposes of this question, the following is allowed:

applying changes in batches
run in single-user mode
change recovery model


texts: How to list all constraints of a table in PostgreSQL?

texts: How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?


texts: MySQL-test suite not included with windows mysql installer?

texts:
I noticed in my MySQL folder there is no mysql-test. Why is that?

Is it possible to add it?


texts:
When setting up master/slave replication in Mysql, do you then need to ignore any tables in /etc/mysql/my.cnf or should replicate_wild_ignore_table be leaved out under normal circumstances?
This is what I'm doing...
my.cnf
replicate_wild_ignore_table = mysql.%,information_schema.%,phpmyadmin.%


texts: Execution time for CONTAINSTABLE/WHERE query

texts:
I am facing an issue for a seemingly trivial database query on SQL Server 2012 accessing a full-text index:

SELECT MyTable.ID FROM MyTable INNER JOIN (
  SELECT [KEY] FROM CONTAINSTABLE(MyTable,FullText,'"ab*"',10)
  UNION ALL SELECT 1
) T ON T.[KEY]=MyTable.ID
WHERE ForeignKey=123

The query takes multiple seconds to return no results. If either the UNION or the WHERE clause is removed the query completes instantaneously. Also the performance varies when changing the ForeignKey value.

My assumption is that SQL Server executes the query in an inefficient way but I don't know what to do about that.

UPDATE

Execution plans generated with SET SHOWPLAN_TEXT ON

Full query:

|--Nested Loops(Inner Join, OUTER REFERENCES:([MyTable].[ID]))
   |--Index Seek(OBJECT:([MyTable].[IX_ForeignKey]), SEEK:([MyTable].[ForeignKey]=(123)) ORDERED FORWARD)
   |--Concatenation
        |--Filter(WHERE:([MyTable].[ID]=FulltextMatch.[docid]))
        |    |--Sort(TOP 10, ORDER BY:(FulltextMatch.[score] DESC, FulltextMatch.[docid] ASC))
        |         |--Table-valued function
        |--Filter(WHERE:(STARTUP EXPR([MyTable].[ID]=(1))))
             |--Filter(WHERE:([MyTable].[ID]=[Expr1003]))
                  |--Constant Scan(VALUES:(((1))))

Without WHERE ForeignKey=123:

|--Nested Loops(Inner Join, OUTER REFERENCES:([Union1004]))
   |--Concatenation
   |    |--Sort(TOP 10, ORDER BY:(FulltextMatch.[score] DESC, FulltextMatch.[docid] ASC))
   |    |    |--Table-valued function
   |    |--Constant Scan(VALUES:(((1))))
   |--Index Seek(OBJECT:([MyTable].[IX_ID_WithForeignKeyColumn]), SEEK:([MyTable].[ID]=[Union1004]) ORDERED FORWARD)

Without UNION ALL SELECT 1:

|--Nested Loops(Inner Join, OUTER REFERENCES:(FulltextMatch.[docid]))
   |--Sort(TOP 10, ORDER BY:(FulltextMatch.[score] DESC, FulltextMatch.[docid] ASC))
   |    |--Table-valued function
   |--Index Seek(OBJECT:([MyTable].[IX_ForeignKey]), SEEK:([MyTable].[ForeignKey]=(123) AND [MyTable].[ID]=FulltextMatch.[docid]) ORDERED FORWARD)


texts: MySQL specific database configuration file

texts:
In MySQL's configuration file I've globally disabled autocommit as so.

[mysqld]
autocommit=0

I need to turn MySQL's autocommit on for a specific Ruby on Rails database though. It could be for the user or the database itself (doesn't matter). Thinking it would look something like this.

[mysqld]
autocommit=0
execute_sql="Custom SQL to set autocommit for a database"


texts: Restoring ORACLE 11gR2 database with Flash Recovery Area Only

texts:
I was succeded in making backup using RMAN in Archivelog mode in my host server and this is the screenshot when i list the backup,

and the next step, i copy content from Flash Recovery Area folder inside BACKUPSET Folder to a new host to execute the restore.

but unfortunately when i logged in to the new host and issue list backup command in rman, I cant see any backup file thus i cant restore the database. Please help me was there anything missing in my procedure ??

Thanks again


texts:
I need to upgrade my Sharepoint2013 Farm, and the SQL2012 servers are in AlwaysOn mode.

I was wondering if I could use Always On in the following catastrophic scenario:

Take a VM snapshot of Sharepoint Web Front End servers;
Shut down SQL#2;
Upgrade Sharepoint Farm (we are 1 SP and 27 CUs short);
Catastrophic failure during upgrade or upgrade fails somehow;
Shut down SQL#1;
Shut down Sharepoint farm;
Power on SQL#2 (so AO listener is now pointed here);
Restore Sharepoint VMs;

Questions:

Am I all back to normal as if upgrade never happened at this point? Would this really work?
Now, if I power on SQL#1 at this point, will #2 resume Always On on #1 properly, or will I get some sort of desynchronised alerts? Or, in the worst that could happen, will SQl#1 start to override SQl#2?

Thanks


texts:
I have a stored proc spGetSites that returns three columns for SiteName, SiteId and UnitCount. The stored proc runs perfectly fine.
I'm trying to store the results of the stored proc to a temp table @Site to use for my report with below syntax. This time I got an error message: "INSERT EXEC statement cannot be nested"

Can I not use INSERT and EXEC statement at the same time?

DECLARE @Site TABLE (SiteName VARCHAR(100), SiteId INT, UnitCount INT)

INSERT INTO @Site
EXEC spGetSites @SiteId = 0


texts: Are availability groups required for AlwaysOn

texts: Beginning a project to create an HA SQL server environment.  We are limited to using SQL Server Standard edition, currently 2016.  From what I have found, AO(AlwaysOn) for standard edition limits you to basic AG (availability group) and thus  only 1 database per AG.  This is going to be tough since most of our SQL instances contain 30+ databases.  Can you implement AO without AGs?  Are there other ways to configure AO to address our needs?  All databases on any of our instances are managed similarly and thus wouldn't need the flexibility that managing at an AG provides.


texts: ndbcluster: Error: 1114, The table is full, when using table

texts:
I have searched for a while about this error. I have a MySQL cluster running with 2 data nodes.  The cluster houses a few other databases.  I have a dump of a database in --tab format from another server.  I have modified the SQL to use ndbcluster as the table engine and can import the SQL successfully.  When I try to import data to the tables using mysqlimport, a few tables will populate but then I get the following message:

mysqlimport: Error: 1114, The table 'doc_archive' is full, when using table: doc_archive

Searching around for this error would suggest that I am out of memory on my data nodes, however that is not the case as you can see:

ndb_mgm> all report memory
Node 31: Data usage is 45%(179865 32K pages of total 393216)
Node 31: Index usage is 67%(88761 8K pages of total 131200)
Node 32: Data usage is 45%(179863 32K pages of total 393216)
Node 32: Index usage is 67%(88761 8K pages of total 131200)

Importing the database up until it is "full" only increases the data usage on each data node by 2%, and the index usage only goes up 3%.

Here is a copy of my config.ini from the management node:

[ndbd default]
# Options affecting ndbd processes on all data nodes:

NoOfReplicas=2    # Number of replicas

DataMemory=12G    # How much memory to allocate for data storage

IndexMemory=1G   # How much memory to allocate for index storage
            # For DataMemory and IndexMemory, we have used the
            # default values. Since the "world" database takes up
            # only about 500KB, this should be more than enough for
            # this example Cluster setup.

MaxNoOfOrderedIndexes=8192
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=100000
MaxNoOfExecutionThreads=8
MaxNoOfConcurrentOperations=256000
MaxNoOfTables=2048
MaxBufferedEpochs=100000
TimeBetweenEpochsTimeout=32000
TransactionDeadlockDetectionTimeout=1000000
TransactionInactiveTimeout=10000000
MaxNoOfTriggers=3072 # Default is 768

datadir=/var/lib/mysql-cluster   # Directory for this data node's data files
serverport=1187

NoOfFragmentLogFiles=1125
RedoBuffer=32M

[ndb_mgmd]
# Management process options:
nodeid=30
hostname=192.168.2.30
datadir=/var/lib/mysql-cluster

[ndbd]
nodeid=31
hostname=192.168.2.31

[ndbd]
nodeid=32
hostname=192.168.2.32

[mysqld]
nodeid=33
hostname=192.168.2.33

[mysqld]
nodeid=34
hostname=192.168.2.34

Where else should I be looking?  There is plenty of disk space and memory to spare.


texts: SQL query does not recognise derived table

texts:
So I've made a simple SQL query:

SELECT companyname
FROM works w1, (
                SELECT companyname,sum(salary) AS sumsal
                FROM works
                GROUP BY companyname
               ) w2
WHERE
w1.companyname=w2.companyname

it works fine so far but then I add the following line in the where clause:

AND w2.sumsal=(SELECT MIN(w2.sumsal)
               FROM w2);

I get:

ERROR 1146 (42S02): Table 'companydb.w2' doesn't exist

I'm using MySQL 8.0.


texts: Adding DATETIME() and Status column with xp_FileExist result

texts:
I'm trying to add a two columns in addition to the results of xp_FileExist. Here's my T-SQL:

DECLARE @path varchar (100)
DECLARE @file varchar (100)
SET @file = '<location>\*.txt'
SET @path = 'dir /B ' + @file
EXEC xp_cmdshell  @path, NO_OUTPUT

CREATE TABLE FILEEXISTS (
        [NAME] VARCHAR (50) NULL)

INSERT into FILEEXISTS exec master..xp_cmdshell @path

SELECT *
FROM FILEEXISTS

I tried to define columns in the "Create Table" section and add them after the @path but it throws an error every time saying something in the line of too many parameters. Any pointers?


texts:
PostgreSQL implements the execute ... using option to pass parameters into dynamic SQL, and as far as I can tell this feature was introduced in version 8.4. We are using Greenplum, which is forked from PostgreSQL at version 8.2, so it does not have this feature.

Is there any other way to do the same thing in Greenplum or PostgreSQL 8.2?


texts: MySql - ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump

texts:
while trying to restore a MySql Dump, i get the following error:

ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes

any ideas?


texts: In pgAdmin 4, show multiple lines of a TEXT column

texts: A have a TEXT column in which the values contain newlines. When I query the column in pgAdmin, I only see the first line of each value. Can I make pgAdmin show multiple lines in a cell?


texts: Removing REM INSERTING and SET DEFINE OFF from SQLcl Output

texts:
I ran below script to get data from Oracle, and then load to Postgres with psql.

SET SQLFORMAT INSERT
SET FEEDBACK OFF
SET TERMOUT OFF

SPOOL asset_file.sql
SELECT * FROM FINANCE.ASSET;
SPOOL OFF
QUIT

However in SQLcl output, the first few lines are:

REM INSERTING into finance.asset
SET DEFINE OFF;
Insert into finance.asset( ...

which results in error because the first two lines not recognized by Postgres, using psql client with -f option.

What are options that I can further put in SQLcl script to ensure those first two lines not being output? I don't want REM INSERTING and SET DEFINE .. in the output.


texts: MySQL general log as table -- Scheduling cleaning

texts:
Rolando had posted as an answer in other questions how to enable the general log as a table, I have done this but I would like to schedule cleaning in order to only keep the last 3 days. He posted this, but I'm unsure on how to use it, could I use this as a bash script and add it to cron as a scheduled task?

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
INSERT INTO mysql.general_log_new
SELECT * FROM mysql.general_log WHERE event_time > NOW() - INTERVAL 3 DAY;
DROP TABLE mysql.general_log;
ALTER TABLE mysql.general_log_new RENAME mysql.general_log;
SET GLOBAL general_log = @old_log_state;


texts: SQL Stored Procedure with Output Parameter

texts:
I know how to write a stored procedure with output parameters. But I don't know the reason why I would use it over just using a simple SELECT statement. A normal stored procedure can still return an output in a grid (see below examples).
Can anyone give a real life example where I can use an SP with output parameter over with SP without output parameter?
Examples
-- Using output parameter
SELECT @var = COUNT(*) FROM table1 WHERE gender = @gender...

-- Without output parameter
SELECT COUNT(*) FROM table WHERE gender = @gender...


texts:
I have a stored procedure in my database. As per my knowledge it's pre-compiled subroutine.

Question 1: If I execute following queries from Hibernate, will database create a separate execution plan for each query? In which case SQL Server update/change stored procedure Execution Plan?

EXECUTE myProcedure 'vicky.thakor', 'Routine Check Up'

and

EXECUTE myProcedure 'chirag.thakor', 'Routine Check Up'

Question 2: This is bit of programming question but you guys may explain me bit of it. In Java there is something called PreparedStatement. PreparedStatement is similar to pre-compiled query.

PreparedStatement generates query like following and put value in place of ?.

SELECT * FROM user_master WHERE user_name = ?

But database will receive the final query like

SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

then how database help to get best performance when using PreparedStatement.


texts: SQL Server: Recursive dynamic SQL puzzle

texts:
I was looking at this post of trim all columns of a table. In the answer query script the column names gets concatenated without any looping operation. I simplified it to check its work flow as explained below.

Create a new database
Create a new table with three columns as in below script.(insert
values not required)

Create table table1(id int, name varchar(20), city varchar(20))

Execute below T-SQL Script, which results xxx [id] [name] [city]  in single row.

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS
SELECT @a

Where as below script results in 3 rows

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT 'b' = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS

To understand more better I executed the below query using Top 1. This results xxx [id]

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT Top 1 @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS
SELECT @a

I got puzzled With Top 2 query. This resulted xxx [id] [name].

Where as I was expecting xxx [id] xxx [id] [name] i.e. First value + second value. Where am I going wrong.

I am curious to know how the column names getting concatenated. Sorry I could not frame better title for this puzzle.


texts: How to create scheduler for manually refresh Materialized View if the MV get need_compile stallness status

texts:
I've a problem to Automatically Refresh Materialized View, if the stallness condition is NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH.

I've MV with this PL/SQL :

CREATE MATERIALIZED VIEW "some_schema"."MV_TEST_BRO" ("some_field", "some_field", "some_field")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "some_tablespace"
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT * FROM some_table;

btw, my MV is too complex to put it here so I made it simple in here.
After I got an update in table master I test it with below query to get status stallness of my MV.

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

And, I create PL/SQL to refresh that MV like this :

BEGIN
DBMS_REFRESH.make(
    name                 => 'some_schema.MINUTE_REFRESH',
    list                 => '',
    next_date            => SYSDATE,
    interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
    implicit_destroy     => FALSE,
    lax                  => FALSE,
    job                  => 0,
    rollback_seg         => NULL,
    push_deferred_rpc    => TRUE,
    refresh_after_errors => TRUE,
    purge_option         => NULL,
    parallelism          => 4,
    heap_size            => NULL);
END;

BEGIN
DBMS_REFRESH.add(
    name => 'some_schema.MINUTE_REFRESH',
    list => 'some_schema.MV_TEST_BRO',
    lax  => TRUE);
END;

But the interval of that dbms_refresh is not working at all. So I created some scheduler to do the job, this is my PL/SQL :

BEGIN
    sys.dbms_scheduler.create_job(
    job_name => '"some_schema"."REFRESH_MV"',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin
       DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH'');
    end;',
    schedule_name => '"some_schema"."EJS_MV_REFRESH"',
    job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'Scheduler untuk merefresh MV pada schema some_schema',
    auto_drop => FALSE,
    enabled => FALSE);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
    sys.dbms_scheduler.enable( '"some_schema"."REFRESH_MV"' );
END;

So far this solve my problem to automatically refresh MV every 1 minute. But it's not effective at all. So, My Problem is :

How to make this job_action to execute DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH''); every 1 minute if the status of stallness is not FRESH ?

I don't know much about how to use pointer or whatever the name to get the result of query to check SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME; and use this status to trigger the refresh, it's just like IF-THEN condition.

Please help me, if you know how to get that...


texts: How to return some * data with time interval?


texts: How to select name by 2 fields in another 2 tables in mySQL

texts:
I got confused trying after making several searches on google to find a way to do the following:

I have table called students that contains 3 colums (student_id(primarykey), firstname,lastname),
the second table called courses contains 2 columns (course code,course_name) and
the third table called registration contains (student_id,course_code),

I need to select the name of students that are registered in course that includes the word for example "Structures" in the course_name field.

For example (consider the snap-shot)

Regardless the concat of the firstname and lastname, this is easy to do it.

So I will get these student names : SomeOne & GoodStudent

P.S. I created table Registration by inserting from the first two tables together.

So I tried like the following:

select concat(firstname,lastname)
from students
  left join registration
where students.student_id = registration.student_id
  and registration.course_code like 'Structure%';


texts: Slow inserts in oracle 12c inmemory table

texts:
We are running some tests to check the in-memory performance on a 12c (12.1.0.2.4 EE) RAC. Servers have 56GB memory and 20core CPUs.

Our plan is to have a few read-performance critical tables in memory and the rest in disk. The test was to first populate the tables using our insert tool and then run queries on it using JMeter (web application benchmark tool).

The insert tool basically reads records from a file and then inserts records to the DB in blocks and commits.

We started testing with one table and observed slow insert rates straightaway. But when the table is made a no inmemory table the insert rates were fine.

The table has 90 columns, 1 trigger, 15 indexes.

The test preparation and results are given below.

Preparation

1) Create the table, trigger, indexes.

2) Make table in-memory using "alter table test_table inmemory priority critical"

Results

Without Inmemory option (~7000 recs/sec)

Avg time to read 1 record  = [0.0241493] ms
Avg time to insert 1 record  = [0.141788] ms
Avg time to insert 1 block of 500 number of rows  = [70.894] ms
Avg time to commit 2 blocks(500 rows per block)  = [3.888] ms
Total time for 2000 blocks of inserts  = [141.788] s, at [7052.78] recs/s
Total time for 1000 number of commits  = [3.888] s
Total time for 2000 blocks of inserts + 1000 number of commits  = [145.676] s
Total time to read 1000000 number of records from file  = [24.1493] s
Total time to read 1000000 number of records + 2000 blocks of inserts + 1000 number of commits  = [169.825] s

With Inmemory option (~200 recs/sec)

Avg time to read 1 record  = [0.0251651] ms
Avg time to insert 1 record  = [4.62541] ms
Avg time to insert 1 block of 500 number of rows  = [2312.7] ms
Avg time to commit 2 blocks(500 rows per block)  = [3.32] ms
Total time for 200 blocks of inserts  = [462.541] s, at [216.197] recs/s
Total time for 100 number of commits  = [0.332] s
Total time for 200 blocks of inserts + 100 number of commits  = [462.873] s
Total time to read 100000 number of records from file  = [2.51651] s
Total time to read 100000 number of records + 200 blocks of inserts + 100 number of commits  = [465.39] s

The memory parameters of the DB are given below.

NAME                            TYPE        VALUE

lock_sga                        boolean     FALSE
pre_page_sga                    boolean     TRUE
sga_max_size                    big integer 30G
sga_target                      big integer 30G
unified_audit_sga_queue_size    integer     1048576
inmemory_clause_default         string
inmemory_force                  string      DEFAULT
inmemory_max_populate_servers   integer     8
inmemory_query                  string      ENABLE
inmemory_size                   big integer 10G
inmemory_trickle_repopulate_    integer     1
servers_percent
optimizer_inmemory_aware        boolean     TRUE
buffer_pool_keep                string
buffer_pool_recycle             string
db_block_buffers                integer     0
log_buffer                      big integer 1048552K
use_indirect_data_buffers       boolean     FALSE
memory_max_target               big integer 0
memory_target                   big integer 0
optimizer_inmemory_aware        boolean TRUE
pga_aggregate_limit             big integer 8G
pga_aggregate_target            big integer 4G

We are also tried the following, but the results were the same.

1) Stop one instance on the RAC (2 node RAC)

2) Change the inmemory priority to "high" then "low".

Hope someone can point me in the right direction.


texts: How to design a main list and sublists schema

texts:
I am finding it difficult to design a database schema for a list feature. there would be a master list which then can be subsectioned into further lists.

each user is able to mark a candidate as a favourite candidate and can have many favourite candidates.

if a candidate is deleted from a list then it should still remain part of favourites, or be part of the main favoirtes list without belonging to a particular list.

a user can mark many candidates as favourite
a user can have many favourites lists
a favourites list can have many candidates

the following is what I have come up with so far.

as shown in the above image I am allowing the FavouritesListId to be nullable, this way if a list item is not associated with a certain list this means it is still favourited but not part of a particular list. I want to know if this is the optimal solution or if there is a better design approach available.

and here is the script to the schema:

    USE [Favourites]
GO
/****** Object:  Table [dbo].[Candidates]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Candidates](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Candidates] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FavouritesList]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FavouritesList](
    [Id] [int] NOT NULL,
    [Title] [nvarchar](50) NULL,
 CONSTRAINT [PK_FavouritesList] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FavouritesListItem]    Script Date: 10/05/2015 18:43:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FavouritesListItem](
    [Id] [int] NOT NULL,
    [FavouritesListId] [int] NULL,
    [CandidateId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
 CONSTRAINT [PK_FavouritesListItem] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Users]    Script Date: 10/05/2015 18:43:52 ******/


texts: PostgreSQL: How to restrict resources per DB, Schema or User

texts:
Is there a way to configure PostgreSQL server instance to limit resources, e.g. work_mem or max_parallel_workers, by databases or schema? (or even limit a specific user)

To my knowledge, parameters from postgresql.conf affect the entire postmaster instance.

Background of this question is multi-tenancy based on DB schema and resource isolation.


texts: Permissions for sys.database_mirroring in SQL Server

texts:
I need to assign permission to use sys.database_mirroring and of course I want to give the minimum amount of privilege. When I looked at microsofts homepage it says:

  To see the row for a database other than master or tempdb, you must
  either be the database owner or have at least ALTER ANY DATABASE or
  VIEW ANY DATABASE server-level permission or CREATE DATABASE
  permission in the master database. To see non-NULL values on a mirror
  database, you must be a member of the sysadmin fixed server role.

After trying on a test server I can't figure out what the last sentence mean. Anyone know in which cases sysadmin privilege is needed?


texts: Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR

texts:
I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database:

SELECT  *
FROM    Users u
        LEFT JOIN Comments c
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'

The only index on the Users table is a clustered index on ID.

The Comments table has the following Non-Clustered indexes as well as Clustered Index on ID:

CREATE INDEX IX_UserID ON Comments
(
    UserID,
    PostID
)

CREATE INDEX IX_PostID ON Comments
(
    PostID,
    UserID
)

The estimated plan for the query is here:

I can see the first thing the optimizer will do is perform a CI scan on the users table to filter only those users where DisplayName = Alex, effectively doing this:

SELECT  *
FROM    Users u
WHERE   u.DisplayName = 'alex'
ORDER BY Id

and retreiving results as such:

Then it will scan the comments CI and for every row, look to see if the row satisfies the predicate

u.Id = c.UserId OR u.Id = c.PostId

Despite the two indexes, this CI scan is performed.

Wouldn't it be more efficient if the optimizer did a separate seek on each of the indexes in the Comments table above and join them together?

If I visualise what that would look like, in the screenshot above we can see the first result of the Users CI scan is ID 420

I can visualize what the IX_UserID Index looks like using

SELECT      UserID,
            PostID
FROM        Comments
ORDER BY    UserID,
            PostID

so if I seek to the rows for user ID 420 as an index seek would:

for every row where UserID = 420, I can look if  u.Id = c.UserId OR u.Id = c.PostId of, course they all match the u.Id = c.UserId part of our predicate,

So for the second part of our index seek, we can seek through our index IX_PostID which can be visualised as follows:

SELECT      PostID,
            UserID
FROM        Comments
ORDER BY    PostID,
            UserID

If I seek to Post ID 420 I can see nothing is there:

So we then go back to the results of the CI scan, move to the next row (userId 447) and repeat the process.

The behaviour I have described above is possible using in a WHERE clause:

SELECT      UserID,
            PostID
FROM        Comments
WHERE       UserID = 420 OR PostID = 420
ORDER BY    UserID,
            PostID

Plan here

My question therefore is, why isn't an OR condition in a JOIN clause able to perform an index seek on appropriate indexes?


texts: localtimestamp or curent_timestamp for default value

texts: In Oracle-12c I have some columns that I am converting from timestamp with time zone to timestamp with local time zone. Some of those same columns also currently have a default value of CURRENT_TIMESTAMP. Should I change the default values to use the LOCALTIMESTAMP function instead? The columns seem to populate fine if I just leave the CURRENT_TIMESTAMP default values. Is there any practical reasons or gotchas for using one over the other in this case. I have been combing the docs but can't find anything.


texts: What's the difference between DISTINCT_SCAN and IXSCAN in explain output?

texts:
In Mongo 3.4 explain("executionStats").distinct(...), for some queries
the winning plan contains an IXSCAN step, and for other queries it contains an DISTINCT_SCAN step.

What is the difference between the two? Is one of them faster than the other?


texts: How to improve performance joining tables with no primary keys

texts:
Given two large tables of ~8,000,000 rows each. They are being joined on substrings of

name
address
zipcode

Naturally, the query is taking a long time. What are the best methods to improve it


texts: Excel --> MS SQL; skip blank calculated rows

texts:
Trying to put the final touches on a workflow I've been setting up and am running into a bit of a problem.

What I am hoping to do is as follows:

Have an Excel spreadsheet with multiple tabs that I give to an "end user"
Have one tab on which the end user inputs data and have another tab that has a bunch of values that are calculated based on the user input tab and some other lookups, etc.
Have an SSIS package that imports non-blank rows from the calculated tab into a table in SQL.

The way the spreadsheet is currently set up is I have an IF check in the fields to either set them to a value or set them to nothing ('').

The problem I have is that when I do the import into SQL, the rows with '' fields are imported as empty rows, presumably because the fields aren't actually blank (they have formulas in them).

Anyone know of a way to fix / work around this?  I tried changing the formulas to set the fields to #N/A instead of '', but if I do that, the import fails.

Thanks!


texts: Choosing the right field data type in SQL Server 2008

texts:
I got a request to create a new table that will store certain information for customers. The definition of the table is somewhat irrelevant except for one field. This field will store ANSI characters that are retrieved from a terminal screen.

There will be about 50 million rows at first, and initially, we estimated the field will required <= 7,500 characters, so I opted to use the VARCHAR(8000) datatype. After more analysis, we determined that probably about 90% of the 30 million rows will be <= 8000, but the other 10% will be <= 15,000.

I obviously can't use the VARCHAR due to its 8,000 limit, so I assumed my only option is the text datatype. I'm concerned the overhead of 'text' and that it may be removed eventually. I'm now looking at VARCHAR(max), but I've never used it in a production environment.

Should I use varchar(max), text, or look for a way to compress that 10% of data and store it in a varchar(8000)?


texts: How to restore SQL Server 2008 R2 backup to SQL Server 2008

texts:
I am unable to restore a backup from a 64-bit instance onto a 32-bit instance.

Source Server:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

Destination server:

Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (Intel X86)

Error while restoring:

  Restore failed for Server 'servername'
  The database was backed up on a server running version 10.50.2500. That version is incompatible with this server, which is running version 10.00.4064. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.


texts: Transaction Replication on Azure MI servers

texts:
I'm struggling to verify whether an Azure Managed Instance of SQL Server can both subscribe to Transactional Replication, and Publish, at the same time - I've been googling like a demon and stackexchanging and all the rest of it and am getting wildly conflicting answers!!

Please please please - is this possible? If so, I'll work out how, but as the MI hasn't been put up yet I can't just try it and see...I can't submit the proposal to our change review board until I know how many servers I need, which I don't know until I know if I can do this...I'm stuck in a loop!!

Finally (and I'm happy to put this as a new question if needed...) when setting up the second publication from our other MI, there's no option to change the username for the publication - is this a usual expected limitation? For on Prem I'm used to being able to supply a different username for different publications...my colleague is concerned that having the same user means (and I quote): "that would be trying to use the same log reader agent on two different Publications (with different tables in them)? I'm concerned about conflicts and performance" - personally I don't see how having the same user will cause an issue though???


texts: Are there tools to perform data comparison between two different schemas?

texts:
We are migrating our database to a new schema but want to validate the data has been moved correctly.

Traditional data comparison tools can compare two databases for differences if the schemas are the same. In our case there have been changes to the table designs but all the data from the old schema is in the new one, it's just been moved around a bit, and I need to make sure it is correct. We have tens of millions of rows so manual inspection is not an option.

Are there any tools which could aid in this type of comparison?

If not are there any libraries/frameworks which could help kick start the development of a custom solution?

I'm happy to use a database specific solution if necessary, in this case for SQL Server 2008.

My soluton: I'm comparing the two data sets by creating a VIEW of each table on the old database with the same fields as the new database table.

I then compare the data using the technique described here: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

I'm lucky in this migration as the overall table structure is similar to the old database, with fields moved from one table to another, dropped or added. In the case of the dropped and added there is nothing to compare, for the fields which have moved or been aggregated I do calculations in the view to provide the correct information for comparison.

The UNION comparison shows me only the rows with differences, so as soon as the data is correct I get an empty result set.


texts: Why changing the database owner doesn't change the owner in PostgreSQL dump?

texts:
I was exporting a database using pg_dump command when I realized that the owner of the database had to be changed. So I change it, and make extra sure that the newuser is correct. Then try the command again:

pg_dump -h localhost -U postgres database_name -f mydb.dump

But this didn't work. The output file still identified the owner as the olduser. Why could this be happening? How can I make sure this doesn't happen?


texts:
Instance info: Microsoft SQL Server 2017 (RTM-CU13) Standard Edition (64-bit) on Windows Server 2019 Datacenter
Location: AWS

A Java app on another AWS server logs into the SQL instance using a local SQL account, and 95% of the time, the login connects, and everything is fine. The other 5% I get following error:

"Login failed for user 'Blah.' Reason: Failed to open the explicitly specified database 'Blah_Database.' [CLIENT: xxx.xxx.xxx.xxx]"

I know the 2 IP's that the login request is coming from and the connection string names the database which is present. The permissions are set correctly.
I've captured the event running a Profiler Trace looking for Event Class "Audit Failed Login." But, This doesn't give me any more info than the SQL error log.
Any other event classes I can add to the profiler to gather more info?
I've verified the the database Auto-Close feature is off. I've also increased the remote login timeout to 30.


texts: Should I backup or copy [an Access database file]?

texts:
Access has the option of creating a backup of dbase, but is there any difference between doing this and 'saving database as'?

Thanks for your time and patience for the ignorant.


texts: Duplicated id error, key is actually NOT duplicated

texts:
I have the following query:

WITH data AS (
    SELECT
        profileid as id_user,
        cast(terms_accepted as boolean) as terms_accepted_passport,
        lastname as last_name_user,
        firstname as first_name_user,
        picture_serving_url as picture_user,
        is_active as status_user,
        is_passport_active as status_passport,
        language as language_id_user,
        to_timestamp(created, 'YYYY-MM-DD HH24:MI:SS') as created_user,
        to_timestamp(modified, 'YYYY-MM-DD HH24:MI:SS') as modified_user,
        passport_completion_level as completion_level_passport,
        email as email_user,
        about_me as description_user,
        uni_code as institution_id_user,
        to_json(metadata) as metadata_misc
    FROM import_temp_table
)
INSERT INTO passport (
    id,
    public,
    status,
    completion_level,
    user_id,
    terms_accepted
)
SELECT
    (SELECT MAX(id) + 1 FROM passport),
    TRUE,
    CASE status_passport
        WHEN LOWER('true') THEN 1
        WHEN LOWER('false') THEN 2
    END AS status_passport,
    completion_level_passport,
    id_user,
    terms_accepted_passport
FROM data

So I was trying this query and it throws the following error:

ERROR:  duplicate key value violates unique constraint "passport_pkey"
DETAIL:  Key (id)=(108) already exists.

Problem is, id 108 DOESN'T exist. When you perform a single insert with the hardcoded id as 108, it performs it, however, when you try and loop it like this here, it doesn't. Any idea how can I accomplish this?

EDIT

Table definition for Passport is here:

CREATE TABLE public.passport
(
    id integer NOT NULL DEFAULT nextval('passport_id_seq'::regclass),
    public boolean,
    status integer,
    completion_level character varying COLLATE pg_catalog."default",
    user_id character varying COLLATE pg_catalog."default",
    application_id integer,
    terms_accepted boolean,
    CONSTRAINT passport_pkey PRIMARY KEY (id),
    CONSTRAINT passport_application_id_fkey FOREIGN KEY (application_id)
        REFERENCES public.application (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT passport_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)


texts:
I'm trying PgBouncer for the first time with a simple Python web application (Flask + psycopg2), and I have a hard time interpreting the meaning of its log messages by simple googling. I'm basically seeing a bunch of:

2014-06-09 09:25:07.867 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 login attempt: db=vinum user=vinum
2014-06-09 09:25:07.867 20980 LOG S-0x1b38bf0: vinum/vinum@127.0.0.1:5432 new connection to server
2014-06-09 09:25:07.875 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 closing because: client close request (age=0)
2014-06-09 09:25:15.626 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 login attempt: db=vinum user=vinum
2014-06-09 09:25:16.058 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 closing because: client close request (age=0)
2014-06-09 09:25:16.762 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 login attempt: db=vinum user=vinum
2014-06-09 09:25:16.796 20980 LOG C-0x1b1b3a8: vinum/vinum@unix:6432 login attempt: db=vinum user=vinum
2014-06-09 09:25:16.796 20980 LOG S-0x1b38d58: vinum/vinum@127.0.0.1:5432 new connection to server
2014-06-09 09:25:17.181 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 closing because: client close request (age=0)
2014-06-09 09:25:17.240 20980 LOG C-0x1b1b3a8: vinum/vinum@unix:6432 closing because: client close request (age=0)

I find these multiple closings with age=0 worrying, do they mean that the connections are always recreated, thus never pooled?


texts: I understand how it works, but the book I'm studying doesn't tell me why you'd want to partition. Could someone shed some light? I can't understand any concept unless I see how it's useful.


texts:
I have an old Sql Server db with a ‘free entry’ field that is being accessed via a Postgres 10.6 foreign table.  This field contains a lot of garbage characters (higher than Ascii 128) which Postgres has a difficult time rendering and results in ‘empty looking’ records.

I say empty looking as I am able to query those missing records out of the foreign table using the regexp_replace function to substitute spaces for those problematic characters.

This leaves me with two questions:
1)  The Postgres database encoding is UTF-8, so shouldn’t that be able to render any characters properly?

2)  Since I can query it, the Postgres db is obviously processing this data at some level.  Why does it display that entire field as blank until the special characters are replaced?


texts: Use jsonb array inside where IN or ANY in PostgreSQL

texts:
I want to select all products that have active shipments (those have one or more shipment_ids in shipments table).

My product table has a jsonb column called data like this:

{
    "name": "foo",
    "shipments": [1,2,5,7]
}

I thought it would be as simple as something like to this:

... where shipment.id IN (products.data->'shipments')

or

... where shipment.id = any(products.data->'shipments')

but it is not.

After some digging I have managed to get it working, but I do not know if that is the simplest and most efficient solution for this:

select "products".* from "products"
   cross join LATERAL (
  select array_agg(ps.shipment::int) as ids
  from jsonb_array_elements(products.data -> 'shipments') as ps(shipment)
  ) ps
  where exists(select from shipments as "sh" where sh.id = any (ps.ids))
order by id

So do I actually need that cross join? Or there is a way to simply cast the JSON array to a Postgres integer array?


texts: Changing SQL Server (MSSQLSERVER) Service Account

texts:
I am sorry if this has already been answered in the past.
We have SQL Server 2016 which is our Dynamics NAV Database. Currently all backups are stored on local drive of the server. I have been given a task to change the destination of backups to a NAS box network drive. On the maintenance plan I changed the destination path to the network drive \10.##.#.##\databasebackup. I received following error:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\10.##.#.##\databasebackup\navdatabase.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

After having a look at SQL Server configuration manager, I found that the Log On As account for SQL Server (MSSQLSERVER) is NT Service\MSSQLSERVER but Log On As account for SQL Server Agent (MSSQLSERVER) is domain service account. The service account used for SQL Server Agent (MSSQLSERVER) has read/write access to the network drive \10.##.#.##\databasebackup. In this case I believe I need to change the Log On As account for SQL Server (MSSQLSERVER) to a domain account, am I correct? If I change the NT Service\MSSQLSERVER to a domain account which has read/write access to the network share, does it break anything?
Thank you in advance for your help.


texts: How to turn Event Scheduler On permanently in MySQL?

texts:
How do I turn on the event scheduler in mysqld permanently? I'm using phpMyAdmin and I have scheduled an event to copy records from one table to another everyday at a specific time, but the records are not being copied everyday. I've tried setting the event scheduler on.

SET GLOBAL event_scheduler="ON";

When I restart my pc, the event scheduler is turned off. How do I make it be always on, and not turned off when mysqld is restarted?


texts: MongoDB - Update all documents NOT returned in aggregate query

texts:
I'm using PyMongo to write a Mongo update script. I have an aggregate query which returns a sum of all searches performed by each user in the last 30 days:

cursor = mongo_search_logs_collection.aggregate([
    {
        "$match": {
            "created": {"$gte": datetime(search_count_start_date.year, search_count_start_date.month,
                                         search_count_start_date.day)},
            "primarySearch": True
        }
    },
    {
        "$group": {
            "_id": "$createdBy", "searchCount": {"$sum": 1}
        }
    }
])

I would like to iterate over all documents NOT included in the aggregate query results from above, and set their searchCount to zero. Is there an easy/clean way to do that?

I tried simply iterating over all users, checking if a user exists in the list of users returned by the aggregate, and then updating each search count accordingly, but this is taking a LONG time...I'd prefer a faster way of doing it if possible.


texts: How do I store phone numbers in PostgreSQL?

texts: Let's assume I want to store phone numbers in a database. I may accept phone numbers from outside of the United States. How would I go about storing these phone numbers?


texts: Possible to have a statement in stored procedure outside of current transaction?

texts:
During a series of statements in my stored procedure I wish to log an entry by inserting a record. That record needs to survive even if the overall SP ends up rolling back.

Is this possible?


texts: issues with creating varchar returning functions

texts:
I have an issue while creating functions in sql server, am very new to sql server
Below mentioned is the code which i used, please any issues with syntax.

CREATE FUNCTION Fn_Check_Customer_Credit (@AcctCode VARCHAR(20), @CurrentAmount NUMERIC(20,2), @CurrentDate DATETIME, @CompCode VARCHAR(20), @CurrentUser VARCHAR(20))
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @Str_Block_Limit     VARCHAR(100)
  DECLARE @Str_Check_Days      VARCHAR(100)
  DECLARE @Str_Acct_Lock       VARCHAR(10)
  DECLARE @Str_Return          VARCHAR(100)
  DECLARE @Num_Credit_Limit    NUMERIC(20,8)
  DECLARE @Num_Credit_Days     NUMERIC(20,8)
  DECLARE @Num_Current_Credit  NUMERIC(20,8)
  DECLARE @Num_Total_Pending   NUMERIC(20,8)
  DECLARE @Num_Temp            INT
  DECLARE @Num_CurrentBal      NUMERIC(20,8)

  SELECT @Num_Temp = COUNT(*) FROM GN_USER_SETTINGS_DETAIL WHERE USER_NAME = @CurrentUser AND SETUP_CODE = 3;  -- Credit Limit Exception
  SELECT @Str_Block_Limit = CONFIG_VALUE FROM GN_ELIXIR_CONFIG WHERE CONFIG_NAME = 'BLOCK_CUSTOMER_CREDIT_LIMIT' AND COMP_CODE = @CompCode;
  SELECT @Num_Credit_Limit = CREDIT_LIMIT, @Num_Credit_Days = CREDIT_DAYS, @Str_Acct_Lock = LOCK_ACCT FROM AC_SL_MASTER WHERE ACCT_CODE = @AcctCode;
  SELECT @Num_CurrentBal = (SUM(A.AMOUNT) + SUM(A.DN_AMOUNT))
            FROM (SELECT SUM(ISNULL(DOC_BALANCE, 0)) AMOUNT, 0 DN_AMOUNT
                  FROM Fn_Aging(@CurrentDate,@CompCode,@AcctCode)
                  UNION ALL
                  SELECT SUM(ISNULL(ITEM_QTY, 0) * ISNULL(ITEM_RATE, 0) - ISNULL(DISCOUNT, 0)) AMOUNT, SUM(ISNULL(ITEM_QTY, 0) * ISNULL(ITEM_RATE, 0) - ISNULL(DISCOUNT, 0)) DN_AMOUNT
                  FROM VW_PENDING_DNOTE
                  WHERE ACCT_CODE = @AcctCode) A;
  IF @Str_Acct_Lock = 'Y'  -- CURRENT CUSTOMER IS LOCKED.
    RETURN 'Account is Locked';
  IF @Str_Block_Limit = 'N'  -- NO NEED TO CHECK THE CREDIT LIMIT.
    RETURN NULL;

  IF ISNULL(@Num_Temp, 0) > 0  -- CUSER IS ALLOWED TO ENTER BEYOND THE CREDIT LIMIT OF THE CUSTOMERS.
    RETURN NULL;
  IF (@Num_Credit_Limit  0)  ---- CURRENT CREDIT IS GREATER THAN OR EQUAL TO THE CREDIT LIMIT.
    RETURN 'Credit Limit Exceeded.';
  ELSE
    RETURN '0';
END;


texts: Is there a recommended way to update a database schema when updating and app's RPMs?

texts:
I have a pretty standard server layout:

2 Application servers running Tomcat on Redhat.
2 Database servers running Postgres on Redhat. One database is a master and replicates to the slave.

I'd like to automatically upgrade the scheme when I update the web apps. The web apps are deployed using RPMs. I'm at a loss to figure out if there's a recommended way of doing this.


texts: Isssue with Database Links converting OEM Job to DBMS_Scheduler Job

texts:
We had an OEM job that would run a PL/SQL block using a particular database users credentials.  The credentials are the same for two different databases, so there was no problem for the block to use a private database link without credentials to pull data from the second database into the first.

We migrated the job to DBMS_SCHEDULER, but the job fails with the following error:

ORA-04052: error occurred when looking up remote object PATIENT.PTTRANS@BILL10G
ORA-00604: error occurred at recursive SQL level 2 ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from BILL10G

I haven't found definitive information, but the things I have read seem to indicate that since scheduler jobs do not have a login, they cannot use database links that use passed credentials.  That would make sense since I get the same error whether the job is owned by SYSTEM or by the user that normally runs the block (and still can when logged in).

Can anyone provide further explanation of this situation or more importantly explain ways to work around this issue?  I can think of several, but I'm not sure I like any of them.


texts: Targeting MSX with job on Multi-Server Administration

texts:
I am using Multi-Server Administration to execute jobs on multiple targets. This works well, but I am not able to enlist the master as a target. I receive the following error when attempting to enlist the MSX as a TSX:

  Server 'XXXXX' is an MSX. Cannot enlist one MSX into another MSX.
  (Microsoft SQL Server, Error: 14299)

I would think that running these same jobs on the master would not require managing a local job as well as the multi-server jobs.

Any help is appreciated.


texts: Is there a more efficient way of using a match score in MySQL full text search?

texts:
SELECT i.*, MATCH(i.description, i.keywords) AGAINST ("searchterm") AS score
        FROM items AS i
        WHERE i.instock=1
        AND MATCH(i.description, i.keywords) AGAINST("searchterm")
    ORDER BY score DESC  LIMIT 0, 80

I'm wondering if there is a way to write the query so that the match operation is not preformed twice?


texts: Search for partial matches in website domain names

texts:
I have a Postgres database with information about websites and a table called sites with a column host. This already has an index for text pattern operations which is excellent for searching from the start of the domain name: WHERE host LIKE 'www.bran% but not so good for partial matches such as WHERE host LIKE '%.bran%' which result in a scan. There are currently around 7.5 million hosts so search performance is already noticeable.

Based on the suggestion below I've added a trigrm index (you need to enable to extension in order to do this:

create extension pg_trgm;

and then add a suitable index:

CREATE INDEX trgm_idx ON sites USING GIST (host gist_trgm_ops);

As with all indices this may take some time depending upon the size of your database.

Before the index my queries were taking about 180s to find 200 items in 7.5 million but this is complicated by a correlated subquery, which seems to be complicating the matter and dominating the query and suggests I should be looking at optimising something else first, specifically a date index.

So the results of the comparison: using a text_ops index takes about 2s and using a trigram about 7. I think I might have more luck if I create an index that only contains the relevant part of the host name.


texts: Sync MySql Replication from Master2 to Master1 after failover

texts:
Due to Disk space issue Master2 server crashed and it was rebuilt again using mysqldump of Master1.
Both Master 1 and Master2 are in sync and Master2 is in Read_only mode.
During the crash, I stopped the Slave instance in Master1 and it's been over a week now. Now how to restart replication from Master2 to Master1.
Can changing the Master Log position in Master1 to current Master2 status works?


texts: Does SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime?

texts:
Putting together a quickie proc to help with debugging, I ran into a what seems to be a error in the compiler.
create proc spFoo
    @param bit
as
begin
    if @param = 0
    begin
        select *
        into #bar
        from [master].dbo.spt_values
        -- where number between ...
    end
    else
    begin
        select top 10 *
        into #bar
        from [master].dbo.spt_values
        order by newid();
    end;
end;

Attempting the above returns the following error

Msg 2714, Level 16, State 1, Procedure spFoo, Line 19
There is already an object named '#bar' in the database.

In a human-readable sense, the proc appears to be fine: only one select into statement will ever be executed since they're wrapped inside the if-else blocks. Very well though, SQL server can't confirm that the statements are logically excluded from each other. Perhaps more confusing though is that the error remains when the drop table #foo is placed inside the if-else block ( which one assumes would tell the compiler to deallocate the object name ) as below.
create proc spFoo
    @param bit
as
begin
    select top 1 *
    into #bar
    from [master].dbo.spt_values

    if @param = 0
    begin
        drop table #bar;

        select *
        into #bar
        from [master].dbo.spt_values
        -- where number between ...
    end
    else
    begin
        drop table #bar;

        select top 10 *
        into #bar
        from [master].dbo.spt_values
        order by newid();
    end;
end;

The proc itself is fine. I sucked it up and wrote the create table #foo( ... ) and insert #foo ( ... ) statements, I'd been trying to skip with the select * into  syntax. At this point, I'm just trying to understand why the compiler crapped out on me with the lazy-guy syntax. The only thing I can think of is that the DDL command reserves the object name IN TEMPDB.
Why the bold text?
create proc spIck
as
begin
    create table #ack ( col1 int );
    drop table #ack;
    create table #ack ( colA char( 1 ) );
    drop table #ack;
end;

This fails with the same error code as above. But the following...
create proc spIck
as
begin
    create table ack ( col1 int );
    drop table ack;
    create table ack ( colA char( 1 ) );
    drop table ack;
end;

... succeeds. The same follows above to the original proc attempt. So...
My Question Is This
What is the difference ( and why is it present ) in object name reservation for TempDB objects as opposed to user databases. None of the Logical Query Processing references nor DDL command references I've reviewed appear to explain this.


texts: What ports does SQL Server use on Linux? I want to be sure I'm forwarding the ports required when I run it in a container.


texts: Will a DBCC CHECKDB with repair have to rescan?

texts:
I've run a DBCC CHECKDB and it found allocation errors. When I run it with REPAIR_ALLOW_DATA_LOSS - will it need to take the same amount of time to scan for errors, or will it attempt to use the last CHECKDB I ran and go faster?

(SQL Server 2000.  Yes its ancient, no I can't upgrade.)


texts: Avoiding repetition without creating a view

texts:
Suppose that I have a query Q1 and I need to run a query like the following:

Q1
union
select *
from (some query that uses Q1 outcome)

I would like to do that:

Without creating any view
Without running twice the Q1 query.

How can I do that on PostgreSQL?


texts: What would cause "SELECT permission denied" for an object?

texts:
This is a work backwards kind of issue, since I can't tell you all of the requirements to cause this to happen.

I'm getting the following error for a table (TABLE01) in one database (DB01), but not a table (TABLE02) in the other (DB02), for a brand new user (USER01), when building a view (VIEW##) and granting SELECT permission on the view for the user.  I can correct it by granting SELECT permission on the table (TABLE01) in the database that's causing the issue (DB01).  But why wouldn't I need to do it in DB02?

CREATE VIEW [DB01].[x].[VIEW01] AS SELECT * FROM [DB01].[dbo].[TABLE01]

GRANT SELECT ON [DB01].[x].[VIEW01] TO [domainx\USER01];

SELECT * FROM [DB01].[x].[VIEW01]; -- throws error below
-- The SELECT permission was denied on the object 'TABLE01', database 'DB01', schema 'x'."

GRANT SELECT ON [DB01].[dbo].[TABLE01] TO [domainx\USER01]; -- fixes error above

BUT IN DB02, I ONLY HAD TO RUN TWO COMMANDS TO GRANT SELECT PERMISSION.  WHY WOULD THIS BE?

CREATE VIEW [DB02].[x].[VIEW02] AS SELECT * FROM [DB02].[dbo].[TABLE02]

GRANT SELECT ON [DB02].[x].[VIEW02] TO [domainx\USER01];

SELECT * FROM [DB02].[x].[VIEW02]; -- works fine; returns results


texts: Ordering of a dimension in sql server datawarehouse

texts:
Built and deployed a simple cube with a date dimension.  The dimension has things like day of month.  How do i sort it?  it always shows in the reports as
1, 10, 11, 12, ... 19, 2, 20, 21

Design.

DimDate (3 columns)

DateID  KEY
Date    DATETIME  {Values  1/1/2000, 1/2/2000, ect}
DAY     VARCHAR(2)   {Values 1,2,3,4,5,6,7,ect}

FactAppointment (one column)

DateID  {Values key to dim}

I set the dims sort column to key or date and neither works.

When excel, or any other bi tool is connected and the date dim is a column, a simple count is the value the dates are not sorted.

Picture:  Column headers are not ordered properly.
1, 10,11,12,...,19,2,20


texts: Cannot open the datefile when running a scheduled ssis package. SQL

texts:
So I have researched this problem thoroughly, or so I think. I want to schedule an upload of a CSV to our database in SQL Server.  I have created the SSIS package using tasks --> import data.  When I initially create and run the package it works fine.  However, when I want to schedule the package to run I run into an error.  "Cannot open the datafile "C:\Users\mydomainaccount\CsvToUpload.csv"  The file is saved locally on my laptop, however, the ultimate goal is to have the source be a mapped drive where our CSV files are.

This is what I have do so far to trouble shoot:

When creating the package I have saved the SSIS package using both the SQL Server option and the File System option, both will work initially running the package, but neither work when attempting to schedule the job.  Also, for protection level I have tried the Encrypt sensitive data password and the do not save sensitive data options.

I checked and made sure that SYSTEM has full control of the folder and file where the package and csv are located.

The job step is being run as a proxy account that I created, I have also tried running it as the SQL Server Agent Service Account.

The owner of the job is my domain account.  My account has sysadmin privileges for that database and server.  Under the msdb database option I have given my account SQLAgentOperator, Reader, and User Roles.  the NT Service\SQLAgent and NT Service\MSSQL accounts have those same roles and sysadmin privileges.

So basically, I know the package will run, it is just when scheduled and seems to have some sort of permissions issue that is not allowing it to run.  Any help or suggestions would be great!

https://i.stack.imgur.com/XqZWb.png


texts: Upgrade Database from SQL Server 2008 to SQL Server 2008 R2

texts:
I've upgrade my database from SQL Server 2008 to 2008 R2 (v10.50.1600.1) on a remote server. Version of my local SQL Server is 10.50.1600.1.

When attempting to restore database from remote server to my system, I get this erro. How can I solve my problem?


texts: MYSQL ASSERTION on same last name and dob

texts:
I have to make an assertion on this:
No patients should have the same last name and date of birth and are treated by the name same doctor.
CREATE TABLE Patient(
    SSN                 varchar(11) primary key,
    First_name          varchar(255),
    Last_name           varchar(255),
    Birthday            Date,
    Doctor              varchar(11),
    FOREIGN KEY (Doctor) REFERENCES Doctor(SSN),

);

CREATE ASSERTION patient Check(
not exists(

?
);


texts: Import csv data from localhost emacs SQLi error MySQL

texts:
I'm trying to import

LOAD DATA LOCAL INFILE "/home/frederic/cowtest.csv"
    INTO TABLE cow_db1 FIELDS TERMINATED BY ","
    ENCLOSED BY '"' LINES TERMINATED BY '\n'
    IGNORE 1 LINES (state_abb, c_code, state_name);

I get: ERROR 1148 (42000): The used command is not allowed with this MySQL version

I connected mysql with M-x sql-mysql and

  User = root
  Password = .....
  Database =
  Server = localhost

But running directly from the shell after

mysql -h localhost -u root -p --local-infile

does not return the error but

Query OK, 243 rows affected (0.05 sec)
Records: 243  Deleted: 0  Skipped: 0  Warnings: 0

I tried with LOAD DATA LOCAL INFILE instead (has recommended in other solution) and then it returns ERROR 29 (HY000): File '/home/frederic/cowtest.csv' not found (Errcode: 13)

So it is likely that things goes unspecified about SQLi extension, and the  --local-infile specification.

Many thanks
frederic


texts: Why do different accounts connecting to the same database see different data?

texts:
At my work, we have an Oracle database that I can connect to using different accounts.

The server name and port are identical between the accounts, but each account sees different data, and can see different table schemas. (I do mean different columns in different tables. i.e. if I add a column to a table in one account, I don't see that change reflected in the other account.)

Is there a name for this practice? I would like to read up on it, but I could not find anything in google.

Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


texts: SQL Server - Queries are much faster on the application when run second time

texts:
When I click on a screen on my vb application, it takes approx 6/7 seconds to display the results. When I click off the screen and click back again into the screen, it returns immediately.

I ran profiler, and can see there are multiple queries running when clicking on this screen. There wasn't anything glaring obvious to fix on these queries; when run one by one in SQL Server, they ran within 1 second. The execution plan didn't recommend any missing indexes (I did add 2 indexes myself to prevent some key look ups but that was it).

My last test was to update statistics. I did this, updated all statistics on our local copy, but this still doesn't return the results on the application any faster.

Any advice on how to return these results immediately on the application? Perhaps statistics are missing from these queries? Or is there a way to auto update stats or similar for these specific queries?

SQL Server Settings:

Auto Create Stats - True

Auto Update Status - True

Auto Create Incremental Stats - False

Auto Update Stats Async - False


texts:
On our SQL Server cluster, I get messages when the backup runs on secondary node.

BACKUP failed to complete the command BACKUP LOG activity. Check the backup application log for detailed messages.

and

DESCRIPTION: the target database, 'engageone', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

I understand that it is because the secondary DB is read only.
I would like to solve this problem.
I think if I set secondary DB as Readable Secondary = Yes or Read-intent only , after that this problem will be solved.
But I have question?
If I set parameter "Readable Secondary" to Yes or Read-intent only and secondary DB will be disconnected and someone sends query to secondary DB?
What will happen? The secondary DB will response? or not?
Of course, all our application send queries to Cluster name.


texts: When do I use another type index than integer? (eg string) [integer vs string index]

texts:
I was reading about relational databases like sqlite and I always see the schema for user storage like:

UserID (index) | Username | Password

but wouldn't it be actually much faster to use Username as an index?

For example when a user logs in you query WHERE username == some_name, the database engine has to search through all record for this data, right? (namely SQLite? - I am using this)

Why do I always see this "ID" and not just username as the index?

Assuming all names will be unique, what is the point in an integer based index?

What is the actual difference in terms of lookup speed? Or space? (assuming a maximum character count at 24 characters).

Or would it be even better to just make a additional/separate lookup table for username<->userid resolving and then load the data with userid from the main table where the index is the userid?


texts:
I am trying to find the best method to store medical ailments , like Diabetes, Hypertension, Cancer etc in a
database.

My form will list over 200 ailments and the user will check those that affect him. I could serialize those
selected and then store it in a database but I want to be able to use the information at a later stage.
For example I want to know what ailments are selected with Diabetes and Hypertension.
Does this mean I will have to create a field for each ailment in my database ?

I am confused here.

I would appreciate any assistance.

Thanks
Kimmy


texts: Moving PostgreSQL database to new server

texts:
I had PostgreSQL 8.3 running on a windows machine. The hard drive recently became unbootable, but the data is fine. We got a new machine and I'm re-installing Postgres. The new installer only has version 8.4 and a variety of 9.x releases.

Would I be able to install 8.4 and move the data over with no problems, or do I need to find the 8.3 installer. How about version 9?  Is the migration as simple as moving the old "C:\Program Files\PostgreSQL\8.3\data" to the exact same place on the new directory.

Thanks,
Kevin


texts: Is it possible to do an in-place upgrade of SQL Server 2005 Standard to SQL 2012 Enterprise?

texts:
At the moment, I have a SQL 2005 Standard box. I'd like to upgrade it to SQL 2012 Enterprise in-place.  When I say in place, I mean an install similar to Office 2010, e.g. it removes the older (Office 2007) version, binds itself to extensions once held by its predecessor and keeps all the settings as well.

Is this possible?

If not, what should be the procedure?  So far, I got this.

Back up the databases
Write down all the settings.
Uninstall SQL 2005.
Install SQL 2012 Enterprise.
Restore settings
Restore the databases.


texts: How to turn off autocommit in phpMyAdmin

texts:
I want to practice rolling back commands, savepoints and more on phpMyAdmin but I couldn't find a way to turn off autocommit in phpMyadmin. Currently I use the command SET autocommit = 0;.
                                                                                I want to know how, instead of typing this command before each query, if there is another way to perform this task?


texts: mysql multiple database prioritize innodb buffer pool size

texts: With a mysql instance and multiple databases within that instance is there a way to allocate which database you would like to assign priority to the buffer pool size?


texts: How will you map the permission control between two entities?

texts:
I have the following entities: Producer, Consumer.

A Producer produces Food for the Consumer.
A Consumer consumes Food produced by the Producer.

Now I am tasked to design a schema which maps which consumers can eat food produced by each producer.

As an example, I have Producers p1, p2, p3 and Consumers c1, c2, c3.

   c1  c2  c3
p1  N   Y   N
p2  Y   N   N
p3  N   N   Y

where N = No and Y = Yes

Keep in mind that

the number of consumers and producers will keep growing.
the default state of whether a consumer can eat food from a new producer is No

How should I define this schema?


texts: Is there a way to make the subscriber database read-only when using Transaction Replication?

texts:
I have set up Transaction Replication and its working well.

I have a few different web apps that connect to the publisher and subscriber database and I am concerned that if they use the subscriber database for inserting/updating/deleting intentionally, or accidentally data will be lost. (it should essentially be used as a read-only database because I have not setup updatable subscriptions).

I tried changing the database properties to be read-only, but this caused the replication to fail.

I was thinking maybe a before and after script during replication to add/remove the database read-only flag?

Is there a better way to do this?


texts:
I am implementing a sharded cluster in a Windows environment.

I first create the config server as follows:

C:\MongoDB\bin>mongod –configsvr –bind_ip 192.168.1.4 –port 3000 –dbpath C:\Sharding\configServer

Then I try to connect to the mongos instance which is listening on port 3000:

The command I use to connect to the mongos instance is as follows:

C:\MongoDB\bin>mongos –configdb 192.168.1.4:3000

My problem is that I cannot connect with the mongos instance as I get the below error. I cannot figure out what is this problem and how to solve it.  I searched on the Internet however all suggestions that I found did not solve my problem and I'm literally stuck.


texts: Improving accurate count mechanism in PostgreSQL

texts:
I have a problem I have been facing for some time and was ignoring for a while. The problem of counting rows. I have read that it is slow as the PostgreSQL has to validate each row thus have to do sequential scan. I am currently using PostgreSQL 13.
Potential solutions I came across:

Using Estimate.
Although the estimation was closer when querying a single table, the result was far off when using joins. Also I want exact count as I need to show the exact number to the user like how many transactions he did.

Using an external table to maintain the count.
The idea here was to use a rule or after insert trigger to update the count using count = count + 1 The problem here is the user can add multiple filters (like date, userid etc), which will change the count but the external table is only storing the count.

How would I solve this? I am trying to reduce the time to get the count to less than 3-4 seconds. Currently, there are almost 1 billion records in a table, although this table is split into 12 partitions for each month. Is this even possible?
Eleven partitions won't have inserts but could have delete operations. The user can apply filters which will change the count for any partition.
I am using a b-tree index on the date column. In explain analyze, the index is being used but since the records are huge, the b-tree index cannot fit in RAM as it's size is also big.
The tables are partitioned by each month and each partition has an index on the date, which means there is one index for each month.


texts: DDL statements being commited within transaction

texts:
I am working on a script that involves a lot of structural changes, and like to return to a "base" condition while I work on what all needs to be changed, so I have my changes wrapped in a begin/rollback transaction.

This works just fine IF there are no errors, but when I make a mistake (attempting to drop a column that doesn't exist or which has a FK constraint, etc), the rollback does not work.  So, objects that I have renamed with sp_rename or columns that I have dropped, have the wrong name/don't exist.

Assuming that this is documented/correct behavior, where is it documented?  Probably just my eyes glazing over...but I can't recall/find anything that says a failure causes a commit.


texts: How do I create a user invitation model?

texts:
I am developing an application which allows 'users' to create 'lists'. I have a many-to-many relationship between 'users' and 'lists' (this might not be right). Additionally 'Lists' have many 'Tasks'.

What I would like to do is extend this model to include the idea of 'invitations'. I would like users to be able to invite one another to other lists. A user can create many invitations. A user can create an 'invitation' and the invitation would have one or many 'invitees' which, in turn, would be 'Users'. So am confused about how this should be organized in a relational db.

I think the core problem is: users 'own' invitations but also can be the recipient of an invitation. Clear as mud? :)

I was hoping someone could offer some advice on how this might be accomplished. Any example ERDs would be useful. If my question requires further clarification I can provide it.

Thanks!