texts: Speeding up Postgresql manual index reset?

texts:
I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records).

To solve this, I run the following (as pseudocode):

last=1
for db in databases:
   ALTER TABLE table1 DROP CONSTRAINT table1_pkey
   CREATE SEQUENCE temp_seq START last
   UPDATE table1 SET table1_pk = nextval('temp_seq')
   ALTER TABLE table1 ADD PRIMARY KEY table1_pk
   last = nextval('temp_seq')
   DROP SEQUENCE temp_seq

This goes through all the databases and resets their indices starting from 1. First database has indices 1-50, second 51-125, third 126-223 and so on.. After this I dump and restore them to one database and there is no overlap anymore.

Everything works well, other than the index reset part is extremely slow with bigger databases (many GBs of data). I found out that postgres is running VACUUM ANALYZE after every UPDATE table1 SET table1_pk = nextval('temp_seq'). This makes everything even slower than before. According to my understanding, this is done to prevent transaction ID wraparound? From the PostgreSQL docs: https://www.postgresql.org/docs/9.3/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

  PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction's XID is "in the future" and should not be visible to the current transaction. But since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss. (Actually the data is still there, but that's cold comfort if you cannot get at it.) To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions.

Is this the correct reason? Is there a faster way to do primary key reset? Or to tune the vacuuming parameters without any data loss?


texts: How do I horizontally partition an oracle database table, and should I?

texts:
We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment.

Create tenant specific aggregate tables, and query from those.
Horizontally partition the data based on tenant.

The first option means that for every new tenant that comes onboard, we'll need to create a new set of tables. This isn't that difficult, as a new tenant signing up comes with a few weeks notice, and a lack of reporting will be revealed early on if we forget.

Partitioning the data to me sounds like a better approach, since we're not duplicating the data. We don't have to rely on a process to transfer new data to the aggregate tables.

I'm wondering which of these options would be better, if anyone has had similar experiences before. Does partitioning the data actually help? Or would it be not so much different from leaving all the data in one 'space'?

And, with Oracle 10g, how does one horizontally partition data? If I had the following table:

TABLE Transaction(id, tenant_id, a, b, c, d)

We'll be migrating to Oracle 11g soon, so any differences in partitioning across versions would be appreciated.

(Note: I tried to use a partitioning tag, but not enough rep, if someone else could add a tag that'd be cool)


texts:
I need to do the auditing in database, but different the normal process that administrator use, (active audit in first installation or alter ) I don't have time window for this.

My scenario:
tree users with select grant just. But, for others users in same place, using the global user whit delete, update grant.

And I don't simply change the grant these users, because others applications use in font the same user and password.

Exist other form that I take a report with , what each one user does in database?


texts: Rebuild or Reorganize indexes based on fragmentation

texts:
I am using an Index Defrag Script, which provides a nice way to rebuid or reorganize indexes based on fragmentation percentages. I like to rebuild my database indexes if their fragmentation is over 30%, and reorganize if < 30%.

If an index doesn't allow page locks (@AllowPageLocks=0) or contains LOB objects, then the script will reorganize instead of rebuild, even if the index fragmentation value exceeds the rebuild threshold value.

I have an index with 35% fragmentation which doesn't rebuild. I just need to know , what is reason behind this logic. i mean what is reason behind to reorganize if index table contains LOB objects and (@AllowPageLocks =0) , allow page locks.


texts: Slow insert performance in mysql innodb table for solr indexing

texts:
I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow.

Following are the InnoDb parameters.

| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 10                     |
| innodb_buffer_pool_size         | 12884901888            |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 5000                   |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | ON                     |
| innodb_flush_log_at_trx_commit  | 0                      |
| innodb_flush_method             |                        |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 180                    |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 104857600              |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 90                     |
| innodb_old_blocks_time          | 100                    |
| innodb_open_files               | 300                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 1                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 32                     |
| innodb_read_io_threads          | 64                     |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | OFF                    |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 32                     |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | ON                     |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 1.1.8                  |
| innodb_write_io_threads         | 64                     |

The OS is linux and physical memory is 20GB.

Please help.

Thanks
Kuldeep


texts: How to mimic a full outer join using mysql views?

texts:
I have all kinds of views in mysql with employee data.  I have tried to use methods like this -https://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join but they simply don't work for views.

I end up getting more of a LEFT JOIN as a result.  Is there a way to merge two views without including duplicates in mysql - I am currently using a 4 view process to duplicate this.


texts: Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.


texts: Keep count in temporary variable for later use in query

texts:
I have used WITH query for this as below

WITH my_count AS
(
    SELECT count(emp_name)
    FROM   employee
    WHERE  joined_date < '2017-11-30'::Date
    AND    region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value)
VALUES ('count des', '2016-11-30', my_count);

But this gives me an error as below:

  ERROR:  column "my_count" does not exist
  LINE 4: ...rty_value) VALUES ('count des', '2016-11-30', my_co...
                                                                  ^
  SQL state: 42703
  Character: 244

So How do we keep count in a temporary variable for later use? If this is not possible, is there any other way to achieve this?


texts:
I know there are tons of posts about this on here but nothing seems to work.  I have tried every command I could find it will not work.
I just recently setup mysql on Ubuntu (SQL version is 8.0.23-0ubuntu0.20.04.1).  Afterwards i installed myphpadmin and I can get to it, but I noticed that it said that root doesn't have a password, even though I set one in the mysql_secure_installation.  I don't really want to go any farther until i get a password on root, as eventually i'm going to have this server exposed to the outside, but every single command I have tried seems to do nothing.  I am testing if it is working by entering "sudo mysql -u root" with no password and i'm assuming if there is a root password set then it will prompt for one instead of just going straight into the mysql> prompt.
These are the commands that i've tried:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): 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 '('newpassword')' at line 1

mysql> SET PASSWORD = PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): 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 'PASSWORD('newpassword')' at line 1

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
RESULT: Query OK, 0 rows affect (0.01 sec)

Seeing this made me think its working, so i ran FLUSH PRIVILEGES; and refreshed myphpadmin but it still shows that it doesn't have password, so i kept going.
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): 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 'PASSWORD('newpassword')' at line 1

shell> sudo mysqladmin -u root password
New password: newpassword
Confirm new password: newpassword
RESULT: Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

This one seems like the right path maybe?  But it says i need SSL...which is weird because i'm on the computer in a terminal, i don't know how to do an ssl connection to mysqladmin on the same computer using the terminal.
shell> sudo mysql_secure_installation
RESULT: Success.  All done!

This acts like it works, but it does nothing
Change from myphpadmin:  I tried changing it from myphpadmin as well, it goes through with no issues, but the password still seems to be the same.
mysql> UPDATE user SET authentication_string=PASSWORD('newpassword') WHERE User='root';
RESULT: ERROR 1064 (42000): 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 '('newpassword')' at line 1

There was also mention of getting the first root login from /var/log/mysqld.log, but that doesn't exist on my computer
There are probably a few other odd things here and there i've tried but nothing has worked, does anyone have any insight into this?


texts: Which account should be used for the SQL Server Service logon account where the server has been added to a domain?

texts: I have installed SQL Server 2017 Express onto Windows Server 2012 R2. I need to add a self signed certificate to SQL Server Protocols in order to enable wire encryption. This comes with a requirement that the SQL Server logon account has full control permissions over the certificate. The server machine is on a domain. When I try to grant full control over the certificate to the default "NT Service\MSSQL$InstanceName" account, it throws an unrecognised name error. Should this issue be resolved by changing the SQL Server logon account to a domain account with administrator permissions? Are there any potential hazards associated with this practice or is this the recommended solution to this problem?


texts: Do all entities in Chen notation have to be diagrammatically linked?

texts: Is it good design practice to visually link each entity when using Chen's notation?


texts: SQL Server 2017 password complexity rule

texts:
SQL and Windows version: Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)   Jan 23 2020 21:00:04   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

Servers are in Workgroup.

Our Password Complexity Parameters for SQL login:

has at least one digit
has at least one uppercase character
has at least one special character
has at least one lowercase character

We are able set initial password for new SQL login as a TestingDB@001 and testingdb@001. But new initial password should be TestingDB@001 NOT testingdb@001 as per above Complexity Parameters. Is this expected behaviour of SQL 2017 for SQL login password?

We have tried to create OS user (non admin windows account), OS level also its taking both passwords (TestingDB@001 and testingdb@001). As per above Complexity Parameters, it should accept only TestingDB@001 password. Is the above SQL login password issue is due to OS?

Please suggest how to achieve above Complexity Parameters in password creation.

We have set below policy in local computer policy on DB server.


texts: MySQL dump restore; verify integrity of restore

texts:
Below is how I am loading a 30gb dump file. What is the best way to verify it works? Should I check for errors in nohup.out? If an error happens will it stop?

 nohup mysql < all_db1.sql  &


texts: Restore from SQL Server 2014 Enterprise to BI

texts:
Is it possible to restore a SQL Server 2014 Enterprise database with table partitioning (Production) to a SQL Server 2014 BI Version (Test/Development) ?

Of course I don't expect to use the table partitioning Feature on BI version, but I do expect a runnable version ...


texts: PostgreSQL stored procedure which can return one of two custom rowtypes

texts:
I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this:

CREATE OR REPLACE FUNCTION get_numbers(_type integer)
    RETURNS SETOF RECORD
    LANGUAGE plpgsql
AS $$
    DECLARE
        _number_column text;
    BEGIN
        CASE _type
            WHEN 1 THEN
                _column_name := 'normal_numbers';
            WHEN 2 THEN
                _column_name := 'special_numbers';
        END CASE;

        RETURN QUERY EXECUTE 'SELECT * FROM ' || quote_ident(_column_name)
    END;
END;

And I have two different custom composite types:

CREATE TYPE normal_number AS (
    id_normal_number integer,
    normal_number text,
);

CREATE TYPE special_number AS (
    id_special_number integer,
    special_number text,
);

When I call it SELECT * FROM get_numbers(1) I want to get:

id_normal_number  normal_number
-------------------------------
1                 00001
2                 00002
3                 00003

