texts:
I have an existing MongoDB setup (part of a Graylog installation) where I'm attempting to configure a replica set with a primary, secondary and arbiter. I had the primary and arbiter configured successfully but then for some reason my primary has become the secondary and the application is unable to start now and also isn't allowing me to add in any other nodes in the replica set.

my_mongodb_0:SECONDARY> rs.status()
{
    "set" : "my_mongodb_0",
    "date" : ISODate("2019-01-02T19:50:19.291Z"),
    "myState" : 2,
    "term" : NumberLong(12),
    "heartbeatIntervalMillis" : NumberLong(2000),
    "members" : [
        {
            "_id" : 1,
            "name" : "node2:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 9182,
            "optime" : {
                "ts" : Timestamp(1545938968, 1),
                "t" : NumberLong(12)
            },
            "optimeDate" : ISODate("2018-12-27T19:29:28Z"),
            "configVersion" : 166709,
            "self" : true
        },
        {
            "_id" : 2,
            "name" : "arbiter1:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "lastHeartbeat" : ISODate("2019-01-02T19:50:18.868Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "lastHeartbeatMessage" : "Connection refused",
            "configVersion" : -1
        }
    ],
    "ok" : 1
}

...

my_mongodb_0:SECONDARY> rs.add( { host: "node1:27017", force: true } )
{
    "ok" : 0,
    "errmsg" : "replSetReconfig should only be run on PRIMARY, but my state is SECONDARY; use the \"force\" argument to override",
    "code" : 10107
}

Ideally I'd like to add the other node but with the state that it's in now not sure if I can still do this or if I have to revert it to a stand-alone setup first and configure it again. I know the primary election can't occur now and I haven't found a way to force the primary but just wanted to see if anyone else had any ideas.

I'm guessing now it matters what order you add the DB servers and the arbiter but I haven't been able to find a definitive answer to this...


texts:
When I was connecting to my database through SQL Developer tool I was getting error message as below:

ORA-00257, 00000, "archiver error. Connect internal only, until freed."
Cause:  The archiver process received an error while trying to archive
  a redo log.  If the problem is not resolved soon, the database
  will stop executing transactions. The most likely cause of this
  message is the destination device is out of space to store the
  redo log file.
Action:  Check archiver trace file for a detailed description
  of the problem. Also verify that the
  device specified in the initialization parameter
  ARCHIVE_LOG_DEST is set up properly for archiving.

So I archive the log files by,

 alter system archive log all;

Still the same issue was there. So I checked the space using below commands:

SQL>  select name, round(space_limit/1048576),round(space_used/1048576),
round((
round(space_used/1048576)*100)/(round(space_limit/1048576))) pct_used
2  from  v$RECOVERY_FILE_DEST;
NAME
--------------------------------------------------------------------------------
ROUND(SPACE_LIMIT/1048576) ROUND(SPACE_USED/1048576)   PCT_USED
-------------------------- ------------------------- ----------
C:\app\oracle\fast_recovery_area
                    104448                     63931         61

Lots of space are still available and I checked the below command also:

SQL> show parameter log_archive;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=
(jordan_local,india_
                                             local)
log_archive_dest                     string
log_archive_dest_1                   string      location=c:\app\oracle\f
                                              ast_recovery_area\local\ARCHIV
                                                 ELOG\ valid_for=(all_logfiles,
                                                 all_roles) db_unique_name=loca
                                             l
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      SERVICE=local LGWR SYNC db_uni
                                                 que_name=india_local

I checked archive log list too:

SQL> archive log list;

Database log mode                     Archive Mode

Automatic archival                    Enabled

Archive destination                   %ORACLE_HOME%\RDBMS

Oldest online log sequence            10655

Next log sequence to archive          10655

Current log sequence                  10657

What could be the reason for the error?


texts: Does column size increase break SELECT *?

texts:
I searched a little bit on this topic before realizing it is somewhat tricky (for me, at least) to come up with an effective search phrase for it because it overlaps with other common topics.

So here is my question spelled out:

Suppose I have a varchar column that is going to be altered (increased in size).

And I also know that there is code (I know this is bad practice! but that code is what it is at this stage - lobbying developers to change it is a later step) that uses SELECT *.

Will the altered column break queries that use SELECT *, or will that code continue to function?

Thanks in advance for any help.

jrdevdba


texts: Can I share a MySQL database among multiple users exclusively?

texts:
This can be a silly/invalid question but I am going to ask it anyway. I want to setup a testing environment where many developers can run their functional test against a single common database.

Now, how can I ensure that the developers don't mess up with the test data when they run their test simultaneously? Is there a way to partition the db based on the user accessing it? Partitioning may be the wrong word here but I think you get what I mean.


texts: Is there any way you can tell MySQL to set value to 0 if value for unsigned column is < 0?

texts:
I have a column in MySQL that is set as unsigned; however because I do calculations on this column within MySQL it's possible (though unlikely) that we could try and set this column to a value < 0.

In this case I just want it to set it to 0 rather than try and set it to a negative value and hence cause an error.

An example would be:

UPDATE my_table SET comments=comments-3 WHERE something=123;

Is there a simple way to do this?


texts: What is the proper way to store a full table of data outside of a database? It needs to be able to be restored as well

texts:
I have a large SQL Server 2005 database that hasn't had the best maintenance over the years (I just took it over recently).  There are around 100 tables that can be completely removed from the database, as well as some that just need data after a certain point removed.

My employer wants to keep the data, but not in a database, and wants to be able to restore it to that database if the need arises.

The smaller tables could of course be exported as Excel files, but many are larger than xls or xlsx would allow.  I also thought of storing the data as .txt, .sql or .csv files, but I want to be certain that I'm doing this properly and won't end up with corrupted tables that can't be restored.

As far as querying the tables for the data, I can handle that. So my main question is, how do you properly store a table of data outside of a database?

Thanks,
Jamie


texts:
I am write a SQL query to replicate below Excel result.

In Column A I have 252 values in ASC order from 10/30/2018 to 10/29/2019
In Column BV I have respective PL Number
I want to calculate TestPL column having value as Ignore first 3 PL Values and 4th Cell would be addition of B2:B5, 5th Cell would be B3:B6 and so on for all 251 values

TestPL should start from row 4 and should have sum of cells B2:B5, and row 5 would be  B3:B6 and so on in a stream till row 252 which would B249:B252


texts: Optimize wildcard searching for a NVARCHAR(MAX) column

texts:
I have been asked to improve the searching capabilities of a LIKE '%SOMETEXT%' predicate on an NVARCHAR(MAX) column. I am aware of full text indexes but am not sure if they are the best strategy.

A traditional rowstore index obviously wouldn't do the job given that NVARCHAR(MAX) isn't an acceptable index key value and also I'd be searching using (LIKE '%SOME TEXT%') so I wouldn't be able to write a sargable statement.

Is a full text index the best option for this case?


texts:
I am trying to bulk insert data from a file to MS SQL Server using the bcp utility but it is not functioning as I expect it to with regard to errors. I want to understand what I'm doing incorrectly or where my assumptions are incorrect.

With the below command:

bcp mydatabase.dbo.mytable IN "c:\infile.txt" -f "c:\infile.fmt" -T -m0 -S myserver -e "c:\errors.txt"

I expect bcp to include all records in the file in a single batch and to fail after encountering the first error and to report the errors in the error file. It however completes by importing as many records as possible through the whole file. It doesn't stop or roll-back any transaction.

If I change the -m option to -m 1, then it does fail after the first record, but does not roll back the batch at all (e.g. the records in the batch imported prior to the error are persisted in the import table after the bcp finishes).

The documentation of bcp appears to indicate that the batch would be rolled back on encountering the first error, and that the batch will include all records in the file if no batch size is specified.

The bcp utility version is 13.0.1601.5.

from:Microsoft BCP Utility Documentation

  -b batch_size

  Specifies the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that
  imports the whole batch before being committed. By default, all the
  rows in the data file are imported as one batch. To distribute the
  rows among multiple batches, specify a batch_size that is smaller than
  the number of rows in the data file. If the transaction for any batch
  fails, only insertions from the current batch are rolled back. Batches
  already imported by committed transactions are unaffected by a later
  failure.

and

  -m max_errors

  Specifies the maximum number of syntax errors that can occur before the bcp operation is canceled. A syntax error implies a
  data conversion error to the target data type. The max_errors total
  excludes any errors that can be detected only at the server, such as
  constraint violations.

  A row that cannot be copied by the bcp utility is ignored and is
  counted as one error. If this option is not included, the default is
  10.


texts: Median absolute deviation for time series outlier detection in Amazon Redshift

texts:
Context

I am tasked with trying to detect outliers in time series data in an Amazon Redshift (PostgreSQL) system. Also known as a public holiday detector around the office. The method I have been using takes a windowed average of the previous N data points, I have also a windowed Standard Deviation and then take those stats about the N prior points and apply the following to the current datapoint:

(x0 - avg(x1:xN)) / stddev(x1:xN) > threshold

So window length and threshold have been sufficient to play with but this method is not a robust method since we have had significant growth after an advertising campaign then the series is straying hugely away from the running average and standard deviation threshold and everything is an outlier.

Reducing the window length allows it to adapt to these changes faster but it doesn't have as good of a long term model.

Increasing the threshold for this sort of growth would mean previous outliers we detected will no longer get detected.

These related questions provide suggestions in R but the answer quite often mention Median Absolute Deviation as a robust method:

https://stats.stackexchange.com/q/1142/90134
https://stats.stackexchange.com/q/104882/90134

https://en.wikipedia.org/wiki/Median_absolute_deviation

tl;dr

  How do I implement Median Absolute Deviation on a times series data set in Amazon Redshift?

I'm not sure if I'm missing something fundamental about the method but I'd like it to only work on a window and not have it work on the entire data set. Although the Median Window functions do not allow a frame clause.

If not this method, then point me in the right direction of more sophisticated outlier detection queries in Amazon Redshift would be appreciated.


