texts: SQL Server 2012 + SQL Server 2008R2 on the same VM

texts:
I can't immediately find reference to it, but I remember reading somewhere that it's best practice not to mix versions of SQL Server on the same host. I believe the rationale was that often times, especially with clustering, you will need to install (Windows) hotfixes and those hotfixes might affect the different versions of SQL Server in unexpected ways.

However, it appears that mixing versions of SQL Server is supported by Microsoft.

I've tried searching on this, but it's been difficult to find a definitive source telling me that it would be OK to mix versions of SQL Server.

Can anyone definitively say it is OK to mix versions, or instead show strong evidence for why you should not?


texts: Why can't I access some tables in postgresql

texts:
I am running into some strange problem in PostgreSQL

$ sudo -u postgres psql Blogging
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

Blogging=# \dt
               List of relations
 Schema |         Name          | Type  | Owner
--------+-----------------------+-------+-------
 public | Blogs                 | table | e
 public | Posts                 | table | e
 public | __EFMigrationsHistory | table | e
(3 rows)

Blogging=# select * from Blogs
Blogging-# ;
ERROR:  relation "blogs" does not exist
LINE 1: select * from Blogs
                      ^

The same problem occurs when I log in as the owner of the tables:

$ psql -U e Blogging
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

Blogging=# \dt
               List of relations
 Schema |         Name          | Type  | Owner
--------+-----------------------+-------+-------
 public | Blogs                 | table | e
 public | Posts                 | table | e
 public | __EFMigrationsHistory | table | e
(3 rows)

Blogging=# select * from Blogs;
ERROR:  relation "blogs" does not exist

I created the database when running my application which uses Entity Framework, following https://docs.microsoft.com/en-us/ef/core/get-started/?tabs=netcore-cli. But I had some problem with the application, not sure if they are related to my question here.


texts:
i'm trying to understand how partitioning works, and main manual doesn't help me a lot.

Also, PostgreSQL 10 supports partitioning natively... but what does it mean?

is it possible to declare table:

CREATE TABLE public.audit_logs (
        log_type varchar(31) NOT NULL,
        id int8 NOT NULL DEFAULT nextval('audit_logs_id_seq'::regclass),
        additional_data varchar(1024) NULL DEFAULT NULL::character varying,
        week int NOT NULL DEFAULT date_trunc('week', CURRENT_TIMESTAMP),
        CONSTRAINT audit_logs_pkey PRIMARY KEY (id)
)
WITH (
        OIDS=FALSE
)
PARTITION BY  (week) ;

and how to create auto tables public.audit_logs_N, where N would be week number?

Do i need to create a trigger?
or what?


texts: Are there problems with using SQL Server AlwaysOn Availability Groups with Windows 2008R2?

texts:
[Cross post from StackOverflow]

Brent Ozar published this webcast (slides, video with free registration) entitled "Real-Life SQL Server 2012: AlwaysOn Lessons Learned", in which he repeatedly states, with varying degrees of force, that

  using Windows 2008R2 with SQL Server 2012 Availability Groups is a bad idea

Among issues he raises are:

Quorum recalculation (a feature of Windows Failover Clustering introduced in Windows 2012)
Numerous bugs requiring Windows Hotfixes (list in AlwaysOn docs, "StackOverflow patch"), though I'm much less interested in problems that are fixed by bringing my deployment up to date.

He doesn't talk about other specifics of why using AGs with Win2008R2 is a bad idea, other than stating that AlwaysOn "pushes Windows clustering harder than any other app, and is uncovering breaking points". It is worth noting though that this is a supported configuration (AlwaysOn system requirements).

What problems have been seen in new deployments using SQL Server 2012 AlwaysOn Availability Groups with Windows Server 2008 R2, and are these eliminated by using Windows 2012? Are there problems seen in Windows 2008R2 that are fixed in Windows 2012 but do not have hotfixes available in 2008R2?


texts: What makes an Oracle Applications DBA different from an Oracle DBA?

texts:
All Oracle databases support some type of application that the Oracle DBA would need to have some knowledge of.  What makes an Oracle Applications DBA different than this?  Does the term mean that they support the Oracle Applications and not the database, or does it include responsibilities for both the Oracle Applications and the database.  If the latter then this seems to be done differently than every other type of application that can run with an Oracle back end.  For example, you don't see Oracle .NET DBAs.  Can someone clear up my limited understanding on this topic?

Update:
It seems very clear from the two answers so far that I did not word the question well enough.  My context is not Application DBA in the Developer DBA sense vs. Production DBA.  I am asking about the particular job title of "Oracle Applications DBA" or "Oracle Apps DBA".  Do a search for the phrase and you'll get results like the following:

http://becomeappsdba.blogspot.com/
http://appsdba.info/
http://teachmeoracle.com/interview.html
etc.

The best explination I have found so far is from http://www.club-oracle.com/articles/what-is-oracle-applications-259/ which says:

  Oracle Applications is an Enterprise Resource Planning Software, which
  is run by large industries to control their industry-wide operations.
  This software involves separate segments known as modules for separate
  business processes. Each module is basically a collection of forms
  (with some data displaying reports) where at each stage of the
  business process, data needs to be entered. The forms enforce industry
  best-practices controls on data input and process flow.


texts: Are there any downsides to referencing tables from another schema using synonyms?

texts:
We are developing a modular system composed by an administration module and a few web apps. The database model is also split in that way: we have a schema for the admin module and another for each application. For example:

Users will be shared across the system (you register and are given access to one or more applications), so the Users table is in the admin schema.
Application ACME will generate reports and store them, so the ACME Reports table is in the ACME schema.

Simple enough, except ACME reports are associated with a user. We could simply have copies of the Users table in each schema, and update them each time the "users master table" (admin schema) is changed, but that would be extremely painful to work with. Instead, we are sharing the table, and creating synonyms to abstract the code from that fact.
So, in our admin schema:
create table admin_users ( ... );
grant select, references on admin_users to acme;

And in our ACME schema:
create synonym acme_users for admin.admin_users;
alter table acme_reports add constraint fk_user foreign key (user_id)
      references acme_users (id);

This works just fine and will keep the referential integrity across schemas, but are there any downfalls to this approach we should be aware of, or any best practices we should follow here? With regards to either accessing tables from another schema, or using the (private) synonyms.
All the schemas will be on the same Oracle database, so there shouldn't be any significant performance impact (compared to having all the tables in the same schema). Right?
If this approach is valid, is there any practical way to manage the grants? This works:
grant select, references on admin_table to app_schema

But the number of tables which need to be granted will grow over time, and so will the number of schemas. Whenever we add a new schema, we would have to grant it the permissions for all the shared tables.
We thought of granting the permissions to a role instead, and just giving that role to the new schemas. But apparently that doesn't work:
ORA-01931: cannot grant REFERENCES to a role

Is there any other way to manage it?


texts: Order by column should have index or not?

texts: I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How performance will affect by making or not making index to that column?


texts: Why does this execution plan have Compute Scalars?

texts:
I have a simple SELECT statement.

USE [AdventureWorks2014]
GO

SELECT *
FROM Sales.SalesOrderDetail sod

The execution plan has two Compute Scalar.

Why is this?  I was expecting to just get the Index Scan or maybe a Table Scan?

The first (rght-most) has

 [[AdventureWorks2014].[Sales].[SalesOrderDetail].LineTotal] = Scalar Operator(isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPrice] as [sod].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sod].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2014].[Sales].[SalesOrderDetail].[OrderQty] as [sod].[OrderQty],0),(0.000000)))

When second has:

[[sod].LineTotal] = Scalar Operator([AdventureWorks2014].[Sales].[SalesOrderDetail].[LineTotal] as [sod].[LineTotal])


texts: Enabling/disabling/changing Oracle auditing without a shutdown?

texts:
I have a large database that needs auditing on a very detailed level (every select, update, insert, and delete, along with the actual text of the statement) for about half the users.  I know how to do this (here is a related question for anyone interested), but I also realize we cannot do this for any extended amount of time because of how much quickly we would be collective massive amounts of data.  So while there is a scheduled downtime coming up that we can implement the auditing, to change it to fine tune it (as management changes the request of what data they desire) or to disable it once we have enough data would require us having to take the database down to disable this.  While this wouldn't be too horrible to do if we were able to schedule a short downtime late at night, it would be really nice if this could be avoided altogether, but every reference I've seen so far requires the database to be brought down and back up.

So, my question (which I believe to be general enough for the purposes of this site, even though the back story is specific) is if there is a way to enable/disable/change auditing without shutting down the database.

Edit:  Oracle version 11r2.  As for AUD$ vs. FGA, I'm not sure what FGA is, but AUD is the table which will hold the data, so I am assuming that one.


texts: Why backup database by Barman can't reach end(pending)?

texts:
Did operations:

barman cron
barman receive-wal --create-slot pg
barman receive-wal pg
barman switch-wal --force --archive pg

Then run check and backup command:

-bash-4.2$ barman check pg
Server pg:
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
    replication slot: OK
    directories: OK
    retention policy settings: OK
    backup maximum age: OK (no last_backup_maximum_age provided)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: OK (have 0 backups, expected at least 0)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    systemid coherence: OK (no system Id stored on disk)
    pg_receivexlog: OK
    pg_receivexlog compatible: OK
    receive-wal running: OK
    archive_mode: OK
    archive_command: OK
    archiver errors: OK
-bash-4.2$ barman backup pg
Starting backup using postgres method for server pg in /var/lib/barman/pg/base/20200305T124531
Backup start at LSN: 0/5100A7B8 (000000010000000000000051, 0000A7B8)
Starting backup copy via pg_basebackup for 20200305T124531
(pending)

It can't finish after a very long time. Checked /var/lib/barman/pg/base/20200305T124531/ path:

backup.info
data

In data, it seems the data from pg server already been copied. See the content in backup.info:

backup_label=None
begin_offset=42936
begin_time=2020-03-05 12:45:31.020705+09:00
begin_wal=000000010000000000000051
begin_xlog=0/5100A7B8
config_file=/var/lib/pgsql/10/data/postgresql.conf
copy_stats=None
deduplicated_size=None
end_offset=None
end_time=None
end_wal=None
end_xlog=None
error=None
hba_file=/var/lib/pgsql/10/data/pg_hba.conf
ident_file=/var/lib/pgsql/10/data/pg_ident.conf
included_files=None
mode=postgres
pgdata=/var/lib/pgsql/10/data
server_name=pg
size=None
status=STARTED
systemid=6795775088862271947
tablespaces=None
timeline=1
version=100012
xlog_segment_size=16777216