And for SELECT * FROM get_numbers(2) it should return:

id_special_number  special_number
--------------------------------
1                  S0001
2                  S0002
3                  S0003

The point is to call one function, execute dynamic SQL query, get some columns and cast it to custom row type according to procedure parameter (to have different column names in the result).

I tried to create another 2 procedures get_normal_numbers() and get_special_numbers() declared with RETURNS SETOF normal_number / RETURNS SETOF special_number and from the inside call get_numbers(1) or get_number(2) but I didn't manage to get it working.

I don't know how to properly cast the returned set of rows or how to correctly return that set from get_numbers(1) -> get_normal_numbers() -> application

I can edit / add some more details if it'll be needed.


texts: Specify Import Row Range for SQL Server Task Wizard

texts: I would like to import a csv file into an empty table in SQL Server Developer 2016, using Task Wizard.  I know with BULK INSERT there are options to specify firstrow and lastrow; does Import Wizard have a similar feature?


texts:
I would like to use the following syntaxis in my PL/pgSQL code:

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

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


texts: Installing service packs with SQL Server services stopped

texts:
I always installed Service Packs on the servers with running SQL Server Services. This time I am faced with the situation where I have to patch multi-instance SQL Server that has its services stopped for each instance. There is no posiblity of starting the services without breaking one of the applications. It is not part of a cluster, just a regular stand-alone server.

My question: Will stopped services affect instalation of SP?


texts: Getting the row before a row with a certain value in SQL

texts:
I have a table like below where user actions are stored with a timestamp. My goal is to identify the action that happened before a specific action (named reference_action) and count the number of those actions to see which actions happens before the specific actions and how they are distributed.
I am aware of window functions like LAG() where I can get the row before a certain row but can't figure out how to include a constraint like WHERE action_name = "reference_action".
The query engine is Presto and the tables are Hive tables but I'm mostly interested in the general SQL approach, therefore that shouldn't matter much.

session
action_name
timestamp

1
"some_action"
1970-01-01 00:01:00

1
"some_action"
1970-01-01 00:02:00

1
"some_action"
1970-01-01 00:03:00

1
"desired_action1"
1970-01-01 00:04:00

1
"reference_action"
1970-01-01 00:05:00

1
"some_action"
1970-01-01 00:06:00

1
"some_action"
1970-01-01 00:07:00

2
"some_action"
1970-01-01 01:23:00

2
"some_action"
1970-01-01 02:34:00

2
"desired_action1"
1970-01-01 03:45:00

2
"reference_action"
1970-01-01 04:56:00

2
"some_action"
1970-01-01 05:58:00

3
"some_action"
1970-01-01 01:23:00

3
"some_action"
1970-01-01 02:34:00

3
"desired_action2"
1970-01-01 03:45:00

3
"reference_action"
1970-01-01 04:56:00

3
"some_action"
1970-01-01 05:58:00

The result should look like:

action
count

"desired_action1"
2

"desired_action2"
1

There are two rows where "desired_action1" is directly followed by a row with "reference_action", when ordered by timestamp, hence the count being 2. The same logic applies for why the count is 1 for "desired_action2".
The goal is to know what a user did before he made a purchase (purchase = reference_action). To understand what he did before, I want to look up the action that happened before a purchase. Therefore I need to know the action_name in the row before a reference_action. desired_actions have to be counted, reference_actions are just the rows after the actions I want to count and used to determine which values should be counted.


texts: What is default behavior of PostgreSQL without specifying COLLATION?

texts: What is default behavior of PostgreSQL, if i don't specify collation externally? One of the main questions, is it case-sensitive or not?


texts: sql backup restore converts createdAt to local TZ

