texts: MySQL collate error occurs when sent by PHP app but does not occur in phpMyAdmin

texts:
I have a query that uses:

t3.`value` LIKE "%someval%" COLLATE utf8_general_ci

I load the page in my browser and get this error printed to the screen:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

The query is also echoed to the page. And if I copy and paste the outputted query into a phpMyAdmin SQL window, it runs fine, producing the expected result.

I had a previous problem where PHP could not use LOAD DATA INFILE. I wonder if this is a similar type of problem. But I can't see how it would be.

This is running on an Ubuntu Server 10.04 MySQL Apache PHP stack.


texts: Equivalent to PostgreSQL's Dollar-quoted String Constants for SQL Server

texts:
Is there an equivalent of PostgreSQL's Dollar-quoted String Constants, on SQL Server?

I would like to enter HTML string literals that would potentially contain single or double quotes in them.

Example:

UPDATE table_name
SET    column_name = $$
  Here's a string that contains "double quotes".
$$
WHERE  condition = true

Edit:
The reason I'm asking this is because I would like to update some rather big HTML "web parts" that are stored in the database (yuck!). Because at work we use that kind of CMS (which I won't give you the name). So I don't want to have to escape my single quotes every time. That's why I'm asking about that kind of feature. If I understand correctly, QUOTENAME would still require me to double my single quotes.


texts: Same script produces 'Specified key was too long' on one server and not on another

texts:
Senario: I have a CMS with a database .sql file.

When I try to load the .sql file on my local phpMyAdmin, its successful, it loads all the tables into phpMyAdmin.
When I try to load the same file on Real CMS host(Network Solutions) phpMyAdmin, it is not loading the tables.

The error I get on the Real CMS Server is:

  Specified key was too long; max key length is 767 bytes

What could cause the same script to produce this error on one server and not on the other?


texts: Insert from Another Table + Removing Duplicate Entries in Postgres

texts:
I'm trying to insert data from one PostgreSQL table to another while removing duplicate entries but I'm having some trouble with the code.

I have two tables I'm working with: mytesttable, which contains test entries and entries, which is my main entries. The entries table has a primary key of (username, date, time, x0, x1, x2).

Code that I'm trying to implement from this post: Optimal way to ignore duplicate inserts?

BEGIN;
INSERT INTO entries (username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT username, date, time, x0, x1, x2, x3, x4, x5, x6
FROM mytesttable;
EXCEPTION
WHEN unique_violation
THEN
/* ignore */;
END;

And my error message:

ERROR:  duplicate key value violates unique constraint "log_pkey"
DETAIL:  Key (username, date, "time", x0, x1, x2)=(duplicate key information) already exists.
ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION

UPDATE

I tried to implement one of the suggestions from below with no luck. The documentation for BEGIN says that you need a semicolon and I'd get a syntax error on the INSERT without it, so I kept it.

BEGIN;
INSERT INTO log (username, date, time, x0, x1...)
SELECT username, date, time, x0, x1, x2...
FROM stevietable;
EXCEPTION
WHEN unique_violation
    THEN RAISE NOTICE 'I am ignoring a unique violation';
END;

And the error:

psql:/tmp/test.sql:4: ERROR:  duplicate key value violates unique constraint "log_pkey"
DETAIL:  Key (duplicate key information) already exists.
psql:/tmp/test.sql:7: ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION
        ^


texts: mysql < dump.sql or source dump.sql consume all system memory

texts:
I am trying to restore a large dump (~300GB MyISAM) into a Mysql server version 5.6.48, but if using:

mysql < file.sql

or

mysql> source file.sql

In both cases, after starting the process all memory RAM/SWAP gets used, looks like the memory is not released, or is trying to read all input into memory.

Any idea of how to import/restore the file without consuming all system memory?

What currently I am doing as an ugly solution, is to import by chunks but restarting the server to free memory after every import, something like:

for t in chunks_*
mysql db_name < $t && service mysql-server restart && sleep 2


texts: How can I find who has allocated a user lock via dbms_lock?

texts:
We have a print queue process that acquires a user lock via dbms_lock.allocate_unique. It performs the work and then releases the lock. The process works very well.

Today, we have a process that allocated a lock, as seen in this output from dbms_lock_allocated:

NAME            LOCKID      EXPIRATION
printer_lock    1073741825  11/30/2013 9:35:46 AM

Whatever session has this hasn't released the lock. Is there any way we can find the session that allocated this lock, so we can end their session and release the lock?

EDIT:

Querying DBA_LOCKS does not show any sessions with the lock type of 'PL/SQL User Lock'.

Thanks!


texts: SQL server disk write queue dramatically grows sometimes

texts:
I have SQL Server 2008 and a single database with full recovery model. Usually, the queue length is less than 1. But sometimes it grows up to several thousands (3000!!) for a few seconds. At this time many of write queries ends up with timeout error.

Using Resource Monitor, I found that at this moment sqlserver.exe writes a large amount of data to the main database file (MDF). Thoughh usually it writes to transaction log (LDF).

Using SQL Server Profiler, I found that heavy queries are not running at that moment.

I think, that it is some kind of SQL server's background operation, but I wonder what kind?

Database also has READ_COMMITED_SNAPSHOT ON and the mirroring (synchronous mode) enabled. Can this fact be the cause of my issue?

UPDATE:
I found that writing to log (not to data file) is default behavior of Full recovery mode. And log can only be copied to data file by Backup Transaction Log operation. Still don't understand why SQL server copying log every ten minutes...


texts: Create table using select statement Truncated incorrect datetime value:

texts:
I have looked up different answers on similar question before posting it here.

So i have a table with user id and date column

I would like to store the results of select query to a temporary table.
On using the below statement:

Create Temporary table dummy_table (
select User_Id , DATE_FORMAT(Date,'%M %Y')
from result_table.user_signup_date );

throws error
Error Code: 1292. Truncated incorrect datetime value: '2019-06-16 00:00:00 UTC'

I noticed the error occurs only when i format date column. I tried converting date to string. I also tried with str_to_date(DATE_FORMAT(Date,'%M %Y') ,'%M %Y %H:%i:%s') and few more syntaxes but with no result.

I am new to Sql. Any help would be appreciated. Thanks in advance


texts: We had a oracle database instance on a server A. We created a replica of server A named B. Where all the files, directory structure and  environment variables were replicated on server B. I wanted to ask if I can directly start the database on the replica server or I need to do some pre-requistes to start the database on the replica server B.


texts:
I have like 20 Listners on 2014 with approx 500 DBS,

what is the best way to migrate these with least downtime, effort

My idea is:
  Stop access
  Backup dbs
  Start restore dbs
  While restore is running:
  Drop AOG on 2014
  Create it on 2016
  Done

This seems simple, but with TB data this will take some time.

Have found this ->
Recommended ways to upgrade AlwaysOn AG from 2014 to 2016
But it niether does explain

Hope someone can help


texts: SQL Server placement of Files SAS SSD vs SAS HDD

texts:
I've got a new server which will host MS SQL Server Virtual Machine along with Other VMs on HyperV. SQL Server will account for most(70%) of the Load.
The server has below Storage:
a. 2 x 240GB Read Optimised SAS SSD (Intel S4610) configured in RAID1 for Host OS
b. 2 x 480GB Mixed Use SAS SSD (KIOXIA PM5-V)configured in RAID1 - For DB Files
c. 4 x 1.2TB 12Gbps SAS 10KRPM HDDs configured in RAID5(no hotspare) - For DB Files + Other VMs
The SSDs are over-provisioned by leaving 10% space unallocated on both RAID volumes, yielding 200Gb and 400Gb usable space respectively.
Based on my research, below is the data I have:

Data files (mdf) are generally read-intensive and can benefit from SSD.
Log files (ldf) generate Sequential write IO. They don't benefit too much from SSD and can negatively affect SSD Endurance.
TempDB is Read/Write intensive - It can cause SSD burnout. Can be slower on HDDs.

I was planning to put Data files on SSD and put Log Files & TempDB on SAS HDDs considering that tempdb can cause premature SSD failure. Does this still hold good in 2020? Am I being overcautious about this?
Any suggestions on how to configure storage?
Below are virtual file stats obtained from current(old) server by running query mentioned here:

The old server has D:(data) & E:(log) drives running on separate
RAID5 and RAID1 Arrays respectively.
The server was rebooted at
yesterday midnight and these stats represent usage at 3pm (15hrs of
usage).
SQL Server Version: Microsoft SQL Server 2012 - 11.0.5343.0
(X64)


texts: How to get lsn(xid) numbers of transactions from WAL file?

texts:
I'm looking for a way of getting transactions from WAL files.
Basically I need their LSN numbers.
(ideally both transactions LSN numbers and WAL files first and last LSN numbers)
I have a PostgreSQL cluster binary backup and set of WAL files.
I would like to perform PITR but first I need to find the point where I need to restore to.
So I need to find a particular transaction LSN or XID number which later I'm goin to use as recovery_target.
Could you please give an idea how to get LSN numbers or XID numbers from WAL file ?


texts: Identify column name information from table schema

texts:
I have multiple tables with with varying names for the same piece of data. For example, day vs business_day. I'd like to identify what column names exist for which table. I think this can be done using schema information, but I'm not familiar with it.
For simplicity, the tables below are 3 separate tables with these column names.
table_1
day    city  weather

table_2
business_day location  status

table_3
day   city  rain

Where day and business_day and city and location are the same piece of information, but unfortunately different naming conventions so I wouldn't be able to use the same selection criteria in a WHERE clause. I'd like to search the table information to see which tables have day or business_day and city or location. The other columns are not important to me in this example. How can I identify table information in this way?


texts: Help With MySQL Computed Columns/Auto Updating Field Based On Average

texts:
I am currently building a social network type application on-top of MySQL and have run into a problem I can't wrap my head around. In my web application each user has a field called "weight" and this weight is calculated by a few different factors.

The number of connections a user has
The number of connections a users friend has

Essentially a users weight is affected by their network and the weight of each of their friends. I need to average out the weight score of a users network and then use that to update a users self weight score. I would like it to be a living thing where the weight can change at any time if the network average changes for a user (but performance wise this might not be a good idea).

I was thinking once or twice per day maybe via a scheduled trigger, a users weighting score could be calculated. My question is, how can I best implement something like this keeping in mind if I have 100,000 users and or millions in future, the performance of updating that many rows. Am I thinking about this all wrong?

Thank you in advance.


texts:
The nested set structure is well-known and involves using left and right values to denote the relative heirarchy of nodes on one axis.

Is there a generalization of this structure to higher dimensions?  For example, a 2-dimensional case could be used for describing nested bounding boxes for the elements of an image.  The bounding box for a car contains bounding boxes for the wheels, the spokes of the wheels, and so on.

Is it reasonable to simply extend the basic form by adding top and bottom columns and checking for nesting with similar comparisons?  Does this work for even higher dimensions?  Any non-obvious caveats to this approach?

Edit: perhaps I should clarify, I'm aiming for RDBMS/SQL approaches here.


texts:
So I have a stored procedure that calls an AS400 Linked server. Now, the stored procedure will execute successfully. However, it is used in a SQL Agent Job and each time the job runs, it gives the Linked Server has Run out of Memory exception.

Why does the fact that it is running in a job environment make it differ from running as the raw SQL code? And how would I go about solving/fixing this?


texts: select a value from different tables depending on a condition

texts:
I am working on an inventory system. We have stored transactions in a transactions table which stores (challanid, challandate, toid, fromid, toenittyid). Toentity can be a branch, distributor, dealer or stockist which are stored in different tables. I need to build a query for transactions over a period of time which shows all transactions. For this I need to show entityname which needs to be withdrawn by toid from respecitve dealer/distributor or stockist tables depending on toenitytid.

For example, if toentityid is 2, we need to get branchname from branch table, if toentityid is 3 we need to get stockistname from stockist table by toid and so on. I tried switch case, but that does not work to change the table name. I need help to complete this query. Thanks a lot for your help.


texts: Delete statement not responding to Kill session command

texts:
Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size.

delete from [table-name]

This table does have a foreign key constraint which is not enabled for checking and I know that no rows exist in the other table.

The database is running with "Is Read Committed Snapshot On" enabled and in Simple recovery mode.

After this had run for a few hours I issued a kill session command, because we were running out of disk space for the log file. I added another log file to allow the system to continue to function.

The log file is continuing to grow and when I run kill session with statusonly it returns this message:

SPID 123: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

I am at a loss as to what to do about this query to get it to rollback and also just understanding what is going on, can anyone suggest what I can look at?


texts:
I have created chat application which ping server every sec for messages. Currently I have done load test with httperf, with 500 request/sec.
 "Connection timed out & MySQL server has gone away with innodb engine".

my.cnf has following configuration
query_cache_type=1
key_buffer_size=384M
query_cache_size=64M
back_log=500
join_buffer_size=1M
query_prealloc_size=150K
sort_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=4M
key_cache_age_threshold=3000
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size=8G
innodb_log_file_size=200M
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
max_connections=2000

Following are the timeout variable,
+---------------------------------+-------------------+
| Variable_name                  | Value             |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time     | 0                 |
| connect_timeout                 | 10                |
| datetime_format                 | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout          | 300               |
| explicit_defaults_for_timestamp | OFF               |
| flush_time                      | 0                 |
| innodb_flush_log_at_timeout     | 1                 |
| innodb_lock_wait_timeout        | 50                |
| innodb_old_blocks_time          | 1000              |
| innodb_rollback_on_timeout      | OFF               |
| interactive_timeout             | 28800             |
| lc_time_names                   | en_US             |
| lock_wait_timeout               | 31536000          |
| long_query_time                 | 10.000000         |
| net_read_timeout                | 30                |
| net_write_timeout               | 60                |
| rpl_stop_slave_timeout          | 31536000          |
| slave_net_timeout               | 3600              |
| slow_launch_time                | 2                 |
| system_time_zone                | UTC               |
| time_format                     | %H:%i:%s          |
| time_zone                       | SYSTEM            |
| timed_mutexes                   | OFF               |
| wait_timeout                    | 28800             |
+---------------------------------+-------------------+


texts: Write to table with transaction without locking

texts:
Is it possible to write to a database table using a transaction without locking it?

I want to run an update query on single field in a single record, but just in case there is an error in my application I want to be able to roll it back. The application could take a second or two to complete the entire process, so I'd like to prevent it from locking the table, as it's a heavily read table. From the application's perspective the field is insignificant, so I don't care if there are dirty reads on it.

(I'm aware of NOLOCK and READPAST for SELECT statments, but I'm not looking for that as a solution.)

Any help is appreciated.


texts: MariaDB doesn't read all options from /etc/mysql/conf.d/ after upgrade

texts:
I am using Ubuntu 20.04LTS. I upgraded Mariadb 10.5.5 to 10.5.8 using mariadb's own repositories. After upgrade I noticed API server was unable to connect to DB server through private network. After some tinkering, I realized mariadb is no longer reading bind-address from /etc/mysql/conf.d/customizations.cnf and was listening to 127.0.0.1 instead of 0.0.0.0. Then I proceeded to adding bind-address to /etc/mysql/my.cnf and then it worked again.
/etc/mysql/mariadb.cnf is the config used. It is symlinked to /etc/mysql/my.cnf. Which contains line !includedir /etc/mysql/conf.d/
All the other configurations such as innodb_buffer_pool_size are still read from file: /etc/mysql/conf.d/customizations.cnf, but bind-address is not. Why is that? And yes its under [mysqld].
My /etc/mysql/conf.d/customizations.cnf:
[mysqld]
bind-address = 0.0.0.0
skip_name_resolve=1
innodb_buffer_pool_size=20G
max_connections=550

[mariadb]
log_error=/var/log/mysql/mariadb-errors.log


texts:
I am trying to normalize a few tables. Here is one of my tables

 SalesID|Order1Name|Order1Date|Order1Amt|Order2Name|Order2Date|Order2Amt|Order3Name......Order10xxxx
 ---------------------------------------------------------------------------
1001     | first   | 1/1/18   | 111.00  |  second  | 2/1/18   | 222.00

I want to get it to

SalesID |OrderNum  |  OrderName  |  OrderDate  |  OrderAmt|
------------------------------------------------------------
 1001      1          first          1/1/18         111.00
 1001      2          second         2/1/18         222.00
 1001      .
 1001      .
 1001      10

I have been trying to run with unpivot - how do make sure I have the ordinality (column2) info?


texts:
question - I'm working on a project that involves heavy computation and will continually grow in terms of the database it works with. I want to build a small cluster (NUCs I'm thinking) the run a private cloud to run this off of. The code really will only need the CPUs for calculations, and the database will need the disk space so this architecture seems like a good way to get the most out of the hardware and easily add additional nodes as needed (as the database grows and/or more models require more computation).