Check process, got:

barman    5661  0.0  0.2 258560 19204 ?        Ss   12:45   0:00 /usr/bin/python2 /bin/barman -c /etc/barman.conf -q receive-wal pg
barman    5671  0.0  0.0 176384  3416 ?        S    12:45   0:00 /usr/pgsql-10/bin/pg_receivewal --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/var/lib/barman/pg/streaming --slot=barman
postgres  5675  0.0  0.0 397052  3844 ?        Ss   12:45   0:00 postgres: wal sender process streaming_barman 127.0.0.1(41570) streaming 0/534365C8
barman    5745  0.0  0.2 258564 19080 pts/0    S+   12:45   0:00 /usr/bin/python2 /bin/barman backup pg
barman    5751  0.0  0.0 176132  3316 pts/0    S+   12:45   0:00 /bin/pg_basebackup --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_streaming_backup -v --no-password --pgdata=/var/lib/barman/pg/base/20200305T124531/data --no-slot --wal-method=none
postgres  5752  0.0  0.0 396724  7980 ?        Ss   12:45   0:00 postgres: wal sender process streaming_barman 127.0.0.1(41618) sending backup "pg_basebackup base backup"

Why it can't finish? status=STARTED Didn't update to finish status.


texts: mysql trigger AFTER INSERT if condition syntax error

texts:
#1064 - You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near '' at line 7