texts:
I have a remote MySQL DB in the cloud on JAWSDB.
I have createdAt and updatedAt timestamps for records that are in UTC TZ.
I'll backup these DBs using mysqldump -h HOST -u USER -p > backup.sql. I then restore these to a local MySQL Server using
mysql -u USERNAME -p DB_NAME < backup.sql
However, upon inspecting the new local backup...I notice that the createdAt timestamps are for my local TZ (EST and so currently 5hrs before the UTC version that are actually in JAWSDB). The updatedAt timestamps are still in UTC though.
Here's how I created those timestamp fields btw.
 `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

So it seems like the restore of the dump is converting from UTC to EST just for the createdAt field. Could this be due to an engine or mysql version mismatch between JawsDB and my local server?
Any insights? Thanks!


texts: Best way to migrate a huge SQL Server database with low downtime over network

texts:
Problem definition

Our database server needs to be transferred to an other datacenter. It runs on Microsoft SQL Server 2012 Enterprise (64-bit) and contains two databases of about 2TB and 1TB.

Having little to no downtime for this would be ideal.

Workload

Those databases are used for a .NET website and are constantly getting updated.

Having it not available over the weekend would be acceptable though. The currently in use DB would remain the only one in use until the switch over to the new one.

That switch would ideally be made just by changing DNS entries to point to the new DB server while making sure the DB is not being updated.

Also, time taken by this operation does not really matter as long as the switch from one server to the other (downtime) is kept low.

Approaches considered

Backup and restore

This has been done in the past but involved a high downtime even though it was done through an internal network, so more efficiently than through Internet
Log shipping

As far as I understand, this approach would minimize downtime by configuring a master/slave and transferring  an exact copy of the master DB to its slave being read only. As mentioned above, no access to the slave would be necessary and we just need a way to have a replica of the master DB without data corruption.

It also seems to be quite efficient in terms of resources utilization and wouldn't impact to much the master performance.

I might be wrong about this approach so feel free to correct me.
Database mirroring

I'm not too aware of that approach but it seems like a valid option. No need to have real time sync and performance of the master is quite important so asynchronous would be the way to go if this approach were to be chosen.
Other options?

That server runs directly on bare metal hardware so lower level solutions are unfortunately not an option. Maybe there is a better way to get this done?

Constraints

As described, those databases are quite big to the point they are hard to maintain but that's an other problem.

The versions of SQL Server will be the same (Microsoft SQL Server 2012 Enterprise 64-bit).

It will have to be transferred over network between two datacenters so most probably over Internet. Having disks sent from one site to the other for an initial sync is unfortunately not an option. Having some sort of security for the transfer would be ideal but we will do the best of this situation.

That should give a quite good overview of our needs for this task and hopefully some of you had to face that situation before.


texts: Combine datetime + string to make a proper datetime

texts:
I need to take a DATETIME column (Column A) that contains only a date, then add a VARCHAR(5) column (Column B) which contains a 24-hour representation of a time (i.e. 1330) and combine them to get a valid DATETIME to be used in a DATEDIFF command.

So for example, Column A contains 2013-07-01 00:00:00.000 and Column B contains 1330. The result should be 2013-07-01 13:30:00.000.

Edit: just to claify things, this is an existing DB that stores the data this way, it is out of scope of my project to completely change how things work to make it store the data in a proper way. If I could I would but I can't so I am stuck with the multiple columns. Also the data in Column B will always be a 4 digit time as the input page has checks to make sure time is a valid military time format.


texts: Like with the results of a subquery (multiple values)

texts:
What I'm trying to do is the following concept:

SELECT field1,field2,field3
FROM table1
where field3 like '%'+(SELECT distinct field from table2)+'%'

But this is not allowed because like does not accept multiple values. How can I rewrite this query so I can wildcard match a field with multiple result values from another table?


texts:
I have a database which contains a table BUILDING with in each row details about some building, another table BUILDING_UNIT contains rows with details about a single building unit which refers with a foreign key to the belonging BUILDING.ID.
The BUILDING_UNIT table also refers to a table CATEGORY which tells whether the BUILDING_UNIT is of category A,B,C,D again with a foreign key pointing to CATEGORY.ID.

Now the final cost of renting the building unit depends on its building, category and on the number of days it is rented and specific period of the year.
We only rent them weekly so I might as well use weeks only however I'd like it to be as flexible as possible in the future.

I cannot convince myself on a table which can represent this situation.

Do I have to use a table with coefficients for each day of the year and then a table with coefficients for A,B,C,D and then a table with coefficients for each Building and then somehow calculate a result?

Is there some standard and recognized implementation for problems of this type?

Thank you

EDIT: Notice the solution should abstract from the formula for calculating the cost which might change in the future.
However I might be asked to make a specific week of the year, for building unit X inside building Y to cost 300$ while the week after 600$.
Generally building units inside the same building and in the same week cost the same, however that might change in future so I'd like to treat already all specific cases.


texts: Can you change the value according to the date?

texts:
Is there a formula that can change the value according to the date of the day?
Examaple: in April it's 10 and in November it's 5, August is 6.
Kind regards
Karsten


texts: mongodb balancer does not move chunks because of obscure error

texts:
After carefully following this guide, on how to migrate a 3 member replica set to a shard containing 2 X 3 member replica sets, after sharding one of my collections nothing happened. Looking into the logs, i got this:

2014-06-22T16:49:55.467+0000 [Balancer] distributed lock 'balancer/vt-mongo-6:27018:1403429707:1804289383' unlocked.
2014-06-22T16:50:01.830+0000 [Balancer] distributed lock 'balancer/vt-mongo-6:27018:1403429707:1804289383' acquired, ts : 53a70939025c137381ef2126
2014-06-22T16:50:01.945+0000 [Balancer]  ns: database.emailevents going to move { _id: "database.emailevents-_id_MinKey", lastmod: Timestamp 1000|0, lastmodEpoch: ObjectId('53a6d810089f342ad32992d4'), ns: "database.emailevents", min: { _id: MinKey }, max: { _id: -9199836772564449863 }, shard: "replicaset2" } from: replicaset2 to: replicaset4 tag []
2014-06-22T16:50:01.945+0000 [Balancer] moving chunk ns: database.emailevents moving ( ns: database.emailevents, shard: replicaset2:replicaset2/vt-mongo-4:27017,vt-mongo-5:27017,vt-mongo-6:27017, lastmod: 1|0||000000000000000000000000, min: { _id: MinKey }, max: { _id: -9199836772564449863 }) replicaset2:database_replicaset2/vt-mongo-4:27017,vt-mongo-5:27017,vt-mongo-6:27017 -> replicaset4:replicaset4/vt-mongo-1:27017,vt-mongo-2:27017,vt-mongo-3:27017
2014-06-22T16:50:01.954+0000 [Balancer] moveChunk result: { errmsg: "exception: no primary shard configured for db: config", code: 8041, ok: 0.0 }
2014-06-22T16:50:01.955+0000 [Balancer] balancer move failed: { errmsg: "exception: no primary shard configured for db: config", code: 8041, ok: 0.0 } from: replicaset2 to: replicaset4 chunk:  min: { _id: MinKey } max: { _id: -9199836772564449863 }
2014-06-22T16:50:02.166+0000 [Balancer] distributed lock 'balancer/vt-mongo-6:27018:1403429707:1804289383' unlocked.

generally, this error:

result: { errmsg: "exception: no primary shard configured for db: config", code: 8041, ok: 0.0 }

is the result of the machines not being able to talk to one-eachother, but this isn't the case. I have manually checked and all the machines are able to talk to eachother.


texts: can I do a restore controlfile from autobackup out FAST RECOVERY AREA?

texts: I know that I can issue the command restore controlfile from "location_here". But there is some config that can I do for issue the command restore controlfile from autobackup even whether the autobackup of the controlfile is not in fast recovery area?


texts: Oracle dbms_scheduler: Can I create a job that will not overlap with itself?

texts:
How can I ensure that a dbms_scheduler job will never have more than one instance running?

For example, if I have a job that is scheduled to run once a minute, but it actually runs for three minutes, I don't want a second and third instance of the job to start and run concurrently.

(note) I previously asked this question specifying that either dbms_scheduler or dbms_jobs solutions were acceptable.  I have since learned that dbms_jobs does not work well on RAC, hence this dbms_scheduler-specific question. (end note)


texts: Subquery returned more than 1 value. please help. i beg for mercy

texts:
This is may table. In one column there are daily dates and in the other is a classification.

I use this query to count how many registers are in a month and which ones are C or D, but the subquery can only return one value . How can i fix it?

select FORMAT(fecha, 'yyyy-MM') as FECHAS, COUNT(*) as TOTAL,
(select count(tipo) from prueba where tipo = 'C' group by FORMAT(fecha, 'yyyy-MM')) as CREDITO,
(select count(tipo) from prueba where tipo = 'D' group by FORMAT(fecha, 'yyyy-MM')) as DEBITO
from prueba
where fecha >= '01-08-2014'
group by FORMAT(fecha, 'yyyy-MM')


texts: Error running PostgreSQL query from SQL Server 2016

texts:
The below query runs well in SQL Server 2016:
select ResellerId, vCompanyName ,x.*
from wlt_tblReseller AS main
outer apply ( select (count (ipkReportTypeId)) AS "count", vReportTypeName
              from wlt_tblReseller _all
              inner join wlt_tblClient clients  on clients.ifkParentResellerId = _all.ResellerId
              inner join wlt_tblReport_CompanyMaster reporslogs on reporslogs.ifkCompanyId = clients.ClientId
              inner join wlt_tblReports_TypeMaster rpt_types on rpt_types.ipkReportTypeId = reporslogs.ifkReportTypeId
              where RootResellerId = main.ResellerId
                and rpt_types.bStatus =1
                and bIsStatic =1
                and vReportTypeName  is not null
              group by ipkReportTypeId,vReportTypeName
            ) AS x
WHERE IsMiniReseller = 0
  and ResellerId <> 1
  and vReportTypeName is not null
order by  vCompanyName desc

But when I take it to PostgreSQL and change OUTER APPLY to LEFT JOIN LATERAL, it does not run and produces the following error:

ERROR:  syntax error at or near "WHERE" LINE 9:    )AS x WHERE
IsMiniReseller = 0 and ResellerId <> 1  and v...
SQL state: 42601 Character: 649

What could I be missing?


texts: Is  it possible to configure service broker to work with resource governor?  Currently all commands are being sent to the internal group but I want them to go elsewhere.


texts: Oracle RAC: Can two transactions share an SCN?

texts:
Under what conditions (if any) can two transactions share an SCN?

To put another way, is there a guarantee that if several DML statements share an SCN they are guaranteed to be part of the same transaction?


texts: In using SQL Server's Change Data Capture, I notice that the fn_cdc_get_all_changes_... table valued functions all use NOLOCK when querying against the capture tables. Is this a recommended practice when querying against the capture tables and if so, why?


texts: Dynamically REVOKE CONNECT FROM guest in SQL Server 2005

texts:
I wrote this simplistic script (I'm not very familiar with TSQL and dynamic SQL) to revoke all connections from guest accounts:

DECLARE @database_id int, @database_name nvarchar(100);

DECLARE database_cursor CURSOR FOR
SELECT name
FROM [master].sys.databases
WHERE name NOT IN ('master', 'tempdb')
AND state = 0

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @database_name

while (@@FETCH_STATUS <> -1)
        BEGIN
                Print @database_name
                exec('USE [' + @database_name + ']')

REVOKE CONNECT FROM 'guest'

                FETCH NEXT FROM database_cursor
                INTO @database_name
        END

CLOSE database_cursor
DEALLOCATE database_cursor

But apparently the REVOKE was still being applied to the master database.

So then I modified the script to this:

DECLARE @database_id int, @database_name nvarchar(100);

DECLARE database_cursor CURSOR FOR
SELECT name
FROM [master].sys.databases
WHERE name NOT IN ('master', 'tempdb')
AND state = 0

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @database_name

while (@@FETCH_STATUS <> -1)
        BEGIN
                Print @database_name
                EXEC('USE [' + @database_name + '];'+

'REVOKE CONNECT FROM GUEST;'

                    );

                FETCH NEXT FROM database_cursor
                INTO @database_name
        END

CLOSE database_cursor
DEALLOCATE database_cursor

The script runs, but the changes aren't be saved for some reason because the guest account is not revoked when I check for it afterwards.

Sorry, if I'm missing any other details.  I'm so unfamiliar to this realm, I'm not even sure how to frame the question.

Thanks in advance!

Update 1:
I'm checking that it's been revoked using this script from the DISA STIGs:

SELECT COUNT(grantee_principal_id)
FROM sys.database_permissions
WHERE grantee_principal_id = 2
AND state = 'G'
AND permission_name = 'CONNECT'


texts:
I'm using MySQL. I have three tables, and in every table, I have a time column.

With a single query I want to count the number of rows that are between two given dates.

I have no need with a connection between the tables (although they are related), so join is not necessary.

Is it possible?

Failed attempt #1

SELECT
  COUNT(a.time),
  COUNT(b.time),
  COUNT(c.time)
FROM
  tbl1 AS a,
  tbl2 AS b,
  tbl3 AS c
WHERE
  a.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 20:46:55'
AND
  b.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 18:46:55'
AND
  c.time BETWEEN '2018-03-07 18:32:55' AND '2018-03-07 18:46:55' ;


texts: Should a TeamMembers table have a relationship with the Student Table or the Enrolment Table?

texts:
I'll keep it nice and short and simply link to the ERD:

Users can be assigned a role, one of those is student.
So for the sake of this, Students are the same as Users!

A Student can be enrolled in many OfferedUnits. A Student can also be in Many Teams and a Team can have Many Students.

The issue I am having is this: Should the Many-to-Many relationship that allows a Student to be in many teams and a Team to have many students be between the Users Table and the Teams Table or the Enrollment Table and the Teams table?

The argument for the Enrollment option is as such: You will only be able to assign a Team to Enrolled students. That's the only reason I can think of doing it that way.

Otherwise, it logically doesn't make sense to connect Enrollment to Teams, because it's the STUDENT who is in a Team, not their Enrollment.

What do you think?


texts: How to match similar records using multiple columns

texts:
Given a GUID for an event, say logins, how might I search for similar or dissimilar events, utilizing the other columns data?

For example:
Find me similar logins to [GUID] {Server:"s123", State="NY", Status="503", etc...}

OR

Find me logins the opposite of that example given?

I feel there must been a name for this, but Full-Text, or "%Like%" syntax I do not believe would be sufficient.

Is indexing the data with solr or elasticsearch the solution?


texts: Should I archive and prune data from mysql table?

texts:
Here is my situation, I have a table with partition on ScheduleTime and index on Id and ScheduleTime

CREATE TABLE `tab1` (
  `Id` bigint(20) NOT NULL,
  `UserId` int(11) NOT NULL,
  `content` varchar(20) DEFAULT NULL,
  `ScheduleTime` datetime NOT NULL,
) ENGINE=InnoDB ;

ALTER TABLE `tab1` ADD PRIMARY KEY (`Id`);

I have only one master (write intensive)  no slave. Table rapidly grows(Currently More than 100M records) but need only current month data. Older data need for historical reference only.
Here I have options:

Archive partition which is older than 1 month and delete it from working table
Keeps as it is. ( I suspect after billions of rows It would affect performance)

If I choose option 1 then I have options

Older data archive in another table in same database.
Maintain another archive database
Archive as compressed  and store in separate disk

As little knowledge in database I don't know which is best practice or how everyone handle in this situation.


texts: Connecting to a MySQL database on Mac from a VMWare fusion Virtual Machine

texts:
I have MariaDB (5.5.34-MariaDB) installed on a Mac (OS 10.8.5) I can connect to the database fine locally (127.0.0.1) on port 3306. But when I try to connect to the database from windows 7 64bit running in VMWare fusion I get an error message.

I am using 32bit ODBC, as I believe that is what is required. I do have Little Snitch running on the mac side, but I have checked this and it is not blocking the connection. Windows side does have the firewall enabled, but I have added a rule to allow outgoing connections on port 3306. I have tried telnet from windows on port 3306 to the Mac and it also fails. Pinging the Mac from windows works fine.

Checking my.cnf, there does not seem to be anything that would block the connection. No bind-address = 127.0.0.1 in the file. I have added permissions for the user to connect from the IP using the GRANT SQL command. I am at a loss on how to get this working so any ideas would be appreciated


texts: SSDT/SSIS 2012 - How to automatically load some projects?

texts: I did not use the default projects folder in SSDT, ie SSIS 2012. I used my own instead. Every time I open SSIS, I have to load the projects manually. Is there any way I can load them automatically ?


texts:
Does anyone know how to tell when SQL Server mirroring was removed from a database? My log file is flooded with login failures but I can't find mention of removing of a mirror.

Is there any DMV way of finding this out?


texts:
I am trying to find a solution for this problem, I have two tables

Table no 1

| no | ticket |
|----|--------|
| 1  | T1     |
| 2  | T2     |
| 3  | T3     |

Table no 2

| id | CustomerId |
|----|------------|
| 1  | C1         |
| 2  | C2         |

I require a below set

| Tickeid | CustomerId |
|---------|------------|
| T1      | C1         |
| T2      | C2         |
| T3      | C1         |

It basically cycle with ticketid , I started with a cross join on both table but couldn't get the required result

EDIT:1
One change that can happen is the values in table 2 can have more than 2 values
like in table 1 have T1,T2,T3,T4 and table 2 have C1,C2,C3 then I require
T1C1,T2C2,T3C3,T4C1

Again

if table 2 is just C1

then I just Need T1C1.


texts: How can I model a database structure to retain student information?

texts:
I am completely new to databases and SQL.

I have generated a database in myphpAdmin consisting of a table with the columns (name, student number, grade). Basically this database will hold the grades for all the students in a school. In my web application program, the index.php displays all information in the database on the browser.

So basically each student will have a row in the database. Over time, the grades for each student will be added to the database once they sit an exam. How can I just add more information to the grade column of each student row without losing the grades that are already there? (As the UPDATE operation in SQL seems to override the old data).

Or am I going about this the wrong way and need to structure each student as a database in themselves and then add their grades into those individual databases, with grade1, grade2, grade3, etc.?


texts:
I have googled this error message and it says:

  INS-30014: Unable to check whether the location specified is on CFS
  Cause: The location specified might not have the required permissions.
  Action: Provide a location which has the appropriate required permissions.

But I am already the administrator and run the installation file as administrator. Any idea? Any help will be appreciated. Thanks.


texts: Is there a way to clone a row in a "maintainable" way (with PostgreSQL)?

texts:
I often have to clone rows in my applications so I do it the long way :

INSERT INTO mytable (a, b, c)
SELECT a,b,c FROM mytable WHERE id=12;

(id is declared as a SERIAL PRIMARY KEY, 12 being the row to clone).

The thing is, when I add new fields to mytable I must remember to update the clone functions to also clone those new fields.

I would like to clone rows in a more maintainable way. Something like :

INSERT INTO mytable
SELECT *, id:DEFAULT FROM mytable WHERE id=12;

As I write it, I also think it would be nice to clone detail rows : if I have a detail/child table declared with referential integrity like this :

CREATE TABLE mychild (
  id serial PRIMARY KEY,
  parentid int REFERENCES mytable(id),
  a text
);

I could clone mytable and all childs in one command rather than :

clone mytable (the long way)
get the new id
clone all mychild, setting parentid to the new id

Is there anything in PostgreSQL (or extensions) that could help dealing with these cases ?


texts: Oracle Performance - Varchar2 (x char) x Varchar2 (x byte)

texts:
I know the definition diference between Varchar2 (x char) x Varchar2 (x byte).

  varchar2(20 char) means you can store 20 characters -- whereas varchar2(20) means you can store 20 bytes.

What about the performance?
Is there a problem if all my database is using Varchar2 (x char), even though my whole database is just using english?

Thanks!


texts: completely remove mariadb database and root password

texts:
The system is Fedora 32. Mariadb was installed and operational; it is now necessary to reinitialize Mariadb and all data and users.
I removed mariadb-server and mariadb and erased /etc/my.cnf and my.cnf.d. I then reinstalled mariadb and mariadb-server and ran the mysql_secure_installation script. It found the original root password but otherwise completed normally.
I then logged into mariadb. I expected to find the users from the original installation had been removed; however they had not.
show databases; shows the original tables. They were not removed.
select * from mysql.user; showed the original users.
How can mariadb be completely removed including all users, data, and passwords?


texts: Replication fails because of a non-existent foreign key

texts:
I have been trying to get a previously working replication to start working.

I have tried many settings, but I can't seem to make it work.

Here is the scenario.  I dropped all the FK constraints from the destination database (call it DestDB).

I then re-initalize my replication and when I Runs I get this (de-identified) error:

  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  "FK_TableOnlyInDestDB_MyReplicatedTable". The conflict occurred in database
  "DestDB", table "SomeSchema.MyReplicatedTable",
  column 'MyReplicatedTableId'. (Source: MSSQLServer, Error number: 547)

It is driving me nuts because the FK indicated does not exist in that database.

Now, I did copy this database (backup and restore) from another database.  So the only thing I can think of is that it is somehow crossing the streams.

But that seems unlikely.

Any ideas would be great!

NOTE: This is the query I ran to ensure that I have no FKs (It returned no rows):

use DestDB

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

I also checked to ensure that the FK in question was not on the source table (in the database I am replicating from).

I have also tried: EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Also tried Dropping MyReplicatedTable.  Dropped just fine and replication re-created it.  (Still gave me the error though.)

I tried creating an empty database as a subscriber.  That worked fine, so it is something in the DestDB that is causing the issue, but the FK in the ERROR does not exist.


texts:
We have a 2 node AAG setup with vCaC6 application running on it. Quite often, I get deadlock alerts from the server, and by the time I open the activity monitor, the deadlocks are no longer present.

I assumed that they were getting resolved automatically. But after Googling a little bit, I came across a VMware article which mentioned that this was a known problem and by design. The factors mentioned by Vmware are

1) Slow networks with large latency numbers between Manager Service (Application Server) and Model Manager (Repository) and DB
2) Unreliable networks with intermittent network/connectivity failures
3) Misconfiguration or underpowered MS SQL DB servers
4) Any factors that would slow down or prevent completing SQL DB stored procedures calls from remote clients, or result in the MS SQL DB server changing lock escalation levels from row to table.

I checked if there were any network waits happening on the server and could not find any.

This is what I find from the vCAc6 database, when I run sp_who2. Lot of these.

I also noticed this in a VMware KB article which says "The deadlock and retry is a standard part of the product and usually not a problem. Every site is different in load and configuration. In this case we identified a blocking scenario that would probably occur in other sites. However, this has not been reported and testing has not revealed this level of activity"

Does this mean, there is nothing to fix this issue? I dont think the AAG itself is under powered. What other ways can I explore to check if there are any network latencies between the application server and the Database?

Thanks!


texts: SQL log shipping from different hosts on different versions

texts:
We have a current setup where we have two SQL 2008R2 database servers in production.  Our reporting team requested a single place they could query data from both servers (to avoid both hitting production directly and the performance issues with linked servers).  So we set up log shipping where we ship several databases from both sources to a target server.  It works great, the databases on the target server are read only during the day and only unavailable when the log shipping restores are scheduled at night.  However, the vendor applications for both source servers are going through an upgrade project, and they will unfortunately have to be be different versions (SQL 2014 and 2016 respectively).
Most of what I have researched seems to say that you cannot log ship from a different source version, but I've also found information suggesting that you can ship from a lower to a higher version, but am unable to find anything official.
So, my question is, is it possible to log ship from both 2016 and 2014 sources into a 2016 destination, AND leave the databases read only when they are not restoring?  And is there official Microsoft confirmation of this?


texts: Why is it necessary to grant access to PostgreSQL sequences?

texts:
Using PostgreSQL I see that the pg_dump command (and probably other commands) requires access to 'sequences'. I understand these to just be incremental IDs that are necessary for row identification...
Why is it necessary for server roles to be granted access to sequence objects? What are the potential security problems if every role had access to all sequences of accessible schemas?


texts: Modify TNSNAMES.ORA file to connect to an Oracle Database

texts:
I would like to modify TNSNAMES.ORA file to connect to an ORACLE Database and I noticed that on my PC there are 3 different files with name TNSNAMES.ORA files. Which of these three should I modify in order to make connection

D:\app\XXX\product\11.2.0\client_32\network\admin
E:\app\XXX\product\11.2.0\client_64\network\admin
D:\app\XXX\product\11.2.0\client_1\network\admin


texts: PostgreSQL Bloat Warning: Vaccuum does not help

texts:
We use check_postgres to monitor our DB. We get this warning:

POSTGRES_BLOAT WARNING:
DB "foo" (db foo) index foo_text_index_id_uniq rows:?
pages:485435 shouldbe:330673 (1.5X) wasted bytes:1267810304 (1 GB)

According to the docs a vacuum should help, but the autovacuum does not help.

What can I do to get rid of the bloat?


texts: Ms-Access 2007-2010 SQL - from single row record how to create multiple rows


texts:
Consider the following query:

MERGE [Parameter] with (rowlock) AS target
    USING (SELECT @AreaId, @ParameterTypeId, @Value)
            AS source (AreaId, ParameterTypeId, Value)
    ON (target.AreaId = source.AreaId AND
        target.ParameterTypeId = source.ParameterTypeId)
    WHEN MATCHED THEN
        UPDATE SET target.Value = source.Value, @UpdatedId = target.Id
    WHEN NOT MATCHED THEN
        INSERT ([AreaId], [ParameterTypeId], [Value])
        VALUES (source.AreaId, source.ParameterTypeId, source.Value);

Statistics I/O gives the following output:

  Table 'ParameterType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Area'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Parameter'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Worktable appears in the messages tab which makes me think that tempdb is being used by MERGE.

I am not seeing anything in the Execution plan that would indicate a need for tempdb

Does MERGE always use tempdb?

Is there anything in BOL that explains this behavior?

Would using INSERT & UPDATE be faster in this situation ?

Left

Right

Here is the table structure


texts: Strange looking where clause in sql server

texts:
I've found fa strange looking code in T-SQL.
Here is the strange looking part:

WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (businessLogicCondition))

The query works well, but what is the point to do @term1 IS NULL OR (@term1 IS NOT NULL. Maybe it is connected with the fact that sql server doesn't have short-circuits WHERE condition evaluation.
From my C# background it seems really strange.

The question is: is it really a good practice to do such double comparisons? does it affect execution plan?

Added:
my businessLogicCondition is misleading, so here is revised version:

WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (ua.FirstName LIKE @term1 OR ua.SecondName LIKE @term1))

from my point of view the query is equivalent to:

WHERE @term1 IS NULL OR ua.FirstName LIKE @term1 OR ua.SecondName LIKE @term1

the question is whether the original option can improve execution plan, or it depends?


texts: Does a materialize view refresh in its creation?

texts:
I create a materialized view with the following cast function. It does not give me an error during creation although it exists an error. But refresh gives the error.

CREATE MATERIALIZED VIEW XXXX_ROW_MV
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('null' AS NUMBER)   currency_rate,
     CAST ('null' AS NUMBER)   conversion_factor;

Does this mean that materialized views compile until it is being refreshed?

refresh can be done by using following code

begin
  DBMS_MVIEW.refresh('XXXX_ROW_MV');

end;


texts: Indexes slowing Insertions(Can't use Explain Insert to Analyse)

texts:
I have a MySQL database,  I am parsing files from a log and inputting it inside.

So far including a simple 4MB file in the database could take me about 4 minutes to insert ! and i have about 10,000 of such files to process.

At first i thought it's my script but after further digging i learned that this could be related to indexes.

I have a table with 4 columns, 1 primary key and 2 foreign keys which are by default indexes.

From this is it possible to know if the indexes affect insertion performances ? I can't seem to use Explain Insert with MySQL so i don't know of any more information to provide.

CREATE TABLE `as_path` (
  `AS_Path_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Path_Index` int(11) DEFAULT NULL,
  `AS_No` int(11) NOT NULL,
  `Update_ID` int(11) NOT NULL,
  PRIMARY KEY (`AS_Path_ID`,`Update_ID`,`AS_No`),
  KEY `AS_No_fk_idx` (`AS_No`),
  KEY `Update_ID_fk_as_path_idx` (`Update_ID`),
  CONSTRAINT `AS_No_fk` FOREIGN KEY (`AS_No`) REFERENCES `as_detail` (`AS_No`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `Update_ID_fk_as_path` FOREIGN KEY (`Update_ID`) REFERENCES `update_detail` (`Update_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1074256 DEFAULT CHARSET=utf8;