I'm confident on my coding end for this and I've basically already built the database structure in Postgres - what I'm struggling with is whether or not I can scale this database out horizontally or not. I don't need full replication (though partial would be nice so one failed node doesn't result in data not being available), I just really want to be able to scale out the size of this thing and easily add hardware and/or more VMs as needed. I've seen stuff like pg-pool and pg_shard - not sure if that's what I need - just want to be able to take my existing database structure and spin up additional servers with additional disk space to house the additional data as needed. Any ideas?


texts: Putting some calculations on a database entry from phpmyadmin

texts:
I am creating an online store website for sale/purchase. I have to put a specific percentage of an amount (User enters) in database.

for example
If user enters 100. I have to deduct 30% of it and I want to put 70 in database. Is there a way I can perform this check from phpmyadmin ?. I know I can do it from code but can I do it from phpmyadmin ?

Thanks for help.


texts: In MySQL 5.5: can I capture the output of SHOW ERRORS?

texts: Is it possible to capture the messages that can be listed with SHOW ERRORS and store them in a log table? Like, is there a table somewhere, that I can select from?


texts: What am I doing wrong with the Database Tuning Advisor?

texts:
My background: I'm a dev/architect, not a DBA. Sorry!

So we have a ~400 table 75GB database. I ran Profiler for ~24 hours ("Tuning" template minus LoginName) and have ~7GB of usage recorded in a trace file. I then ran the Database Engine Tuning Advisor on this trace file (no partitioning, keep clustered indexes, PDS Recommend: Indexes) against our production database (after hours). I gave it ~4 hours to analyze. And here are the summary results I got:

The things that stood out here to me that seem "wrong" are:

It only tuned ~9k out of ~530k queries
It recommended I drop a ton of indexes (in fact, most of them)
It recommended I create 0 indexes

Does this sound right to you guys? I expected it to drop most of my indexes but then to create a ton of new indexes. Also, if it takes 4 hours to analyze 9k queries, is it even feasible for me to get this to consider a normal day's worth of usage? Compared to most large databases, ours is fairly light on consumption (~50 users total).

I think I'm either misunderstanding something or am simply doing something wrong.


texts: How to make a union view execute more efficiently?

texts:
I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process every night.

In several places in our code we need to run queries that combine the active and archive tables, almost invariably filtered by one or more fields (which we've obviously put indexes on in both tables).  For convenience it would make sense to have a view like this:

create view vMyTable_Combined as
select * from MyTable_Active
union all
select * from MyTable_Archive

But if I run a query like

select * from vMyTable_Combined where IndexedField = @val

it's going to do the union on everything from Active and Store before filtering by @val, which is going to kill performance.

Is there any clever way of making the two sub-queries of the union view each filter by @val before they create the union?

Or maybe there's some other approach you would suggest that achieves what I'm going for, i.e. an easy and efficient way of getting the union record set, filtered by the indexed field?

EDIT: here's the execution plan (and you get to see the real table names here):

Oddly enough, the active table is actually using the correct index (plus a RID lookup?) but the archive table is doing a table scan!


texts: How to query from the result of a changed column of a table in postgresql

texts:
I am working with Presto on Amazon Athena. I have a string time column in a table and now I want to change that time to date time type and then query data for selected dates.

Is there a direct way to do so? One way I could think of is

1) add a new column
2) insert values into it with converted date
3) Query using the new column

Here I am stuck with the 2nd step with INSERT so need help with that

  ALTER TABLE "db"."table"
    ADD COLUMN new_date timestamp