DELIMITER //
  CREATE TRIGGER after_tableA_insert AFTER INSERT ON tableA
  FOR EACH ROW
  BEGIN
     IF(TIMESTAMPDIFF(HOUR, `triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN
       INSERT into error
       SET triedTime = NEW.changedat;
  END //
DELIMITER ;


texts: SSIS Get max value in column and create new column?

texts:
I'm importing two data sources. The problem is that one data source has a date, the other does not. Everything in the data flow has the same date and is inserted as one transaction into the same destination table.

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

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

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

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

Update: Possible Solution?

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


texts: Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED

texts:
Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few - it's OK here to use a lower transaction level since I know the relevant values won't be updated while this task is running so before calling each stored proc I run:

set session transaction isolation level read uncommitted

then call the stored proc which gets a subset of IDs to process. SQLFiddle of the overall operation: http://sqlfiddle.com/#!9/192d62 (somewhat contrived but keeps to the structure of the original queries)

The reason I ask is the deadlocks continue to happen and looking into the Monitor output there's one thread requesting a shared lock while another holds an exclusive lock over the same space (or vice-versa) - shouldn't setting that transaction level prevent the need for a shared lock? Is there a reason to get a shared lock aside from repeatable-read?

Using InnoDB.

Relevant lock information from show engine innodb status (edited to match table name from SQLFiddle):

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 481628 page no 24944 n bits 112 index `PRIMARY` of table `events` trx id 27740892 lock mode S locks rec but not gap waiting

and

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 481628 page no 24944 n bits 112 index `PRIMARY` of table `events` trx id 27740898 lock_mode X locks rec but not gap waiting

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 481628 page no 25267 n bits 112 index `PRIMARY` of table `events` trx id 27740898 lock_mode X locks rec but not gap waiting

Note that query 1 and 2 in the SQLFiddle match the first and second entries in the engine output. The PRIMARY key is only over the id column (as can be seen in the Fiddle)


texts:
I am a developer who has been asked to look at a database slowness issue.
I have had a look at the waits on the server and retrieved the following using a query from Red Gate's wait stats white paper.
The results are:

Wait type         wait_S            Resource_S     Signal_S     WaitCount      Percentage

PAGEIOLATCH_SH      393375.51       391289.91      2085.59      76733247       32.60

CXPACKET            367110.76       334340.19      32770.57     34663540       30.42

OLEDB               84136.77        84136.77       0.00         78106          6.97

SOS_SCHEDULER_YIELD 77896.38        15.97          77880.41     254840824      6.46

PAGEIOLATCH_EX      65944.98        65550.98       394.00       16298376       5.46

ASYNC_NETWORK_IO    45579.23        32512.01       13067.22     125659899      3.78

WRITELOG            37415.61        35457.02       1958.59      18559390       3.10

LCK_M_IX            36602.81        36587.52       15.29        67118          3.03

LCK_M_X             35503.99        35494.55       9.44         32114          2.94

LCK_M_U             30464.81        30452.64       12.17        28824          2.52

Does anyone have any advice on what to do next?

Does CXPacket suggest a maxdop change?  The server is a virtual server showing 4 CPU's in windows task manager.


texts: Why are InnoDB's index keys limited to 767 bytes?

texts:
MySQL's official documentation states about InnoDB:

  An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.

The meaning of this statement is clear to me, however I do not understand the reasons of this limitation, which seems arbitrary (why 767?).
Any pointers?


texts: How to design a database for storing a sorted list?

texts:
I am looking to store a sorted list inside a database. I want to perform the following operations efficiently.

Insert(x) - Insert record x into the table
Delete(x) - Delete record x from the table
Before(x,n) - Return the 'n' records preceding the record x in the
sorted list.
After(x,n) - Return the 'n' records succeeding the record x in the
sorted list.
First(n) - Return the first 'n' records from the sorted list.
Last(n) - Return the last 'n' records from the sorted list.
Compare(x,y) - Given two records x and y from the table, find if x >
y.

The simple method I could think of is to store some kind of a 'rank' attribute in the table and query by sorting on that attribute. But in this method inserting/modifying a record with a rank becomes a costly operation. Is there a better method?

Specifically, I am looking to implement the table using Amazon's SimpleDB. But a general answer for a relational database should also be helpful.

Update on load profile:

Since I am planning this for a web application, it depends on the number of users that use the app.

If there are 100k active users (super optimism :P), then my very approximate estimate per day would be

500k selects, 100k inserts and deletes, 500k updates

I would expect the table to grow up to 500k in total.

I am looking to optimize on the updates, insert and the Compare operations. The rank of the items will be constantly changing and I need to keep the table updated.


texts: Upgrade Postgres without doing a pg_dumpall

texts:
I'm upgrading from postgres 8.4 to 9.0 and looking at the manual it looks like this is my only choice:

pg_dumpall -p 5432 | psql -d postgres -p 6543

Which means I'll need to stop any writes to the database and wait whilst a couple of gb's of data is transferred.

Is there a better way in which I can use the Write Ahead Log (WAL) to replay any new SQL onto the new database?


texts: Database for Orders/Inquiries with "custom" Product Data

texts:
At the moment i practice with SQL Databases and created a simple Python tool to manage Order and Inquiry with a simple GUI for testing.

Now I have a question i didn't found a good solution for:

There are standard values for Products like a price and costs etc. These could vary from Order to Order (volume effects etc.).
So i need to save them on a different place.

What is a common way to do that?

Having an extra table for Products used in Orders where i copy all standard values from the Product Database and change them there if the price or anything needs to be changed in an order?


texts: Postgres copy data with \xYY as plain string instead of interpreting as encoded string

texts:
I have a log file full of URLs, generated by Bro IDS.

When Bro logs a URL with non-ascii characters in it, it inserts \xYY where YY is the hexadecimal character code. Also, some URLs contain "\x".

Is there a setting or flag I can use with the COPY or \copy command to stop postgres from trying to interpret these string sequences and just put them in the text field as is?


texts: Database Design: Should I use a Materialized View

texts:
I am designing a database based off of the NBA stats website (http://stats.nba.com/player/#!/201939/?p=stephen-curry)

If you go to the link posted on the player's stat, you will notice that the player's stats are organized based on different factors (such as the season, month, location, home or away, etc), which you can set based on your filter setting.

The question is, how did the person who designed the site database allow for efficient querying of the each players stats. Each player will have a series of game logs, which will record their stats based on each quarter.

Did they use materialized view to calculate the monthly and seasonal stats for efficient querying?

I try to design the database below:


texts:
I'm sorry for my English.
I have a problem with db mysql.
I have a table like this

id|rat|time
1 |2  |12:00
2 |0  |12:55
3 |0  |13:05
4 |6  |13:45
5 |0  |14:00
6 |8  |15:00
7 |0  |16:30
8 |9  |17:00

I wan't to get rows like that,
id = 2 row there rat=0 and id=4 there rat=6
i wan't id = 4 row's time minus id=2 row's time
AND

id = 6 row's (rat=8) time  minus id = 5 row's (rat=0) time

We will be table like this

id|time
1 |50 minutes
2 |1 hour
3 |30 minutes

we will get beetween time  first row  rat=0   until first rat>0


texts: What is the meaning of "-x-icu" in PostgreSQL's "collates"?

texts:
I had this query:

SELECT * FROM table ORDER BY label ASC;

Since the labels are not in English, they didn't get sorted in the right order (ones beginning with "ö" were not in the bottom/end).

I therefore tried:

SELECT * FROM table ORDER BY label COLLATE "sv-SE" ASC;
SELECT * FROM table ORDER BY label COLLATE "sv_SE" ASC;

These gave errors about those collations not existing, which baffled me.

After a bunch of searching, I figured out to do:

SELECT * FROM pg_collation;

Which revealed that it's supposed to be:

sv-SE-x-icu

It worked when I used that identifier, but what's with the "-x-icu" stuff? What's that all about? I hate that they always have to mess with the standard locale identifiers so you can never rely on just the standard "language_location" format.


texts: Counting distinct entries in a column using relational algebra

texts:
So I have a table similar to this one. Each user has posted a review about one or more hotels(A,B,C,D) but on different dates so there are no duplicate tuples even though a person might have reviewed the same hotel more than once.

I need to count the number of DISTINCT hotels every user has reviewed using RELATIONAL ALGEBRA only. How can I do that?

example to show notations I use:

R = ƔUser,COUNT(Hotel_reviewed)->Num_Reviews (InitialRelation- table 1)

would give the number of reviews by each user

The result should be the following table:

example to show notations I use:

R = ƔUser,COUNT(Hotel_reviewed)->Num_Reviews (InitialRelation- table 1)

would give the number of reviews by each user


texts: How to alter column to make it primary key when one already exists mysql8?

texts:
id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
 | title      | varchar(255)    | NO   |     | NULL    |                |
 | category   | varchar(255)    | NO   |     | NULL    |                |
 | summary    | varchar(10000)  | NO   |     | NULL    |                |
 | detail     | mediumtext      | NO   |     | NULL    |                |
 | created_at | timestamp       | YES  |     | NULL    |                |
 | updated_at | timestamp       | YES  |     | NULL    |                |
 | image      | mediumblob      | YES  |     | NULL    |                |
 | image1     | mediumblob      | YES  |     | NULL    |                |
 | image3     | mediumblob      | YES  |     | NULL    |                |
 | phold      | varchar(50)     | NO   |     | NULL    |                |
 +------------+-----------------+------+-----+---------+----------------+

I have this database now,I want to make a one-one relationship with with another table with where I use column ( title ) to link specific data. When I run
mysql> alter table tours add primary key (id,title);

OR
mysql> alter table tours add constraint pk_tours primary key (id, title);

The error:
ERROR 1068 (42000): Multiple primary key defined

on both queries.
I want another table named itinery where I use to link it with former table with help of volumn title ;
I am using mysql 8 with laravel 8.
Thanks


texts: SA Admin SQL-Server 2012 x64 keeps changing constantly

texts:
I know this is a newbie question, but really I can't find some precise info on this issue.

I'm trying to figure out how to create a new user with admin server role on SQL Server 2012 x64.

I mean, I do know how to do it, both graphically and through sqlcmd, fact is, if I create user let's say on prompt, like this:

1> CREATE LOGIN '<Login_Name>' with PASSWORD='<Password>'
2> GO

Then:

1> SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
2> GO

It does create the user, and adds it to srvrolemember, BUT, it keeps changing password, it never saves with the password I supply on CREATE LOGIN, BTW I don't remember sa user password either, with that user is the same story.

And I do notice, the encrypted dot length when I create new Login is the same as sa user, so I think it's the same password, also can't change sa password, it keeps the same lengthy password which I can't remember. No matter how many times I try.

I login into server with Windows Authentication.

I hope I explained myself, any ideas about this?

Thanks in advance!


texts: What should I set DATE field as default value when NO_ZERO_DATE is enabled in MySQL?

texts:
On shared hosting is not possible to change the NO_ZERO_DATE unless doing with a query but it seems more like a fast fix that the most correct one
Some of my tables has as default date "0000-00-00" what should be the correct setting when NO_ZERO_DATE is enabled? At this time I was thinking at a fixed date in the past like "1900-01-01" but not sure if is the most correct way to create the table


texts: Get Results from DMV Query into SQL Server Table

texts:
today I began to troubleshoot an issue that I was asked to resolve, and for the first time I found myself querying in the SSAS database. I obtained a very useful set of results (who was connecting to what applications/cubes and for how long), but for the life of me I cannot figure out how to get the results from that query into a database table so that I can track the data. Any help would be much appreciated, and thank you in advance for any assistance you are able to provide.

Here is the query I am running:

Select connection_id , connection_user_name ,
connection_impersonated_user_name , connection_host_name ,
connection_host_application , connection_start_time ,
connection_elapsed_time_ms , connection_last_command_start_time ,
connection_last_command_end_time , connection_last_command_elapsed_time_ms ,
connection_idle_time_ms , connection_bytes_sent , connection_bytes_received ,
connection_data_bytes_received FROM $system.discover_connections

This returns all of the information I am currently interested in seeing.


texts: Differences between development and live database setups

texts:
I was wondering if there are any best practice recommendations on setting up databases in a dev environment versus a live environment with regards to configuration.

Should they be as similar as possible (preferably identical)? Or should the dev environment be purposefully under powered in order to push developers to write optimal queries?

This could be as simple in MySQL as setting query_cache_size and related values to lower when on the dev machine.


texts: How to prevent or notice automatic connection retry in SQL Server and OLE DB

texts:
I have a c++ application that connects to SQL Server 2005 using OLE DB / Native client. If the connection gets broken or for example the session is killed from the database something causes the connection to do an automatic reconnect. The problem is that in that case for example all temporary tables created previous connection are lost.

Is there any way to prevent this from happening, or somehow notice that this has happened? That way I could do the same steps as in the original connection.

I tried to add "ConnectRetryCount=0" and "Connect Retry Count=0" to connect string, which I found from here: https://msdn.microsoft.com/en-us/library/jj730312%28v=sql.110%29.aspx but that made no difference, even when I tested the same application native client 11 and SQL Server 2014.


texts: what columns to include in the index to improve the performance of the following update?

texts:
I have the following update:

  UPDATE dbo.fees
  SET amount = @amount,
   currencyCode = @currencyCode,
   feeType = @feeType,
   countryCode = @countryCode,
   gatewayCode = @gatewayCode,
   programAP = @programAP,
   programED = @programED,
   programEX = @programEX,
   mandatory = @mandatory
  WHERE feeID = @feeID  and endDate = @endDate
  and programAP = @programAP and programED = @programED and programEX = @programEX
  and (countryCode = @countryCode or countryCode IS NULL)
   and (gatewayCode = @gatewayCode or gatewayCode IS NULL)

this is the table definition:

CREATE TABLE [dbo].[fees] (
[ID]            INT              IDENTITY(1,1)   NOT NULL,
[feeID]         INT                              NOT NULL,
[startDate]     DATETIME                         NOT NULL,
[endDate]       DATETIME                             NULL,
[amount]        DECIMAL(10,2)                        NULL,
[currencyCode]  CHAR(3)                              NULL,
[feeType]       CHAR(1)                              NULL,
[countryCode]   CHAR(2)                              NULL,
[gatewayCode]   CHAR(3)                              NULL,
[programAP]     BIT                                  NULL,
[programEX]     BIT                                  NULL,
[programED]     BIT                                  NULL,
[mandatory]     BIT                                  NULL  CONSTRAINT [DF_fees_mandatory] DEFAULT ((0)),
CONSTRAINT   [PK_fees]  PRIMARY KEY CLUSTERED    ([ID] asc) WITH FILLFACTOR = 90)

this is the index I intend to create in order to improve the performance of the update.

create nonclustered index i_feed2 on dbo.fees  (feeID ,programAP,programED,programEX,endDate) include (countryCode,gatewayCode )
with(data_compression=page,online=on) on [NONCLUSTERED_INDEXES]

A question came to my mind though:
what sort of data analysis should I do, to identify which of these columns would be better in the index or in the include part? Plus, the order of the columns in the index, is there any starting point as to find out, for this query in particular, or other queries that might hit this table, the best order of the columns?

There are no other indexes on this table.

I cannot change neither the table definition nor the update statement, but I can add indexes.

the solution

On this occasion though, even after adding the index as planned, while monitoring the execution and using the very helpful procedure sp_blitzcache
that I contacted the third party company requesting them to alter the procedure accordingly (adding a with recompile and sort out the updates as described in the comments and the accepted answer)

sp_blitzcache in action:


texts: Clever repeatable benchmarking and bottleneck detection test bench - PostgreSQL

texts:
Has anyone usefull ideas, inspration or literature for me to design a good test bench for PostgreSQL. I need repeatable results, which are applyable in realworld and production environement (no Journaling off for example ;) ). I need the testbench to see effects on query and/or configuration tuning. Further it would be interessting to detect CPU, Memory or HD bottlenecks.

I know this question is rather general, but right now I try to understand the process of improving prameters and tuning queries through consistent and repeatable results. Down the road it is about tuning and optimizing a routing database for GIS applications.

Thanks for your input!
Martin


texts:
Assuming I have the following table:

Create Table [dbo].[Test_IP]
(
  [IP] varchar(40),
  [IPType] varchar(6)
)

There are more triggers on this table, but all of them are AFTER triggers, and it is large DB with many tables, views and stored procs, and it been used from several processes.

I added a log table and triggers like this

Create Table [dbo].[Log_Test_IP]
(
  [Action] varchar(10),
  [IP] varchar(40),
  [IPType] varchar(6)
)
GO
Create Trigger MYTR_Log_Test_IP_INS On [dbo].[Test_IP] After insert AS
BEGIN
   insert into  [dbo].[Log_Test_IP]([Action],[IP],[IPType])
       select 'Insert', [IP], [IPType] from inserted
END
GO
Create Trigger MYTR_Log_Test_IP_DEL On [dbo].[Test_IP] After delete AS
BEGIN
   insert into  [dbo].[Log_Test_IP]([Action],[IP],[IPType])
       select 'Delete', [IP], [IPType] from deleted
END
GO

Now I'm getting to the interesting part: when I directly insert to the Test_IP table I can see the Insert trigger is working, but when it works regulary from an application or service (which I have no idea what it does) I don't see any Insert records in the log table, I can only see the 'Delete' records even though it was enpty in the start.

My conclusion is that there is some way to bypass the triggers, there are many triggers and stored procedures in the DB and I have no idea where to look.

So, my question is how can you bypass the trigger?


texts:
I have sqlserver DB , and there I have few tables.

I have 1 table that not save each row what is the datetime the each row created.

Is there any way to know when which row was created?
Is there any way (without changing the application that created the row
on this db, I can't add any SP to do it) that from now, when the
row is created, it will automatically add the created dateTime?


texts: Can I update only the needed changes in a database?

texts:
First of all, I apologize if you can't understand very well what I am saying and it can be edit for better understanding of my post. The next example is for better understanding of databases with control version.

Suppose I have a software used by several businesses. This software need a database to function correctly, that means every business have one. I have a central database using migrations and scripts to have control version but this central database do not comunicate with the others. Every business have a different version from each other, including the central database that always have the last version.

To update the databases to my last version I apply idempotent scripts (like Entity Framework) so I can detect the version and continue from there. Let say the business with the version 15.11 have an error with the database and need to be solve right away. I recreate the bug, fix it, create a script, and bring the update to the company. Now this company have a different version, like 15.11.1. and the last script it's included in the central database.

So here comes my question, if I decide to update in the future that same database to the last version, my idempotent script do not apply this change again?

Another question is: Can I apply differents scripts in a database? i.e. if in the update are 5 scripts and I only want to apply the #1, #3 and #5. The idea is for reduce the updating time if I have change already applied.

I am open for all kind of opinions and if I have some concepts wrong, please feel free to correct me.

Thank you.


texts: Create table on a Master - Master config avoiding replication

texts:
I have a Master-Master configuration.

What I want is to create an auxiliary table only for a report project so it's not necessary to replicate it.

Is there a specific way to make this in the CREATE TABLE line?


texts: How to replace column if not null in select statement?

texts:
I have a Contact (ID, FirstName, LastName, MiddleName).

For example:

| ID | FirstName | LastName | MiddleName |
|====|===========|==========|============|
| 1  | Mark      |Jordan    | E          |
|----|-----------|----------|------------|
| 2  | James     | Blunt    | NULL       |
|----|-----------|----------|------------|

I want to output

Mark E. Jordan
James Blunt

So, if MiddleName is not null, add a '.' after, otherwise output null.

I am aware of isnull function, however what I need is an isnotnull function. How can I finish the query?

My query so far:

select FirstName + ' ' + isnull(...)  + LastName
from Contact


texts: How to rewrite CASE expression to short-circuit evaluation

texts:
I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase?

Demo data:

CREATE TABLE #Docs (
    ID INT NOT NULL
    ,DocType TINYINT NOT NULL
    );

CREATE TABLE #DocsItems (
    IDDocs INT NOT NULL
    ,Amount NUMERIC(19,6)
    );

INSERT INTO #Docs(ID, DocType) VALUES(1,1),(2,1),(3,2),(4,2),(5,2),(6,2);
INSERT INTO #DocsItems(IDDocs,Amount) VALUES(3,50.),(3,25.),(3,33.),(4,44.),(4,123.),(6,11.);

Subject query:

SELECT
    -- expression
    SumAmount = CASE
                    WHEN D.DocType <> 1 THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

Query plan:

If I rewrite the query (on purpose) to:

SELECT
    -- expression
    SumAmount = CASE
                    WHEN 2 = 1 /* rewrite*/  THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1

The plan goes the way it should / could / would  in the original query:


texts: Sql Server thinks I have different Data Compression when swaping partitions

texts:
I am setting partition switching for my database.

I created a script using SqlCmd variable so that the same script would make both my source and target tables for partition swapping.

Here is an example:

--:setvar IsArchive ""
--:setvar Suffix _Partitioned

:setvar IsArchive Archived
:setvar Suffix _Archived

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR order.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE)
GO

I run it once with the top two :setvar options and again with the bottom two :setvar options.

So they are very similar with mostly only name differences:

[Orders].[Order_Partitioned]
[ArchivedOrders].[Order_Archived]

When I run this:

ALTER TABLE Orders.Order_Partitioned SWITCH PARTITION 1
      TO ArchivedOrders.Order_Archived PARTITION 1

I get the following error:

  ALTER TABLE SWITCH statement failed. Source and target partitions have different values for the DATA_COMPRESSION option.

But they clearly must be the same!  Or at least it seems to me that they must be the same.

Why would Sql Server think that they are different data compressions?


texts:
I have 2 tables:

Contacts
Helpers

The Helper field (which is linked to the Helper table) in Contacts is a multi-valued field so more than one helper can be selected and displayed in a report.
Each Helper is linked to more than 1 Contact.
I'm trying to create a query selecting all contacts for one helper, and display all helpers for the resulting contacts.  I think I am off-base with multi-valued fields, but cannot find another solution.
It is in Access 2010.  Tables do not have multi-value fields.  They are:
Contacts
ContactID
Surname
Email

Helper
HelperID
Name


texts:
I am using Oracle 12c Standard Edition Release 12.2.0.1.0. Everything works perfect. But while I was checking the alert log file I saw the following message several times.

Error Message

An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.

Any idea about this? I don't understand what it says so can somebody please help me out?

In response to @MichaelKutz' comment I can confirm that no ORA-nnn errors occur before or after the entry in the alert log file.


texts: Availability Group Always ON 2016 HADR_SYNC_COMMIT

texts: I know it is not the best practice but we have a two node AG 2016 between two data centers. Server 1 and HQ and Server 2 at DR and they are clustered (multisubnet). AG is setup in Synchronous Commit mode. Everything was running okay for almost six months but the last two weeks most applications are running slow. I have around 40DBs in one AG. I observed a wait type HADR_SYNC_COMMIT one some of the transactions. They even stay for 30 seconds sometimes. I really dont want to lose data in case of failover and thats my only reason to keep them in Synchronous commit mode. I was wondering if anyone faced this kind of issue.


texts:
I am trying to merge the nested MYSQL queries into one

First of all I fire this query

select names , city , category from names_data group by names , city ,category ;

this produces a result of ~28000 from master names_data table

now for each entry in the result
I fire this query inside a python loop

select date, name, city , old_mul, new_mul from name_changes where name=' + name + ' and city=' + city + """' order by date desc limit 2 ;

I just want the last 2 records date wise for each name and city combination from this name_changes table

I have tried few JOINS and groups operation but was not able to achieve what this

Right Now I have 1 + 28000*1 queries

I want to reduce the queries

Help is really appreciated :)

p.s. the tables are different


texts: Error during upgrade : Oracle 11g to 12c

texts:
While upgrading catupgrade.sql, we are getting below error.

  Due to errors found during the upgrade process, the post
  upgrade actions in catuppst.sql have not been automatically run.

  * THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE *

  Evaluate the errors found in the upgrade logs
  and determine the proper action.

  Execute the post upgrade script as described in Chapter 3
  of the Database Upgrade Guide.

  REASON:
  catuppst.sql unable to run in Database: EluxTest Id: 0

          ERRORS FOUND: during upgrade CATCTL ERROR COUNT=6

  Identifier CATALOG 20-01-27 02:20:04 Script = c1102000.sql
  ERROR = [ORA-12801: error signaled in parallel query server P000 ORA-01115: IO error reading block from file  (block # )

  ORA-01115: IO error reading block from file 2 (block # 215616)

  ORA-27072: File I/O error

  Additional information: 4

  Additional information: 215616

  Additional information: 212992

  ORA-06512: at line 8
  ]
  STATEMENT = [BEGIN
   EXECUTE IMMEDIATE
    'create unique index WRH$_EVENT_HISTOGRAM_PK on WRH$_EVENT_HISTOGRAM
      (dbid, snap_id, instance_number, event_id, wait_time_milli, con_dbid)
     local nologging parallel
     tablespace sysaux';
  EXCEPTION
   WHEN OTHERS THEN IF (SQLCODE = -942) THEN NULL; ELSE RAISE; END IF;
  END;]

Tried various solutions mentioned below posted online in various forums, but nothing worked.

Increased SGA to 6GB
Altered Data file sysaux01 to AUTOEXTEND OFF
Altered datafile to ONLINE


texts: I recently created a new database and INFORMATION_SCHEMA was there. I am aware that users should not mess with backend services, but INFORMATION_SCHEMA has 0 bytes. I checked all the tables and there was nothing inside. Is it safe to delete INFORMATION_SCHEMA?


texts: How to set a trigger on delete multiple parent and child rows?

texts:
Below are the tables of relevance:

Table_B
B_ID(PK)  Name  A_Id
1         A     1
2         B     1

Table_C
C_ID   B_Id(FK)
1      1
2      1

Now I want to delete rows from Table_B and from its child table Table_C.
Below is the query I am firing:

delete from Table_B where A_Id = 1

I want to delete all rows from its child too. Is it possible with a Trigger? I have made a Trigger on Table_B, but it deletes only one row; so I have to run a loop.

Thanks.


texts: Distinct Children rows ordered by a column of Parent

texts:
I've got a query where I want to retrieve distinct Child rows, but ordered by a column of Parent. If I do the following, I get an error because the column specified in the ORDER BY is not included in the DISTINCT list:

SELECT
    DISTINCT c.foo, c.bar
    FROM Parent p
    JOIN Child c on c.parentId = p.id
    ORDER BY p.createdDate

However, if I add p.createdDate to the select list, I will lose distinctness of Child rows, as p.createdDate makes them all distinct.

If I use a CTE or subquery to first do the ordering, and then select distinct rows from that, the outer query doesn't guarantee that it will maintain the order of the inner/cte query.

Is there a way to achieve this?


texts: fetch data from multiple tables using single query


texts: PGPool for Postgres load balancing, where to sit it

texts:
So I've been doing a stack of reading. I'm still not 100% clear of the best way to go about it.

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

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

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

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


texts: Netezza migration to Azure Synapse - Stored Procedures?

texts:
I have looked everywhere for useful documentation that comprehensively gives me the differences between Netezza stored procedures and functions so I can find Azure Synapse equivalents.
Is there any site or link that documents this sort of thing without having to look up each individual incompatibility by running the procs in Azure and having them fail?
I guess I would like to proactively fix these rather than reactively fixing them to save time
Any ideas?


texts: Want to get vat-amounts in invoice table

texts:
I have three tables:

  tblInvoices: fldInvoiceID, fldInvouceDate, fldInvoiceCustomer

  tblInvoiceItems: fldInvoiceID, fldItemID, fldItemQty, fldItemProdID, fldVatID

  tblVatRates: fldVatRateID, fldVatRate

with the following records:

1, 0;
2, 6;
3, 12;
4, 21;

Now I want to print at the bottom of my invoice a list of total vat-amounts per fldVatRate.
Example:
0 % - 12.00 €
6 % - 25.65 €
12 % - 0.00 €
21 % - 5.19 €
I've try a lot of t-sql statements but never get the real thing. The last t-sql give me the desired outpu BUT the 12% is not mentionde while it is 0. How can I get the wanted output ?
The sql untel now:

SELECT [HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblInvoiceDetail.fldItemQty,
   [HJ-Admin].tblInvoiceDetail.fldItemUnitPrice, [HJ-Admin].tblInvoiceDetail.fldLineTotal,
   [HJ-Admin].tblVatRates.fldVatRate, [HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100 AS fldVatAmount,
   [HJ-Admin].tblInvoiceDetail.fldVatRateID

FROM   [HJ-Admin].tblVatRates LEFT OUTER JOIN
       [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID

Result:

As you can see the Vat-rate 6% (ID = 2) is mentioned twice while it shoulde be once with the total of the two records (= 1.725000). The NULL-values are just fine to see the amount is NULL.

I would like an output like this:


texts: UNIQUE constraint on large VARCHARs - PostgreSQL

texts:
I have a column defined like:

data_url character varying(32768) NOT NULL

and UNIQUE constraint on that column:

CONSTRAINT unique_data_url UNIQUE (data_url)

When a large object is being inserted into the table, the following error message appears:

ERROR:  index row requires 32584 bytes, maximum size is 8191

How can PostgreSQL be set in order to be able to index object larger that 8191 chars? Space and speed are not a problem. It is a rarely changed table of at most hundreds of rows.

Environment: PostgreSQL 9.3.6, Fedora 20 x64


texts: What is the purpose of setting serveroutput in oracle?

texts:
Could someone let me know what is the purpose of using set serveroutput on/off in oracle?

I ran the below query to see what it is doing. However I need to know much about serveroutput. Kindly help me

 set serveroutput on;
     select * from tab;
    select * from tab;
     select * from tab;
     set serveroutput off;


texts: Export pipe-delimited data with progress db

texts:
I've been handed a task where we need to dump specific tables in a progress database (10.2B) to pipe-delimited flat files (50+ tables) and send them to a third party for analysis every week.

Looking at the sqldump command it seems it only supports comma delimited or quoted... but I have data in these tables with both commas and quotes.

How can I do this?


texts:
Recently we got this alert from monitoring team.

  SQLServer:General Statistics Logins/sec has exceeded the threshold of
  2

I tried to know a bit about it and if the value is less than

  2 per second indicates that the application is not correctly using connection pooling.

What does Application is not correctly using the connection pooling --  What does it mean ?

And few DMVs might help to find out the Logins/sec like the session_id and application

sys.dm_exec_sessions
sys.dm_exec_connections
sys.dm_exec_requests

But I am not able to connect the link between the Logins/sec and session ids.
Can anyone help me to understand how to deal with performace counters and does number of connections = number of session_ids (SPIDs) ,is this true ...Please help.

Thanks.


texts:
It could be just a question for a micro-performance, but it always keeps coming back to my head (just making me crazy). I can't find any answer in Google about it. I just want to clarify it.

I have these two example queries.

SELECT tbl_person.first_name, tbl_person.last_name, ....
FROM tbl_person
WHERE tbl_person.tbl_person_id=1
LIMIT 1

and this one.

SELECT p.first_name, p.last_name, ....
FROM tbl_person p
WHERE p.tbl_person_id=1
LIMIT 1

Is there any advantage or disadvantage between them?

I know the second one is easier to read and write, but it doesn't matter because the query will be just generated.

I am interested in performance impact. If the query has a few hundred times more characters, does it hurt performance?


texts: How to import Sybase .db file into SQL Server database?

texts: I've been given a Sybase .db file by a client and I need to import that into a new SQL Server database so that I may see the data as well as the tables and their relationships, any ideas on how that might be done shall be really appreciated :)


texts: How would you make this function work on any table?

texts:
I'm trying to break all the rules of databasing using the stuff function. I want to smush every applicable row into just one, for science, you know?

Problem is, I have to write a new function every time. They look like this:

    ALTER function [dbo].[stuffMeds](@VisitID varchar(55))
    returns varchar(max)

    as
    begin
    declare @string varchar(max)

    set @string = (select stuff( (
    select  distinct top 500 ', ' + DataItemID  from
    EmrAcctPha_Medication med
    where
    VisitID = @VisitID
    order by ', ' +  DataItemID desc
    for xml path ('')),1,2,'')
     )

    return @string

    end

used in a query like this:

    select AdmitDateTime, RoomID, dbo.stuffMeds(VisitID)
    from AbstractData

It turns a list like this:

Albuterol Sulfate
Amlodipine Besylate
Aspirin
Benztropine Mesylate
Bisacodyl
Ciprofloxacin
Collagenase
Divalproex Sodium
...

into a string like this:

Divalproex Sodium, Collagenase, Ciprofloxacin, Bisacodyl, Benztropine Mesylate, Aspirin, Amlodipine Besylate, Albuterol Sulfate

I can write it as a dynamic stored procedure, but you can't call sp_executesql from a function (or at least, I don't know how).

Question How would you write this function in a way that it could be used on any table?


texts: Get list of excluded databases from command line by asking user

texts:
Based on this I'm working on this bash script for export all databases from MySQL but exlude some ones and also system ones. This is how it looks like:

read -e -p "Enter MySQL root user: " -i "root" root_user
read -e -p "Enter MySQL root user password: " root_password

FILENAME="mysqlbck"-$(date +%d-%m-%Y-%T).sql
read -e -p "Enter backup filename: " -i "$FILENAME" file_name
read -e -p "Enter directory for save backup: " -i "/home" directory

DATABASES_TO_EXCLUDE=""
EXCLUSION_LIST="'information_schema','performance_schema','mysql'"

for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done

SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"

for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done

MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump -u"$root_user" -p ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > "$directory/$filename"

I have two main issues around it:

DATABASES_TO_EXCLUDE should holds a comma separated list for each DB that I want to exclude, how do I ask, in command line, for those names and build something like db1,db2,db3 and so on and put the values on the var? I mean something like the flow below:

Enter DB name: db1
// DATABASES_TO_EXCLUDE="db1"
Enter DB name: db2
// DATABASES_TO_EXCLUDE="db1,db2"
Enter DB name: db3
// DATABASES_TO_EXCLUDE="db1,db2,db3"
Enter DB name:
// got empty value stop asking for DB name
// and continue script execution on EXCLUSION_LIST line and below

I'm having two issues executing the script as it's right now. See the console output below:

Enter MySQL root user: root
Enter MySQL root user password: ********
Enter backup filename: mysqlbck-12-03-2015-11:49:22.sql
Enter directory for save backup: /home
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
/usr/local/bin/script-task: line 132: /home/: Is a directory

Why do I get access denied while trying to use mysqldump? I have tried the way you are seeing on the script above and also tried this one:

    mysqldump -u"$root_user" -p"$root_password" ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > "$directory/$filename"

Both have failed with same error, any advice?

As per second issue shouldn't $directory/$filename read as /home/mysqlbck-12-03-2015-11:49:22.sql or the problem is the %T parameter and the : on the filename? Where is the error?


texts: Transaction rollback hanging from past two months(MSSQL 2016)

texts:
I executed a query using SQLCMD on 2019-01-30, and the query caused ldf file to bloat exponentially and it became 2TB in size, then my MSSQL started rollback of transaction at 2019-02-01. I used KILL SPID WITH STATUSONLY to monitor the rollback process: progress of rollback was about 2% a day for first two weeks, afterwards the rollback process got stuck at 22% from 2019-02-14, and it's still there today.

I'd appreciate if I can get expert opinion on if there is any way to fix this issue - how to stop or speed up rollback?

Please find below code details about this issue:

SQL

BEGIN TRAN
  DECLARE @m int
  SELECT @m = @@ERROR
  DECLARE @tbname_old varchar(50) = 'OTS_ARCHIVE'
  DECLARE @tbname_new varchar(50) = 'OTS_ARCHIVE2'
  DECLARE @column_old varchar(30) = 'GuID_ID'
  DECLARE @column_new varchar(30) = 'GuID_ID_old'
  DECLARE @sql varchar(50) = '[' + @tbname_new + '].[' + @column_old + ']'
  DECLARE @sqlid varchar(100) = 'CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)'
  DECLARE @date as datetime
  DECLARE @i int
  DECLARE @f int
  set @date = '2017-01-01'
  set @i = 0
  set @f = 27
  WHILE @i < @f
  BEGIN
  EXEC ('INSERT INTO ' + @tbname_new +
    ' select
    GuID_ID
    ,Box_ID
    ,Start_Time
    ,End_Time
    ,Duration_Time
    ,ots_count
    ,Group_ID
    ,' + @sqlid + ' from ' + @tbname_old
    )
  END
IF @m = 0
  COMMIT TRAN
ELSE
  ROLLBACK TRAN
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage

Activity monitor


texts: Is there failover server parameter in PostgreSQL connection string?

texts:
Is there failover server parameter in PostgreSQL connection string that can be used to automatically switch from primary server to secondary sever ?

We have been using similar functionality on MS SQL Server, but now we have switched to PosgreSQL 9.3 and searching for something similar.

UPDATE

I'm using .Net C#, DevArt dotConnect for PostgreSQL driver


texts: Is it good practice to create a separate table for views count?

texts:
I have created a blog table where I'm having a field called views_count but I have heard that updating the views_count field on each page view is strain. So I have created now a seperate table for views count as below:

views:
id,
blog_id,
ip_address,
counter

Now I'm storing unique visits in views table. And when I save record in view table I also update blog field views_count field, so is this a good approach? Or is there a better alternative?

Full Creates Schema:

CREATE TABLE `video_blog` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` int(11) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `sub_title` varchar(255) DEFAULT NULL,
  `slug` varchar(255) NOT NULL,
  `video_embed_code` text,
  `video_thumbnail` varchar(255) DEFAULT NULL,
  `video_thumbnail_alt` varchar(255) DEFAULT NULL,
  `description` text,
  `views` int(11) UNSIGNED NOT NULL,
  `is_active` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Table structure for table `video_blog_category`

CREATE TABLE `video_blog_category` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `order_by` int(11) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Table structure for table `video_blog_views_tracker`

CREATE TABLE `video_blog_views_tracker` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `video_blog_id` int(11) UNSIGNED DEFAULT NULL,
  `user_ip_address` varchar(255) DEFAULT NULL,
  `counter` int(11) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Note: Our website blog is getting millions of visitors daily. So the new table will get updated frequently.


texts: Sort values in MySQL using HAVING clause and IN function

texts:
I have this portion of code with user's phone extensions:

GROUP BY IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
HAVING Ext IN (207, 213, 235, 214, 226, 218, 224, 227, 216)

It works fine, and it sorts values in ASC order ( 207, 213, 214..) Is it possible to sort values in different order? Something like this:

( 235, 207, 218 .. )


texts: Script to Delete records in multiple table

texts:
I would like to delete a specific record from two or more tables which are linked to one another.

For Example: I have two tables, Students and winners I would like to delete the names Roy and Peter from both the tables in one shot.

table: Students

> ID     name      class
> 1      Roy         2
> 2      James       3
> 3      Carl        4
> 4      Peter       4
> 5      Alice       5

table: Winners

St_ID          achievement
1              1
2              1
3              3
4              5
5              5

I have more than 100 tables with 50 specific records to be deleted from all the tables.


texts:
When the SQL Server tablediff utility generates a file with differences between source and destination, if SSIS executes the script file which contains Arabic letters, then those letters are inserted as symbols.

Is there any way to to specify the code page for the output file ??
Or any work around ??

Note : it only works correctly when I manually change the encoding of the file from (UTF-8 without BOM) to (UTF-8).

Thanks


texts: Update Query said "ORDER_DETAILS"."QUANTITY": invalid identifier

texts:
Update Query said ORDER_DETAILS.QUANTITY is an invalid identifier. I check and there is a table named ORDER_DETAILS with a field named QUANTITY.

Error at Command Line : 14 Column : 57
Error report -
SQL Error: ORA-00904: "ORDER_DETAILS"."QUANTITY": invalid identifier
00904. 00000 -  "%s: invalid identifier"

///////////////////////////////////////////////

UPDATE
(SELECT Orders.Order_ID, Order_Details.Quantity, Products.Units_In_Stock, Orders.Shipped_Date, Orders.This_Order_Was_Shipped
FROM Products
INNER JOIN Orders
INNER JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID ON Products.Product_ID = Order_Details.Product_ID
WHERE ((Orders.Shipped_Date Is Not Null) AND (Orders.This_Order_Was_Shipped='YES')))
SET Products.Units_In_Stock = Products.Units_In_Stock - Order_Details.Quantity;


texts: Cassandra: Request did not complete within rpc_timeout

texts:
i was working with Cassandra 1.2.4 probably, after restoring some key-space when i tried to query in a key-space it gave me Request did not complete within rpc_timeout

so i checked system.log & output.log under /var/log/cassandra path

i just find this exception:

  Exception in thread Thread[ReadStage:42,5,main] java.lang.RuntimeException: org.apache.cassandra.io.sstable.CorruptSSTableException: java.io.EOFException

what is the reason ? and how can i get rid of rpc_timeout

thanks in advance,


texts:
I am trying to SELECT values from a column in my database in order to perform a PIVOT on those values without hard-coding them.

SELECT *
FROM (SELECT ItemType, [Status] FROM [EVAULTTEST].[dbo].EvidenceItems WHERE ItemType IS NOT NULL) AS ei

PIVOT(
COUNT([Status])
FOR [Status]
    IN (SELECT DISTINCT ei.[Status] FROM [EVAULTTEST].[dbo].EvidenceItems AS ei)
) AS piv

However, the SELECT inside of our IN is giving us the error:

Incorrect syntax near 'SELECT'. Expecting '.', ID, or QUOTED_ID.

How can we use a SELECT inside of a FOR IN statement?


texts: Sql failover cluster issue with different edition setup ( enterprise with standard)

texts:
I have come across one client who has setup like this where dba has installed
sql server 2014 instance  with enterprise edition setup on node1 and on node2 he has added mssqlserver 2014 in failover cluster with setup of standard edition .
They have never tested the failover before last night they did the patching on passive node and tried to failover active node( node1 enterprise) to passive( node2 standard) but it got failed and services doesn't come up on both the nodes. In the last they had to remove the node2 from cluster and end up repairing sql services on node1.
May i know the cause of it? can it be due to edition mismatch
setup detail abcdxyz sql instance (enterprise) running on node1
added the node to failover cluster on noode2 with standard setup .


texts: Increase speed altering column on large table to NON NULL

texts:
I recently added a NULL-able bit column to a table which has close to 500 million rows.  There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time routine to assign 0 or 1 to all existing rows (updating the rows in small batches).  Every row should now have a 0 or 1 in that column.

I want to make the bit column non-nullable however when I tried doing so via ALTER TABLE t1 ALTER COLUMN c1 bit not null, it started running for 3 minutes and I stopped it because it was blocking all reads to the table and I suspected it was going to take a long time to complete.  It's possible it wouldn't take too long but I couldn't risk too much unavailability.  The rollback itself took 6 minutes.

Do you have any suggestions on how I can make the column non-nullable without it taking potentially hours to complete?  Additionally is there any way to estimate how long the ALTER TABLE ALTER COLUMN statement I started and then cancelled would take to complete?

I am using SQL Server 2017 Web Edition.


texts: Trigger Performance : Is Using Trigger is a good way?

texts:
We are using SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, around 100 columns and 10K records and increasing rapidly on daily basis. Around 200 users work with this application and updates also occur very frequently.
I know handling it via code will require very frequent connections to database.
I want to know which is a better approach for doing this. Is handling it through code will provide a better performance than using SQL Server trigger for that.


texts: Would it be correct to add extra columns to the junction table according to normalization rules?

texts:
Hello I have many to many relationship. I learning normalization rules.
I add quantity because quantity column is used only for products associated with the invoice.
My question is: Can I add extra column in junction table? Is it OK to normalization rules or is there a better solution?


texts:
Upon modeling my very first data warehouse model (for historical stock market data), I stumbled across a data relationship that I don't know how to best account for. The fact tables include  multiple years of end-of-day data and intraday data for various assets and exchanges. Now I expect to filter the data by date, which is why I created a date dimension. Similarly, I want to filter intraday data by time, which includes a date. This far it seems like a usual Snowflake Schema to me.
However, trading days depend on both the date and exchange. Similar is true for opening hours of an exchange. Both of these dimension tables would mean a join of two snowflake leaves. Is this the standard way of doing this or is there a better design pattern?


texts: Why when I JOIN to a casted field in an index view, the execution plan warns about "type conversion in expression"?

texts:
I thought an indexed view was materialized and therefor stored on disk. If that's the case, I would've thought that any type conversions I'm doing in the view would materialize as that datatype on disk and I wouldn't have to worry about casting issues while using those fields in JOIN and WHERE predicates against the view.

Why is this not so?


texts: Forcing postgres to use a GIN index on a varchar[]

texts:
In a postgres 9.4 database, I have a table with a field identifiers: varchar(512)[] that contains a list of identifiers for each row. When adding a new row, I want to make sure its identifiers list does not overlap with any existing row, so I want to search first for such a conflicting row using a GIN index on that column.

    Column      |           Type           |                        Modifiers
----------------+--------------------------+-----------------------------------------------------------
id              | integer                  | not null default nextval('my_table_id_seq'::regclass)
identifiers     | character varying(512)[] |
... other columns ...

Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id)
    "gin_idx_identifiers" gin (identifiers) WITH (fastupdate='on')
    ... other indexes ...

The problem is that by default, the following query does not use the GIN index:

EXPLAIN ANALYZE
SELECT * FROM "my_table"
WHERE "my_table"."identifiers" && ARRAY['a_sample_identifier']::varchar(512)[] LIMIT 1;

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..20.97 rows=1 width=1368) (actual time=31120.985..31120.986 rows=1 loops=1)
   ->  Seq Scan on my_table  (cost=0.00..4318413.15 rows=205920 width=1368) (actual time=31120.983..31120.983 rows=1 loops=1)
         Filter: (identifiers && '{a_sample_identifier}'::character varying(512)[])
         Rows Removed by Filter: 14572058
 Planning time: 0.135 ms
 Execution time: 31121.023 ms