texts: Entity-Relationship Diagram problem: trying to resolve a tentative many to many relationship

texts:
The case study states "each car belongs to a car type" and "each mechanic specializes in a car type or many car types".

My current entity type definitions:

Car
CarType
Mechanic

My Entity-Relationship Diagram so far:

Car        1-----0..*   CarType
CarType 0..*-----1      Mechanic
Car     0..*-------0..* Mechanic

As stated in the title, I am attempting to resolve a possible many to many relationship.


texts: SQL Server MAXDOP , microsoft reommendation

texts:
Microsoft's recommendation for setting MAXDOP is based on logical processors count.
https://support.microsoft.com/en-gb/kb/2806535

On my server which has one hex core CPU, hyper threaded,6 physical cores -> 12 logical. So by Microsoft's recommendations MAXDOP should be set to 8, however a few resources I've read say to set it to the number of physical cores , so 6 in my case, which is it ?! Or is this a question with out an answer, a depends scenario ?


texts: Poor performing Query -Tsql execution plan - estimated number of rows =1 Paste the Plan

texts:
The query and the execution plan are available at the link below.

This is from vendor application that I support. I know that there has to be a better way to write this code but the vendor says that it is the best that they can do.

I have a problem with the number of joins, the indiscriminate use of nolock, the top(1) and the use of the subqueries.