-- Need correction in select statement that I don't understand
    INSERT INTO "db"."table" (new_date)
    (SELECT new_date from_iso8601_date(substr(timestamp,1,10))

Could some one help me with correction and if possible a better way of doing it?


texts: Symantec Backup Exec runing DBCC CheckDB and Backup on my SQL server

texts:
What is Symantec Backup Exec (TM)?
I noticed that it is running in one the database server i am maintaining.
I am a newbie in db administration and i am not sure what it is.
It is running DBCC CheckDB on different databases and running Backup on different databases.
Can anybody give some info on this?Is this something windows server team does to protect data?
I did some research and all i can see is information that says it is some kind of data protection program.


texts:
Is it possible to use/log in to a different account in pl/sql. For example, I would like to write to a file in a folder only a specific account has access to. Here's an example of what I mean:

Other code...
LOG_IN_OTHER_USER('SPECIALUSER', 'SECRETPASSWORD');
UTL_FILE.PUT_LINE(OutputFile,the_outfile);
UTL_FILE.FFLUSH(OutputFile);
LOG_OFF_OTHER_USER('SPECIALUSER');
Other code...


texts: Retrieve result set with proportion of some column value

texts:
I have a table called PORTFOLIOS in which I have PORTFOLIO_ID, PORTFOLIO_NAME and SCORING_RULE columns. The SCORING_RULE column can have two different values (like double_scoring or single_scoring).

Now the requirement is I have to see what is the percentage of each scoring rule by combining all the records.

when I select the bunch of records from the table by limiting it with ROWNUM condition the result set should reflect the those percentages.

For example in the above table I have total 20 records in which 40% of records have double_scoring and 60% records have single_scoring as SCORING_RULE. So when I select first 10 records with below query then the result set must contain 4 double_scoring and 6 single_scoring records. How to ensure this kind result set. The database is oracle 10g.

SELECT * FROM PORTFOLIOS WHERE ROWNUM <=10


texts:
I have a large table (around 100 million rows). I am trying to optimize searches.  Most of my searches use two columns: bar nvarchar(64) and foo nvarchar(64).
I am only interested in foo when:
foo = 'string01'
OR
foo = 'string02'
OR
foo IS NULL

I was thinking on creating the following non clustered index:
CREATE INDEX ixbar
ON myTable (bar)
INCLUDE (foo)
WHERE foo IN ('string01', 'string02') OR foo IS NULL

But the OR statement is not allowed in a WHERE clause for an index. This makes me think that probably I am not approaching this correctly.
I would appreciate any suggestion on the most efficient way of doing this.
Thank you!


texts: SQL Query to select rows based on specific condition

texts:
Below is an example of childs daily activity. I want to select the next rows after the last 'Play' activity based on user id.

id          timestamp               activity
567     1541300537817000            Play
567     1541300584959000            Games
567     1541300711884001            Play
567     1541300749548000            Study
567     1541300804865000            Movie
678     1542073550481000            Computer
678     1542073551422000            Play
678     1542073551478000            Play
678     1542073561885000            Play
678     1542073567663000            Sleep
908     1543580126943000            Tv
908     1543580212409000            Sleep
908     1543580245227000            Play

Expected output

id          timestamp               activity
567     1541300749548000            Study
567     1541300804865000            Movie
678     1542073567663000            Sleep

I tried doing this:

SELECT DISTINCT t1.*
FROM `table` t1, `table` t2
WHERE t1.id = t2.id
  AND t1.timestamp > t2.timestamp
  AND t2.activity = 'Play';

But it is giving me events after 1st Play activity.


texts: Create a unique constraint on a column based on distinct value from another column in the same table

texts:
I want to make a column unique, but it has to be unique based off a distinct value from another column in the table.

Consider the following table:

CREATE TABLE locations(
  locationId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  locationName VARCHAR(50) NOT NULL,
  companyId INT NOT NULL FOREIGN KEY REFERENCES companies (companyId)
);

My goal is to have location name be unique for each value in the companyId column.

For example, given the following table:

    [locationId]   [locationName]   [companyId]
=====================================================================
    1              'Example A.'     1
    2              'Example B.'     1
    3              'Example A.'     2
    4              'Example C.'     2

If I attempted to execute the following query:

INSERT INTO locations (locationName, companyId)
VALUES ('Example B.', 2)

This would not give me a unique constraint error because no row in the locations table has a locationName of 'Example B.'and a companyId of 2

However, the executing a query like this should produce a unique constraint error:

INSERT INTO locations (locationName, companyId)
VALUES ('Example C.', 2)

because a row with locationName 'Example C.' and companyId 2 already exists.

I've tried the query

CREATE UNIQUE INDEX [UNQ_locationName_companyId]
ON dbo.[locations]([locationName])
WHERE ([companyId] IS DISTINCT);

however IS DISTINCT is not valid SQL syntax, but I can't think of the proper way to accomplish this.

Any help would be very appreciated!


texts: Does SSRS report user needs database level permission?

texts:
I have a separate reporting server and a separate database server.  Now on the Reporting server site settings I could see there are some roles and users.  Suppose a User BOB has ‘POWER USER’ role assigned and the user can create and deploy report.

Now the confusion lies here as I want to remove some database logins does BOB needs to have database level permission (example execute permission and database reader ) on the database and also on the report server database  or the permission assigned to the role on SSRS will be good enough.

Does SSRS report user needs database level permission or the login that is used on the data source will only need database level permission.


texts: Link Server Error - From SQL2008r2 to SQL2014

texts:
I'm getting the following error when I try to query a table using a link server.
The link server is created on SQL2008r2 instance to connect to an SQL2014 instance.

  The OLE DB provider "SQLNCLI10" for linked server "server\SQL2014"
  does not contain the table ""db_name"."dbo"."table_name"". The table
  either does not exist or the current user does not have permissions on
  that table.

Here is the TSQL i'm using to create the link server

EXECUTE sp_addlinkedserver @server = N'server\SQL2014', @srvproduct=N'sql_server', @provider=N'SQLNCLI10'

EXECUTE sp_addlinkedsrvlogin @rmtsrvname='server\SQL2014', @useself='false',@rmtuser='sa', @rmtpassword='sa_password'

When I test the connection that succeeds

The linked server login is sa so permissions can't be an issue.

sa user is enabled and table exists i can connect to and query all the tables directly on the SQL2014 instance using the sa user.

Where am I going wrong?


texts: Best way to quickly and effectively drop a database and delete all files?

texts: If I set a Microsoft SQL Server database to offline and drop it, it will be deleted, but the log and data files will remain. If I do it in online mode, there is a chance that another connection will stop me from dropping it. In a dev environment, or for a database that I want to periodically blow away and recreate on production, what's the best sequence of T-SQL commands to blow away a database, along with its data files and log files, that will work as long as I have sa privileges and no one is maliciously trying to stop me from dropping the database?


texts: How can I design Address table with different types?

texts:
I have a Address table and different address types like Personal and Corporate. I design a table but I think is breaking normalization rules. How can I improve this design?
My design:


texts: Point-In-Time Recovery of a single table having full physical backup in MySQL?

texts:
I need to do a point-in-time recovery, having a full physical backup and the bin-logs available.
One way is for sure by mean of an external instance where one can restore the full database and then dump the desired table.
Is there a way to perform this task, in place?


texts: how to dump via mysqldump hugh table into chunks

texts:
i have very hugh table , around 100 M records and 100 GB in a dump file , when i try to restore it it to a different DB  i get sql query lost connection , i want to try and dump this table into chunks (something like 10 chinks of 10 GB) where each chink will be in seperate table.
what i managed to optimized so far is this :

mysqldump --skip-triggers --compact --no-create-info --single-transaction --quick --max_allowed_packet 1G  -h {host} -u {user} -P 3306 -p{pwasword} {my_schema} {}> /mnt/datadir/{table_name}.sql

and now the output is that i have 1 file {table_name}.sql i na size of 100 GB i want to get 10 files in sizes of 10 GB each


texts: Optimising group by on self referential join with millions of rows

texts:
I have a table (actually materialized view) with millions of rows. Schema is something like:

id
correlation_id
name

I want to be able to determine, which ids appear together, and how many times. For example, given the rows:

id, correlation_id, name
1,       1,         'foo',
2,       1,         'bar',
3,       2,         'baz'
1,       2,         'foo'
2,       2,         'bar'

I want to get something like

id1, id2, count
1,    2,    2
1,    3,    1
2,    1,    2
2,    3,    1
3,    1,    1
3,    2,    1

To explain, foo is with bar across 2 different correlation ids. But is with baz in only one correlation id.

My naive implementation is this:

SELECT f1.id, f2.id, COUNT(f2.id)
FROM foo f1
INNER JOIN foo f2 ON f1.correlation_id = f2.correlation_id AND f1.id <> f2.id
GROUP BY 1, 2;

Problem is, it results in a very expensive sort. The explain query looks like this:

GroupAggregate  (cost=546688884.02..569757450.38 rows=272085025 width=38)
  Group Key: f1.id, f2.id
  ->  Sort  (cost=546688884.02..551775813.05 rows=2034771611 width=30)
        Sort Key: f1.id, f2.id
        ->  Merge Join  (cost=1.12..37356601.80 rows=2034771611 width=30)
              Merge Cond: (f1.correlation_id = f2.correlation_id)
              Join Filter: (f1.id <> f2.id)
              ->  Index Only Scan using foo_id_correlation_id on entities f1  (cost=0.56..823407.15 rows=20284706 width=31)
              ->  Materialize  (cost=0.56..874118.92 rows=20284706 width=31)
                    ->  Index Only Scan using foo_id_correlation_id on entities f2  (cost=0.56..823407.15 rows=20284706 width=31)

I've tried 3 different indexes (one you're seeing in the example above):

An index on id

An index on correlation_id

A sorted index on id and correlation_id (i.e. (id ASC, correlation_id ASC))

None of them really had any affect on the final cost, the sort always seems to be needed.

Slightly at a loss as to what is the best way to index my data to make this query inexpensive to run. Any help is appreciated.


texts: Supplying root password to MySQL V8 sort of fails

texts:
On Ubuntu 20.04 I have installed MySQL V8.0.25. But I kind of fail to apply a (valid) root password. The apt-get process for installing mysql-server did not ask for a root password.
And I cannot enter mysql with the "mysql -u roor -p" command, I always do with "sudo mysql".
So i first tried with the process described by this MySQL page. But this did not work at all, because V8 seems not to support the PASSWORD-function.
So instead of running
UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

I used this:
UPDATE mysql.user
   SET authentication_string = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('MyNewPass'))))), password_expired = 'N'
   WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