If I set enable_seqscan = off, then the index is used (and the query is much faster, unsurprisingly):

                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1755.88..1757.62 rows=1 width=1368) (actual time=0.065..0.065 rows=1 loops=1)
   ->  Bitmap Heap Scan on my_table  (cost=1755.88..359284.85 rows=205920 width=1368) (actual time=0.064..0.064 rows=1 loops=1)
         Recheck Cond: (identifiers && '{a_sample_identifier}'::character varying(512)[])
         Heap Blocks: exact=1
         ->  Bitmap Index Scan on gin_idx_identifiers  (cost=0.00..1704.40 rows=205920 width=0) (actual time=0.056..0.056 rows=1 loops=1)
               Index Cond: (identifiers && '{a_sample_identifier}'::character varying(512)[])
 Planning time: 0.126 ms
 Execution time: 0.102 ms
(8 rows)

However, I have read that setting enable_seqscan = off globally is dangerous. How should I force postgres to use the index?


texts: Disk space recovery on Mongodb replicaset secondaries

texts:
Is it possible to recover disk space on replica set secondaries based on the results of a repairDatabase call performed on a primary? I can't seem to locate anything that would work short of bringing the secondaries down and restarting them minus their membership in the replica set. In my scenario I don't have the option to bring them down nor will repairDatabase work on a secondary. I looked at compact, but it does not free disk space.