I am looking for a recommendation on how to do this better as the vendor does not appear to have the ability to improve on the query.

This query negatively impacts the performance of my production environment. Any advice would be greatly appreciated.

https://www.brentozar.com/pastetheplan/?id=HJlq-ERAb


texts: Difference between "Field Specification" and "Field Domain"

texts:
I'm currently in the process of learning (Relational) Database Design based on two popular books, online tutorials and articles.

Some places, when they talk about attributes constraints, they call it an Attribute Domain.
Some other places, when they talk about attribute specification, they call it a Field specification.

However, some other places, they say that they are both the same, but attribute domain seems to me to be part of the field specifications since by definition, attribute domain sounds like the Physical Element part of field specification:

  Physical elements: Data type, Length, Decimal Places, Character support, input mask, display format

What is it now?


texts:
Can we filter the articles in the publication (which has few subscribers already) while adding the new subscriber?

look at the last statement ‘Subscribing to individual articles within a publication can get tricky’ in the below article

http://www.sqlservercentral.com/blogs/kendalvandyke/2010/08/31/replication-gotcha-2D00-including-an-article-in-multiple-publications/

Please help me in understanding.


texts: SQL column goes from first to last position in output

texts:
I have an SQL query inside a SQL Server Integration Services (SSIS) package where the preview shows the column order correctly, however the actual output uses a different column order.

This is exported to a .csv file for import into another application and needs to be in a specific order.

I need the Date column first, which shows up that way when I preview the data output in the OLE DB Source Editor window; however, when I click on the "Columns" to display their order it shows them outputing in the last position, which is what they do.

Here is the SQL query:

DECLARE @now DATETIME;

SET @now = GETDATE()-45;

SELECT CONVERT(VARCHAR(8), [ORDERS].[STARDAT], 10) AS [MM-DD-YY], [ORDERS].[CLIENTID],
    [ORDERS].[SAMPNAME], [ANALYTES].[OP10AN], [RESULTS].[FINAL]
FROM [ORDERS], [RESULTS], [RUNS], [ANALYTES], [SamNam]
WHERE [ORDERS].[sampname] = [SamNam].[SName]
    AND [RUNS].[RUNSTS]= 'D'
    AND [RUNS].[COMPDATE]>@now
    AND [ORDERS].[SAMPTYPE] IS NULL
    AND [ORDERS].[ORDNO] NOT IN (308333,308334)
    AND [ORDERS].[CLIENTID] <> 'Ind Waste'
    AND [RESULTS].[ORDNO]=[ORDERS].[ORDNO]
    AND [RESULTS].[RUNNO]=[RUNS].[RUNNO]
    AND [RESULTS].[ANALPRINT]= 'Y'
    AND [ANALYTES].[SINONYM] =[RESULTS].[SINONYM]
    AND [ANALYTES].[TESTCODE] =[RESULTS].[TESTCODE]
    AND [ANALYTES].[OP10AN] IS NOT NULL
ORDER BY  [ORDERS].[ordno];

Any input into why the query is getting turned around?


texts: Instant File Initialization | Lock Pages in Memory Issues

texts:
I have a server that I'm coming onto performance tune and for the life of me I can'g get these rights enabled.  So I ran

PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami
nofault\administrator

Then

PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami /priv

PRIVILEGES INFORMATION
----------------------

Privilege Name                  Description                               State
=============================== ========================================= ========
SeLockMemoryPrivilege           Lock pages in memory                      Disabled
SeIncreaseQuotaPrivilege        Adjust memory quotas for a process        Enabled
SeSecurityPrivilege             Manage auditing and security log          Disabled
SeTakeOwnershipPrivilege        Take ownership of files or other objects  Disabled
SeLoadDriverPrivilege           Load and unload device drivers            Disabled
SeSystemProfilePrivilege        Profile system performance                Disabled
SeSystemtimePrivilege           Change the system time                    Disabled
SeProfileSingleProcessPrivilege Profile single process                    Disabled
SeIncreaseBasePriorityPrivilege Increase scheduling priority              Disabled
SeCreatePagefilePrivilege       Create a pagefile                         Disabled
SeBackupPrivilege               Back up files and directories             Disabled
SeRestorePrivilege              Restore files and directories             Disabled
SeShutdownPrivilege             Shut down the system                      Enabled
SeDebugPrivilege                Debug programs                            Enabled
SeSystemEnvironmentPrivilege    Modify firmware environment values        Disabled
SeChangeNotifyPrivilege         Bypass traverse checking                  Enabled
SeRemoteShutdownPrivilege       Force shutdown from a remote system       Disabled
SeUndockPrivilege               Remove computer from docking station      Disabled
SeManageVolumePrivilege         Perform volume maintenance tasks          Disabled
SeImpersonatePrivilege          Impersonate a client after authentication Enabled
SeCreateGlobalPrivilege         Create global objects                     Enabled
SeIncreaseWorkingSetPrivilege   Increase a process working set            Disabled
SeTimeZonePrivilege             Change the time zone                      Disabled
SeCreateSymbolicLinkPrivilege   Create symbolic links                     Disabled

No prob I go Local Security > Local Policies > User Rights Assignment grant the LockPages in Memory to Network Administrator and perfom volume maintenance tasks.  No Good.

I tried giving the privileges to everyone ( I know bad Idea but just to test )  still no good.  And yes I restarted SQL server after each try.  Has anyone had any similiar issues with this, real cheap performance gain and I usually have no problem with this.

Thanks


texts: How to choose a collation for international database?

texts:
I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the query itself (because there are more than one correct ways to do that), as follows:

SELECT a < b COLLATE "de_DE" FROM test1;

Assuming this is the correct way to work with international data, which is the best collation for the database itself? PostgreSQL documentation says:

  The C and POSIX collations both specify "traditional C" behavior, in which only the ASCII letters "A" through "Z" are treated as letters, and sorting is done strictly by character code byte values.

I think this is the best choice in this case, or am I wrong?

(Bonus question: is it too slow to select the collation in the query itself?).


texts: How do I get the types of columns for postgresql?

texts:
I am trying to get the type of the column. For instance I have two columns for names and ids and they are text and integer respectively. I have 3 of them:

name    id
george  5
takashi 2
hans    8

Is that possible to get a result like that?

name id
text int
text int
text int