texts: Does SQL/MED (ISO/IEC 9075-9) defines a writable foreign data wrapper interface

texts:
I just can't find anything about it.

I don't have the paper or draft of the SQL:2011, and have almost only drafts from SQL:2008, 2006, 2003.

Does anyone know if "SQL/MED (ISO/IEC 9075-9)" defines a writable foreign data wrapper interface?


texts: Table cache hit rate is 0% - is that a huge concern?

texts:
Hi all of a sudden my server has slowed to a crawl, queries that were 0.10 of a second consistently now taking 20 seconds.  somewhat randomly, sometimes it snaps right back.  so I have a really sudden and harsh performance problem.

I ran mysqltuner.pl and it reported one thing that might be a problem :

[!!] Table cache hit rate: 0% (400 open / 1M opened)

I am just starting to delve into this performance problem, but does anyone know if this a big immediate red flag?

I can do some DD but am pressed right now.  any help much appreciated.

Don


texts: Two facts, two dimensions, one query, one cube, can it be done and how?

texts:
I have four tables, two Facts and two Dimensions. Below I've listed the relevant columns of these tables, and the results I would like to see in the Cube.

Fact Order table
----------------
SK_Order
FirstEverOrder

Dim Order
---------
SK_Order

Fact Product Order
------------------
SK_Order
SK_Product
Number of Products

Dim Product
-----------
SK_Product
Product Name

In the results I would like to see the First Ever Order Rate. That is to say, out of all the times a product appears on an order, how many of those orders were the first ever order the customer placed?

Products      First Ever Order Rate (%)
--------      --------------------------
Product 1     3%
Product 2     2%

An example situation for the use of this report might be that you want to advertise those products that appear frequently on first orders.

So far I have my tables related together in the cube as follows:

Please, I do want to do this in the cube. I know it can be done via a stored proc with an SSRS report, but I want to do it in the cube.

I don't know what to do on either the Calculations or the Dimension Usage relationships tabs, or if I need to do anything more in the Cube Structure tab. Please be detailed in your answer.


texts: Best way to check if record exists or not in MySQL

texts:
I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.

For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .

Query goes something like this inside procedure

set @cnt = (select count(*) from table where date_field between date1 and date2) ;
if @cnt > 0 then
    select * from table where date_field between date1 and date2 ;
else
    select * from table order by date_field desc limit 0,20 ;
end if ;

Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.


texts:
I hope someone can tell me what I'm doing wrong in this query. I'm unable to find the cause of the error.

I have 2 tables with 2 columns each, see the CREATE statements here:

CREATE TABLE TableA(datum DECIMAL(8,0), colA INTEGER);
CREATE TABLE TableB(datum DECIMAL(8,0), colB INTEGER);

INSERT INTO TableA values('20160104', '1');
INSERT INTO TableA values('20160101', '2');
INSERT INTO TableA values('20160102', '3');
INSERT INTO TableA values('20160105', '2');
INSERT INTO TableA values('20160102', '6');
INSERT INTO TableA values('20160105', '4');

INSERT INTO TableB values('20160107', '5');
INSERT INTO TableB values('20160103', '8');
INSERT INTO TableB values('20160107', '2');
INSERT INTO TableB values('20160101', '1');
INSERT INTO TableB values('20160101', '4');
INSERT INTO TableB values('20160105', '3');

What I'm trying to achieve is to get a result with all the dates and the sum of the columns for that date.
The query that I use is the following:

select datum, sum(resColA), sum(resColB)
from
    select datum, sum(colA) as resColA, 0 as resColB
    from TableA
    group by datum
union
    select datum, 0 as resColA, sum(colB) as resColB
    from TableB
    group by datum
group by datum
order by datum

It keeps telling me:

  [SQL0104] Token ( is invalid. Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

Both legs of the union work fine as separate SELECT statements, but when I put them together, the whole thing doesn't work any more.

Does anyone have an idea what I'm doing wrong? I can't figure it out.


texts:
I'm executing the following command in a shell script and I'm executing this on an RHEL-6 server and MySQL version 5.1.47-

mysqld_safe --defaults-extra-file=/custom/path/of/my.cnf

The path of my.cnf can be anything. (If I am not wrong, as a developer we cannot determine where mysql could be installed.) After the above command in the script, there are a few mysql commands to be executed. When the script executes the above command, the output console displays that the mysql service has successfully started. But when the control comes to the part where the mysql commands are to be executed, the following error message comes up-

mysqld dead but subsys locked

Any idea where I am going wrong?

This is what my my.cnf contains-

[mysqld]
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_file_io_threads=4
max_binlog_size=20M
max_allowed_packet=16M
max_connections=500
log_bin=logFile.log
binlog-do-db=test
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog-format=ROW
log_warnings=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


texts: Database upgrade script for removing columns

texts:
So I have a SQL database project which tracks our application's database schemas, stored procedures, etc. It also has deployment script generation sections, including pre- and post-deployment scripts. Part of these scripts includes doing things like caching values of columns that have been removed so they can be moved elsewhere (usually normalized into its own table).

The problem is that the upgrade script process I have implemented, which automatically detects the version and what updates need to be applied at deploy-time, includes references to columns which no longer exist. This is fine when deploying to either an empty database (so the script executor doesn't know there's no column) or to a being-upgraded database, as it can see the column before the deployment. The script validator however doesn't like that I'm referencing columns from a table that it knows about, even if the section which references it will never get called because of the conditions around it.

My current solution is to store in a string all of the parts of the script which handle columns being removed, then running those sections dynamically using EXEC. This is a little icky to me (and EXEC is a code smell issue), though not the worst thing. This is code that's only run once per DB and is never accessible during production operation.

What I want to know is, is there a better way of doing this? Can I somehow comment to the validator that "This won't run, please ignore it"? Is this whole process terrible? (I'm not that experienced of a DB admin at all, so any expertise here would be very helpful.) If I'm doing this all wrong, how do you handle one-off scripts for database updates? I've also considered doing one-off conditional compilations using SQLCMD declarations/labels, then automatically removing sections that aren't needed for a particular version-to-version upgrade using a script.

As a last note, we are using SQLPACKAGE to generate our upgrade scripts. Is there possibly a way to tell the tool to omit some sections in certain cases?


texts: How to ensure that only unique row combinations are present?

texts:
I have three tables which can be called set, item and assignment. The assignment table assigns an item to a set, thereby my goal is to ensure that each combination of items is unique. Hence, if item A and item B form set 1, there is no second set allowed which is formed by item A and item B. A combination of item A, item B and item C, however, is distinct and hence ok.

My original approach was using a hash with a unique constraint for each set which is formed by the IDs of the associated items. This hash would then be updated by a foreign table trigger on each insert/update operation on the assignment table. However, I failed in implementing such an update function and don't even know whether it's possible or not.

Does anybody know whether this is the way to go or whether there's an easier solution to my problem?


texts: How to add a new listener on a new port and restrict it to one instance only

texts:
I am running oracle 11.2.3 and we have a server with multiple instances running. All of them are registered to port 1521 with the default listener. Now we are required to make one instance available on a new port. I was wondering if there is a way to add a new listener to a new port and restrict it to register only one instance so that this listener can not make connections for other instances.
Current Listener Configuration:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
    )
  )


texts: How to create a database with "polymorphism"

texts:
I'm sorry I'm new when it comes to databases, I'm using Access right now to develop a database to store calibration data for a bunch of different types of transducers.  Each type of item is fundamentally different from the other so I'm planning to make a table for each item type, but across the board they all must have a unique ID number (so it can be related to one or many calibrations).  Here is an example in rusty SQL, hopefully it makes sense.

create table accelerometer (GAC_id, name, range, serial)
create table loadcell (GAC_id, name, description)
create table potentiometer (GAC_id, product_id, tag_id)

As you can tell the 3 tables have different columns but across the 3 tables there MUST be a UNIQUE GAC_id number, something that no matter what table there is only one GAC_id in the entire database.

One idea is if I were to create a 'master table' that had a GAC_id and a part type then would have a relationship to the appropriate sub-category part id but I don't think you can create a 'variable' relationship, can you? Following the example above...

create table Part (GAC_ID, part_type, accelerometer_ID/loadcell_ID/potentiometer_ID)

... where it chooses the third column based on the value in the second column

Anyway really need help here, thanks


texts: How can I give a group of developers the ability to view the query plan, without the ability to run a trace or to view the server state?

texts:
I have a group of 5 developers that need to be able to view the plans of queries they have written for a database, and that database only.  I refuse to make them a member of sysadmin.  I do not want them to be able to run a trace, ever, on this server, nor do I want them to be able to run dmv's.

These 5 developers are members of a windows group.  I have made them a member of db_owner.

This is what I have found:

A server principal with DENY for server permission ALTER TRACE will encounter Error 262 when attempting to view SHOWPLAN output
This happens even if the database principal has a GRANT for SHOWPLAN database permission
This happens because SHOWPLAN permission is implied by ALTER TRACE permission
This does not happen if the database principal is the database owner
A server principal with GRANT for server permission ALTER TRACE will be able to get SHOWPLAN output without needing to explicitly granting SHOWPLAN for individual databases

Only sysadmin and if you are mapped to the dbo user name within the db, does it not perform the check.

Is there a way to give these developers the ability to view plans without the role of sysadmin or granting alter trace or granting view server state?


texts: find the .cnf file used by the running mysql instance

texts:
I wanted tho know the .cnf file read by my running mysql instance. I used to look at ps command.

ps -ef | grep mysql

10801 ?        Ssl    0:27 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock

But in redhat 7 and 8 i am not able to see a detailed output.

ps ax | grep mysqld

798 ?        Sl     0:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Is there a way to get details via ps command or any other way to check which conf file is being used by my mysql instance.

Thanks in advance


texts:
How can we drop a constraint without losing the automatically created index?

If it's possible using a single query.


texts: How do I find the error at a line number in a Oracle Live SQL script?