Informational link: http://www.mongodb.org/display/DOCS/Excessive+Disk+Space


texts: Report-builder: How can I show the labels in a List when the dataset is empty?

texts:
An equivalent question was answered for a tablix, but I would like to do this for a list:
"how to keep the structure of a tablix where there is no data".

I am showing about half a dozen fields in the list, each with their own label (which I want to repeat for each row).
If there are no rows, the user would like to use the report as a "template" that can be filled in manually so I would like to print the labels at least once.

How to keep the structure of the Tablix when there is no data to show


texts: Insert value into table without knowin column name

texts:
I'm trying to update values in table using column name from another table, why it is not working?

You can see that I have in table TableOfDocuments, in column WORD1, the name DANA.

Now I need to go to the other table where column name is DANA and add 1
this is my code:

 UPDATE BagOfWords
 SET (SELECT WORD1 FROM TableOfDocuments WHERE ID=1)=1
 WHERE USER_ID=1

with this code I'm getting the column name in the other table (Dana):

SELECT WORD1 FROM TableOfDocuments WHERE ID=1

BagOfWords Table

USER_ID|DANA|LOVES|EAT
----------------------
   1  |  0  |  0  |  0

TableOfDocuments Table

ID|WORD1|WORD2|WORD3
--------------------
1 | Dana| loves |pizza