I have seen this code below and it did not work for me

select column_name,data_type
from information_schema.columns
where table_name = 'table_name';


texts: How to activate shard balancer in sharded MongoDB

texts:
I have a very simple MongoDB database structure. Also I have 5 shards in it (3 of them are replicasets). But we are testing DB loading and it seems that shard balancing isn't currently enabled.

On mongos I have checked the next:

mongos> sh.getBalancerState()
true
mongos> sh.isBalancerRunning()
false

I can't set BalancerRunning to true. I tried:

sh.startBalancer()

Please help me to start it for all my shards. Thank you.


texts: SQL performance issues with remote query across linked server

texts:
This sproc

create proc dbo.Get_Accounts as
begin
  declare @current_date datetime
  set @current_date = dbo.fn_currdate()

  select [fields]
  into dbo.current_accounts
  from linkedserver.database.dbo.accounts
  where date = @current_date
end

continually fails after 10 minutes with the following error message:

  Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
  reported an error. Execution terminated by the provider because a
  resource limit was reached. [OLE/DB provider returned message: Timeout
  expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
  ICommandText::Execute returned 0x80040e31: Execution terminated by the
  provider because a resource limit was reached.].

However, when I run the same query from the same database (not on the remote one) in an interactive query window with the date hard-coded:

  select [fields]
  into dbo.current_accounts
  from linkedserver.database.dbo.accounts
  where date = '1/20/2012'

It returns in 30 seconds.

The local server is SQLSERVER 2008, the remote is SQLSERVER 2000.

We have done the following to no avail:

Recreated the stored proc.
sp_recompile on the stored proc
update statistics on dbo.accounts
dropped and recreated the indexes on dbo.accounts
dropped the index on dbo.accounts and try
DBCC FREEPROCCACHE & DBCC DROPCLEANBUFFERS on both local and remote servers
Rebooted the remote server (not an easy option on the local one)

Questions

Can anyone explain this bizarre behavior?
Any suggestions on other options for correcting it?


texts: Will 'sleeping' connections open files other than the socket in MySQL?

texts:
Recently, in our MySQL replication setup, the replication slave crashed due to 'too many open files' error. We brought it backup, but trying to figure out what caused the unexpected hike in the number of open files. We habitually see too many connections in 'sleep' status. Would these 'sleeping' connections open files other than the sockets they keep open?

MySQL version is 5.6.20. OS is RHEL 6.4

Edit: Our MySQL is started with a huge number of 'open-files' limit and the OS has already allowed mysql user to open that many number of files. I am doing an RCA and would like to know whether the sleeping connections would open files other than the network socket they open.


texts: Check if database is restored to the most recent log file

texts:
Folks,

I am running a copy of our production database as a backend for an Instance of SQL Server Reporting Services. The copy is being updated off transaction logs which are taken frequently (hourly) for backup purposes using RESTORE DATABASE WITH NORECOVERY / WITH STANDBY. I obviously cannot use the built-in transaction log shipping facilities as the database is already being BACKUP LOGged and trying to use log shipping would break both mechanisms' log chains.

But I need some way to monitor if my copy is up-to-date, ideally without changing the original database's schema or data. My first approach would contain retrieving the most recent log backup's last LSN using RESTORE HEADERONLY FROM DISK and comparing it to the LSN of my copy instance. But I am having difficulties finding an LSN which would be able to match the result from RESTORE HEADERONLY - what I see in sys.database_files seems to be a different thing entirely.

Where would I find matchable LSNs?


texts: Creating a UNIQUE constraint from a JSON object

texts:
Lets take some example table peoples , that got only 2 fields: id and data(json).

SELECT data FROM peoples ;
{"name": "Adam","pos":"DBA","age":22 }
{"name": "Alice","pos":"Security","age":33 }
{"name": "Bob","pos":"Manager","age":42 }

I want to create constraint for "pos" field, that must be unique.
I've searched over the internet about JSON constraints but no results.

How can I handle this problem ?


texts: Alternatives to using subqueries as parameters?

texts:
I have a very large non-partitioned table that is taking a long time to return results from this query:

SELECT
    (SELECT Listagg(VALUE_STRING, ', ')
            WITHIN GROUP (ORDER BY VALUE_STRING) AS VALUE_STRING
     FROM   ATTRIBUTE_DATA AD
     WHERE  AD.id = DT.dataid
            AND AD.defid = 3070056
            AND AD.attrid = 4
            AND vernum = (SELECT Max(vernum)
                        FROM   ATTRIBUTE_DATA AD
                        WHERE  AD.id = DT.dataid
                                AND AD.defid = 3070056
                                AND AD.attrid = 4)
            AND defvern = (SELECT Max(defvern)
                        FROM   ATTRIBUTE_DATA AD
                        WHERE  AD.id = DT.dataid
                                AND AD.defid = 3070056
                                AND AD.attrid = 4)
    GROUP  BY AD.id) AS PlantNumbers
FROM WORKTABLE W
    left outer join CUSTOM_FORMS FORMS
                ON FORMS.volumeid = w.subwork_subworkid
                    AND FORMS.versionnum = -1
                    AND FORMS.rowseqnum = 1
    left outer join DATATREE DT
                ON DT.name = FORMS.document_number_text_field
                    AND DT.ownerid = -2000
                    AND DT.SUBTYPE = 144
                    AND DT.dataid NOT IN (SELECT dataid
                                        FROM   dtreeancestors
                                        WHERE  ancestorid = 3109628)

When I take just this subquery alone it takes 12 minutes to run:

SELECT Max(vernum)
FROM   ATTRIBUTE_DATA AD
WHERE  AD.id = DT.dataid
   AND AD.defid = 3070056
   AND AD.attrid = 4

How can I improve this script.


texts: Define calculated column of BOOLEAN/TINYINT(1) type in a View?

texts:
I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view:

CREATE VIEW `ItemView` AS
SELECT Item.ID
    ,ItemProperty1
    ,ItemProperty2
    ,ItemProperty3
    ,EXISTS(SELECT ItemComponent.ItemID FROM ItemComponent WHERE ItemComponent.ItemID = Item.ID) AS Operation1Required
    ,Item.Property4 <> ItemType.Property AS Operation2Required
FROM Item
INNER JOIN ItemType
    ON Item.TypeID = ItemType.ID
ORDER BY ItemProperty2 ASC

At the moment, MariaDB metadata says that the type for the "Operation1Required" and "Operation2Required" columns is INT, even though they are both expressions with fundamentally Boolean results.

I want them to be BOOLEAN/TINYINT(1), because the view is being read by a strongly-typed client which is expecting Boolean values for these columns.

But the CAST() and CONVERT() functions in MySQL/MariaDB 10.1 don't seem to be able to convert to TINYINT(1).

Is there another way I can specify the correct data type for these calculated columns in the View?

I looked at How to cast an integer to a boolean in a MySQL SELECT clause? and tried it out, but the results of IF(expression, TRUE, FALSE) still seem to be INT according to phpMyAdmin's report of the view's structure.


texts: Extended event duration Millisecond or Microsecond?

texts:
There was a question in this forum about Extended Event module_end duration unit, which I answered.  Details here.

Is it always in microseconds for all events?


texts: How can I call a SQL Server stored procedure from Oracle and retrieve a scalar return value?