which I found described here.
The query
select host, user, authentication_string, password_expired from mysql.user;
shows a nice table:
   +-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | joomla           | $A$005$MF`6ea"OfH5v1kTuRW0zJS5MKk82btugdAz62uWe6QkxnrXtTLtx5M. | caching_sha2_password |
| localhost | debian-sys-maint | $A$005$l%.r}2CBQT:+DV)a9S/UJUDJoFA8PhnCIE.E3zDFbBeUZ5vTrNSZpZDDv05 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
s%nn69n9�NkFf7xoPdW/CCD/NjvLhTKXtx8gQmTX.RpIbOcHWsA. | caching_sha2_password |
| localhost | root             | mypass                                                                 | auth_stock            |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+

but still "mysql -u root -p" does not work with the suppiel password.
I still get message:
$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I also tried this - without any success:
UPDATE mysql.user
   SET authentication_string = 'mypass', password_expired = 'N', plugin = ''
   WHERE User = 'root' AND Host = 'localhost';

The ALTER cmd did not work
neither
ALTER USER 'root'@'localhost' IDENTIFIED BY 'oldpass';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

nor
ALTER USER 'root'@'localhost' IDENTIFIED BY 'oldpass' REPLACE 'mypass';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

I spent now many hours in this and have no clue how to preceed. Any help is appreciated.


texts: What's an example of an exploitation of the security from enabling Ole Automation Procedures Server Configuration option?

texts:
I have a need to enable to the Ole Automation Procedures Server Configuration Option on my SQL Server, but was wondering what specific kind of security risks do I incur by doing so?

What is an example of how the security can be exploited with this option enabled?


texts:
I'am trying to set up backup of Oracle DBMS.

Content init.ora file:

log_archive_dest_1='LOCATION=<location>';
log_archive_dest_state_1=ENABLE;
log_archive_format='%t_%s_%r.arc' scope=spfile

I executed the following commands:

alter database archivelog;
alter system archive log start;
alter system archive log current;
alter database create standby controlfile as '<location>';

Then, I copied the created log files (.arc), control file (.ctl) and data files (.dbf) to the replica.

Content init.ora on replica:

log_archive_dest_1='LOCATION=<location>'
log_archive_dest_state_1=ENABLE
log_archive_format=%t_%s_%r.arc
control_files='<location>'
STANDBY_FILE_MANAGEMENT=AUTO
compatible ='11.2.0'

I executed the following commands on the replica:

startup nomount
alter database mount standby database;
recover standby database;
alter database open;

But I get error:

ORA-10458: standby database requires recovery
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: filename.dbf'

Someone with such already faced, can you help?


texts: DB2 Query Error SQL0204N Even With The Schema Defined

texts:
I'm using Python to access tables in DB2 10.1.0

I have a login account named foobar and a schema with the same name. I have a table named users under the schema.

When I'm logged in as foobar, I can run the following query successfully from the command line:

select * from users

I have a small Python script that I'm using to connect to the database.  The script is:

#!/usr/bin/python

import pyodbc

if  __name__ == "__main__":

    accessString ="DRIVER={DB2};DATABASE=MYDATABASE;SERVER=localhost;UID=foobar; PWD=foobarish1;CURRENTSCHEMA=FOOBAR"
    print accessString
    cnxn = pyodbc.connect(accessString , autocommit=True)
    cursor = cnxn.cursor()
    query = "SELECT * FROM USERS"
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
        print 'Row data'
        print row[0]
    cursor.close()
    cnxn.close()

When I run the script, I get the following error:

  ('42S02', '[42S02] [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  "FOOBAR.USERS" is an undefined name.  SQLSTATE=42704\n (-204) (SQLExecDirectW)')

This usually means that the schema isn't defined.  However, if I change the query in the script to:

VALUES CURRENT SCHEMA

the script runs successfully and it returns

FOOBAR

I've also tried adding the schema directly to the table name, making the query

SELECT * FROM FOOBAR.USERS

and I still get the same error.

Does anyone know how to fix this so I can query the user table?  Your assistance and insight is appreciated.


texts: Is there a reason I should not set my default data location to google drive when setting up PostgreSQL?

texts:
I'm brand new to working with databases and am setting up a PostgreSQL database which will be used for personal projects. I'm setting it up with the postgis extension and will use both for some basic geospatial projects. I use the google drive back up and sync utility on my mac and use my google drive directory for everything. I'm not too worried about storage capacity on either my local disk or my google drive. When installing postgresql, I'm required to specify the install locations for both PostgreSQL and PostgreSQL data. Is there any reason I wouldn't want to set at least the data location on google drive? What about putting the whole install in that directory? Many thanks in advance

Don't know if it matters at all but I'm running mac os x 10.13


texts:
I have pgAdmin 4 and PostgreSQL 10.

I can't execute a query without quote marks i.e. I want to excute this

select * from table

but it only works if I use

select * from databasename."table"

How can I remove this restriction?

The name of the table is Complexitie. I was using postgresql the last year and I remember I could write queries in a simple way without mandatory syntax things like case senstive, mandatory quotes.

I read something about to setup arbitrary SQL queries en Postgres, but I can not find steps to configure it. thanks


texts: Which Indexes in which situations is more efficient in SQL Server 2012

texts:
I use SQL Server 2012.

I want know when I want to know in what situation I have to use CREATE NONCLUSTERED COLUMNSTORE INDEX .... and in what situation I have to use Create NonClustered Index... Include and in what situation I have to use Create NonClustered Index... with all columns.

What are the advantage and disadvantage relative to the other

Exemple :

Use [AdventureWorksDW2012]
Go

CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactResellerSales
ON dbo.FactResellerSales
(ProductKey, UnitPrice, CustomerPONumber, OrderDate);

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;

Drop Index csi_FactResellerSales On [dbo].[FactResellerSales]
Create NonClustered Index Ardalan
    On [dbo].[FactResellerSales] (ProductKey) include(UnitPrice, CustomerPONumber,
                                                      OrderDate)

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;

Drop Index Ardalan On [dbo].[FactResellerSales]
Create NonClustered Index Ardalan1
    On [dbo].[FactResellerSales] (ProductKey,UnitPrice, CustomerPONumber, OrderDate)

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;
Drop Index Ardalan1 On [dbo].[FactResellerSales]


texts: Select pairs (a,b) excluding (b,a) with same value

texts:
For a given structure like this:

+----+----------+
| ID |   Name   |
+----+----------+
| a  | ZZZZZZZZ |
| b  | YYYYYYYY |
| c  | WWWWWWWW |
| d  | ZZZZZZZZ |
| e  | YYYYYYYY |
| f  | WWWWWWWW |
+----+----------+

Get the list of pairs (ID, ID2) with same Name, excluding all records where (ID2, ID) has the corresponding (ID, ID2).

+----+----------+-----+
| ID |   Name   | ID2 |
+----+----------+-----+
| a  | ZZZZZZZZ |  d  |
| b  | YYYYYYYY |  e  |
| c  | WWWWWWWW |  f  |
+----+----------+-----+

I've found this solution, but I wonder if there is a more "academic" solution.

;WITH Pair AS
(
SELECT
    CASE WHEN p1.Id > p2.Id THEN p2.Id ELSE p1.Id END as ID,
    CASE WHEN p1.Id > p2.Id THEN p1.Id ELSE p2.Id END as ID2,
    p1.Name
FROM Pairs p1
     LEFT JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id <> p2.Id
)
SELECT DISTINCT ID, ID2, Name
FROM Pair;

Rextester

CREATE TABLE Pairs(Id varchar(5), Name varchar(20));
INSERT INTO Pairs VALUES
('a','ZZZZZZZZ'),
('b','YYYYYYYY'),
('c','WWWWWWWW'),
('d','ZZZZZZZZ'),
('e','YYYYYYYY'),
('f','WWWWWWWW');


texts: mysql greatest n per group: latest message content and time for each conversation

texts:
I have two tables, one for topics, and one for messages.
In terms of relationship, messages are posted within a topic, so each topic has many messages.

I am trying to make a query that would:
- list all the topics
- get the count of messages for each topic
- get the latest message posted for each topic (time and content)

I basically tried to do a query similar to:

SELECT
  topic.*,
  COUNT(msg.id),
  MAX(msg.time),
  msg.content

FROM topics

LEFT JOIN (
  SELECT
    msg.id,
    msg.time,
    msg.content,
    msg.topic_id
  FROM messages AS msg
  ORDER BY time DESC
) AS msg
ON msg.topic_id=topic.id

GROUP BY topic.id

However, this results in an error

  Expression #... (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column 'msg.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I understand that I could just toggle the only_full_group_by setting, but my understanding is that I then have no guarantee that the msg.content will return me the latest message as expected.

I found many greatest n per group questions online, but most seem to only deal with a single table with the same table being queried in the query and subquery.

Is there any reliable way to achieve this with MySQL?


texts: Copy On Write Transparent database for MySQL

texts:
It is possible to create a transparent database to another production database server? I mean, testing new functions in a production database is too dangerous. What if we create a database which based on Copy On Write mechanism (because don't want to copy a big amount of data every time), and make some modification on that?  The original production database will not modified, only the COW database. After the test, the COW database can be dropped or saved, etc., but the original can operate without any kind of danger.

Doe this solution exist?  Has anyone implemented it?


texts: MySQL - How to make a DATEDIFF in years?

texts:
This is my first post (and English is not my mother tongue) so I hope I won't screw this post up.

I am new to MySQL but I learned a lot with 100+ youtube tutorials and your numerous posts. I designed my database using PHPmyadmin and MySQL Workbench.

In my table subventions (grants), I created a trigger to be able to calculate the annual amount of money of a grant, using my columns subventions.début (beginning), subventions.fin (end) and subventions.montant (amount).

As DATEDIFF only gives me an answer in days, I naively thought that I only had to divide it by 365 days to obtain my answer. It works if my beginning date is the first of january and the end date the 31st of December, but does not if it stars somewhere else during the year. Here is my trigger:

SET NEW.montant_annuel = NEW.montant / (datediff(NEW.fin,NEW.début)/365)

Here is my table and the result in the 'montant_annuel' column:

If I use 365.25 (which is the "real" number of days in a year), it does not work either.

Thanks! :)


texts: ssis cannot convert between unicode and non-unicode data types even though the source and the destination columns are both unicode

texts:
I faced an error: 'SSIS cannot convert between unicode and non-unicode string data types' even though the source and the destination columns are both unicode (NVARCHAR) data type. The source db and the destination db are on the same server.

The source is a query:

SELECT
   CASE
        WHEN [Gender__c] = 'Female' THEN 'N'
        WHEN [Gender__c] = 'Male' THEN 'M'
        WHEN [Gender__c] = 'Nainen' THEN 'N'
        WHEN [Gender__c] = 'Mies' THEN 'M'
        When [Gender__c] = 'Ei tiedossa' THEN 'Ei tiedossa'
        ELSE NULL
    END as Gender__c
  FROM [database].[table]

Anybody seen the same?


texts: Installation of Oracle Instant Client on an Ubuntu 64 machine

texts:
I would like to install Oracle Instant Client on a Ubuntu 64 bit machine.

Following this link, I have installed the rpm packages basic, development and sqlplus using alien.

Then:

sqlplus  username/password@//dbhost:1521/SID
sqlplus: command not found

export ORACLE_HOME=/usr/lib/oracle/12.1/client64
ls $ORACLE_HOME
bin  lib

sqlplus
sqlplus: command not found

I have tried the python script in this link, but this did not work. It conflicts with the installed 12.1 packages as it tries to install 11.2.


texts: Can't get any fragmentation of index for testing in SQL Server database

texts: I am filling my table in my SQL Server database with some data and I have partitions. I have one clustered index in the table. The problem I am facing is that I am not getting any fragmentation which I need in order to test. After reading some articles, I came to conclusion that I need to use file groups, but I am not sure how to achieve that.


texts: How to implement paging in an effective way?

texts:
I have a database query which could result in a big result set. The client which displays the data receives the data over network, so the idea was to minimize the amount of transferred data by retrieving only the first 50 results from the database and sending them over to the client. Then I will provide a possibility to jump to the second page to retrieve the next 50 results etc. (something similar what e.g. google offers)

The question is what is the effective way of implementing paging. I want to make sure that mssql uses cache as much as possible and that the same is not executed once again everytime I change the paging.

There are more clients who are querying the database in the same time. Used sql engine: MS SQL 2005

My ideas were:

Use prepared sql statemenst to ensure execution plan sharing
use ROW_COUNT variable to retrieve only the needed rows

But is it really the most effective way? Or do you think that it would be better to retrieve the whole result set and implement paging in the code which sends the data over to the client?

Thank you for your tips!

Regards,
Tomas


texts: how exactly code.org lost data with 32-bit index? Suppose they use MySQL database

texts:
Today the headline The 32-Bit Dog Ate 16 Million Kids' CS Homework. Quote from blog.code.org

  The way we store student coding activity is in a table that until
  today had a 32-bit index. What this means is that the database table
  could only store 4 billion rows of coding activity information. We
  didn’t realize we were running up to the limit, and the table got
  full. We have now made a new student activity table that is storing
  progress by students. With the new table, we are switching to a 64-bit
  index which will hold up to 18 quintillion rows of information. On the
  plus side, this new table will be able to store student coding
  information for millions of years. On the down side, until we’ve moved
  everything over to the new table, some students’ code from before
  today may temporarily not appear, so please be patient with us as we
  fix it.

We now suppose the use MariaDB/MySQL. Can you explain me in detail (with SQL statements and error messages) this issue? I don't understand this data loss very well.


texts: Does MySQL do UPDATE in place on NOT NULL column?

texts:
Assume I have a table like this in MySQL:

CREATE TABLE Foo (
Key INT NOT NULL
, MyCol INT NOT NULL
PRIMARY KEY (Key))

And I execute a statement like this on:

UPDATE Foo
SET MyCol = 13
WHERE Key = 42

Will MySQL do an INSERT followed by DELETE of this row behind the scenes (and thus, potentially cause a page split) or can the UPDATE happen in place on the row?

Under which conditions do each of these situations happen?


texts: Choosing right database for storing bank transactions

texts:
I am starting a new project within GCP and I am trying to choose a right tool for storing bank transactions:

I don't need transactions, these will be basically write-only, no updates
I don't need joins and relations
I need grouping and sums
Ideally I would like to use a managed tool within GCP
I need to be able to have unique records (identified by ID or by the uniqueness of the record)

GCP solutions:

Firestore

I really like the way of authorisation via service accounts, no need for users/passwords
Fulfils 1, 2, 4, 5
Completely unsuitable to fulfil point 3

CloudSQL

fulfils 3 and 5, but I find it too complex for my need
old-way authorisation of MySQL - one cannot use GCP authorisation

Elasticsearch

fulfils 3 and 5 but also too complex
Not within GCP, not managed, cannot use GCP auth

BigQuery

Seems to fulfil all 1, 2, 3, 4
Unfortunately it seems it cannot prevent having duplicate records
Data becomes available after long time

BigTable

Seems too complex for such simple requirements

Could you think of another approach?


texts: Select into existing table where ID is in another table

texts:
I have one table containing ~35k IDs (TableC), one table that features multiple columns (TableA), amongst others the ID from the before mentioned table and a third empty table, featuring the same columns as the second table (TableB).

Now I'm required to copy every entry from TableA to TableB where the ID of TableA is in TableC. I came up with this query:

SELECT * INTO TableB
FROM TableA
WHERE ID IN
(SELECT ID FROM TableC)

But this leads to the error There is already an object named 'TableB' in the database... I'm not allowed to delete and re-create TableB for this.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

How can I copy  every entry from TableA to TableB where the ID of TableA is in TableC?


texts:
I have no clue what to set up more.
I tried a lot.

My Problem is that on my test Server everything is runing perfectly see

but on my production Server its not! see

I found this on my Production Server:
"Using locked pages for buffer pool"

This is shown in the error log of the SQL Server.
This line only Shows up in the production System.

Both Systems are SQL 2008 R2 Enterprise with WIN2K8 R2 both in 64 bit.

Locked Pages in Memory is not enabled in GPEDIT.MSC

Can someone explain that to me?


texts: Understanding query variance in exec time in PostgreSQL?

texts:
I want to execute a set of queries many times through psql and to study running/execution time of each query. Of course, I want to run them many times to reduce effect of external factors through statistical analysis, but I expect variance to be quite high because in my personal experience, 10 times that you run the same query, 3 or 4 of them I get a relatively lower absurd value.

So how can I track down or at least understand the nature of these external influences? I am making sure that all my data is in cache and I have nothing in background. But still seems like something is amiss.


texts: Force a Member to Become Primary in mongodb

texts:
I looked into answers but nothing works for me in mongo 2.6.11 cluster 'nms' (members nms01m and nms02) below. I want my current secondary nms01m to become primary, and now nms02 is primary instead of nms02 (after nms01m failure last night). I tried stopping nsm02, restarting nms01m , changing priority, rs.reconfig(cfg, {force : true}) but nothing helps. It is always a struggle for me

nms:SECONDARY> rs.conf()
{
        "_id" : "nms",
        "version" : 193970,
        "members" : [
                {
                        "_id" : 0,
                        "host" : "nms02:27017",
                        "priority" : 2
                },
                {
                        "_id" : 1,
                        "host" : "nms01m:27017"
                }
        ]
}


texts:
I have 3 tables as follow:

class
  id:bigint(PK)
  name:varchar (unique)

principal:
  id: bigint(PK)
  pid:bigint
  role:boolean
  uniqueConstraint(pid, role)

entry:
  cid: (FK, reference class)
  pid: (FK, refernce principal)
  object_id: bigint
  code: tinyint
  PK: (cid, pid, obj)

The query must check existence of a record in entry using a parameter set.

suppose parameter set is as follow:

class's name: Class#3
principal id as user: 3
principal id as role: 2
object ID: 45

I wrote 2 queries, one using join and one using sub-query, but I don't know which one performs better in large scale production environment:

Query number 1:

select id
from entry
where pid in ( select id
               from principal
               where (pid=2 AND role)
                  OR (pid=3 AND !role))
  AND cid = (select id
             from class
             where name='Class#3')
  AND object_id=45

And query number 2:

select e.id
from class c
inner join entry e on e.cid=c.id
                  and c.name='Class#3'
inner join principal p on p.id=e.pid
                      and p.id in ( select id
                                    from principal
                                    where (pid=2 AND role)
                                       OR (pid=3 AND !role))
where e.object_id=45

Of course there's an additional condition for checking code which I didn't include it in queries

Suppose there are 100 rows in class, 10000 in principal and over 250000 in 'entry', and query(as explained) must be executed for each request, and there are 3000 users working on system constantly and simultaneously

Which of these queries will perform better And why? the reason is so important for further works
Is there better approach to write the query than these 2 approaches or even better approach to construct the schema?

Regards

PS:
I Have read this question about comparing sub-query and join, but my question is not exactly a simple comparison

UPDATED: fixed the role column name in principal table


texts: Remove nulls from TSQL connection string

texts:
I have a bunch of queries, against a couple different databases, that I use to generate delimited reports. I'm trying to create some new reports against a MSSQL database and I'm having trouble replicating our other processes to get consistent results.

When I query against the other sources I get '' and 0 in place of nulls. When I query against MSSQL I get the nulls.

I'm using DSN strings => ODBC => remote database for some queries... and straight connection strings for other queries.

So against a DB2 database:

SELECT OrderID, CustomerName
FROM OrderTable LEFT JOIN CustomerTable on OrderCustomerID = CustomerID

Assume the CustomerTable doesn't have a Customer, so for OrderID 1, no customerID the result would be (output as a | delimted list):

0|Joe Schmoe
1|NULL
2|Jane Doe

When using DB2, I get "1|". When using MSSQL, I get "1|null".

Using ODBC, connection strings, server settings... how can I return "" or 0 on nulls to get the output to match our other processes?

I've tried a straight connection (no DSN/ODBC). I've tried different settings in ODBC. All three SQL drivers (SQL Server, SQL Native Client, SQL Server Native Client 10.0). All are coming back with NULL instead of "default" for the data type.

Update/Clarification
I'm working with an "in house" ETL program that pulls and transforms data from in-house databases for export to other institutions/customers mainly via delimited text. Previously all of the queries I've used, when hit with nulls, have simply output ""/0. MSSQL obviously doesn't do that, so I'm trying to mimic the other sources at the most generic level I can.

I understand how IsNull/Colesce works, and if need be, I will use these to get the job done. The obvious issue with using IsNull/Colesce is the added code, across what will be many queries. Added code means harder maintenance, more creation time, more points of failure, etc. If I can simply tell the connection level to replace all nulls, I would think it'd be much easier... but that makes the question Is it possible? and if so how? Either in the SQL2005 Server instance, or on the other side of the connection at the ODBC connection or connection string.


texts: Changing the representation of NULLs in pg_dump plaintext output

texts:
I'm trying to convert a large-ish Postgres DB (500 GB) to SQL Server 2012.  After investigating a few third-party tools and being disappointed in features, performance, or both, I started pursuing a simple pg_dump/bulk import solution.  Things looked promising until I realized that pg_dump represents NULLs in plaintext as "\N", which causes the bulk insert to vomit in cases of type mismatch.  Even if I were to automate the pg_dump process to produce a single file per table, some of the individual tables involved are very large (20-50 GB) and performing a comprehensive find-replace using even fast file editing options in linux, or a Perl script, add too much overhead to the time required for the import/export.

I'm hoping there's a way to modify the NULL representation in the pg_dump output that I'm not aware of, or failing that, to get some recommendations for alternative approaches to this process in terms of tools or strategies.  Thanks in advance for your help.


texts: How can I best combine "get categories" and "has user access"?

texts:
I was wondering if it's possible to create a query for the following problem. I only want to select categories from the (MySQL) database (if the user has access).

A user has multiple roles;
A category has multiple roles.

I have the following tables:

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  email VARCHAR(127) NULL ,
  username VARCHAR(16) NULL ,
  password VARCHAR(64) NULL ,
  PRIMARY KEY (id)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE roles_users (
  user_id INT(11) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (user_id, role_id)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE forum_categories (
  id INT(4) NOT NULL AUTO_INCREMENT ,
  name VARCHAR(30) NULL ,
  description VARCHAR(70) NULL ,
  image VARCHAR(20) NULL ,
  parent_id INT(4) NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE forum_categories_roles (
  category_id INT(4) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (category_id, role_id) ,
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

I removed the relations

Example:

User A has role_id A
User B has role_id B
User C has role_id A and C

category A has role_id A
category B has role_id B and  C
category C has role_id C and B

user A has access to category A
user B has access to category B and C
user C has access to category B and C


texts: Merge data with UNION ALL and INNER JOIN in MySQL

texts:
I try merge a data to one row. I have query:

(select t.`id`,
        t.`coins_id`,
        t.`first_coin`,
        t.`second_coin`,
        t.`price` as `price_old`,
        null as `price_young`,
        t.`time`
from hist_all t
inner join (
  select `first_coin`,
        MIN(`time`) as `MINDATE`
  from hist_all group by `first_coin`
) t1
on t.`first_coin` = t1.`first_coin`
and (t.`time` = t1.`MINDATE`)
WHERE (second_coin = 'USD' OR second_coin = 'USDT')
and `time` between (NOW() - interval 9999 minute)
AND NOW()
group by t.`first_coin`
order by t.`ID`)
UNION ALL
(select t.`id`,
        t.`coins_id`,
        t.`first_coin`,
        t.`second_coin`,
        null as `col_null_1`,
        t.`price` as `new`,
        t.`time`
from hist_all t
inner join (
  select `first_coin`,
         MAX(`time`) as `MAXDATE`
   from hist_all
   group by `first_coin`
) t1
on t.`first_coin` = t1.`first_coin`
and (t.`time` = t1.`MAXDATE`)
WHERE (second_coin = 'USD' OR second_coin = 'USDT')
and `time` between (NOW() - interval 9999 minute)
AND NOW()
group by t.`first_coin`
order by t.`ID`)

This query reurn for example:

I need such a result:

The id, coins_id and time values are irrelevant, I gave them for example.


texts: Can I make a sequence skip certain values?

texts:
In SQL server 2014 we are using sequences for some integer surrogate keys.

They begin in the negative values counting up.

Can we program the sequence to skip zero when the time comes?

The reason is because it's too easy for higher layer developers to accidentally initialize to the default value of the int and we'd like to protect against this in business logic (easy enough!)

But zero is actually a possible value in our sequence one day. It's not a problem to use any of the numbers, just easy to erroneously use zero.

We could write any number of validations and constraints to prevent this but I think the sequence itself would be the easiest.


texts:
I am attempting to Use the SUM() function with an update query and I am getting an error of

  Msg 130, Level 15, State 1, Procedure PYES, Line 144
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I am not sure how to re-write this in order to achieve my desired result.  Can someone please provide assistance?

;WITH CTE As
(Select pkID,serviceman, jv
 FROM OPENQUERY(XXX.XX.XXX.XXX,
                'Select
                    jb.pkID As pkID
                    ,cm.cn As serviceman
                    ,jb.jv As jv
                    FROM arica jb
                    INNER JOIN serviceman cm
                    ON jb.spID = cm.spID
                    INNER JOIN salesperson sp
                    ON jb.uqID = sp.uqID
                    INNER JOIN jointca js
                    ON jb.css = js.sid
                    INNER JOIN jart jp
                    ON jp.pkID = jb.pkID
                    WHERE jb.css IN (''R'',''H'',''p'',''S'',''O'',''N'')
                    GROUP BY jb.pkID, jb.jv,cm.cn') v
WHERE jv > 0)
UPDATE t
SET INP = SUM(ISNULL(INP,0)+ISNULL(SUM(jv),0))
FROM tertiarydata t
INNER JOIN CTE cte
ON t.pkID = cte.pkID;

EDIT
Thanks to the dupe by @SP_BlitsErik - I modified my code to the below - but that returns multiple entries for the field serviceman I want to only return 1 row for each serviceman listed

;WITH CTE As
(Select pkID,serviceman, jv
 FROM OPENQUERY(XXX.XX.XXX.XXX,
                'Select
                    jb.pkID As pkID
                    ,cm.cn As serviceman
                    ,jb.jv As jv
                    FROM arica jb
                    INNER JOIN serviceman cm
                    ON jb.spID = cm.spID
                    INNER JOIN salesperson sp
                    ON jb.uqID = sp.uqID
                    INNER JOIN jointca js
                    ON jb.css = js.sid
                    INNER JOIN jart jp
                    ON jp.pkID = jb.pkID
                    WHERE jb.css IN (''R'',''H'',''p'',''S'',''O'',''N'')
                    GROUP BY jb.pkID, jb.jv,cm.cn') v
WHERE jv > 0)
, uCTE as (
  select pkID,serviceman, sumJV=sum(jv)
    from cte
    group by serviceman,pkID
)
Select * from uCTE


texts: How can I minimize SQL Server data loss in AWS in the event of failure?

texts:
If I am running a SQL Server in AWS. What is a low cost solution to minimize my risk of data loss in the event of failure at the database level?

Lets assume I am performing backups every 6 hours so worst case I can have my servers back up in a couple hours but depending of the time of the failure I could lose 6 hours of data.

Lets assume I am ok with the 2 hours to bring the servers back up but I am not ok with 6 hours of data loss.

Is there a way to narrow my dataloss gap to around 15 mins or less without getting into replication etc.

I am open to any way to host SQL Server in AWS


texts: Is there a way to DENY CRUD operations on connections from linked server?

texts:
I have a SQL Server instance, RPT1 (2008 R2), that was set up for "power users" to run their own queries. This server is linked to our production reporting server, PRD1 (SQL Server 2008 instance), with views (on RPT1) that query the linked server (PRD1) directly.

The linked server has been created with an account that requires the ability to CRUD on the server (for report auditing, and such).

As the users gain sophistication, they also request greater permissions (e.g. to create their own views). I'd like to ensure that they are not going to be doing any CRUD operations on the linked server, but the account with which it was linked has those permissions.

The first resolution path that I see is to create a different user, with lower permissions, and re-link the server.

My second path is to see if there's a way to explicitly DENY CRUD on the PRD1 server for connections coming from a linked server.

Is it possible to explicitly deny CRUD only from the link? Or should I simply set about re-linking the server with a different account? Is there another obvious solution that I'm overlooking?


texts: mysql v8.0.16 unable to initialize the database following fresh installation

texts:
After a fresh installation of mysql 8.0.16 I get these errors:

2019-06-02T06:00:29.777041Z 0 [System] [MY-013169] [Server] C:\mysql\bin\mysqld.exe (mysqld 8.0.16) initializing of server in progress as process 11288
2019-06-02T06:00:29.839450Z 1 [ERROR] [MY-012576] [InnoDB] Unable to create temporary file; errno: 2
2019-06-02T06:00:29.839647Z 1 [ERROR] [MY-012929] [InnoDB] InnoDB Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again.
2019-06-02T06:00:29.840528Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-06-02T06:00:29.840565Z 0 [ERROR] [MY-013236] [Server] Newly created data directory C:\mysql\data\ is unusable. You can safely remove it.
2019-06-02T06:00:29.892808Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-06-02T06:00:29.897636Z 0 [System] [MY-010910] [Server] C:\mysql\bin\mysqld.exe: Shutdown complete (mysqld 8.0.16)  MySQL Community Server - GPL.

Any help would be appreciated.


texts: How to dump PostgreSQL database whose owner has no password?

texts:
First, some background on my setup:

The server has root access disabled. So, I log-in as (say) john who also belongs to the sudo group and is therefore able to run superuser commands.

I created a new password-less user#1 santa (using the command sudo adduser --shell /bin/bash --gecos 'Santa Claus' --disabled-password santa).

Then I changed the login session's owner to santa using the command: sudo su - santa and created a new PostgreSQL database: createdb myapp_db (the database got created without asking me for password).

Now, given the condition, how do I make an SQL dump of the database myapp_db (whose owner is santa) using the command pg_dump?

This is supposed to work, but it isn't:

john@host:~$ pg_dump myapp_db -U santa -h localhost --no-owner -W > myapp_db_backup.sql
Password:

When I run the above command, it asks me for "password" as you can see. What password am I supposed to enter here? I didn't enter any password for the database when creating it, nor does the database owner santa have any.

So, I tried entering the password of john, the sudo user, and got this error:

"FATAL:  password authentication failed for user "santa""

I also tried (which didn't work either):

john@host:~$ su - santa
santa@host:~$ pg_dump myapp_db -U santa -h localhost --no-owner -W > myapp_db_backup.sql
Password:

This time, I created a password for the user santa and entered it. Still get the error:

"FATAL:  password authentication failed for user "santa""

What am I missing here?

(Please let me know if I am missing any necessary details.)

More Information

(As requested in the comments.)

john@host:~$ sudo su postgres
postgres@host:~$ psql -l

                                         List of databases
          Name           |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------------------+----------+----------+-------------+-------------+-----------------------
 app_db                  | santa    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres                | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |          |          |             |             | postgres=CTc/postgres
 template1               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |          |          |             |             | postgres=CTc/postgres
(4 rows)

Output of \du santa:

john@host:~$ sudo -u postgres psql
postgres=# \du santa

                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 santa     | Superuser, Create role, Create DB, Replication | {}


texts:
I have a table with several "categories", called collection (e.g. A, B, C, ...).

I would like to create a SQL query to select N records by each collection.

For example:

I would like to select 2 records by each collection.

Table stac_item:

id | collection | name
1  | A          | name_01
2  | A          | name_02
3  | A          | name_03
4  | B          | name_04
5  | B          | name_05
6  | B          | name_06
7  | B          | name_07
8  | C          | name_08
9  | C          | name_09

I expect the following result:

id | collection | name
1  | A          | name_01
2  | A          | name_02
4  | B          | name_04
5  | B          | name_05
8  | C          | name_08
9  | C          | name_09

I have created the SQL query below, but MariaDB gives the following error message:

#1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

SQL query:

SELECT *
FROM stac_item a1
WHERE a1.collection IN
(
    SELECT a2.collection
    FROM stac_item a2
    WHERE a1.collection = a2.collection
    LIMIT 2
)

Would anyone know an alternative that I could use? I have searched on the internet, but I was not able to find a solution.

Thank you.

EDIT 1:

I use the following MariaDB Docker image: mariadb:10.5.3


texts: What can I use for the PowerShell notin operator in v2.0?

texts:
I developed a PowerShell script in v4.0 and then learned that the -notin operator isn't supported on v2.0 (which is the version that I need to run it in). What is the best alternative to the -notin operator for v2.0? I'd prefer to minimize code refactoring, if possible.

Here is a snippet of code where I am using the operator:

$NoBackups += $BackupList |
            Where-Object { $_.DatabaseName -notin ($NoBackups.DatabaseName) } |
            Where-Object { ($_.RecoveryModel.ToString().ToUpper() -ne "SIMPLE") -and ($_.LastLogBackupDate -lt $CompareDate.AddHours($LOGCompareHours)) } |
            SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate, `
                @{Name='Message';Expression={"Transaction Log Backup is more than $([Math]::Abs($LOGCompareHours)) hours old"}}