So, after getting help is it possible to do something like this in order not to type WORD1---WORD5 separately? Obviously I tried but this doesn't work, what do I need to add or change in order to make this work?

 declare
      l_col varchar2(128);
    begin
      select word1,word2,word3,word4,word5 into l_col from TableOfDocuments WHERE ID=1;
      execute immediate 'UPDATE BagOfWords SET ' || l_col || ' = 1 WHERE USER_ID=1';
    end;


texts:
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (2048062382); index ID: 1, database ID: 28
TABLE level scan performed.
- Pages Scanned................................: 1019182
- Extents Scanned..............................: 127400
- Extent Switches..............................: 127399
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [127398:127400]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 77.25%
- Avg. Bytes Free per Page.....................: 135.7
- Avg. Page Density (full).....................: 98.32%

I have read that Scan Density = 100% is very good, and Logical Scan Fragementation <1% is also great.  77% Extent Scan Fragmentation troubles me, but the internet says to ignore it.

I'm analyzing a single-table slow performing query.  It runs ~30 seconds on first execution, then 200 ms on second and subsequent executions.  I can reset this behavior with DBCC DROPCLEANBUFFERS.

Is the high Extent Scan Fragmentation an important clue?

(If not, I'll likely add another question about my single-table query).


texts: AWS Postgres setting pg_trgm.word_similarity_threshold

texts:
Trying to set pg_trgm.word_similarity_threshold on an RDS postgres instance (postgres 12 if it matters):
ALTER SYSTEM SET pg_trgm.word_similarity_threshold = 0.3;

I get the error:
must be superuser to execute ALTER SYSTEM command
I don't see this in the parameter groups even though pg_trgm is a supported extension. Is there something I'm missing?


texts: construct a sql , assign it to a variable and execute inside shell script

texts:
I am trying to execute a mysql query inside my shell script, which is not executing .
Here is what i am trying to do

query="\"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';\""

echo $query

"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';"

Inside my shell script , i am executing below which is not working. It is not giving any error but simply not considering -e option and listing down all the valid options for mysql client.

mysql -u$username -p$password -e$query

I even validated below in my shell prompt

echo "mysql -uroot -p -e$query"
mysql -uroot -p -e"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';"

which is perfectly fine and executes without any issues. I am not sure what to do now. please help.Thanks


texts: Designing a database structure for a permissions system

texts:
Scenario

I would now like to implement a permission system so that a user is able to restrict access on his contact properties to his connections, e.g.:

A wants to allow B to access his name, birthday and work mail;
all other properties are private to B; and
A wants to allow C to access his name, phone number and private mail, etc.

Current considerations

I have a database scheme as shown in the picture below for an app that manages contact information for users:

The red table people contains basic information of the user, the yellow tables are arrays so that every user can have multiple email addresses, phone numbers etc. You may expose your contact data to other people by connecting to them as managed by the connection table.

The question

So my question now is, how can I design an efficient permission system that achieves the described goal?


texts: How to resolve “Access denied for user 'root'@'localhost'” error for MySQL using Mac OS X?

texts:
I am working in Symfony application on my Mac and I continue getting this message:

An exception occured in driver: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO)