texts:
I'm developing a PL/SQL solution in Oracle Database Enterprise Edition 12.1.0.2. I'm calling stored procedures in Microsoft SQL Server 2014.
I am using DG4ODBC with the DBMS_HS_PASSTHROUGH package to call those stored procedures.
For stored procedures that return a tabular result set, I am able to use PARSE, FETCH_ROW, and GET_VALUE to retrieve the values. (My solution uses hard parsing, but it works for the volume we're expecting. I'm open to tips if you know how I can use binds, but that's not my question.)
DECLARE
   sql_server_cursor_     BINARY_INTEGER;
   sql_server_statement_  VARCHAR2(32767);
   fetched_value_col1_    whatever_type_from_col1;
   fetched_value_col2_    whatever_type_from_col2;
BEGIN
   sql_server_statement_ := 'EXEC "myDatabase"."dbo"."myProcedure" @p_one = N''' || sanitize__(my_argument_) || ''';';
   sql_server_cursor_ := dbms_hs_passthrough.open_cursor@sql_server_link_;
   dbms_hs_passthrough.parse@sql_server_link_(sql_server_cursor_, sql_server_statement_);
   WHILE dbms_hs_passthrough.fetch_row@sql_server_link_(sql_server_cursor_) > 0
   LOOP
      dbms_hs_passthrough.get_value@sql_server_link_(sql_server_cursor_,  1, fetched_value_col1_);
      dbms_hs_passthrough.get_value@sql_server_link_(sql_server_cursor_,  2, fetched_value_col2_);
      dbms_output.put_line('fetched_value_col1_  ' || fetched_value_col1_);
      dbms_output.put_line('fetched_value_col2_  ' || fetched_value_col2_);
   END LOOP;
   dbms_hs_passthrough.close_cursor@sql_server_link_(sql_server_cursor_);
END;
/

I have a stored procedure in SQL Server that returns a scalar integer.
DECLARE @return_value int;
EXEC @return_value = "myDatabase"."dbo"."myProcedure" @p_one = N'HelloWorld', @p_two = 42;
SELECT @return_value;

From Oracle, how would I call this procedure and retrieve that scalar value?


texts: Will hotfixing parameter sniffing with optimize for xy help?

texts:
I figured out a parameter sniffing issue and after doing experiments have decided to introduce a code (query) change in app code that uses optimize for unknown hint.

Now, I need to make sure the situation won't come up again until this code change is completed and deployed. So I thought about temporarily creating a sql agent job that executes the relevant query with the parameters required to create an accepatable plan for every kind of parameter value like:

...OPTION (OPTIMIZE FOR (@xyz = 123));

The question is, will execution of this plan in an agent job after any index & statistics maintenance jobs even hit the same query, if the same query coming from the app's code does not yet have the query hint inside of it?
If the agent job's queryplan won't match the original query because of the hint, there is not use of it...


texts: Help me to query this two tables properly

texts:
Here is my mySQL query which I used to get the details from three tables.

    SELECT `pg`.`id`,
ref, business_name, `usr`.`full_name`,
date, `pmt`.`amount`, `pg`.`status`
    FROM kv_ad_orders AS `pg`
    LEFT JOIN kv_users AS `usr`
           ON `pg`.`sales_rep_id` = `usr`.`ID`
    RIGHT JOIN kv_payments AS `pmt`
            ON `pg`.`ref` = `pmt`. `transId`

here it shows only the kv_payments table entries only. Not the whole results. I want to  display all the selected columns.

If I make it as left join table, than its showing other results except the amount.


texts:
We have a website that stores form data in a web hosted MySQL database. Whenever form data is entered into the MySQL server tables I need update all necessary tables on our local SQL Server database.

I have seen several articles that describe how to update MySQL using SQL Server triggers but nothing that goes the other way.

If I create a linked server and linked tables in SQL Server can I create a trigger on a MySQL table from the SQL Server management studio?


texts: Can I change the SID of an Oracle database?

texts:
The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

Is there an easy & quick way to change the SIDs of the test databases on the server?

Dropping & recreating of the database is an option for me. But I'm looking for something requiring less time.

The other option to assign names in the clients tnsnames.ora is prone to errors, because they are not administrated centrally.

Compared with the time to drop & create a database on SQL-Server, the amount of time required to create a new Oracle database is excessively greater.
Further on SQL-Server you can rename SQL-Server instances. [ Usually you rename the server where SQL-Server is running and have some problems until you rename the server too ].


texts: SSMS does not open popup wizard to create a new schema

texts:
When I right click on 'schemas' and then on 'create new schema' my SSMS only opens the query editor but on every tutorial I saw a pop up wizard opens instead. How can I access this popup wizard? I can't find the right setting.
I'm currently following the course Developing SQL Databases on edx but I've also seen the popup wizard on youtube tutorials. I'm using the version 17.9.1 of SSMS

Many thanks in advance!


texts: Avoiding code duplication for counting and selecting the same resultset

texts:
I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is true. How do I perform the 2 queries (counting + paged result) without having to replicate the entire FROM + WHERE sections?

My FROM and WHERE are non-trivial, so I'd like to avoid having to duplicate them across the 2 queries. My primary goal is avoiding duplicate code as opposed to improving performance.

This is what I have currently:

CREATE PROCEDURE [dbo].[myProcedure]
    @includeCount bit,
    --... where parameters
    @skip int,
    @take int,
    @totalCount int OUTPUT
AS
BEGIN
    IF (@includeCount = 1)
        SELECT @totalCount = COUNT(*) FROM <Complex joins + where>

    SELECT (alias1.field1, alias2.field2, alias3.field3)
    FROM <Complex joins + where>
    ORDER BY [alias1].[Key]
    OFFSET @skip ROWS
    FETCH NEXT @take ROWS ONLY
END
GO

Is there a way to "extract" the common parts (the FROM+SELECT statements) in a way as to avoid the massive duplication while retaining the same performance characteristics of the original queries? Would it pose a problem to my ORDER BY and SELECT sections which depend on aliased tables inside FROM? How would I access those?


texts: Possible to use a Trigger as failsafe for insert in two databases?

texts:
(This is for a project on MSSQL 2008R2 Server)

I have been tasked with splitting a table into an internal (same db as before) and an external one (different server).
Currently the idea is to read only from the external table and to insert into the external table IF possible. If the external is unreachable the inserts are to be redirected into the internal table until the external is back online. The records within the internal table will be moved into the external table manually afterwards.

At the moment we have created a view that points to the external table and a instead of insert Trigger that does the following:

ALTER TRIGGER dbo.Log_IoI ON dbo.Log
INSTEAD OF INSERT
AS
BEGIN
    BEGIN TRY
        INSERT INTO LINKEDSERVER.dbo.ExternalLog (...)
        FROM INSERTED
    END TRY
    BEGIN CATCH
        INSERT INTO dbo.InternalLog (...)
        FROM INSERTED
    END CATCH
END

(Let me state at this point, that we are using a view to replace the table because there are a ton of triggers and stored procedures targeting this logtable and we can't change them without an incredible amount of extra work.)

I thought this was a good idea at first but two problems reared their ugly head.

After a lot of try and error I can't seem to be able to contain critical error in the try. For example if the insert into the external db fails because the external db went offline I get the following Message:

OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

After some research I have found some discussions over at SQL-Central that it is not possible to do this kind of error-handling within a trigger.

This has me stumped and brings me to my question:

IF this is true, is their any other way to build a failsafe "fork in the road"-Trigger?

I have tried to use
    SAVE Transaction
before the Try and then to Rollback at the beginning of the catch. It had no effect.

I am open to a new direction to solve this problem as I am completly stumped and I might be kicking a dead horse by trying to make this solution work.

PS: Second problem is performance. If the trigger would work in case of a failure of the external db the trigger would wait for a timeout on evrey insert until the insert would be redirected to the internal database. Really bad for performance seeing as it is a logging table.


texts: postgres npgsql DDL statement success or failure

texts:
I am using npgsql with a postgres DB, writing a DB updater that will need to execute a series of (usually) DDL statements like "ALTER TABLE ADD COLUMN blah..." and the like. The simple way to do this is with Command.ExecuteNonQuery, however I need to know if each statement succeeded or not. ExecuteNonQuery returns "the number of rows effected" but it appears that this is always 0 for DDL regardless of success or failure, since the content of the table is not altered, only the structure.

What is the simplest way of determining the success or failure of arbitrary DDL (and update) commands read in from a text file? Can/Should/Must I wrap each statement in a procedure with an exception handler? Is there a simpler, more straightforward way?

One issue I see with wrapping in a procedure is that the procedure itself must then be part of the DB, and the update process is no longer as independent. I would like to avoid this. Of course, my updater could create the procedure if needed, and remove it after, but this seems cumbersome. Am I missing something obvious?

________Edit for clarification:

@Laurenz Albe is of course completely correct, if there is a syntax error, key duplication, missing column referenced, etc., an exception will be thrown. This probably covers nearly all cases. However, I have encountered issues where the SQL is valid but may or may not actually do anything useful - for example, if there is a WHERE clause, I may be expecting some (particular) number of 'rows effected'. The absence of an exception merely tells me that the statement was valid. When can or cannot I rely on the return value of ExecuteNonQuery, or is there a simple alternative?


texts: Insert values from a record variable into a subclass table

texts:
My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME schema. I want the output_table to have the same schema PLUS some extra computed columns.

Will I have to use a less elegant solution than the one linked? Or is there a solution where I can append columns onto the _tbl_in record before I insert it to the subclass _tbl_out?

Here is the code for the previous solution for reference:

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

  Call (important!):

SELECT gesio(NULL::t, 't1');

  t and t1 being the tables with identical schema.


texts: SELECT with lots of left join is very slow in SQL Server 2014

texts:
I've got a query with, more or less, this structure:

SELECT
    MAINTABLE_ID
FROM
    MAINTABLE WITH (NOLOCK)
LEFT JOIN
    TABLE_1 WITH (NOLOCK) ON TABLE_1_FK_MAINTABLE_ID = MAINTABLE_ID
LEFT JOIN
    TABLE_2 WITH (NOLOCK) ON TABLE_2_FK_MAINTABLE_ID = MAINTABLE_ID
[...]
LEFT JOIN
    TABLE_N WITH (NOLOCK) ON TABLE_N_FK_MAINTABLE_ID = MAINTABLE_ID
WHERE
    TABLE_1_ID IS NULL
    AND TABLE_2_ID IS NULL
    [...]
    AND TABLE_N_ID IS NULL

N is about 50. The table names are aliases to four part named tables. This query is pretty fast from SQL Server 2008 to SQL Server 2012. But in SQL Server 2014 (same database) it never ends. The query has been tested with different hardware configurations (these work with the others editions of SQL Server).

What's wrong with it?

Thank you.


texts:
I have just downloaded the latest Community Installer (5.5.27) along with the MySQL WorkBench (5.2.41). I clicked OK on all default settings on setup.

When I try to "Open a connection to start querying" on the WorkBench home page and chose "Local Instance MySql 55", I keep getting stuck on a never-ending "Opening SQL Editor" screen ("An SQL Editor instance for "Local Instance MySql 55" is opening and should be available in a moment")

Any idea on what could be wrong ?

EDIT :
I am able to access it from command line and query it successfully. But still not from query editor.


texts:
I'm a non-superuser on a PostgreSQL 9.1 database, and I've just created a bad trigger function that won't stop. I can see the process, e.g.:

thedb=> SELECT * FROM pg_stat_activity WHERE procpid = 45678;
-[ RECORD 1 ]----+-------------------------------------------------------------------
datid            | 596281
datname          | thedb
procpid          | 45678
usesysid         | 596282
usename          | myuser
application_name | QGIS
client_addr      | 1.2.3.4
client_hostname  |
client_port      | 12345
backend_start    | 2015-04-16 13:45:27.482691+12
xact_start       | 2015-04-16 14:17:34.633156+12
query_start      | 2015-04-16 14:17:34.633665+12
waiting          | f
current_query    | UPDATE ...

But I can't stop it:

thedb=> SELECT pg_terminate_backend(45678);
ERROR:  must be superuser to signal other server processes

Is there anything that I can do?


texts:
I have been trying to setup a local instance for a new application.  I am new to MYSQL and I cannot understand how to actually connect this database to the actual web application sitting in the /var/www folder in my computer.

I have created a  database in command line terminal:

mysql> create database mydatabase
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'host'@'website.local' identified by 'hmypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'host'@'%' identified by 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on database.* to 'host'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

mysql -u host -h 127.0.0.1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)

How to actually connect this database to the website? I also don't understand how to make a schema for this database.  Whenever I try to refresh my web browser I get the following message:

Could not access database!

SQLSTATE[HY000] [2002] Connection refused


texts: comparing ip and subnet (ipv4 and ipv6) - mysql

texts:
How can I check if an ipaddress is part of a subnet, given ipaddress can be ipv4 or ipv6?

I currently have it working for ipv4 only:

set @ip = '1.1.1.1';
set @subnet = '1.1.1.0/32';
set @xor_bw_ip_n_subnet = inet_aton(@ip) ^ inet_aton(substring_index(@subnet, '/', 1));
select ((@xor_bw_ip_n_subnet) >> 32-substring_index(@subnet,'/',-1)) = 0;   -- if ip in subnet, then should be 0

For ipv6, how can I use inet6_ntoa/inet6_aton to check if IPaddress is part of a subnet (basically trying to do bitwise operation on ipv6 address)?

I am using mysql v5.6.19

Thanks,


texts:
Does it make sense to back up SQL Server databases locally in the cloud, particularly AWS?

"SQL Server Timeouts During Backups and CHECKDB" is an article on brentozar.com which points out the fact that virtual servers in the cloud use a single path to access both storage and network. That would mean bandwidth contention between storage reads (like from a backup process) and network access (Availability group network traffic) can cause query timeouts, availability group failovers etc.

A recent assignment saw me troubleshooting a backup process in AWS which involves all Diff/Tlog/Full backups for ~700GB of data being done locally, then being copied off to a domain controller via a Powershell script in Task Scheduler, every minute. It occurs to me that this involves dragging the backups across the same path to the network/storage twice.

Therefore running all backups to a UNC path seems like a no brainer in AWS EC2. Is that correct?


texts: About schema design of NoSQL DB.(How to query multiple data?)

texts:
I just got a requirement, to build a web only with Redis.

Data of the web is something like this(I write as RDBMS):
User Info:
user_id,first_name,last_name,age,.....
Address:
address_id,city,state,country,....

There is no problem at all if only write one user or get one user with "user_id".

But, what should I do if want to get all user younger than 20, or all user live in New York?

I can save all user_id under a key 'New York', but that's ugly isn't it.
If want to add a new condition, that need scan all data to build the index.

And if the condition become, live in 'New York' and younger than 20, well....

Is there have some amazing way to do that with Redis?


texts: Match string pattern to any array element

texts:
I'm trying to match a string pattern to any string in an array:

SELECT 'abc' LIKE ANY('{"abc","def"}') -- TRUE

-- BUT

SELECT 'ab%' LIKE ANY('{"abc","def"}') -- FALSE, I expect TRUE

What is wrong with the second query?


texts: Usage of temporary tables in my tempdb database on sql server 2005

texts:
I am trying to resolve the disk space issue on one of my drives where tempdb residing occupies more than 100 GB: Stats shown below

I read some similar post on the same and i found few queries using the tempdb.

So i am just curious if there is a way i can identify the temporary tables and their usage:

To start, found a query from Olaf which listed the temp tables and the size allocated.

But the above result keeps on changing as temp tables may add and get removed. So i am wondering if there is a way i can track such temp tables and their usage via which i can help to keep the tempdb growth stable.


texts: Azure SQL Server export via sqlpackage produces Error SQL71501 (Unresolved reference to object MS SyncAccount

texts:
I have an Azure SQL Server database with a Geo-replication on Azure, which I would like to regularly backup via sqlpackage.exe (s. command below) using the replicated read-only database.
However, when executing it, it runs into an error (SQL71501) - the error occurs both on the primary as on the secondary database.
As this is a production database with thousands of users, is there any way to fix that in an "open heart surgery", i. e. without taking down the service, copying the database, fixing stuff then deploying that as the life database?
The command I run:
sqlpackage.exe" /a:Export /scs:"Data Source=tcp:xxx.database.windows.net;Authentication=Active Directory Password;Initial Catalog=xxx_Copy;UID=xxx;PWD=xxx"
               /tf:C:\Databases\xxx-$(((get-date).ToUniversalTime()).ToString("yyyy-MM-dd-T-HH_mm_ss")).bacpac

The result I get:
Validating schema

Error exporting database:One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71501: Error validating element [dss]: Schema: [dss] has an unresolved reference to object [##MS_SyncAccount##].
Error SQL71501: Error validating element [TaskHosting]: Schema: [TaskHosting] has an unresolved reference to object [##MS_SyncAccount##].


texts: Report transition from Access to Crystal (multiple queries)

texts:
I have just started a new job where I am responsible for building and improving reports.

There is one report I am trying to move from Access to Crystal.  The data is stored in an application called Acctivate.  The previous person who held my job built the report with a series of queries that built multiple tables, and then a final query which aggregated the information from the tables built by the first five queries.

The PTB want the report in Crystal so the management for each client can call up their info and run the report, rather than have to call me and have me do it.

What is the best approach to this multi-level query (currently make-table) that will let me move the report out of Access?


texts:
Due to running out of disk space, I wanted to transfer 1 SSAS database (cube) to another disk that I connected.
In MS SQL, I am trying to detach and attach a SSAS database that I have just moved to a different drive following using the Move an Analysis Services Database documentation.
when I attach the database I get the following error:

TITLE: Microsoft SQL Server Management Studio
File System Error: The following error occurred while opening the file „\?\J:\MSAS13.VBIDEV\OLAP\Data\Warehouse.0.db\Warehouse.detach_log”: Invalid procedure call or argument.
(Microsoft.AnalysisServices.AppLocal.Core)

But if I check box "Read-only", the database connects without any problem error. But I need full permissions (write too).
I did this procedure the other way around, so I moved from the new disk back to the old disk (where this database was before) and made an attach there. It passed without a problem.
Is there any solution for my problem? Thanks :)


texts: Implementing data warehouse on the cloud

texts:
I need to implement a data warehouse on the cloud for a mid-sized company. The database size will be around 200 Gb.

My options are (so far):

AWS with its database Redshift
Azure with its database Synapse (formerly SQL Data Warehouse).
SAP HANA, a columnar database that works on both AWS and Azure.

Does anyone have experience with these and can share? I'm more interested in performance rather than price. Am I missing other feasible options?


texts: Should I have a parent table with only one field?

texts:
My database has multiple tables that all rely on dates. I was wondering if it would be smarter for me to take the date attribute and throw it in a parent table, rather than give each table a date field. Here are two case examples (one with parent table, and one without):

Tables with parent table:

Schedule
--
PK ScheduleId : int
--
Date : date (unique)

Delivery
--
PK DeliveryId : int
--
Name : nvarchar(100)
Time : time(7)
--
FK ScheduleId : int

Exception
--
PK ExceptionId : int
--
Name : nvarchar(100)
--
FK ScheduleId : int

GenericEvent
--
PK GenericEventId : int
--
Name : nvarchar(100)
--
FK ScheduleId : int

Tables without parent table:

Delivery
--
PK DeliveryId : int
--
Name : nvarchar(100)
Date : date
Time : time(7)

Exception
--
PK ExceptionId : int
--
Name : nvarchar(100)
Date : date

GenericEvent
--
PK GenericEventId : int
--
Name : nvarchar(100)
Date : date

In the first case illustrated, everything references a unique Date entry. I'm split because I'm not sure if it would be better to search by the indexed foreign keys to get all the events (deliveries, exceptions, and generic events), or if it would be faster/ better practice to botch the Date parent table, and just search all the individual tables by a date field. The data model for the second case would look as follows:

The other bonus I would achieve from doing the first practice is that since I'm using entity framework, the object relationships would already be in place, and I could search for a date, and automatically receive all related deliveries, exceptions, and genericevents.

Thank you for your input, it's appreciated more than you know!


texts: What reasons are there to stop the SQL Server?

texts:
All I've read about is how potentially damaging it is to stop SQL Server because it creates a cold cache and sucks up memory. So why would someone want to stop the SQL Server? If you can provide any links to articles so that I can read more into this I would really appreciate it!

This question was posed by my teacher. Unless it's some trick question, it has me absolutely stumped. His exact question was:

  Conduct research using the Internet and learn why someone would want to stop the SQL Server. Explain your answer.

This was in the context of us exploring how to use SQL Server 2008 R2. I'm not sure if he is asking for the obvious answer, or if there is something I'm missing.


texts: Any way to reduce the logging level of SQL Audit?

texts: I set up SQL Audit according to STIG compliance using exactly the recommendations given by the compliance documents. The problem is that after doing so my server is generating roughly 5GB of Audit logs an hour.  I looked around but I see no way to reduce the logging level of the audit logs or do anything that will enable me to not take up so much space. I have to enable this type of logging across 11 servers and I can't justify that amount of space usage for this. Is there a setting I'm missing? Is this amount of space consumption normal even?


texts: Transactional replication from SQL Server 2000 to 2008

texts:
I have legacy databases on two SQL Server 2000 servers that I am trying to replicate to a SQL Server 2008 R2 database using transactional replication.

The 2008 server is not in the same LAN, so I am replicating across the internet. I created aliases so that each server can connect to the others by name (required for replication) more or less as described by MohammedU (and others) here: http://social.msdn.microsoft.com/forums/en-US/sqlreplication/thread/9a8cf6b1-a449-4748-b3c2-1c13e2bfcc5b/

The only difference is that I used Client Network Utility rather than Configuration Manager to create aliases on the SS2000 machines. This seems to work fine.

I set up one SS2000 server, call it Distributor, to be the distributor for both. I have successfully created publications on Distributor and replicated them using push subscriptions to the SS 2008 server, call it Subscriber.

I have now set up publications on the second SS2000 server, call it Publisher. I created push subscriptions for them in the same manner. These subscriptions are not working.

In the Replication Monitor on Distributor, the Distribution Agents for publications on Publisher have the following error messages:

Error message: The process could not connect to Subscriber 'SUBSCRIBER'
Error details: Login failed for user 'SUBSCRIBER\Guest.'
(Source: SUBSCRIBER (Data source); Error number: 18456)

I also tried creating pull subscriptions on Subscriber. The wizard said they were created succesfully, and the corresponding Distribution Agents showed up correctly in the Replication Monitor on Distributor, but the replication never happened. The Agents kept displaying a message that the snapshot was not available, even though it was.

Can anyone help?