texts: Sleeping SPID blocking other transactions

texts:
I'm really having trouble tracking down some blocking we are experiencing.

The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

When I view the Top Transactions by Blocked Transactions Count report, the Blocking SQL Statement is '--'.

I've performed a trace on the SQL and when the blocking happens tracing the root blocking SPID but it hasn't really led me anywhere.  The last trace statement is the same as the sqltext above SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

I've checked all the related stored procedures I can find to make sure they have TRY/CATCH BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN statements (we use stored procedures for everything so there are no standalone statements being ran).  This issue just started happening over the last 24 hours and no one is claiming to have made any changes to the system.

Solution: one of our seldomly used stored procedures had an error with an insert (number of columns didn't match), but we are still confused on what exactly was happening.

When looking at all the trace information, the EXEC statement for this stored procedure was listed at times, but NEVER just before the BLOCK happened on the blockking SPID. It seemed that when it starting blocking, the trace didn't record the execution of it (or any of the statements within it either). However there are other times were the trace did record it's execution and no blocking occurred.

The stored procedure error report came from a user, and I was able to find multiple EXEC statements in traces and run them in SSMS. No time when I ran them did we have any blocking occur or did they hang. They ran as expected (the catch block fired and rolled back the transaction after the error).  After resolving the fixing the stored procedure, we have not seen the issue again.


texts: mysql starts and stops servers automatically non stop

texts:
I am working on macOS High Sierra.

Basically, the problem is that for some reason the mysql server is starting and stopping automatically, and constantly. It does not stop. If I run:

# ps -ax | grep mysql

to see the processes even when I (think) I have uninstalled mysql, it shows:

54948 ttys002    0:00.00 grep mysql

I run the same thing 5 seconds later, and it has a different ID:

54950 ttys002    0:00.00 grep mysql

I cannot sudo kill -9 54950 because by the time I try to kill it, it has already disappeared.

I have tried removing everything related to mysql. I then reinstall it, and the same thing happens. I have tried doing this with homebrew as well.

Please help.


texts: Performance tuning large mysql databases for zabbix

texts:
I have been working on tuning a mysql database for zabbix for quite some time now. We do not actually have a DBA to assist with this.

We have a Zabbix server running 16k values per second.
Our database is MariaDB 5.5.38 running galera replication with only two nodes. I will be happy to provide any information needed to assist in tuning.

The issues we are seeing is our zabbix graphs are delayed and our dbsyncers are maxed out. According the the Zabbix performance tuning book and zabbix support personnel our database is not able to keep up with the amount of data being sent its way.

We have 512GB of RAM on the server, in RAID 10 with SSD's, and 32 Cores at 2.59GHz.

The mariadb, zabbix, and apache services are all running on this server.

What can I do to increase the performance of this server. It does not seem like the OS is being over utilized based on iostat, vmstat and other OS performance checking tools.

Thanks in advance.


texts: InnoDB errors after having deleted "ibdata1"

texts:
By accident, I have deleted the ibdata1 file in the MySQL directory because MySQL wasn't functioning correctly.

After reading so many forums, I found a post that recommended deleting the 3 files ib_logfile0, ib_logfile1 and ibdata1 from the MySQL directory to let InnoDB load again and MySQL come up. And this what I have did.

But now when InnoDB loads as engine the tables disappear.

After I do a graceful restart, InnoDB disappears totally from the server ("have_innodb | No"). But I can see all tables again for databases using InnoDB.

What I'm looking for is a way to load all the tables correctly than ask ibdata to state them as InnoDB then let the InnoDB engine function correctly.


texts: SQL Server 2014 - Replica Server not available

texts:
I'm new to Sql Server and want to install SQL server alwayson clustering on my test environment. I followed the steps by this site:

On each node I can connect to SQL Server but why my 2nd node is not available so that I can add it as my 2ndary Replica?
According to this site, till "Always-On Availability Group Configuration" everything was successful.
p.s. It seems I cannot add my screenshot as I do not have enough reputation.


texts: How can I read an XML file and store the data in XML to our table in SQL Server 2008?


texts: SQL Server 2016 InitializeExternalUserGroupSid failed message on startup

texts:
I have a SQL Server 2016 installation that I inherited. It does not use or have R or Machine Learning configured. When it starts, I see these messages in the log:

InitializeExternalUserGroupSid failed. Implied authentication will be
disabled.
Implied authentication manager initialization failed. Implied authentication will be disabled.

Are these normal and expected or do they indicate a problem?


texts: Expanding parent-child tree with additional tables

texts:
I have readonly access to a table (Oracle database) which contains a hierarchical structure, represented via parent-child.

I need to add another table, which will expand the existing hierarchical tree.
The new table needs to add levels to the tree, including entire sub-trees.

The problem is this:
Any node in the expansion table needs to have a single parent, yet that parent can be in either the original table, or in the expansion table.

How can I accomplish this safely?

Things I thought of so far:

Giving each node in the expanded table two parent (one from each table)
Problem: data integrity!
Each node will have one parent, without knowing which table contains it
Problem: querying will be difficult.
Problem 2: same ID for two different nodes (in different tables)
Each node will have one parent, with another column specifying which table contains it
Problem: querying still difficult.


texts: SQL server schema to accomodate duplicate PK/FK values from autonomous Access data bases

texts:
I have several test stations with Access databases that have identical structures (12 related tables each).  They are independent and all internal PK & FK values (Autonumber PK & Long Integer FK) can duplicate from station to station. The first table in each station has Part Number, Serial Number & Time Stamp fields that, across all stations, will be unique.  (no one unit PN/SN can be on two stations at the same time).
The goal is to push all the test data from all the test stations to a SQL Server database.  The original PK & FK values do not have to be retained. I am familiar with Access and a total newbe with SQL Server.  I have tried to get this working using SSMA & SSMS.  I have migrated a single Access sample with partial success.  The SQL PK values will increment correctly but the FK values are either NULL or if not allowed, '0'.  I would expect there is a standard approach to this issue but to date, I haven't found it.

mendosi, Here is where my ignorance jumps to the top.  How do I publish the SQL table definitions?

peter,  I checked the 'Link Tables' box and my Access front end is connected.  I have several combo boxes that select query criteria that, ultimately, will need pass through queries and / or stored procedures to populate their lists.  But until I get passed the PK / FK issue those will have to wait.


texts: Migration to databases Domain Users do not have access

texts:
I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be failing. I have a domain account who is the job owner. However, when I try to execute the job under my username I get the following error:

  Executed as user: NT AUTHORITY\SYSTEM. Login failed for
  user.....[SQLSTATE 28000) (Error 18456).

Is this related to Domain Users not having appropriate read and write access to the database?

How would I give all domain users permissions to execute stored procedures.


texts:
This is my sample table
BSEG_ID DST_ID  BILL_SQ AUDIT_CALC_AMT  BILL_SQ, MSR_PEAK_QTY_SW, DESCR_ON_BILL, VERSION, AUDIT_CALC_AMT
12334   REV-WSC 62      6.40667         For 0 - 60 (Next 62.000 cubic meter) @US0.10 per cubic meter
12334   REV-WSC 62      64.06667        For 60 - 120 (Next 62.000 cubic meter) @US1.00 per cubic meter
12334   REV-WSC 3.6     11.16           For 120 - 180 (Next 3.600 cubic meter) @US3.00 per cubic meter
12334   REV_WWSC 62     3.20333         For 0 - 60 (Next 62.000 cubic meter) @US0.05 per cubic meter
12334   REV_WWSC 62     32.03333        For 60 - 120 (Next 62.000 cubic meter) @USSAR0.50 per cubic meter
12334   REV_WWSC 3.6    5.58            For 120 - 180 (Next 3.600 cubic meter) @US1.50 per cubic meter
11111   REV-WSC  62     6.40667         For 0 - 60 (Next 62.000 cubic meter) @US0.10 per cubic meter
11111   REV-WSC  62     64.06667        For 60 - 120 (Next 62.000 cubic meter) @US1.00 per cubic meter
11111   REV-WSC 3.6     11.16           For 120 - 180 (Next 3.600 cubic meter) @US3.00 per cubic meter
11111   REV_WWSC 62     3.20333         For 0 - 60 (Next 62.000 cubic meter) @US0.05 per cubic meter
11111   REV_WWSC 62     32.03333        For 60 - 120 (Next 62.000 cubic meter) @USSAR0.50 per cubic meter
11111   REV_WWSC 3.6    5.58            For 120 - 180 (Next 3.600 cubic meter) @US1.50 per cubic meter

Expected Output #1
Range for Bill_SQ
BSEG_ID DST_ID     0-10    10> <= 20    >20
11111   REV_WWSC    0          0         1
11111   REV-WSC     0          0         1
12334   REV_WWSC    0          0         1
12334   REV-WSC     0          0         1

Expected Output #2
Range for Sum of AUDIT_CALC_AMT
BSEG_ID DST_ID      0-10    10> <= 20   >20
11111   REV_WWSC    0         0          1
11111   REV-WSC     0         0          1
12334   REV_WWSC    0         0          1
12334   REV-WSC     0         0          1

This is my current query:
select a.bseg_id,a.dst_id, sum (a.bill_sq) as WaterConsumption ,sum (a.AUDIT_CALC_AMT) as SWConsumption from CI_BSEG_CALC_LN a
--where a. BSEG_ID = '209875510608'
AND a.DST_ID in ('REV-WSC','REV-WWSC')
group by a.bseg_id, a.dst_id;

Please suggest a way for achieving the desired output.
This is what I have done so far
select * from ( select Bseg_ID , BILL_SQ, count (*)
from CI_BSEG_CALC_LN
where BILL_SQ Between '0' AND '5'
--AND BILL_SQ Between '6' AND '10'
--AND BILL_SQ Between '11' AND '15'
--AND BILL_SQ Between '16' AND '20'
--AND BILL_SQ > '20'
group by Bseg_ID, BILL_SQ) PIVOT (Count (BSEG_ID) for BILL_SQ IN ('0-5'));


texts:
I am using percona and the backups run successfully. However after the backup is done and sometimes during the backup MySQL is crashing and I am not certain where to start.

Running show engine performance_schema.memory performance_schema.memory   169474536
running free -g

       total        used  free      shared  buff/cache   available
Mem:   125          58     0           0          66          66
Swap:    9           0     9

running dmesg

Out of memory: Kill process 11779 (mysqld) score 225 or sacrifice child
Killed process 11779 (mysqld) total-vm:36902412kB, anon-rss:31731108kB, file-rss:0kB

The backup script is pretty generic

dest="current_backup/";
xtrabackup --backup --target-dir=$dest --no-timestamp 2> $log;
xtrabackup --prepare --target-dir=$dest 2>> $log;

I am running two instances of MySql on the same server.  But I have a ton of memory and a lot of disk space.
my.cnf

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E RROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_E NGINE_SUBSTITUTION
innodb_buffer_pool_size = 25G
innodb_buffer_pool_instances = 8
innodb_lock_wait_timeout = 3600000
wait_timeout = 86400
interactive_timeout = 86400
max_heap_table_size = 96M
sort_buffer_size = 16M
join_buffer_size = 16M
tmp_table_size = 96M
max_sort_length = 8K
max_allowed_packet = 16M
group_concat_max_len = 16M
max_connections = 200
max_connect_errors = 10
expire_logs_days = 2
max_binlog_size = 128M

symbolic-links=0
log-bin=binlog
binlog-format=ROW
log_slave_updates
wsrep_log_conflicts
expire_logs_days=7


texts: Oracle SQL Developer equivalent of "SELECT TOP xxx ROWS" in SQL Server

texts:
In SQL Server, we have the functionality of "SELECT TOP xxx ROWS" when we right click a table:

This will generate a SELECT statement with all the columns in selected table written in the new SQL statement. Is there any similar functionality in Oracle SQL Developer?


texts: Can we prevent creation of directory NODE0000 during db2 rollforward db  end of logs with overflow log path option?

texts:
I am using command rollforward db ABCD to end of logs overflow log path ('/db2backup/logs') to apply DB2 archived logs to my staging database. My staging database is in rollforward pending state.

Execution of command is fine when instance user have write permission on path /db2backup/log and execution of command is creating an empty directory structure as NODE0000/LOGSTREAM0000/LOGSTREAM0000/. I want to prevent any write access on path and still want to use same location for log apply.

Please let me know if it's possible to prevent creation of directory structure NODE0000/LOGSTREAM0000/LOGSTREAM0000/ during execution of "rollforward db ABCD to end of logs overflow log path ('/db2backup/logs')".


texts: UI freezes when at the particular task and only when accessing from terminal machine

texts:
For some reason User Interface freezes for 7-12 seconds when double click to open a PolicyNumber to display details.
And its only happens when accessing the application from the terminal machine.
From the local server it works fine.

Interesting thing is if I keep open the same PolicyNumber right after I just close it - then it works fine, but if I wait 2-3 minutes and try to open the same PolicyNumber - then it freezes for approx 10-12 seconds.

I used SQL Profiler to track what is going on but couldn't find anything suspicious. Plus it seems like all sql statements and SP's are completed - but the UI still "thinking".

What would that be? What else should I check?


texts: Column must appear in the GROUP BY clause error even though it DOES appear there

texts:
I have a table that looks like this:

order_number | key_a | key_b | key_c | key_d | quantity
-------------|-------|-------|-------|------------------
14400        | 2     | 3     | 4     |    54 | 40
14000        | 2     | 3     | 4     |    87 | 13

I'm trying to run the following query:

select order_number,key_a,key_b,key_c,sum(quantity) from my_table
      group by (order_number,key_a,key_b,key_c);

Yet I'm getting the error:

 ERROR:   column "my_table.order_number" must appear in the GROUP BY clause or be used in an aggregate function

Since the column very clearly IS present in the GROUP BY clause,
I am very confused.

I also tried explicitly naming the column as my_table.order_number as shown in the error message, but no dice.


texts: Are existing entries immediately included into a newly created index?

texts:
Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index:

CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ASC);