I have tried it without password, with password, I have tried it in this mysql -u root, in this manner mysql -u root -p. I have tried shutting down the server and I get this answer:

ldco2016@DCortes-MacBook-Pro-3 ~/Projects/eventcalendar (master●)$ /usr/local/mysql/support-files/mysql.server stop                              [ruby-2.2.5]
 ERROR! MySQL server PID file could not be found!

Somebody help :(


texts: Seperate volumes for tempdb, data, and backups on SQL Servers in VMs

texts:
When configuring new SQL Servers on VMs, it is a good idea to have the Database, TempDB, logs, and backups on separate logical drives, even if the underlying storage is the same? I know that this is a good practice on physical servers; the separation actually helps us to reduce the pains caused by concurrent I/O.

I am trying to understand if having 3 drives instead of 5 would make sense. Can someone please provide their views/suggestions for this idea?


texts: Migrate complete postgres database cluster to another machine using pg_dump

texts:
I am referring to

How to get a working and complete PostgreSQL DB backup and test

In this example/solution, which part of the restore process does actually the "CREATE DATABASE sandbox"? I have been searching for this answer a while now - it seems that pg_dump does not backup the names of the database it creates a backup for --> when I just backup (pg_dump) and restore (psql) it, i get "database sandbox does not exist".

So, if I want to backup and restore the whole cluster with pg_dump, do I have to create all databases on the new server manually? I would like to be able to restore single databases later on, so pg_dumpall doesn't seem to be the right way, or is it?


texts:
I have a bloated plan cache because of Adhoc queries.
I have SQL job that goes in and purges Adhoc plans every hour. (This process was adopted from Kimberly Tripp in regards to Plan cache bloat).
When I look at the top 50 adhoc plans I see this (with much more content in each row) for all 50:

Select '100', 0, ''; begin try dbcc inputbuffer(100) with no_infomsgs end try begin catch select 'RPC Event',0,'(unknown)' end catch; Select '102', 0, '';

How can I prevent that from filling up my plan cache? BTW..
I do have optimize for ad hoc workloads on.

Thanks,
Shelz


texts: What is wrong with this a MySQL 5.6 procedure definition? Use a variable for table name

texts:
Just can't understand why it's throwing this error?

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

drop procedure if exists dividir_tabla_por_usuario;

delimiter //
create procedure dividir_tabla_por_usuario(tabla varchar(100), columna_usuario varchar(100), columna_fecha varchar(100))
begin
  set @i = 0;
  set @q1 = concat('select count(*) from (select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t1 into @c');
  prepare p1 from @q;
  execute p1;
while @i <= @c do
  set @r = 0;
  set @q2 = concat('select * from ', tabla , ' where ', columna_usuario ,' = (select ', columna_usuario ,' from (select @r:=@r +1 as ord, ', columna_usuario ,' from (select distinct ', columna_usuario ,' from ', tabla , 'order by ', columna_usuario ,' asc) as t2) as t3 where ord = ', @i ,' ) order by ', columna_fecha ,' asc limit 30000');
  prepare p2 from @q2;
  execute p2;
  set @i := @i + 1;
end while;
end//
delimiter ;

call dividir_tabla_por_usuario('mytable', 'mycolumn_1', 'mycolumn_2');


texts:
DELIMITER $$
CREATE TRIGGER `merchant_insert` BEFORE INSERT ON `merchant_table` FOR EACH ROW
BEGIN
  INSERT INTO merchant_seq VALUES (NULL);
  SET NEW.merchant_id = CONCAT('MC', LPAD(LAST_INSERT_ID(), 10, '0'));
END $$
DELIMITER ;


texts: Differences between the different installers of SQL Server 2014 Express

texts:
I'm trying to understand the differences between the different installers of SQL Server 2014 Express.  If you are able to tell me whatever you can or find documentation on them, I'd appreciate it.

https://www.microsoft.com/en-us/download/confirmation.aspx?id=42299

Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe
Express 32BIT\SQLEXPR_x86_ENU.exe
Express 64BIT\SQLEXPR_x64_ENU.exe
ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe
ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe
ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe
LocalDB 32BIT\SqlLocalDB.msi
LocalDB 64BIT\SqlLocalDB.msi
MgmtStudio 32BIT\SQLManagementStudio_x86_ENU.exe
MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe


texts: Error when creating an index in SQL Server 2012

texts:
I'm trying to create the following index on our test environment (SQL Server 2012):

CREATE NONCLUSTERED INDEX [iTransaction-OriginatingTransaction] ON [dbo].[Transaction]
(
    [OriginatingTransactionID_EOT] ASC,
    [OriginatingTransactionID] ASC
)

on our transaction table:

CREATE TABLE [dbo].[Transaction](
    [TransactionID] [int] IDENTITY(1,1) NOT NULL,
    [OriginatingTransactionID_EOT] [varchar](50) NULL,
    [OriginatingTransactionID] [int] NULL,
    [Status] [int] NOT NULL,
    [BusinessID] [int] NOT NULL,
    [CreatedUserID] [int] NOT NULL,
    [CreatedDateTime] [datetime] NOT NULL,
    [ModifiedUserID] [int] NOT NULL,
    [ModifiedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED
(
    [TransactionID] ASC
)

I get the following error:

  Msg 681, Level 16, State 3, Line 2
  Attempting to set a non-NULL-able column's value to NULL.

If I run the same create index statement on our live database (SQL Server 2005) then it works without any errors.

Why can't I create this index on SQL Server 2012?

UPDATE: Results of dbcc checktable:

  Msg 8944, Level 16, State 13, Line 1
  Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data), page (1:701352), row 13. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 7198 and 186.
  Msg 8944, Level 16, State 13, Line 1
  Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data), page (1:701352), row 13. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 7198 and 186.
  Msg 8928, Level 16, State 1, Line 1
  Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data): Page (1:701352) could not be processed.  See other errors for details.
  Msg 8976, Level 16, State 1, Line 1
  Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data). Page (1:701352) was not seen in the scan although its parent (1:709848) and previous (1:700903) refer to it. Check any previous errors.
  Msg 8978, Level 16, State 1, Line 1
  Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data). Page (1:701353) is missing a reference from previous page (1:701352). Possible chain linkage problem.
  DBCC results for 'Transaction'.
  There are 2354636 rows in 57941 pages for object "Transaction".
  CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'Transaction' (object ID 954995248).
  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Redebiz.dbo.Transaction).
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.


texts: Client Encoding Mismatch when creating new database

texts:
I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run with postgres 8, which uses sql_ascii for its client encoding. Postgres 9 uses UTF8.

I know how to fix this on the command line by changing the template database's encoding to sql_ascii, but ideally I'd like to figure out how to get the program to create the new database using the new UTF8 encoding.

I found this line in the program

sqlString = "CREATE DATABASE " & m_sProdDBName & " WITH OWNER=" & _
m_s4 & " TEMPLATE=" & m_s5 & " ENCODING='sql_ascii' LOCATION= DEFAULT;"

And I changed sql_ascii to UTF8.

However, the 'Client encoding mismatch' error gets thrown before that line ever gets executed. It happens when the connection to the template DB is being created. I got a message box to pop up with the connection string right before the connection opens. For some reason, it is hitting the messagebox code twice, even though it should only hit it once. The first time, no connection attempt is made. The second time the program goes on and attempts to open the connection, which is when the error gets thrown. Here are the two connection strings:

DRIVER=postgresql;SERVER=Stephane-PC;PORT=5432;UID=postgres;DATABASE=remindex;PWD=

DRIVER=postgresql;SERVER=Stephane-PC;PORT=5432;UID=postgres;DATABASE=template1;PWD=