texts:
When someone runs an Oracle Live SQL script, each statement is executed and the final result (or errors) are displayed below each statement. However, line number errors create a bit of an issue:

The line number states is 77, however this is the 157th statement, hence one does not know what line 77 refers to.  Where can a user find a specific line number in Oracle Live SQL?


texts: How can I get all data from a given table since my last run?

texts:
In querying a table in Oracle Database, is there any way to reliably and precisely get all rows that have changed (via inserts or updates) since the last time I checked?
My tables typically have a column update_date DATE that holds the date/time last updated, and I am currently using that.  I'm finding this isn't fully reliable, as some other stored procedure could insert data with this column set properly via SYSDATE, then commit the transaction a few seconds later when it completes.  If I ran a query after the DATE column was written but before the transaction was committed, my query will contain rows with later update_date values (by a second or two, usually) but I won't see the newest rows that have a short delay in being committed.
In other words, suppose in  my first run I SELECT all rows, and the latest row I get has an update_date of '2021-01-15 08:01:02'. Then my job keeps track of that value, and on my second run, the job now naively uses WHERE update_date > :latest_changed_date_from_last_run, but such a query won't include data from '2021-01-15 08:01:02' that was committed after my last run, nor even perhaps data from '2021-01-15 08:01:01' or '2021-01-15 08:01:00'--or really any past DATE--that was written before but committed after my previous run.
(I'm not having trouble storing or retrieving this "latest_changed_date_from_last_run" value, but there seems to be no way to use it to get all the data I need. I can only get most of it.)
So is there any way to ensure I can get all rows changed since my last run?  If there's no way to do it with a SELECT query, is there some other kind of Change-Data-Capture-lite technique I should be considering for this simple scenario?
(More context: This is in trying to use a Kafka Connect JdbcSourceConnector with an ordinary query, but I imagine this concept could be needed in any system that periodically checks for any updates/inserts in any table.)


texts: Implementing a one to zero or one relationship

texts:
Suposse I am using a SQL platform and want to implement a relationship of cardinality one-to-zero-or-one (1-0..1). This situation would pertain to a business context in which let us say that:

A product can have zero or one price.

So:

What are the differences between the two scenarios (DDL structures) shown bellow?
When should one be preferred over the other?
Is any of them considered a bad design?

Scenario 1:

CREATE TABLE product
(
    product_id       INT      NOT NULL,
    name             CHAR(30) NULL,
    product_price_id INT      NULL,
    CONSTRAINT PK_product       PRIMARY KEY (product_id),
    CONSTRAINT UQ_product_price UNIQUE      (product_price_id)
);

CREATE TABLE product_price
(
    product_price_id INT     NOT NULL,
    price            DECIMAL NOT NULL,
    tax_rate         DECIMAL NOT NULL,
    currency_id      INT     NOT NULL,
    CONSTRAINT PK_product_price            PRIMARY KEY (product_price_id),
    CONSTRAINT FK_product_price_to_product FOREIGN KEY (product_price_id)
        REFERENCES product (product_id)
);

Scenario 2:

CREATE TABLE product
(
    product_id INT      NOT NULL,
    name       CHAR(30) NULL,
    CONSTRAINT PK_product PRIMARY KEY (product_id)
);

CREATE TABLE product_price
(
    product_id  INT     NOT NULL, -- Assume that this column might be set as PRIMARY KEY or UNIQUE.
    price       DECIMAL NOT NULL,
    tax_rate    DECIMAL NOT NULL,
    currency_id INT     NOT NULL,
    CONSTRAINT PK_product_price            PRIMARY KEY (product_id),
    CONSTRAINT FK_product_price_to_product FOREIGN KEY (product_id)
        REFERENCES product (product_id)
);


texts: Access Database that is shared with my company on a network drive..When I copy and paste it to my desktop there linked some how?

texts:
I have an Access Database that is shared with my company on a network drive..When I Click on the drive and copy and paste it to my desktop and make changes to it the database that is on the drive also gets the changes but I dont want to change that one yet.

When I change data in the database on my desktop it changes the database on the drive?
I have tried changing the name, I have checked the source file, and I also checked to see if the program had some kind of link but still can not find any solution?


texts:
i have an Oracle hierarchical query that looks like it will answer what i need. The issue i see with it is that the parent 187091 is not shown in the LEVEL_SAMPLE_NUMBER field.

How would I modify this query to allow the parent 187091 to show in the LEVEL_SAMPLE_NUMBER field?


texts:
I have an odd situation where I have a column called (TransmittalNumber) with datatype of VarChar (75) in a table named (Transmittal Sheet).

I want to query this table to find the transmittal numbers that are duplicated or the ones that starts with the same numbers but ends with a suffix of –W for (West division). Please check the TS# column below:

TSID    TS #    Facility
1    1234       Arizona
2    1234 -W      National
3    2222       Pacif.NW
4    2222 -W      National
5    4444       California
6    4444 -W      National
7    5555        Arizona
8    5555        Arizona

Would something like the following query works?:

declare @TSNumber varchar(75)

set @TSNumber = (select transmittalnumber from TransmittalSheet
)
-   Subquery above returned more than 1 value even with using the IN!
SELECT     TransmittalID, TransmittalNumber, Facility
FROM         TransmittalSheet ts
where  ts.TransmittalNumber like (@TSNumber)

union all

SELECT     TransmittalID, TransmittalNumber, Facility
FROM         TransmittalSheet ts1
where ts1.transmittalnumber like (@TSNumber + '-w')

Prior to 2014, the numbers entered to this column were only added from local regions. Now all TS numbers are entered from a National side, so the older similar local TSs will have –W in the new national entry after a migrate.

The reason of my query now is to clean up the table from the local ones that still have a current active entry in the national.

I've tried a self join with no luck still, any advice?

SELECT ts.TransmittalNumber from TransmittalSheet ts
Full Join TransmittalSheet ts2
ON ts.TransmittalID = ts2.TransmittalID
-- AND
WHERE ts.TransmittalNumber = ts2.TransmittalNumber + ' -W'

I’m using the SQL Server 2008R2


texts: Disable mysql general query log for specific user (or schema)?

texts:
MySQL can log all queries in what is called "General Log"

Is there a way to keep this query log but skip all queries for a specific user? Or maybe for a specific database?

I would like so because I have a database that one user connects to (application user), so I want to have the query log to make debug easier in staging area, but I also have a daemon that connects to this same database with another user, and daemon issues like 1 query per second. So my query logs are 80% filled by this daemon's query so I would like to skip them to make it easier to find app user's queries.


texts: Building a where clause based on user selections

texts:
My group and I are developing a multi agent system (MAS), a system that is supposed to conduct statistics on specific fields of the agents' databases using semantic search and ontology (the user chooses the fields to be considered).

For example, the user might want to know the number of people stored in the database that are male, over 20 years old, lives in NYC, blood type: A, and are single.

So I was wondering if there is a way to write the query in MySQL(MySQL 5.7.10.0) that returns the number of matching tuples (matches the user's selection and conditions) in the database, or if the jsc.jar in JAVA (java statistical classes) or any other JAVA jar can perform the required operation. I know that I should use the "count(*)" in the query, but I'm not sure how to build the "where" part of the query (that relies on the user's selection of fields) at runtime.

I'm looking for a way that a query builds itself (to be specific the "where" part) according to what the user chooses during runtime. Is it possible?


texts:
I have a multi statement table value UDF. That builds a temp table then returns it as the result.

The first time its executed it takes 5 seconds, subsequent executions with the same parameters take 1 second. If I change the parameters it then takes 5 seconds the first time then it goes back to 1 second. If I then change the parameters back to the first ones it still takes one second.

I cant work out what's causing it to take longer the first time a new parameter is used. Do UDF's compile a separate query plan each time different parameters are used? Can I monitor this in Profiler? I can see the sp_Recompile event but I'm not sure what event is fired when a UDF compiles.

Unfortunately I cant post the code but am hoping there is enough information for someone to give me some sort of explanation.


texts:
i have a big postgres db with single table for product and attribute but i want two view of one with product and one with attribute.

for example now i have

tb_products

iditem | name | attributes
-----------------------
1      | art1 | a
2      | art1 | b
3      | art1 | c
4      | art1 | d

what i need is

vs_product

iditem | name
-----------------------
1      | art1

vs_attrib

iditem |  attributes
-----------------------
1      | a
1      | b
1      | c
1      | d

what i have tried is something like this and it seem to work, but is too slow when extractiong attributes

vs_product

SELECT
Min(.tb_products."id") AS "Product ID",
FROM
tb_products
GROUP BY
tb_products.item

vs_attrib

SELECT
a1."id" AS "oldid",
(
SELECT MIN
    ( a2."id" )
FROM
    tb_products a2
WHERE
    a1.name = a2.name
GROUP BY
    a1.name
) AS "newid"
FROM
tb_products AS a1

any ideas?


texts: DB2 9.5 How to get tablespace monitor information?

texts:
How can I get information about tablespace in DB2 9.5?

I need to review information about if the autoresize option is enabled or disabled.


texts: Designing a database for Reading Comprehension

texts:
So I am building an RC app that will require me to fetch data.
I have data in Excel sheet and I would like to load the data in sqlite database. But I am having trouble designing the db.

A little background on the raw_data:

Passage Sheet
P_id,Passage

Questions Sheet
P_id, Q1,Q2,Q3....Q10

Options Sheet
P_id, ?????

Now here in the options sheet I am confused because I have like 200 passages and each passage has around 6-10 questions.

All questions are multiple choice with 5 options.

Can anybody help me in organising my questions_sheet and options_sheet so that I can create a database.


texts: Get percentage free space on database volumes

texts:
I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring.

However, this only gives me an absolute number of MB free. What I really need is percentage free.

Is there another way in SQL Server 2005 to get this?  If not, is there some other light-weight way to get it?  If I can, I want to avoid installing a Java JRE, or Perl, or Python on my database server. Perhaps vbscript, or a small Windows executable on the file system?

Yes, I know I can Google this, and I have.  It looks like there are a few ways to accomplish it, and I'm curious how my DBA brethren have handled this.


texts: How does a integer data record store in SQL Server page

texts:
I have seen the 8KB (8192 = 96 Header + 36 Row Offset + 8060 Free space) page architecture in SQL Server. When it comes to storing a data record in Page i am confused.

In Below table i have create Integer for column ID it should take 4 Bytes but each record size/length is showing in DBCC PAGE Command 11 Bytes.

I have created a simple table as below:

CREATE TABLE PAGETEST
(
    ID int primary key
)
GO
INSERT INTO PAGETEST values (200)

Q 1). Why a simple integer data is taking 4 actual bytes of data + 7 Bytes extra = 11 Bytes.
Q 2). Can anybody explain how does a record store in a page.

Kindly find the images below:


texts: Should future time be stored in UTC or local time?

texts:
Clearly in most cases time should be store in UTC. And in all cases a datetime that records a past event should be stored in UTC. We do this because civil time keeps changing and an event happened at a moment that's detached from whatever the local government thinks the time is.

But when times are detached from dates, when they refer to wall-clock times, I contend that they should be stored in wall-clock time.

Let's say I have a system that has to perform an event at 3pm in a particular time zone, I contend that I'm best to store the time as 3pm. This is even more true when the event is recurring and should always occur at 3pm -- whether daylight savings is in effect or not. And if it should occur for multiple users in various time zones at 3pm-local, storing anything in UTC just seems silly.

To me, this feels obvious. But I'm looking for a standard, a specification, or at least a blog post from a trusted source that can back me up.


texts: What do the HT* wait types mean? (HTREPARTITION, HTDELETE, ...)

texts:
A big query is running right now. The result of a batch mode hash join that spills to disk is streamed into a temp table using select into. The query is showing wait types HTDELETE and HTREPARTITION occasionally. The query is not using columnstore indexes.

I'm quite sure that these wait types are normal for batch mode hash joins that spill to disk. I'm trying to understand what these wait types mean out of curiosity. Maybe this insight can help optimize the query or find problems. So what do these wait types mean and under what circumstances are they expected?

No columnstore indexes. I'm using the left join ZeroRowCsTable on 0=1 trick to enable batch mode for row mode tables.


texts: I have lots of free memory.  How do I use it to increase performance?

texts:
I am running Litespeed (with suExec, PHP SAPI), MySQL, DirectAdmin, named on a VPS with a 16-core Xeon CPU and 2GB RAM.

Currently, only 300 MB out of 2048 are used. It might sound a stupid, but how do i make the vps use more RAM?

my.cnf:

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

[mysqld]
port                                            = 3306
socket                                          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer                                      = 512M
max_allowed_packet                              = 8M
table_cache                                     = 4096
sort_buffer_size                                = 16M
read_buffer_size                                = 8M
join_buffer_size                                = 2M
read_rnd_buffer_size                    = 32M
myisam_sort_buffer_size                 = 128M
myisam_repair_threads                   = 1
myisam_recover
max_heap_table_size                             = 8M
tmp_table_size                                  = 8M
thread_cache_size                               = 8
thread_concurrency                              = 8
query-cache-type                                = 1
query_cache_limit                               = 4M
query-cache-size                                = 16M

log-slow-queries=/var/log/mysqlslowqueries.log
local-infile = 0
wait_timeout = 10
interactive_timeout = 15
max_connections = 35

character-set-server            = utf8
default-character-set           = utf8

skip-networking
skip-federated
skip-symbolic-links

skip-innodb
[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqld_safe]
open-files-limit = 8192

I actually do need to increase MySQL performance because it's the most cpu-consumming process and there are queries that can't be optimized but take some time to execute.

Any suggestions on how to efficiently use memory to increase performance would be appreciated.


texts: automating a database backup, restore and stored proc call

texts:
How do I create a process that will:

backup Database A restore it as Database X (needs to be definable)
then run a stored procedure against Database X.
shrink and truncate its log file.

Anybody got ideas?  Could it be a stored proc on the Master db?

The whole process takes hours and is taking me away from valuable time.


texts: In my database apart from storing the daily backup, the disks are getting used for other purposes also. So how can I do the disk mirroring for Split Mirror Backup if I don't use external storage?


texts: Insert the same time, it is possible to happen?

texts:
Good evening.

I have a doubt about inclusion in the database. A common example is a user account where the user names must be unique.

In the following logic:

User enters data into a form.
Data are collected via $ _POST (or other method)
Before saving the data is checked if the user name is already registered
If not, save the record, if it is, it informs the user that the username that already exists.

Example:

<?php
$username = $_POST['username'];
$query = "SELECT * FROM `user_tbl` WHERE `username` = '{$username}'";
$result = mysql_query($query);
if ( mysql_num_rows ( $result ) > 1 ) {
    /* Username already exists */
    echo 'Username already exists';
} else {
    /* Username doesn't exist */
    /* a certain time is elapsed after checking */
    /* .. insert query */
}

My question is, in a system with high volume of requests, it is possible that between the time to check whether the user name already exists and the inclusion (if the user name does not exist), another user can do the same action in same time?


texts: How can I set a constant number as the lowest number in a column?

texts:
Here is my query?

UPDATE users
SET reputation = reputation - (CASE WHEN id = 29 THEN 2
                                    WHEN id = 30 AND NOT 0 THEN 15
                                    ELSE 0
                               END)
WHERE id IN (30, 29);

Sometimes it throws this error:

  #1690 - BIGINT UNSIGNED value is out of range in '(spy.users.reputation - (case when (spy.users.id = 29) then 2 when ((spy.users.id = 30) and (not(0))) then 15 else 0 end))'

As you see in the error message, reputation column is UNSIGNED, and I need to keep it 1 at least .. something exactly like stackoverflow: if a user has 1 rep and he gets a downvote, his rep will still remain 1. Well how can I fix that update query to prevent failing in such a case?

Noted that using greatest(): doesn't make any different. I mean this won't work either:

UPDATE users
    SET reputation = GREATEST(reputation -
                               (CASE WHEN id = 29 THEN 2 WHEN id = 30 AND NOT 0 THEN 15
                                     ELSE 0 END), 0)
WHERE id IN (30, 29);


texts: How Much Resources Does the BLACKHOLE Storage Engine Consume？

texts:
I'm going to add a MySQL Relay Slave which use BLACKHOLE Storage Engine.

The relay slave will just filter some databases binlog to downstream slave.

Our MySQL Master generates about 15G a day in binlogs.

How much CPU and Memory will be enough for this use case?


texts: Oracle's left join and where clauses errors

texts:
CREATE TABLE "ATABLE1"
  (
    "COLUMN1" VARCHAR2(20 BYTE),
    "COLUMN2" VARCHAR2(20 BYTE)
  );

CREATE TABLE "ATABLE2"
  (
    "COLUMN1" VARCHAR2(20 BYTE),
    "COLUMN2" VARCHAR2(20 BYTE)
  );

Insert into ATABLE1 (COLUMN1,COLUMN2) values ('A','1');
Insert into ATABLE1 (COLUMN1,COLUMN2) values ('B','2');

Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A',null);
Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A','1');
Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A','2');

select ATABLE1.column1, count(ATABLE2.column1)
    from ATABLE1 Left OUTER JOIN ATABLE2 on ATABLE1.column1 = atable2.column1
    GROUP BY ATABLE1.column1;

Result

COLUMN1              COUNT(ATABLE2.COLUMN1)
-------------------- ----------------------
A                    3
B                    0

This works as expected. The thing is that I always want all rows from ATABLE1 to be shown and also apply some restrictions.

select ATABLE1.column1, count(ATABLE2.column1)
    from ATABLE1 Left OUTER JOIN ATABLE2 on ATABLE1.column1 = atable2.column1
    where atable2.column2 = '1'
    GROUP BY ATABLE1.column1;

COLUMN1              COUNT(ATABLE2.COLUMN1)
-------------------- ----------------------
A                    1

Why are not all columns from ATABLE1 being shown even with left join? How can I make them appear?

Thanks a lot in advance.


texts: Brand new postgres setup - what to check first?

texts:
My sys ops team have recently handed over a brand new set of postgres clusters (prod, stage, test etc). We don't have any DBAs and they are under resourced. Mistakes in config have been found before. I'm not a DBA but I understand database concepts and I am happy to do some learning in my own time.

My principle concern is whether I am getting the expected level of performance given the hardware deployed (its all on prem).

I know that different applications will see different performance based on their queries and the stats of the dataset etc, but at least I would like to verify that the hardware, OS and postgres have been correctly installed and configured.

Where should I start? Ideally I could run some perf tests and at least verify that all the instances perform similarly. Is there a set of standard performance tests that I could run?


texts: How to overwrite csv file of MySQL result set?

texts:
My SQL query is given below,

select company.Company_name as COMPANY_NAME,
IFNULL(followup.contact_date, ' ') AS FOLLOW_UP_DATE,
IFNULL((select Full_name from user where user_id = followup.user_id), ' ') AS Employee,
IFNULL(fix_appoinment_order.appointment_dt, ' ') AS APPOINTMENT_DATE,
IFNULL((select Full_name from user where user_id = fix_appoinment_order.user_tme), ' ') as TELEMARKETING_EXECUTIVE,
IFNULL((select Full_name from user where user_id = fix_appoinment_order.user_me), ' ') as MARKETING_EXECUTIVE
from company_order
inner join company on company.Company_id = company_order.Company_id
left join company_history on company_order.Compordr_id = company_history.compordr_id
left join followup on followup.followup_id = company_history.followup_id
left join fix_appoinment_order on fix_appoinment_order.fix_appointment_order_id = company_history.fix_appointment_order_id
where
followup.contact_date = date_format(now() + INTERVAL 1 DAY, "%Y-%m-%d") OR
fix_appoinment_order.appointment_dt = date_format(now() + INTERVAL 1 DAY, "%Y-%m-%d")
INTO OUTFILE '/var/www/backup/AppandFllups.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I know that, the query working fine, I have to set the cron job to get the result set as csv file and send email.

At first time when I execute the query, there is no issue, but next time I got the error as follows,

Error Code: 1086. File '/var/www/backup/AppandFllups.csv' already exists

I understood, the file is already created while I run at first time, but I am unable solve this issue. I used another cron job to fix this issue but it I am not satisfied, is there anyway to do achieve this?


texts: Incorrect EST Timezone instead of EDT in Oracle

texts:
I am not getting correct timezone in Oracle Database. The issue is happening only starting 2021. Please see two same queries - first one for year 2020 and second for year 2021. The first one is correctly giving the timezone as EDT; the second one is incorrectly giving EST. How to fix this?

SELECT
   TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE
FROM DUAL ;

---
EDT

SELECT
   TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE
FROM DUAL ;

---
EST


texts: How to find what tablespace a table/index is in on PostgreSQL?

texts: How do I find out what tablespace a table (or index) is in with PostgreSQL (9.3)?


texts: Advices for modeling facts, time hierarchies based on two conditions

texts:
Sorry if my question does not belong to that SE but I tried to find the best place and it ended up here.

My problem is this :

I've got a table modeled as this in our datawarehouse env. :

MYTABLE (service_id, date_input, date_when_active, date_when_inactive, bunch_of_values...)

Each night this table is updated with new records from our prod env. When a record changes in prod and already exist in dw, a new record is created in dw with the same serviceid and the old record gets updated with sysdate in when_inactive field. The newly created record has its when_inactive field to null.

We use a query to extract data from this table using some conditions to get exactly the value of all services in a precise period. The query goes like this :

select *
from mytable
where service_date_input between :p_date_start and :p_date_end
and :p_date_end between date_when_active and nvl(date_when_inactive,:p_date_end)

What I would like to do is to model a time hierarchy in Cognos Framework Manager so I can drill through fiscal years, periods, weeks, months, etc.

What I can't figure out is if I need to remodel the table to include calculated fiscal_year_id, period_id, week_id, month_id, etc. directly in the DB or is it possible to do it directly in Cognos FWM?

One of the requirement would be to not force the use of two date sets on the user (filter by input date AND fiscal year). The input date should be filtered transparently : When you use a fiscal year parameter to filter or display the data, the input date rule should apply accordingly.


texts:
I'm trying to do a simple update query and I'm getting this error:

  The multi part identifier "customers.customer_name" could not be bound

Query:

Update Suppliers
Set City = (select s.city
    From suppliers AS s
       Where s.supplier_name = customers.customer_name)
Where Exists (select c.city
    From customers AS c
        Where c.customers_name = Suppliers.supplers_name)


texts: SQL Query suddenly slow --- how to test if its network or hardware (memory) issue?

texts:
I'm not a general IT expert. Sometimes with SQL queries ... they will take 10 seconds about 95% of the time.

Every so often, the exact same query will take 5-10 minutes. As will others. The constant is the host computer and current time --- not the query itself.

I'm just wondering if there's any way for me to test or verify what the problem is that is slowing down the query (and that it is, indeed, slowing the query). Would I remote into the host machine and check the memory usage/ processes? The host machine is also in a foreign country --- USA to Germany. Again, 95% of the time, this doesn't matter. I'm wondering if there are temporary 'network spike' issues. I want to know so I can actually complain to IT there ... but I want to pinpoint exactly what's causing the query issues. Maybe this is out of scope of this board ... I know a showplan wouldn't really indicate anything ... or would it? Would network or memory issues slow all steps the same duration? Is there an easier way to troubleshoot this?


texts:
I have a table with this structure:

mysql> describe user_groups;

+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | int(11) unsigned     | NO   | MUL | NULL    | auto_increment |
| user_id  | int(11) unsigned     | NO   | PRI | NULL    |                |
| group_id | smallint(5) unsigned | NO   | PRI | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

primary key of this table is : PRIMARY(user_id, group_id)
I have simple query:

mysql> EXPLAIN SELECT `UserGroup`.`user_id` , `UserGroup`.`group_id` FROM `user_groups` AS `UserGroup` WHERE `UserGroup`.`user_id` IN ( 1, 2 );
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | UserGroup | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+

it has using where; , according it has additional disk seek,I know using where; concept, and Also I know it is not terrible , but conceptually I want to know why this query has using where;, although the condition is on primary key directly and it doesn't have order or anything that causes using where;.
How can I fix this using where;?


texts: AUTO_UPDATE_STATISTICS and FULLSCAN in SQL Server 2008 R2

texts:
Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2?

If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date?

note: The need for the FULLSCAN comes by proved suboptimal plan generation when using non-FULLSCAN statistics.


texts: IMPORT TABLESPACE is hanging in the 'System lock' state

texts:
We have a development database server with Percona-server 5.7.15-9 on it. It replicates two schemas from two different production servers using multi-source GTID replication. Lets call these schemas alice and bob.

On the dev server we clone this replicas of production databases to get databases for development. They are called 1_alice, 1_bob, 2_alice, 2_bob, etc. All of them use the same instance of MySQL.
For fast cloning we use Percona XtraBackup as described here https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/restoring_individual_tables_ibk.html

In the past there were only one replica (alice) and we used binary log position for replication instead of GTID. These times everything worked fine and fast. One day (I'm not sure when exactly) it became broken.

Now when I perform ALTER TABLE 2_alice.access_group IMPORT TABLESPACE query, it hangs in the 'System lock' state. And could be hanging in this state from 1 min 'till 1 hour and more (then it works). There are no more active connections instead of two replicas, but they doesn't use 2_alice schema.

Why is IMPORT TABLESPACE query hanging and how could I debug this case?


texts: Restrict Front Ended User access to Form entry only

texts: I have a front end database that I want to restrict user access to only enter data into a form. I do not want them to be able to scroll through other entries via the form. How can I restrict this in Access 2007-2010?


texts: Why different datetimes freezes a query based on indexes?

texts:
I have a table with a billion records. It has these columns:

Id bigint,
MobileNumber varchar(100),
Date datetime,
Message nvarchar(400)

And it has non-clustered indexes on MobileNumber and Date fields.

I want to find what a MobileNumber has sent us in a specified period. Thus I run this query:

select Message
from ReceivedMessages
where [Date] > 'from-time'
and [Date] < 'to-time'
and MobileNumber = 'number-to-filter'

And this query works lightening-speed fast for past 2 years. But when I change the from-time part to a closer date, it freezes out and takes more than 2 minutes to complete. In other words, based on different inputs, it behaves differently, sometimes even hanging out and not returning for more than 10 minutes.

I expected a consistent behavior. What do I miss about indexing? What can cause this inconsistent performance?

Update: I changed names of columns and table, so I can't attach execution plan as a picture. But here's the issue. Thanks for guiding me.

when I change value of date parameter, SQL changes index seek from IX_MobileNumber to IX_Date. I never thought that SQL creates execution plan based on the value of parameters. How that could be?


texts: How to have one Auto Incremented id for two separate tables in mysql?

texts:
I have 2 different tables, each having its own id column.

I need that if one table is using a specific id, the other table will not use it. This is needed because the object in both tables will later be handled together by another component in the application, and the other component must have them uniquely identified by the id.

I thought to have a table counter with next_id column, and have a before insert trigger to each of these tables, that will take the current id from counter, use it for the new row, and increase it.

My question is, is this synchronized? i.e. what will happen if 2 different applications, or threads in a single application, insert columns to any of these tables?

Do I have to lock the counter table during the trigger? If yes, will it be locked until the end of the entire transaction, or just until the trigger ends?

Is there a better solution for this problem?


texts: SSRS is missing after failover to other node

texts:
We have 2 node setup cluster, after a failover I can't see the reporting services, even the service it self is missing.

I checked control panel program manager, SSRS is not installed, I open reporting service configuration manager when clicking Find, it can't find the server. Is there a way to retrieve that.

There are active reports already running before the failover.

Sorry I'm new in SSRS, I researched the web and can't find any solution.


texts: Connecting SQL Anywhere with Sybase database file on local computer

texts:
I have installed SQL Anywhere 12 in my system.

I have a .db file in my computer. I have no idea what is it's username and password.

If I try to connect to it by providing credentials- username=DBA and password=sql
I'm getting below error-

How can I connect to it successfully?


texts:
Is it possible to find out who used a SQL-Server login?

Let's assume we suspect a person to abuse a SQL login that is supposed to be used by an application only to logon to SQL-Server. Is the domain user who used the SQL login logged anywhere, so that we can make a conclusion who used it?


texts:
I'm able to read CONSTRAINTS in the Information Schema, but how can I get all parameters associated with that CONSTRAINTS to get a full CONSTRAINTS definition by using SQL ?
For example,

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

This will give me list of CONSTRAINTS, but let say, I got a CONSTRAINTS FOREIGN KEY  "user_role_id" and its full definition (via pgAdmin) is

ALTER TABLE app.user
ADD CONSTRAINT user_role_id FOREIGN KEY (role_id)
REFERENCES app.role (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE;

So how do I get this definition by just writing SQL that give me all those options associated with that CONSTRAINTS?


texts:
I need suggestions for my.cnf optimization. Currently I have a server with 256GB Ram and 40(CPUs) along with website hosted on this server.

My Wordpress database size is around 1GB and using myISAM tables.

[mysqld]
bind-address    = 127.0.0.1
performance-schema=1
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
#tmpdir                = /tmp
tmpdir                 = /dev/shm

# MyISAM #
key-buffer-size                = 1024M
myisam-sort-buffer-size        = 512M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-external-locking
skip-name-resolve
# skip-networking

# CACHES AND LIMITS #

tmp_table_size=512M
max_heap_table_size=512M
query_cache_size=128M
query_cache_type = 1
query_cache_limit=3M
wait_timeout=60
interactive_timeout=60
thread_cache_size=128

#tmp-table-size                 = 32M
#max-heap-table-size            = 32M
#query-cache-type               = 1
#query-cache-size               = 0
max-connections                 = 20000

#thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 16384

# INNODB #
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2048M
innodb_buffer_pool_instances = 2
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_read_io_threads=16
innodb_write_io_threads=16

# SLOW QUERY
slow_query_log=1
slow_query_log_file="/var/log/mysql-slow.log"
long_query_time =10

#max_allowed_packet=268435456
max_allowed_packet=524288000
open_files_limit=30000
innodb_file_per_table=1


texts: Sequence: last_value billions bigger that actual count of rows

texts:
In my PostgreSQL 9.6 database, INSERT on a certain table is not possible anymore: The primary key column is an integer and the last_value of the sequence in question is 2,147,484,010 - which is too big for an integer. As a consequence, I get 'integer out of range'.

So far, so good.

The question is: How come? Why is last_value so huge?

The actual count of rows is just 112,698. The table rows are not deleted normally.

And there seems to be no general, reproducible problem. I've tried with the following: I copied that database, deleted all rows from the table in question, reset the sequence, and created entries with an (almost) 'real life query'. I could create more than 6 million rows with any problems.

I know that sequences are not designed to be gapless (e.g. on the above-mentioned test I got a last_value of 8,946,095 for 6,404,000 rows), however having a value 2 billion bigger than the row count seem pretty strange to me. Obviously, something went wrong here.

How could I debug this further?

Edit: data of the sequence is as follows:

+------------+-------------+--------------+-----------------------+-----------+-------------+---------+-----------+-----------+
| last_value | start_value | increment_by | max_value             | min_value | cache_value | log_cnt | is_cycled | is_called |
| 2147484010 | 1           | 1            | 9,22337203685478E+018 | 1         | 1           | 32      | False     | True      |
+------------+-------------+--------------+-----------------------+-----------+-------------+---------+-----------+-----------+


texts: Oracle DBMS_MVIEW.REFRESH debugging output?

texts: Is there any way to see what DBMS_MVIEW.REFRESH is actually doing?  I want to investigate partition change tracking (PCT) and verify that it is indeed only updating modified partitions.


texts: How to keep history of a mysql table that has autonum as key?

texts:
My solution to this (probably typical) Mysql problem is horror performancewise, see below. But first the problem.

I've got a table

prices (fkey AUTONUM*, b STRING[3], c STRING[3], validon DATETIME, price FLOAT)

fkey is the int32 primary key. The (b,c) pair is another UNIQUE index and is needed for various reasons.

The following kind of queries are executed all the time and are the only way to update or insert into the table:

INSERT INTO prices (b, c, validon, price) VALUES (...),(...),...
ON DUPLICATE KEY UPDATE price = VALUES(price)

So autonum runs freely as needed and price is updated if a clash is encountered.

This works superb, until I need to have another table "history" which keeps track of all prices ever inserted or updated. I designed it like this:

history (a INT32, validon DATETIME, price FLOAT)

This table has only one key - the combination of all three columns.

My question is: How to insert rows in table "history" as rows of table "prices" are being inserted/updated? The issue I have is to retrieve the correct autonum values quickly and efficiently.

Right now my solution in pseudocode is this:

LOCK TABLES prices, history WRITE;

k := SELECT MAX(fkey) + 1 FROM prices

// add_to_history() always returns 0 and has only one statement :
// INSERT IGNORE INTO history VALUES (a, b, c);

INSERT INTO prices VALUES ......
ON DUPLICATE KEY UPDATE
    price = if(validon < values(validon),
         values(price) + add_to_history(fkey, values(validon), values(price)),
         price),
    validon = if(validon < values(validon), values(validon), validon)

INSERT IGNORE INTO history
SELECT fkey, validon, price FROM prices
WHERE fkey >= k;

UNLOCK TABLES;

The solution "works", but it blocks the entire server occasionally for random periods of time (not indefinitely, but for hours) and it must go.

Both tables can be either MyIsam or InnoDB (right now they are MyIsam). Please let me know if a certain DB engine is needed to solve this properly.


texts: Oracle cannot connect to SQL developer as sys account

texts:
I am trying to connect to SQL developer as sys account.  It is always giving the error invalid username/password. When I try to connect it through SQLPLUS, it gets connect with any wrong password also.

How can I connect to SQL Developer using sys account?


texts: MySQL Fulltext match with forward slashes

texts:
I'm trying to run a fulltext match against a product database, where the product names have forward slashes in them. These are staple sizes, so it's quite important that they get matched.

However, for every query I execute with a forward slash, I get 0 results.

The query I'm using is:

SELECT `product`.*
FROM `product`
WHERE MATCH (`product`.`name`) AGAINST('26/6')

I have also tried

SELECT `product`.*
FROM `product`
WHERE MATCH (`product`.`name`) AGAINST('\"26/6\"')

However, running the following returns 6 results, as expected:

SELECT `product`.*
FROM (`product`)
WHERE `product`.`name` LIKE '%26/6%'

The table is MyISAM and the product.name is varchar(255), with a FULLTEXT index on that column alone.
If I search using fulltext for something else, like 'tool', I get the results as expected, and the server is configured for 4 character or more matching.

What do I need to do to make this fulltext match work?


texts: Join tables and summarize rows based on condition

texts:
I have two tables.

One containing countries with an ID and the geometry

The other containing customers with a name, geometry and a boolean value showing if they are active or not.
Countries
iD     name     geometry
1634   UK       xxx
2357   USA      xxx
3345   Mexico   xxx
4694   Italy    xxx

Customers
name         geometry    statusActive
Hernandez    xxx         TRUE
Taylor       xxx         FALSE
Monte        xxx         TRUE
Winter       xxx         TRUE
Best         xxx         TRUE
Twist        xxx         TRUE

I want to find out, how many active customers there are in each country.
Step 1 - Join data: I could join the two tables based on the geometry. With ST_Contains I can test, which of the customers is located in which country. This works out well. I get as result a table from Customers which has additionally the column of the country ID. In addition, only customers with an active status are returned.
This is my SQL statement:
SELECT customers.*, countries.id
FROM customers
JOIN countries ON ST_Contains(countries.geometry, customers.geometry)
WHERE customers.statusActive IS TRUE

This is the resulting table:
Customers
name        geometry    statusActive    id
Hernandez   xxx         TRUE            1634
Monte       xxx         TRUE            4694
Winter      xxx         TRUE            2357
Best        xxx         TRUE            2357
Twist       xxx         TRUE            4694

Step 2- Sum up the customers in each country.
I do not know how I can achieve this. I already tried to use GROUP BY based on the country ID, but this results in an error, that I have to include all columns from the SELECT statement. When I remove the customers.* (all fields from customers) from the SELECT, the query is running forever.
This is how the desired result should look like:
NumberActiveUsers   ID
1                   1634
2                   2357
0                   3345
2                   4694

Do I need another function to sum up the entries, or is GROUP BY the correct function?


texts: Can you force SQL Server to use an index on computed column for a commutative operation?

texts:
Can you force SQL Server to use an index on computed column for an commutative operation?

We unfortunately have a table in SQL Server as follows

CREATE TABLE [dbo].[Data](
  [ID] [int] NOT NULL,
  [ValDate] [datetime] NOT NULL,
  [ValHour] [int] NOT NULL,
  [ValMin] [int] NOT NULL,
  [Value] [float] NULL,
  [Flag_ID] [int] NOT NULL,
  CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
  ([ID],[ValDate],[ValHour],[ValMin]) ON [PRIMARY]
) ON [PRIMARY]

Even though ValDate is a DATETIME, it only stores the date portion.

Some old applications using this database makes the following query

SELECT ID, ValDate, ValHour, ValMin, Value
FROM DATA
WHERE ID = @id
  AND @start < DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
  AND DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) <= @end

This slows down with a lot of data, as SQL can't use the dates specified in the WHERE clause to seek and must scan each table entry with ID = @id to find all the rows.

We can't change the applications, so I decided to add a computed column and put an INDEX on it

ALTER TABLE dbo.Data ADD ComputedDateTime AS
  DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
GO
CREATE NONCLUSTERED INDEX [Data_ComputedDateTime_IDX] ON [dbo].[Data]
  ([ComputedDateTime], [ID])
INCLUDE ([ValDate], [ValHour], [ValMin], [Value])

This makes the original query fast, but if I change the order of the DATEADD's around, adding the minute to the date and then the hour the index is not utilized.

So I assume this is because SQL Server doesn't realize the operations are commutative i.e. (date + hour) + minute = (date + minute) + hour

Is there any way to speed up both computation orders, without creating two computed columns and two indexes?


texts: How to move snapshot Azure SQL db from prod to dev on a recurring basis

texts:
How can I take an existing production Azure SQL database (or one of its regular backups) and restore it to a dev environment on a regular basis? I want to give developers the option to work on their regular dev database but also have a snapshot of prod available in order to safely reproduce issues found on production. And optionally, to be able to replace our regular dev database with one of these snapshots manually, if and when we choose to.

Right now, I can create a new database with a new name from one of the built in regular backups from prod, but then I have to update all of the connection strings each time. If I can have a few to swap between then I can use deployment slots to keep copies of the configs, so I could have a 1, 2 and 3 database and shuffle between then or something. Any help would be greatly appreciated! Thanks!


texts: PostgreSQL - Refresh dblink materialized views with incremental data

texts:
I have a few PostgreSQL servers(v12). I want to bring the data from these servers to my central server in a frequent interval.(No options for logical replication). It may be crazy, but I'm thinking of this solution.

So both the tables are now accessible via DBlink from central DB as materialize views. Now I want to refresh this materialize views with incremental data.
Example:
table A:
ID | name
1  | aaa
2  | bbb

select * from mv;
ID | name
1  | aaa
2  | bbb

Now add 2 rows on tbl A
ID | name
1  | aaa
2  | bbb
3  | ccc
4  | ddd

During the materialized view refresh, I just need to add the newly added rows. Its all append-only tables. No updates or deletes. ID is the primary key column. So is this possible to refresh MV with only incremental data?
I want to create a normal view with DBlink, the queries that Im going to run on the central DB is very heavy. Normal views on DBlink may lead to performance issue on pg1 and pg2.


texts: How to generate create table script without primary and foreign key in Oracle

texts:
I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL

But this statement also generates all primary and foreign key scripts that belong to table. So, is there any way to not include primary and foreign keys in create table scripts


texts: Optimize a MySQL Database using MySQL Workbench

texts:
I actually have a few issues.

First, I found this information about optimizing: https://stackoverflow.com/questions/5474662/mysql-optimize-all-tables

The problem is, I don't seem to have a mysqlcheck program anywhere.  I'm using MySQL Workbench.  It's possible I have used other MySQL versions in the past.  When I look for mysql I find in the Program Files folder there is a MySQL folder with a MySQL Server 5.5 folder.  This one has some .ini files and the bin, dat, etc directories, but no executables.  Then in Program Files (x86) there is another MySQL folder.  In that there are several folders including: MySQL Documentation 5.5.30, MySQL Installer, MySQL Notifier, MySQL Workbench CE 5.2.47, and Samples and Examples 5.5.30.  The only one that has mysql.exe is MySQL Workbench CE 5.2.47.  But it does not have mysqlcheck.exe.

I poked around in Workbench and could not find any maintenance or optimization functions.

My database is 17 GB and I removed some large text fields from large tables, and I'm hoping to recover space because my OS is yelling at me to make more space.  Does Workbench have the capability to optimize, and if so how do I do it?  And if not, what can I install to be able to do it?


texts: Separate login during `Too many connections` in AWS RDS

texts: AWS-RDS does not provide any user with SUPER privilege. As a result, 'too many connections' are made, even the dba is locked out. Nobody can login and kill any sleeping connection. Apart from taking pre-emptive actions to avoid 'too many connections' like creating an event scheduler to kill sleeping connections, how to deal with the error?


texts: What is the difference between *data dictionary* and *data dump of SEDE*?

texts:
A meta post (https://meta.stackexchange.com/q/2677/714660) gives the "anatomy" of the data dump from SEDE that includes the structure of the public data dump(tables, columns, data types, etc) and Entity Relationship Diagram.

The stuff above looks similar to data dictionary.

So, what is the difference between data dictionary and data dump of SEDE?


texts: Database has thousands of randomly named tables

texts:
Preface: First of all excuse me for bringing forwarding this weird question, as it could be just me.

Situation: We have a Centos 6.5 Cloud VPS hosting our website, running MySQL 5.1 and it is also used for two way replication to and from our in house MySQL server.

Problem: I recently restarted MySQL for changing some buffer size, when I tried to login, the existing password didn't work and I was able to login using root@localost with an empty password, which raised my suspicions.

When I entered in MySQL DB (to see user table for permissions) I have noticed that there are more than 5k tables, named randomly and few of them have binary data in it too. I can also see in /var/lib/mysql/mysql/ all of the tables have their data files present too.

I suspect having older version of OS and MySQL, our server has been hacked and hacker has injected some code and files there (I can see lots of DLLs and EXE files there, luckily it's a Linux server!)

I want to get rid of these useless tables and protect my server in a safe manner, as this is live customer facing web portal, so I can't try any unsafe deletion.

It is just a simple web app to take orders from clients and store in DB. I guess our system was hacked/compromised. The VPS provider is reliable and we have been with them for many years. I agree it could be incompetence on our part or theirs that we have never bothered to upgrade Centos 6.5 neither MySQL 5.1


texts: Will one nvarchar column affect query performance?

texts:
I have one column that I recently converted to nvarchar(100) to store Asian characters. The other columns in this table, and the other tables in the same database, use varchar, int, bit and datetime. Since that change, some of my queries now take ten to twenty seconds to return less than fifty records. The tables store a thousand records or less.

I've read nvarchar has an associated IO performance cost with it, but this bad for such a small record set? Do I have to adopt an all or nothing approach with nvarchar (convert all varchar to nvarchar) or have I missed something obvious?


texts:
I've been searching and testing for hours and still can't figure this out. MySQL version 5.7.23 on both slave and master.

Replication over SSL is failing with the following error message:

  Slave I/O for channel '': error connecting to master 'replslave1@stuff.stuff.com:3306' - retry-time: 60 retries: 1, Error_code: 2026

Replication works fine without SSL, so I know that the replication user and password are correct, and port 3306 is not blocked. I can connect with SSL from the command line with the same user and password combination:

mysql -u replslave1 -pxxxx -h stuff.stuff.com --ssl --ssl-ca='/etc/mysql/master-ca.pem' --ssl-capath='/etc/mysql' --ssl-cert='/etc/mysql/repl1-pub.pem' --ssl-key='/etc/mysql/repl1-priv.pem' --ssl-cipher='DHE-RSA-AES256-SHA'

When I do this from the command line the cipher shows as DHE-RSA-AES256-SHA.

Any ideas are welcome.


texts: How can I send mail from a contained database is SQL Server?

texts:
How can I send mail with a user within a contained database is SQL Server using Database Mail?

I tried using msdb.dbo.sp_send_dbmail but since any users local to the contained database do not see msdb, therefore msdb.dbo.sp_send_dbmail is unreachable as well. Then I tried creating certificates and signing procedures, first going straight to msdb and then re-routing the whole stuff through the master database (I know, it's not a 'very good idea') but even that does not work (I suppose it would break the containment, hence not allowed).

Is there a way do that at all?


texts: How can I keep the user permission on a table that is dropped and recreated?

texts: I drop, create and bulk insert a table weekly for a user, but the user's permission is removed from the table each time I do that. I right click on the table name and add them each time. Is there a way to keep their name in the permissions?


texts: Physical Move of Secondary Replica to a new Location

texts:
We plan on moving a secondary replica of an SQL2014 AlwaysOn group to another physical location although it will have the same IP address after the move.

The shutdown may be between 24 and 48 hours.

Is there anything that would need to be done on the secondary replica prior to shutdown.

Thanks.


texts: Performance effects of database file size and available space

texts:
I have set the autogrowth and maxsize properties to:

By 10 MB, Unlimited

When I look at the Properties -> General Tab by right-clicking the database in SSMS, I see the file size is 2249MB with 6.1 MB of space available. What does this mean?

I have 700 GB free space remaining on the drive where the log file and database file sits. The log file does have a limit of 2097152 MB. It looks like that space available means log file space?

In terms of database performance, how does the size of the database and log files affect reads and writes?


texts:
I want to start using Query Store with suggested setting by Erin Stellato.
USE [master]
GO
ALTER DATABASE DatabaseName SET QUERY_STORE = ON
GO
ALTER DATABASE DatabaseName SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
GO

But that is a by database setting, and I want to be able to activate on all the databases of an instance at once.
I found SET RECOVERY Model Using sp_msforeachdb (at serverfault)
I can use the OP's solution
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
IF DB_ID() >= 5
ALTER DATABASE [?] SET QUERY_STORE = ON
ALTER DATABASE [?] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
'

While it works I get the errors

Msg 12438, Level 16, State 1, Line 41
Cannot perform action because Query Store cannot be enabled on system database master.
Msg 5069, Level 16, State 1, Line 41
ALTER DATABASE statement failed.
Msg 12438, Level 16, State 1, Line 41
Cannot perform action because Query Store cannot be enabled on system database tempdb.
Msg 5069, Level 16, State 1, Line 41
ALTER DATABASE statement failed.

I tried several variations of the solution by Jason Cumberland but could not get it working.
set quoted_identifier on

EXECUTE master.sys.sp_MSforeachdb '
IF '?' not in ('tempdb','master','model')
begin
    exec (ALTER DATABASE [?] SET QUERY_STORE = ON
ALTER DATABASE [?] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO))
end
'

I am getting the error

Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '?'.

These two work
  --Turn off on all databases
  EXECUTE master.sys.sp_MSforeachdb 'USE [?]; ALTER DATABASE [?] SET QUERY_STORE = OFF'

and
--Check all Query Store database settings at once
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT * FROM sys.database_query_store_options'

Question: How can I turn on Query Store on all the database of an instance without getting an error message?
Note: of the system databases msdb is the only one that that allows query store.  I am not excluding it currently.
I am using SSMS 17.4 against, SQL instance running SQL 2016 (SP1)


texts: CREATE DATABASE permission denied in database 'master'. unable to get the permission

texts:
I am making a database in sql server and it shows an error that "CREATE DATABASE permission denied in database 'master'"
I am using the administrator log in itself.
What i need to do?
Please suggest a fix!


texts: How can I output a new line with `FORMATMESSAGE` in transact sql?

texts:
The following code stats that, how many users that has created at least one post on a specific SE site.

DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(DISTINCT OwnerUserId)
FROM Posts where OwnerUserId is not null)
PRINT FORMATMESSAGE('%s has \r\n %d users', DB_NAME(), @RowCount+1)

which basically works though, the FORMATMESSAGE part does not work as expected.

Usually, \n (in lots of systems and languages) and \r\n (mainly in MS systems) represent new line. I tried both, and even /r/n, none of them worked.

How can I output a new line with FORMATMESSAGE in transact sql?


texts: What is the F301: the CORRESPONDING clause in query expression?

texts:
The SQL 2011 Spec supports a CORRESPONDING clause,

  Feature F301, “CORRESPONDING in query expressions”:

What is this feature? How is it used? And, is it supported by any industry RDBMs?


texts: Varchar(max) field cutting off data after 8000 characters

texts:
I have a field to store some data, the field is declared as varchar(max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest off.

I have verified that all the data is included in my update statement and the query looks fine everywhere else but when I select the data back out it has been cut off.

The data is truncated when I display it on my website and also when I use SSMS to select content from table.

select DATALENGTH (content) from table comes back as 8000.

I set the data using this: update table set content = 'my long content' where id = 1. The content does have lots of HTML in but I can't see that causing issues. The only thing I can see that I am doing is replacing all " with '' as this is user entered content (can't remember why I did that now).

I did manage to get the content to enter correctly by removing all single quotes in the content so I think something odd is going on with my data rather than the database.

Should I be doing something special with the query to use a varchar(max) field?

Using: SQL Server 2008 (10.50) 64 bit.


texts:
I believe this is a firewall rule problem. Therefore, I believe that the issue is finding out what the public IP address of the RDS is. I don't see that anywhere on the RDS interface.

I have two AWS RDS MariaDB databases. I can connect to both without a problem. From the command line, I type:

mysql -u me -p db1.cet4jwf1l8xu.us-east-2.rds.amazonaws.com

I connect no problem. I can also connect to db2 without a problem.

In db2, I want to remotely access a table from db1. I have done this with both Federated and Connect. I get the same error: Can't connect to server, timed out. The syntax for each is pretty much the same:

create table testtable engine=federated connection='mysql://me:test@db1.cet4jwf1l8xu.us-east-2.rds.amazonaws.com/mydb/demo';

It sits for a while and then comes up "can't connect." I can switch to engine=connect and get the same timeout error.

In the incoming security rules for db1, I have port 3306 open to everywhere. So, I assume it shouldn't be blocking it. Inside db1, user me is set to host '%', which I believe means 'anywhere'. So, I don't think that is the problem either. I'm not sure what else could keep it from connecting when I can connect from a basic CLI on multiple other servers.


texts: How do you select non-valid column names used by views / functions and SPs?

texts:
In trying to setup replication I'm finding that some of the objects (views and SPs) are outdated but were not removed from the database. So I'm getting errors along the lines of:

Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164)
Get help: http://help/MSSQL_REPL20164

Invalid column name 'iNoOfUnits'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207

Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL)
Get help: http://help/MSSQL_REPL

Which means i have to open up every view and function (and there are hundreds) to manually search for usage of a particular column (or possibly table) name.

Is there a way I can list all objects in a database and column references that these objects use?

I have seen this question: How to find all views with invalid column names. But I don't think it's returning columns that aren't present on the database.

But I'm wondering if there isn't a better way? How are SPs/Views/etc stored in the database? Is it text? then I would be able to select the text that contains columns that aren't present any more...

I wish the log contained the name of the object that is trying to reference an illegal column name...

===== ANSWER

The easiest way I have found, thanks to the accepted answer below, is to open an SSDT project and import the database. Then search through the solution for columns that cause subscription errors. And fix one-by-one...


texts: mysqldump Not Exporting All Rows or mysql Not Importing

texts:
I have a table that contains almost 4 million rows. I exported it with mysqldump and transferred it via scp to another server and imported it with the mysql command but now it's missing several hundred thousand rows. I've repeated this process several times and each time, it's missing a different amount of rows.

I have tried mysqldump --compatible=ansi because on one export, there was an error in the syntax for some reason. That dump wound up having the most rows on import but still missing hundreds of thousands.

Edit: I tried the -f option but it still prints out a mysql error before I'm returned to the command prompt:

ERROR 1064 (42000) at line 41458: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '286,'54.227.215.70','http://example.com/'),(81547367,'CbLc4lXH',1501560286,'54.7' at line 1

I'm not sure if it exited because of the error or not but I do know I'm missing about a million records after import. The file is 40GB in size and I don't have access to the previous mysql server anymore to do another export. I have all the data I need. I just can't get it imported.


texts: Finding stored procedures with missing indexes

texts:
How to scan for stored proc that need indexes to be created.

Anyone have any idea?  Basically we have stored procedures that run very slowly.  I'm wondering if there is some kind of automated script or app that can scan to indicate if an index needs to be created?


texts: Changing string in all objects in all databases on one server

texts:
Is there a way to find all instances of a string in all objects of all databases on one server?

The reason behind this question is that we need to change a reference to another database (located on another server).

So, we need to find every view, stored procedure, function, etc. that has a reference to the old server. This search also needs to include objects that are not specific to a single database, like system jobs.

So far, the best way I know of doing this is to use the Generate Script wizard on each database individualy and then search for the string in the generated files. The problem with this approach is that it ignores system jobs.

Is there a better way of doing this?

UPDATE 1

I looked into the sys.sql_modules table and that looks like a good alternative to the Generate Scripts wizard.

I have a query for one database that works fine by itself

select
'MyDatabaseName' as DbName,
o.name as ObjectName,
o.type_desc as ObjectType,
m.definition as Definition
from MyDatabaseName.sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
WHERE m.definition Like '%MyString%'

But when I try to use it with sp_msforeachdb, I don't get the same amount of results.

DECLARE
    @SQL nvarchar(4000)
SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''
EXEC sp_msforeachdb @SQL

Even when removing the condition for MyString I still don't get the same number of results (from 267 to 27).

Using msforeachdb would really help me because I need to run this on many databases over many servers.

UPDATE 2

Found the issue in my query, it was missing a [?] in front of the joined sys.objects. Here's the corrected query

DECLARE
    @SQL nvarchar(4000)
SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join [?].sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''
EXEC sp_msforeachdb @SQL


texts: Load data from a Table that has the same structure as Table1 with the only diferrence of an ID not null column?

texts: So, I know that if you want to load data into a table that does not have the same structure as the data, well you have to do it in the way of column by column, the thing that in this situation, I have many tables created exactly with the only diferrence than table2's have an indentity column. So my questions is, is there a way that I can load data with the db2 INSERT INTO table2  SELECT * FROM table1 but skipping this identity column? somehow in the sytax that I can tell that to the database? doing it column by column syntax will take a lot of time.


texts:
I am coming from SQL Server background where SQL Server studio has a very easy way of taking database backup and then doing a restore.

I have been using Oracle 11G express edition for past couple of months and I am not sure of doing the backup and restore in oracle.

I can see the option to backup database and restore databse in oracle server menu but I am not sure how does it work, where does it creates the backup file etc.

Please help


texts: Oracle DB hanging after upgrade to 11.2.0.3

texts:
The database is hanging randomly (with some leniency I would say almost each hour). Top session is:

select privilege#
from sysauth$
where (grantee#=:1 or grantee#=1) and privilege#>0
or
select /*+ connect_by_filtering */ privilege#, level
from sysauth$ connect by grantee#=prior privilege# and privilege#>0
start with grantee#=:1 and privilege#>0

In ash I saw these top wait events:high concurrency on event library cache: mutex X and latch: shared pool

This problem occurred after upgrading from 11.2.0.1 to 11.2.0.3.

These queries are almost always in top sessions but the wait event when the database is not hanging is CPU. In 11.2.0.1 they didn't appear. We are using DRCP.


texts: I have setup transactional replication on SQL SERVER 2014. It was working fine yesterday. Today, I am seeing below error. How to investigate what is the problem. Why log reader agent is not starting up.


texts: When I implement database mirroring in SQL server it shows an error

texts: When I click the start mirroring button it shows the below error message. When I try to connect the mirror and witness server through management studio it's connecting and working fine. The user I am logging to the machine is with the domain user.


texts: I'm trying to fetch unique values from one column (registration_no) which has duplicate values in column(mobileno) and column(email)

texts:
This is my table - I want to get the unique registration_no's which have same mobileno, email and date_of_action.
For instance row no 8,9 and rows 10,11,12,13


texts: Oracle Patch Level Differences (Oracle Enterprise Manager and DBA_REGISTRY_HISTORY)

texts:
There is a difference in the patch level that I am seeing on my Oracle database. On the Oracle Enterprise Manager, it shows that it is patched up to .7 but on DBA_REGISTRY_HISTORY it does not show any of the patches.

APPLY   SERVER  11.2.0.4    0   Patchset 11.2.0.2.0 PSU
APPLY   SERVER  11.2.0.4    0   Patchset 11.2.0.2.0 PSU

Is this a common occurrence?

Thank you


texts: How can I perform a mongoimport to a host that requires an SSL Certificate?

texts:
I am trying to load some sample JSON data into a MongoDB database (hosted on IBM Bluemix).

The connection requires SSL, and a SSL Certificate has been provided, but I keep getting an error when I run mongoimport from the terminal (OS X).

Here is the command I am running (with sensitive data removed, of course):

mongoimport --ssl --sslAllowInvalidCertificates --host=<host_address>:<port>/admin -u <username> -p <password> --drop --file ./sample-dataset.json --db test --collection testColl --sslPEMKeyFile ./SSLCert.pem

And the error I receive:

Failed: error connecting to db server: no reachable servers, openssl error: dial tcp <host>:<port> getsockopt: connection refused

I'm new to SSL, especially when it comes to MongoDB. Am I just not using the right options/config for mongoimport?


texts: Grouping time-series data by time intervals

texts:
Let's say we are storing data for 1000s of devices that collect a single type of data every 10s. Each device can be located in a different timezone. The ability to query quickly to visualize the data is important. We can ask the system questions such as the following:

1. For a specific device, I want the last 7 days of data grouped by day totals for my local timezone.
2. For a specific device, I want the last year's data grouped by month totals for my local timezone.

Storing all the data in UTC seems like the cleanest approach, however it becomes tricky when asking for local groupings of the data. For example, a day grouping for each timezone has different offsets. So if we were to store in say day, month, year "buckets" they would all be grouped relative to UTC which would not be useful for asking questions for timezones other than UTC itself.

If we were to group the data in minute and hour "buckets" (ignoring timezones that are off by less than an hour, e.g. IST +5:30) we could use the hour "buckets" to construct the answers to the above questions. For question 2, there would be 12 groupings of up to 744 hour "buckets" for each grouping.

Does the approach with minute and hour (ignoring timezones that are off by less than an hour, e.g. IST +5:30) "buckets" seem like a decent design? Has anyone designed something similar with a different suggestion?