and the database confirms that CREATE INDEX succeeded.

Will the newly created index immediately include all existing entries from the table or will it be slowly built "in background"? In other words, if I think that creating an index will improve performance then will I see that improvement immediately upon CREATE INDEX completion?


texts: Is it possible to alias a column based on the result of a select+where?

texts:
I have a table like this:

TB1:

COD | A001 | A002 | A003
 1    cars   baby   nasa

and then a second table:

TB2:

COD | NO_COL_TB1 | DESCRIPTION |
 1  |    A001    |  Something
 2  |    A002    |   lasagna

What I'm trying to do is something like this ( obviously this doesnt work )

select A001 as (select description from TB2 WHERE no_col_tb1= A001 )
      ,A002 AS (select description from TB2 WHERE no_col_tb1= A002 )
from TB1

i tried with inner joins and some dynamic sql but I just can't think in a logic for this. There's some questions about this but none of them could help me. I think this is impossible to do with a single statement and only possible with a Dynamic SQL.

EDIT:

The correct result would be:

SELECT COD,
       TB1.A001 AS 'Something',
       TB1.A002 AS 'Lasagna'
FROM TB1

and the result would be:

COD |SOMETHING | LASAGNA |
 1      cars       baby


texts:
Consider a table with the structure of

id            col1
WqDs.first
WqDs.second
eeRT.first
eeRT.second