(I know that no password is needed so that isn't the problem).
Remindex is the database to be created and template1 is the template DB.

In case you need some additional information, the connection object is an ADODB.Connection object and the Provider is MSDASQL.

I'm not all that good with database stuff, so I apologize for my lack of knowledge.

Any suggestions would be greatly appreciated!


texts: Using aggregate function in custom function

texts:
I need to calculate the rms (root mean square) for different columns.
To not repeat myself I would like to create a custom aggregate function rms(numeric) which should do the following calculation:

SELECT SQRT(AVG(POWER(v, 2))) AS rms
FROM (VALUES (1), (2), (3), (4), (5)) AS vals (v);

The function should be called like the following:

SELECT RMS(v)
FROM (VALUES (1), (2), (3), (4), (5)) AS vals (v);

In both cases the output should be a floating number. In my example 3.3166....

I found several easy examples on how to create custom aggregate functions, but I could not find an example which explains how to use another aggregate function (in my case AVG()) within the custom aggregate function.

I also found the question (https://stackoverflow.com/questions/30667245/writing-my-own-aggregate-function-in-postgresql), which asks essentially the same thing. But the accepted answer recreates MAX. I would prefer not to recreate existing functions.


texts: Downgrade SQL Server from Enterprise to Developer edition in an Availability Group

texts:
I will be transitioning some non-production SQL environments from Enterprise to Developer edition over the next few months.  I'm pretty settled on the procedure I will be using, which is laid out in this blog post:
Downgrading SQL Server Editions
I have not been able to find any information specific to any special precautions needed when doing this in an environment with Availability Groups. We have a 2 node Synchronous commit AG.  Considering we are going from Enterprise to Developer, I am not concerned with Enterprise only features being an issue.  My assumed workflow will be:

Downgrade the secondary first according to the blog post above, make sure everything is synchronized afterwards.
Fail over to the secondary(now on developer edition)
Downgrade the old primary, and make sure db's are synced up.

It's essentially the same process I use for rolling patches and upgrades, but not sure if there are special considerations for downgrading editions...  Anyone have any issues doing this?


texts:
Using the Postgres information_schema or system catalogs, is it possible to determine what table columns are referenced by the columns in a view?

information_schema.view_column_usage provides information on columns that are used in a view, not necessarily selected as a column.

pg_class and pg_attribute provide some info, but there doesn't seem to be a way to map columns from a view where an alias has been used.

Is is it possible to get the column references of the selected columns in a view?


texts: How to read/write from replicated secondary in case primary went down in SQL Server?

texts:
I've created a transactional publication in SQL Server, now my requirement is to write on primary and read from subscriber and in case of primary going down for some reason, the subscriber should act as a primary automatically.
For now all I have is a publisher that writes to the subscriber.


texts: We are doing a large ETL load from Oracle 12c. Every now and then Oracle optimizer decides to create a new execution plan for a SQL statement that has previously been running just fine. Usually this results in a 10x performance degradation. When I ask the client DBAs and Oracle consultants (client is a Gold partner), they usually ask us to convert ANSI joins to traditional Oracle joins. And usually this has helped. My question is, is the poor support for ANSI joins acknowledged by Oracle? Does Oracle recommend using traditional joins? (I've read Oracle books stating the exact opposite.)


texts: Oracle 12c - Insufficient Privileges When Trying To Create A Trigger With SYS User

texts:
I want to create a trigger for CRE schema. I'm trying to create this trigger with SYS user but I have insufficient privileges error.
My trigger:
CREATE OR REPLACE TRIGGER CRE.TRIGGER_NAME
AFTER LOGON ON DATABASE
DECLARE
asd1 NUMBER;
asd2 NUMBER;
asd3 NUMBER;
asd4 NUMBER;
asd NUMBER;
qwe VARCHAR2(200);
qaz VARCHAR2(200);
ewq NUMBER;
zaq VARCHAR2(50);
xsw VARCHAR2(50);
BEGIN
CRE.SOME_PROCEDURE_NAME('S', SYSDATE);
END;
/

How can it possible with SYS user?
How can I fix it?
Best regards.


texts: SQL Server mistaken update rows without backup

texts:
I did a mistake UPDATE query yesterday. Database engine is MSSQL and the version is 2017.

I have not any backups. All I have is the database itself. Do you know any ways to redo my modifications to one second before the time which the damn query was triggered?

In addition, I tried transaction logs but every time I want to restore database to time before accidentally UPDATE I face to this message: No packupset is selected in SQL Server. I think it's because my TLog has not period of time due to I dad not backup any transaction log before.


texts:
Afternoon all

I am attempting to analyse some Server Audit logs for one of my Azure DB databases that reported unusual activity.

I have full access to the logs and have downloaded then as well as Merged them using SSMS 17.1.

I have 2 logs:

Log 1 | 20k Rows

Log 2 | 20k Rows

Log 1 + 2 | 6.5k Rows

Individually they read as you would expect, however, when I add them both to the same Merge Audit Files in SSMS, the resulting session only details 6.5k rows with an extremely limited timeframe.

Has anyone come across this to be able to help me understand what is going on?  If I want to try and identify the Queries run from and given IP address, I would rather not have to do it log by log!


texts:
Hope you're doing well.
We've recently moved to Mysql 8 (from Mysql 5.7).
We're using AWS RDS Service with MySql. The AWS Team is also looking for a solution btw.
Since a few days, we're facing several problem with replication. We never met these problems before with the 5.7 version.
First, we had a very import lag among our Master and our two replicas.
This was solved with specifics params on the Parameters group of the replica like :

slave_parallel_type in LOGICAL_CLOCK mode
binlog_group_commit_sync_delay with a 10ms delay
sync_binlog at 0

It seems that the latency is now gone, and it's a good news (i'll wait several days to be sure).
Nevertheless, we're still facing a massive problem with the RAM used on the Replica.
We can't find the problem, perhaps the buffer, but it must be locked with a threshold.
It's a cycle movement like this :

In Green, the production.
In Orange the small replica.
In Blue the most powerfull replica (As you can see, we tried to upgrade the instance, but it's not a RAM limitation problem).
The problem is the same for both. The memory usage is increasing till the replica has to down.
By the way, the swap is never used.
If you have any clue with this, it will help me a lot!
Thanks for reading!
Have a nice day :)
--- UPDATE ---
It seems that the buffer is full, but I don't know with what.
Is there any garbage collector running ? Because seems not, perhaps something to turn on with Mysql8?
Here some queries showing the size of the allocated memory and a zoom on the innodb memory repartition. We see that I Have 12go on this replica. And they must be full if the memory is decreasing :

 SELECT SUBSTRING_INDEX(event_name,'/',2) AS
   code_area, FORMAT_BYTES(SUM(current_alloc))
   AS current_alloc
   FROM sys.x$memory_global_by_current_bytes
   GROUP BY SUBSTRING_INDEX(event_name,'/',2)
   ORDER BY SUM(current_alloc) DESC;

   SELECT *
   FROM sys.x$memory_global_by_current_bytes
 WHERE SUBSTRING_INDEX(event_name,'/',2) = "memory/innodb"
   ORDER BY current_alloc DESC
 LIMIT 10;


texts:
I would like to know if there is any metadata table/registry entry where we can find the backup location of SSRS Encryption Key.

I am working on a power shell report to display the Encryption Key backup date. But the challenge is to find out the location so that the script can be generic.


texts: Calculating Running Average using Over clause in SQL server

texts:
My table looks like this:

CREATE TABLE [dbo].[TimeSeries](
[LOCID] [int] NOT NULL,
[Date] DateTime,
[YEAR] int,
[MONTH] tinyint,
[RTT] [int]
PRIMARY KEY ( [LOCID] ASC,[Date] ASC)
 )

It contains 12 rows(or MONTHS) per LOCID, per YEAR.

I have the following query:

SELECT
    LOCID,
    [Year],
    [Date],
    AVG(cast(RTT as float)) OVER (
         PARTITION BY LOCID ORDER BY Date
             ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) AS avgRTT
FROM TimeSeries

example data:

LOCID   Date    Year    Month   RTT
1   01/01/1982  1982    1   58
1   01/02/1982  1982    2   63
1   01/03/1982  1982    3   34
1   01/04/1982  1982    4   27
1   01/05/1982  1982    5   6
1   01/06/1982  1982    6   4
1   01/07/1982  1982    7   3
1   01/08/1982  1982    8   14
1   01/09/1982  1982    9   22
1   01/10/1982  1982    10  16
1   01/11/1982  1982    11  17
1   01/12/1982  1982    12  44
1   01/01/1983  1983    1   58
1   01/02/1983  1983    2   63
1   01/03/1983  1983    3   33
1   01/04/1983  1983    4   27
1   01/05/1983  1983    5   9
1   01/06/1983  1983    6   0
1   01/07/1983  1983    7   3
1   01/08/1983  1983    8   0
1   01/09/1983  1983    9   6
1   01/10/1983  1983    10  27
1   01/11/1983  1983    11  11
1   01/12/1983  1983    12  48

Now I am trying to execute the above query for a specific MONTH in every year only, calculating the average for the 5 following dates/months/rows. But a WHERE clause to specify the MONTH value will not work because that affects which numbers are averaged.

I need to do this for tens of thousands of LOCID's and millions of rows.

My question is if there is a way to execute the correct moving average for only a specific value for 'MONTH', thus hopefully reducing the processing time a number of times...

example desired output:

LOCID   Date    Year    Month   avgRTT
1   29952   1982    1   37.6
1   30317   1983    1   38

Any pointers will be greatly appreciated.


texts: In SSMS, is "Navigate to" (Ctrl+,) of any use?

texts:
In standard Visual Studio 2012 or higher, IDE window called Navigate to... (Ctrl+,) offers all useful objects in scope of currently open project (see samples) getting increasingly filtered as I am typing so I can quickly navigate to what I need without expanding the tree with hundreds of items.

The same Navigate to... I can find in SQL Server Management Studio 2014 (perhaps since SSMS 2012?) but as I type, I can see only some backup files in SSMS %TEMP% directory. Is this everything what can be get from Navigate to...?

I would like to see it offering me names of tables, stored procedures and SQL functions etc. so I can jump to them in Object Explorer or view their definition. Any tricks to make Navigate to... more useful in the SSMS?

(Currently I have SSMS 2014 connecting to SQL Server 2008 R2.)

Note: I'm aware of various ways of navigating in SSMS, but now I would like to learn more about Navigate to... which is convenient typing-oriented  search&go tool.