How can I JOIN the table to itself using the first part of the id

SELECT idxx, a.col1 AS First, b.col1 AS Second
FROM table1 a JOIN table1 b ON ....

idxx(temporary)    First     Second
WqDs
eeRT

I tried LIKE but it works when an id exists in the id of another row. I thought to trim the first id of the first row and then find it in the second row, but there should be an easier way.


texts: Graphical Actual Execution Plan - Estimated vs Actual number of rows

texts:
I am using:

SQL Server Management Studio v18.1
SQL Server 2017 CU9
AdventureWorks restored from here

Test query:

USE [AdventureWorks];
GO
SELECT TOP (13) *
FROM Sales.SalesOrderHeader AS s
    INNER JOIN Sales.SalesOrderDetail AS d ON s.SalesOrderID = d.SalesOrderID
WHERE s.TotalDue > 1000
OPTION (RECOMPILE);
GO

Turn on Actual Execution Plan (Ctrl+M).  Looking at the 'Clustered Index Seek' I see 13 of 17 (76%).  Looking at the properties I see Estimated Number of Rows or Estimated Number of Rows to be Read value is 3.85562. My question is where is this number 17 coming from?

Actual Execution Plan


texts: Each node holds 50% of data on 2 nodes with replication_factor=1

texts:
Description: Linux OS, Cassandra 3.9, Using CCM to create 2 Nodes, Consistent Level = One

replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}

Using writing script (* ) as below (Cassandra-Python-Driver) to insert data into cluster.

from cassandra.cluster import Cluster
from cassandra import ConsistencyLevel
from cassandra.query import SimpleStatement
cluster = Cluster(['127.0.0.1','127.0.0.2'])
session=cluster.connect('luan')
count = 1
while(count < 500000):
 a=count
 b='luan'
 c='2016-01-01 01:01:01'
 query = SimpleStatement(
    "INSERT INTO t1 (a,b,c) VALUES (%s, %s, %s)", consistency_level=ConsistencyLevel.ONE)
 session.execute(query, (a,b,c))
 session.execute("INSERT INTO t2 (a,b) VALUES (%s,%s)", (1,a))
 count = count + 1

When running (* ) , getting problem when I stopped node1 OR node2 and then (* ) stopped.

  cassandra.cluster.NoHostAvailable: ('Unable to complete the operation
  against any hosts', {: Unavailable('Error
  from server: code=1000 [Unavailable exception] message="Cannot achieve
  consistency level ONE" info={'required_replicas': 1,
  'alive_replicas': 0, 'consistency': 'ONE'}',)})

I read on Cassandra Calculator, with those configurations, it said that Each node holds 50% of your data. (cluster size = 2, replication factor = 1, write level = one)

Case: replication_factor = 2, (* ) OK when I stopped node1 or node2

Question :

1/ Why replication_factor = 1, (* ) stopped when I stopped 1 in 2 nodes ?

2/ Why "Each node holds 50% of your data" as those configurations?


texts: How DB index works internally at high level?

texts:
I have around 500 GB of data in one table of MySQL which has around 5 billion records. It has around 15 columns. It has index on all the required columns. When I do select * from big_table where index_column = some_value it takes couple of minutes to return the data.

I am not sure how indexing works internally here.Here is my understanding

DB vendor will bring indexed column complete(not just data in where clause) data in memory first
Find the values(in where clause) under data fetched in step_1 to get the record row location in actual table.
Then another IO call will go to disk to get all required data based on row location fetched in step_2

Is that correct ? I am not sure on first step i.e. whether complete data of indexed column is fetched in memory or values are searched on disk itself without bringing the complete data in memory ?


texts: Changing column type - view doesn't change

texts:
I noticed that the view column data type doesn't change when I change the datatype of the underlying table. Are there any extra steps I need to take?

Here is an example:

CREATE TABLE test ( amount NUMBER(10,2) );
CREATE VIEW test_vw AS SELECT * FROM test;

SELECT table_name, column_name, data_type, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEST_VW';

ALTER TABLE test MODIFY ( amount  NUMBER(19,2) );

SELECT table_name, column_name, data_type, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEST_VW';

Both queries return the same result:

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ----------- --------- -------------- ----------
TEST_VW    AMOUNT      NUMBER                10          2


texts: MongoDb Volume alert so much iops(>10000)

texts:
I am new for MongoDB. I have setup mongodb for my production server.

I have setup alert in aws ec2 instance for iops and it gives me alert every 15 minutes that iops reached at > 10000.

I am not able to figure out the issue.

I ran mongostat, iostat but these are not showing me any critical issue for ops.

Please help me I am so worried from this issue.

Thanks in advance,

Vijay


texts: Any tips to watch out for when setting one of my databases to Read Only?

texts:
Our front end application uses\ pointed to a different database now in SQL Server 2008R2 in the same instance, so to prevent users from accidentally updating data in the old production database, is it simply just changing the Database Read Only State to "True"? As ADMIN users may still need to access it in case, to verify migrated data to the new DB matches the Old DB but not be able to modify anything.

Also, is it a good idea to change the recovery model for this old production database to Simple vs Full? and no need to run its Jobs or maintenance plans ?

In worse cases, I always can set it back to Read and Write at anytime with no issues correct? Thanks for any advice.