texts: How to update a table with a stored procedure with XML parameter in SQL 2008?

texts:
I have the following Stored Procedure query. This query is incomplete. I need help completing this query.

Problem:
In this SP, I am receiving a well-formed XML string into the SP. My goal is to update those rows in the Notes table that have the matching NoteId for the Notes passed in XML.

For example: If the XML has two notes with NoteId = 1, 2 and the table has three notes with NoteId = 1, 2, 3, I would like to update the NoteText of the table for rows with NoteId = 1, 2. Please help me complete my stored procedure.

Input XML:

<Notes>
  <Note>
    <NoteId>1</NoteId>
    <NoteText>Hello</NoteText>
  </Note>
  <Note>
    <NoteId>2</NoteId>
    <NoteText>World</NoteText>
  </Note>
</Notes>

Table before update:

NoteId | NoteText | IsDeleted
1        Hell       0
2        Worl       0
3        Test       0

Table after update needs to look like:

NoteId | NoteText | IsDeleted
1        Hello      0
2        World      0
3        Test       0

This is the Stored Procedure that I currently have:

ALTER PROCEDURE USP_Notes_UpsertNotes
    (@notesToUpsert XML)
AS
BEGIN

    SET NOCOUNT ON;

        UPDATE Notes
        SET NoteText = n.note_text
        FROM (  select  T.C.value('(NoteId/text())[1]','varchar(500)') as note_id,
                        T.C.value('(NoteText/text())[1]','varchar(500)') as note_text **PROBLEM**
                from @notesToUpsert.nodes('/Notes/Note') as T(C)
             )   as n

        WHERE n.note_id = Notes.NoteId AND Notes.IsDeleted = 0

END
GO

Error screenshot:


texts: MySQL JDBC does not behaves as the MySQL Workbench

texts:
I'm running the following from the MySQL Workbench, and from my local tomcat using jdbc.

START TRANSACTION;
CREATE SCHEMA IF NOT EXISTS `ROOT`;
CREATE TABLE IF NOT EXISTS `ROOT`.`all_tables` (
    `_id` BIGINT NOT NULL AUTO_INCREMENT,
    `class_type` VARCHAR(100) NOT NULL,
    `schema` VARCHAR(30) NOT NULL,
    `table_name` VARCHAR(30) NOT NULL,
    `server_id` VARCHAR(30) NULL,
    `fields` VARCHAR(0) NOT NULL, PRIMARY KEY (`_id`),
UNIQUE INDEX `_id_UNIQUE` (`_id` ASC));
COMMIT;

It is a copy of query right before the execution which I compose in order to run via the jdbc which fails time after time with the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE SCHEMA IF NOT EXISTS `ROOT`;
CREATE TABLE IF NOT EXISTS `ROOT`.`all_table' at line 2

So technically even if only execute the create table this fails with the same error, only line 1.

BUT, when I run the same query via the MySQL Workbench, it works wonderfully... time and time again!

Any idea what I'm missing?

Thanks.


texts: SQL state S1000 in SQLConnect (but not all the time)

texts:
I have a CentOS6 webserver and a database server (MS SQL Server 2012). When I use the website everything is going fine except that once an hour or so I get a can't connect error in my php code. I looked in the ssl_error_log and found the following error at the same time I got the error:

PHP Warning:  odbc_connect(): SQL error: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source, SQL state S1000 in SQLConnect

Can anyone help me solve this strange problem?
I'm not sure if it's a problem with the sql server or the webserver. I can't find any problems in the event viewer on the sql server.


texts:
SQL Server 2008 SP1 here.

We have a machine where sp_setapprole calls take more than 100ms and they are done many times per user operation on an application.

The developer says that in his setup, sp_setapprole calls take less than 1ms. The machine specs are comparable (I have 12GB RAM, 6 cores, he has 16GB and 8 cores).

This problem makes the application performance to be unacceptable.

So, two actual questions:

1.- What are possible causes (and solutions) of slow sp_setapprole?

2.- How would you debug the problem?


texts: Is it possible to force drop of mysql tables with FK?

texts:
For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table.

Is there a short way to drop a table, and its all child tables (in which they have FK to the parent table)?

For example,

CREATE TABLE test
(
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
) ENGINE=InnoDB

and id is a FK in 5 child tables. Is it possible to drop TABLE test, and force drop of all 5 child tables having id FK? instead of dropping child tables one by one?


texts: Options to migrate large database from Sybase ASE to SQL Server with minimum downtime

texts:
The problem:

Our server database contains 1TB of data. It runs on a Sybase ASE 15.7 server and shall be migrated to SQL Server 2014. The servers will be located in the same data center during migration; an acceptable downtime is 6 hours.

We have considered using Sybase Replication Server, as we understand it, this product supports heterogeneous database replication from ASE to SQL. However; we do not have experience with this. Snapshot and transactional replication might be a possible solution.

We have some experience with SQL Server Migration Assistant and have successfully migrated the database objects and data in a test environment. However based on 8% test data, we have estimated that a complete migration of production database will take about 7 days. We can only have 6 hours downtime, and are not sure if this product can be used for data migration in our case. For database objects, this product has worked fine.

Do you have some recommendations to solve this problem?


texts:
I have a function get_sa001 and a view Axis_RefCustomer.

When I execute my function get_sa001 on it's own for a certain period let's say 2016 - 2017, the execution time is about ~6 seconds.

SELECT d."Selling_date",  d."Value_in_EUR", d."Value_in_currency", d."Site"
FROM report.get_sa001('2016-01-01'::date, '2017-03-31'::date, 32) AS d

When I execute a select on the view Axis_RefCustomer, it runs for around ~1 second.

Select a."Selling_currency" FROM report."Axis_RefCustomer" AS a

When I join them together, the execution time is around ~39 seconds !

SELECT d."Selling_date",
a."Selling_currency",
d."Value_in_EUR",
d."Value_in_currency",
d."Site"
FROM report.get_sa001('2016-01-01'::date, '2017-03-31'::date, 32) AS d
LEFT JOIN report."Axis_RefCustomer"
AS a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code"

Is there anyway to reduce the amount of time my query takes to execute ?

What my function does : My function collects data from the dates I put in as parameters and associates them with a view and it sends me back a table of which I use on a 3rd party app.

Here's my explain analyze for 2016 - 2017 of the function on it's own :

Function Scan on get_sa001 d  (cost=0.25..10.25 rows=1000 width=104) (actual time=2522.959..2534.987 rows=53446 loops=1)
Total runtime: 2537.926 ms

Here's my explain analyze for the view on it's own :

Hash Right Join  (cost=3527.82..5840.32 rows=74513 width=4) (actual time=47.363..71.317 rows=77965 loops=1)
    Hash Cond: ((("T12_RefCustomer"."Site")::text = ("T01_References"."Site")::text) AND (("T12_RefCustomer"."Internal_reference")::text = ("T01_References"."Internal_reference")::text))
    ->  Seq Scan on "T12_RefCustomer"  (cost=0.00..348.81 rows=13181 width=29) (actual time=0.002..2.350 rows=13182 loops=1)
    ->  Hash  (cost=1973.13..1973.13 rows=74513 width=22) (actual time=46.591..46.591 rows=74513 loops=1)
            Buckets: 2048  Batches: 4  Memory Usage: 1019kB
            ->  Seq Scan on "T01_References"  (cost=0.00..1973.13 rows=74513 width=22) (actual time=0.014..18.580 rows=74513 loops=1)
    Total runtime: 72.540 ms

Here's my explain analyze for the query using a left join :

Nested Loop Left Join  (cost=1.23..2375.17 rows=1000 width=108) (actual time=2406.131..42314.297 rows=53446 loops=1)
  ->  Function Scan on get_sa001 d  (cost=0.25..10.25 rows=1000 width=168) (actual time=2405.980..2429.250 rows=53446 loops=1)
  ->  Nested Loop  (cost=0.98..2.35 rows=1 width=28) (actual time=0.547..0.746 rows=1 loops=53446)
        Join Filter: (((d."Site")::text = ("T01_References"."Site")::text) AND ((d."Internal_reference")::text = ("T01_References"."Internal_reference")::text) AND (("T02_Customers"."Site")::text = ("T01_References"."Site")::text))
        Rows Removed by Join Filter: 126"
        ->  Nested Loop  (cost=0.57..1.42 rows=1 width=33) (actual time=0.011..0.057 rows=127 loops=53446)
              ->  Index Scan using "T02_Customers_pkey" on "T02_Customers"  (cost=0.28..0.77 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=53446)
                    Index Cond: ((d."Customer_code")::text = ("Customer_code")::text)
              ->  Index Scan using "T12_RefCustomer_pkey" on "T12_RefCustomer"  (cost=0.29..0.64 rows=1 width=29) (actual time=0.007..0.027 rows=99 loops=68927)
                    Index Cond: ((("Customer_code")::text = ("T02_Customers"."Customer_code")::text) AND (("Site")::text = ("T02_Customers"."Site")::text))
        ->  Index Scan using "T01_References_pkey" on "T01_References"  (cost=0.42..0.92 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=6795220)
              Index Cond: ((("Internal_reference")::text = ("T12_RefCustomer"."Internal_reference")::text) AND (("Site")::text = ("T12_RefCustomer"."Site")::text))
Total runtime: 42318.196 ms

What I've done :
I've Vacuumed my tables and reindexed them but the results have not changed.


texts: Import table data using multiple sql file in psql

texts:
Local Machine(l1):
Windows 10
psql
Remote Machine(R1):
linux
EDB(postgres)
On my local I have 1000+ sql script containing insert statement . psql is also installed locally .
I need to run this sql script from my local command prompt using psql client .
I can connect to remote db as psql -h hostnme -U username -p port DBName  . For single file i know that it can be run like \i f.sql . I also tried combining all sql file in one file on windows using command copy /b *.sql new.sql . But the new sql file size is 4 GB . Which is not easy to work with . Is there any way I can run those 1000+ sql script from psql without combining from command prompt ?


texts:
I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the query with dynamic sql it breaks. Here is the statement that works fine.
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(record_name)
            FROM ams_data
            WHERE report_name = 'class_and_component_prices'
            AND commodity ='whey'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

-- construct dynamic SQL
SET @sql ='
SELECT * FROM
(
    SELECT
        record_name,
        value,
        date,
        commodity
    FROM
       ams_data
        WHERE report_name = ''class_and_component_prices''
        AND commodity = ''whey''
) t
PIVOT(
    max(value)
    FOR record_name IN ('+ @cols +')
)  AS pivot_table ;';

EXECUTE sp.executesql @sql

And here is my version that takes parameters.
ALTER PROCEDURE  [dbo].[pivot_record]
(
    -- Add the parameters for the stored procedure here
    @tablename VARCHAR(max) = nass_data,
    @report_name VARCHAR(max) = class_and_component_prices,
    @commodity VARCHAR(max) = whey
)
AS
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
        DECLARE @cols AS NVARCHAR(MAX),
                @sql  AS NVARCHAR(MAX),
                @actualTable AS VARCHAR(MAX)

        SELECT @actualTable = QUOTENAME( TABLE_NAME )
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = @tablename

        SET @sql = '
        STUFF((
            SELECT distinct '','' + QUOTENAME(record_name)
            FROM ' + @actualTable + '
            WHERE report_name = ''' + @report_name + '''
            AND commodity = ''' + @commodity + '''
            FOR XML PATH(''''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'')
            ,1,1,'''')'

        EXECUTE sp_executesql @sql, N'@cols VARCHAR(MAX) OUTPUT',@cols = @cols OUTPUT;

        -- construct dynamic SQL
        SET @sql ='
        SELECT * FROM
        (
            SELECT
                record_name,
                value,
                date,
                commodity

            FROM
               ' + @tablename + '
                WHERE report_name = ' + @report_name + '
                AND commodity = ' + @commodity + '
        ) t
        PIVOT(
            max(value)
            FOR record_name IN ('+ @cols +')
        )  AS pivot_table ;';

        -- execute the dynamic SQL
        EXECUTE sp_executesql @sql;

When I attempt to run this, I get an error from the first sql statement. I printed off the sql statement prior to attempting to execute and store the result in @cols to ensure that it is exactly the same as my first statement that worked.

I am assuming that some quirk with dynamic sql is causing the issue, but assistance would be appreciated.
EDIT: I figured out that I needed to include a SELECT before the STUFF clause, but I still do not get my intended result, now it just outputs an empty table instead of the proper pivoted data.


texts: In Oracle, when would I want to gather index stats, instead of just gathering stats on the table?  In which use case would index stats make a difference?


texts: NVARCHAR column as PRIMARY KEY or as UNIQUE column

texts:
I'm developing a SQL Server 2012 database and I have a doubt about nvarchar columns as primary keys.

I have this table:

CREATE TABLE [dbo].[CODES]
(
    [ID_CODE] [bigint] IDENTITY(1,1) NOT NULL,
    [CODE_LEVEL] [tinyint] NOT NULL,
    [CODE] [nvarchar](20) NOT NULL,
    [FLAG] [tinyint] NOT NULL,
    [IS_TRANSMITTED] [bit] NOT NULL DEFAULT 0,
     CONSTRAINT [PK_CODES] PRIMARY KEY CLUSTERED
    (
        [CODE_LEVEL] ASC,
        [CODE] ASC
    )
)

But now I want to use [CODE] column as primary key and remove [ID_CODE] column.

Is there any issue or penalty if I have a NVARCHAR column as PRIMARY KEY?

[CODE] column value must be unique, so I've thought that I can set an UNIQUE constraint to that column.

Do I have to use [CODE] as primary key or it is better if I set an UNIQUE constraint on [CODE] column?


texts: What is the use of creating full text catalog in full text indexing

texts:
Just want to know about the processes happening in full text indexing. I know by defining the full text indexing for a table, it will index all the words saved in that table.

But i didn't get the use of creating a catalog for a full text index. And how an index related to catalog. Simply what the full text catalog will do in full text indexing.


texts:
I'm creating cron jobs for my OLAP system.

I have a cron job that deletes the records from the summary table first and then insert the records in it again:

Say Deleting 1 month records, and Summarizing-Inserting them again on daily basis (that is the requirement because the older records are changed often),
deleting approximate 5000 rows and inserting same numbers of rows.

If the cron job performs this many deletes on a daily basis, is it safe for database, with regards to possibilities of table corruption or table crash?

Any other option?


texts:
We have doubts regarding default privileges for owner of objects. Let me show by example:

We start by creating a brand new role and schema:

psql (9.6.2)
Type "help" for help.

postgres=# CREATE ROLE bro WITH LOGIN;
CREATE ROLE
postgres=# CREATE SCHEMA bro AUTHORIZATION bro;
CREATE SCHEMA
postgres=# \c - bro
You are now connected to database "postgres" as user "bro".
postgres=# SET search_path TO bro, public;
SET

We check the default priveleges and nothing shows up:

postgres=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

When creating a table, bro can access it by default:

postgres=> CREATE TABLE t1 (a integer);
CREATE TABLE
postgres=> TABLE t1;
 a
---
(0 rows)

So we REVOKE the default privileges for bro on TABLES.

postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE bro IN SCHEMA bro REVOKE ALL ON TABLES FROM bro;
ALTER DEFAULT PRIVILEGES

There's still nothing in \ddp, but we expect that bro won't be able to access the new tables we create:

postgres=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

We create a new table and bro is still able to access it, which seems wrong to us:

postgres=> CREATE TABLE t2 (a integer);
CREATE TABLE
postgres=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 bro    | t1   | table | bro
 bro    | t2   | table | bro
(2 rows)

postgres=> TABLE t2;
 a
---
(0 rows)

Then we try by explicitly revoking privileges from bro for that table and finally is no longer able to access it:

postgres=> REVOKE ALL ON TABLE t2 FROM bro;
REVOKE
postgres=> TABLE t2;
ERROR:  permission denied for relation t2

In the PostgreSQL documentation they state:

  As explained under GRANT, the default privileges for any object type normally grant all grantable permissions to the object owner, and may grant some privileges to PUBLIC as well. However, this behavior can be changed by altering the global default privileges with ALTER DEFAULT PRIVILEGES.

Could someone explain if this is the expected behavior?

Thank you!


texts: How to add a named constraint in Oracle SQL?

texts:
I have a table. I need to add a NOT NULL constraint to one of the columns. This is my best attempt at a solution:

alter table clookup add CONSTRAINT lookup_9 NOT NULL (clookup_col)

This produces ERROR at line 1: ORA-00904: : invalid identifier on the NOT NULL part. Why?


texts:
I was looking in this page but also on stackoverflow and google because I though this may be a quite common question but could not find anything related... I may not be using the right terminology here.

The question is quite straight forward.

Is it a good practice having nested unique keys (or mix of primary and unique keys) inside another unique key?

example:

I have a table with columns A, B, C, D, E, F and G
Primary or UNIQUE Key would be (A, B) and UNIQUE KEYS (A, B, C) and (A, B, D).

I would like to know whether or not this is kind of things are a good practice in general but also for mysql.

Thank you very much


texts:
I encountered an arithmetic overflow in a simple SELECT statement. Query was as below e.g.

SELECT [SaleValue] FROM Sales

[SaleValue] was of data type decimal(9,0) and not a computed column.

The reason this happened was because somehow the column had a row where this field was storing a value GREATER than specified datatype, e.g. decimal(10,0).

I could only get the select to work when I increased the size of the column. The table in question has two other instances in two other columns and rows.

How was this situation possible? How was an out of range value saved in the column in the first place?

I'm using Microsoft SQL server + this is a base table, not a view.


texts: What is the purpose of having a table NOT LOGGED INITIALLY

texts:
I came across this recently. When defining a table in DB2 LUW (at least 9.5 or higher), you can define it as NOT LOGGED INITIALLY.

Example from the book I read:

CREATE TABLE products (
    productID      INT,
    product_Name   VARCHAR(30)
)
NOT LOGGED INITIALLY;

The documentation I have read states that the table is not logged during execution of INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, or DROP INDEX until a COMMIT statement is executed. Everything before the COMMIT is not logged. Everything after the COMMIT is.

And apparently as long as you define the table as NOT LOGGED INITIALLY, at any point you can issue an ALTER TABLE <table-name> ACTIVATE NOT LOGGED INITIALLY to put the table back into a non-logging state until a COMMIT is issued again.

They gave one example that I can see where this could be useful. The book I read stated that you could issue the following

ALTER TABLE <table-name> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

and this will apparently delete all the data in the table without logging it. I can understand this as being desirable in a test environment where you wish to clear data and re-test without the performance overhead of logging the delete for rollback.

But beyond this case, I am puzzled. Is there any reason you would not want logging on a table outside of a test environment? What other uses are there for this kind of table?


texts: SQL Server 2008 - How to schedule a copy of view to another database?

texts:
I am a SharePoint admin and need to copy the log data in database to another temp database for analysis. The data is stored in a view ([WSS_Logging].[dbo].[RequestUsage]). There is a column [LogTime] on the view and any row older than 14 days will be discarded.

I would like to setup a daily schedule job to copy all records one day before to a temp database. I estimate there will be 1GB data daily. May I have your suggestion the best way to achieve this? If possible, please introduce method to check against the copying have not lost any row during the copy, too.

SQL Server is not my professional area. Any help is appreciated. Thanks.


texts: To take a full backup and differential backup of all databases

texts: Can anyone help me with a script to take a full or complete backup of all databases once in a week and to take differential backups daily. ?


texts: Cannot `create function` in plpython3u, permission denied

texts:
As postgres user, I have create extension plpython3u; in my database

then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true

but when my db_user tries

  create function check_data_valid(id bigint)
    returns boolean
    as $$
    -- ...
    return true
    $$ language plpython3u;

I got the error: permission denied for the language plpython3u

So, with my postgres user then I have tried: grant usage on plpython3u to db_user and grant execute on plpython3u to db_user but both returns the error:

relation python doesn't exist

maybe it's because it's an extension... however, I don't what to do so as to create my stored procedure.


texts: How to restore dump from MariaDB 10.1 to MySQL 5.5

texts:
I generate backups of my databases by using mysqldump.exe -u root --all-databases > mariaDbBackup.sql.

When I try to restore my dump by doing mysql -u root -p < mariaDbBackup.sql, I get an

  Error 1064 (42000) near 'STATS_PERSISTENT=0'

Here is the faulty line:

CREATE TABLE `engine_cost` (
  `engine_name` varchar(64) NOT NULL,
  `device_type` int(11) NOT NULL,
  `cost_name` varchar(64) NOT NULL,
  `cost_value` float DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `comment` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`cost_name`,`engine_name`,`device_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;

It is the first line out of seven on the backup that includes STATS_PERSISTENT=0 so I assume it might have to be a compatibility issue. I am using specifically MariaDB 10.1.37 and MySQL 5.5.39.

I assume there have to be some flag in either generating or reading the dump that I can use to be able to do it.

So, how can I restore the dump or how do I generate a compatible dump?


texts: How to select more than 20 rows in SPHINX engine?

texts:
I use SphinxSE instead of FULLTEXT. Sphinx settings are for end-users of a search engine. According to the official documentation,

LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike
in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is
present by default.

Therefore, any query returns only 20 rows
MariaDB [sphinx]> SELECT * FROM t1 WHERE query='test;mode=any';
+------+--------+---------------+
| id   | weight | query         |
+------+--------+---------------+
|  556 |      1 | test;mode=any |
|  864 |      1 | test;mode=any |
| 1329 |      1 | test;mode=any |
| 1781 |      1 | test;mode=any |
| 1832 |      1 | test;mode=any |
| 2157 |      1 | test;mode=any |
| 2388 |      1 | test;mode=any |
| 2889 |      1 | test;mode=any |
| 3118 |      1 | test;mode=any |
| 3155 |      1 | test;mode=any |
| 3255 |      1 | test;mode=any |
| 3485 |      1 | test;mode=any |
| 3495 |      1 | test;mode=any |
| 3623 |      1 | test;mode=any |
| 4034 |      1 | test;mode=any |
| 4525 |      1 | test;mode=any |
| 4563 |      1 | test;mode=any |
| 4659 |      1 | test;mode=any |
| 4736 |      1 | test;mode=any |
| 4807 |      1 | test;mode=any |
+------+--------+---------------+
20 rows in set (0.027 sec)

I experimented with various settings in the sphinx.conf, but am unable to change the setting to return more results in a SQL query.


texts: Mysql Select table records which have not been updated since last month

texts:
I have a table names 'scoredata' having fields
id, lessonid, levelid, studentid, addedate
in which score for each day will be entered by the admin.
I want to select only those studentid whose score has not been
added from last four weeks. How can I do this in a single query.


texts: Restoring database backup file gives access error

texts:
I am running SQL server 2008 and trying to restore from a backup file.

Note: the backup file and folder and read/write access to "everyone". Running the command as "master"

Running

 RESTORE FILELISTONLY FROM DISK = 'D:\path'

Gives the following error:

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'D:\path'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks,
Bruce


texts: Is there any good tool for making Database Design and Prototypes?

texts:
I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good design tool.

Is there any good (and maybe free) Database Design tool?


texts: How can I create a database diagram, showing clearly the FK and PK in SQL Studio 2016?

texts:
I would like to know the best way to create a database structure diagram, that shows relationships between certain tables. I am using SSMS 2016.

I tried using "database diagram" option in the SSMS but it doesnt clearly show the FK and the PK in the tables.

Is there any other way to achieve this easily?
I do not need all the tables, just some selected ones.

Thanks in advance.


texts: Oracle: dbms_repair outputs zero errors about known corrupted object

texts:
In an Oracle 9i instance we have a datafile with corrupted blocks as evidenced by the log generated by the exp utility:

. . exporting table                  IMPORTANT_TABLE
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 120, block # 249555)
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-01110: data file 120: '/ora/data/important_datafile.dbs'

We tried to move all good rows to another table by means of ifentifiying the corrupted row rowid using dbms_rowid.rowid_create(1,a,b,c,0) where a is the DATA_OBJECT_ID of the table, b is the datafile number and c is the block number. Then trying to copy all rows but the damaged one to another table everything fails because most probably there are other corrupted blocks and there are other corrupted rows.

exp doesn't write to the log a list of all corrupted blocks, it just stops exporting that table an continues with the next one.

We tried to use dbv to identify all corrupted blocks with the idea of further filtering out bad rows and be able to copy the good ones, but the output of dbv doesn't include datafile number, and what seems to be a block number doesn't make sense since 249555 is nowhere to be found. Also trying to use dbv to connect to the database providing a SEGMENT_ID for it to report all corrupted blocks seems buggy since it insists in asking for the dmp file instead, even when the documentation states they are mutually exclusive.

Out last attempt was to use dbms_repair.check_object(...) and then dbms_repair.fix_corrupt_blocks(...). We followed this procedure by Burleson Consulting, we created the repair table and the orphans table .

But dbms_repair.check_object(...) yields zero corrupted errors, even when it's demonstrated that there are corrupted blocks in the table.

This is how we ran it:

set serveroutput on
declare corr_count binary_integer;
begin
corr_count := 0;
dbms_repair.CHECK_OBJECT('IMPORTANT_OWNER','IMPORTANT_TABLE',null,dbms_repair.table_object,'MY_REPAIR_TABLE',null,null,null,null,corr_count);
dbms_output.put_line(to_char(corr_count));
end;
/

Obviuosly running dbms_repair.fix_corrupt_blocks(...) does nothing because the package thinks there are no corrupted blocks to begin with.

The datafile is corrupted in all backups and all exports, because of a disk array failure than happened sometime ago.

I know Oracle 9i is end-of-life but the client uses it and I have to support it.

The question is, having attempted all the above:

What's left to attempt?
What are we doing wrong?


texts:
In an application where a user can have copies of a mobile application installed on multiple devices I'd like to know how many iphone and how many android apps each user has.

I tried a query such as

gv2=> SELECT userid,
       COUNT(ALL version_identifier LIKE '%ios%'),
       COUNT(ALL version_identifier LIKE '%android%')
FROM gl.user_device
GROUP BY userid;
                userid                | count | count
--------------------------------------+-------+-------
 46d0f5b7-42b0-4aad-9162-1390c32cb06e |     7 |     7
 5d519794-abfe-4863-82d4-6da33db7637b |     7 |     7
 a81cff6b-30f2-4b6e-a5bf-b1a933904473 |     1 |     1
 b65f0708-0cd1-11e7-878b-06fa189da46b |     4 |     4
 94b91b02-ff43-4a9a-b317-037fa2a347d3 |     1 |     1
 a4cacd98-1216-4801-b058-b28b8fa632a9 |     8 |     8
(6 rows)

Which is clearly wrong.

For example

gv2=> SELECT userid FROM gl.user_device WHERE version_identifier LIKE '%ios%';
                userid
--------------------------------------
 5d519794-abfe-4863-82d4-6da33db7637b
(1 row)

In case it matters I need to support Postgres 10.1 but preferably also 9.5.10 as I have one old Production DB still on that version.


texts: postgres: could not receive data from WAL stream: ERROR: requested WAL segment has already been removed

texts:
Configuration: postgres 9.6 with a 3 cluster node. db1 is the master, db2 and db3 are replicas. WAL files are archived in AWS S3 using custom pgrsync tool. Cluster managed by patroni. The archive_command and restore_command is properly configured on all the nodes.
To simulate: On db1, do heavy writes (like vacuum a large table) and then stop db1 by sudo systemctl stop patroni). db3 becomes the new leader. db2 requests more WAL files, which it gets via the proper restore command from AWS S3, becomes replica to db3.
Now, start db1 again by (sudo systemctl start patroni). But db1 (the old leader and the new to-be-replica) never comes up as a replica and gives the error message could not receive data from WAL stream: ERROR:  requested WAL segment 0000002400053C55000000AE has already been removed. This error message is reported by db3 (the leader), which db1 just logs it.
So, let's see the timeline. Initially db1 was in timeline 35 (0x23) and did write the following files to archive:
0000002300053C55000000AE   (TL is 23 and archive at 53C55/AE)
..
0000002300053C5600000043   (TL is 23 and archive at 53C56/43)

db1 is stopped at this point.
db3's logs show this:
received promote request
redo done at 53C56/A3FFC8C0
selected new timeline ID: 36
database system is ready to accept connections

and db3 copies the following files to archives
00000024.history
0000002300053C56000000A3.partial
0000002400053C56000000A3
0000002200053C55000000AE

As db3 became leader, db2 starts the process to become replica to db3 (which it successfully becomes) and here is the summary of the logs:
restored log file "00000024.history" from archive
restored log file "0000002300053C55000000AE" from archive
restored log file "00000023.history" from archive
redo starts at 53C55/AE0001A8
restored log file "0000002300053C55000000AF" from archive
...
restored log file "0000002300053C5600000042" from archive
Retrieving 0000002300053C5600000043 to pg_xlog/RECOVERYXLOG FAILED  (log by pgrsync)
...
Retrieving 0000002300053C56000000A2 to pg_xlog/RECOVERYXLOG FAILED
restored log file "0000002400053C56000000A3" from archive
consistent recovery state reached at 53C56/A3FFE900

db1 is started now and here are the logs:
LOG: database system was interrupted while in recovery at log time 2021-01-28 04:08:01 UTC
HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target
LOG: invalid record length at 53C55/AE0001E0: wanted 24, got 0
LOG: started streaming WAL from primary at 53C55/AE000000 on timeline 36
FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000002400053C55000000AE has already been removed
... and repeats this over and over

Points to note:

0000002400053C55000000AE was never written to archives by any postgres node. The old leader (db1) copied the archive 0000002300053C55000000AE (note: 0023, not 0024) before it was stopped.
The new leader (db3) copied 0000002200053C55000000AE (note: 0022, not 0024)
max_wal_size is set to 1024 on all nodes.
After db3 became the new leader, there was hardly any activity on the nodes. db3 only writes WAL files every 10 mins (archive_timeout=600s).

Questions:

Is there any thing wrong in the configuration that makes the the old leader asking for a WAL segment, which the new leader does not have?
How to restore the old leader (db1) at this state, without having to erase and start over?


texts:
I need to dump a database from a server that uses the old password hash. It's a shared server and I am unable to change the user's password to the new hash.

Attempts with MySQLWorkbench fail with the error:

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

Does anyone have a solution?

PS, in the end I did get SSH access to the server, so I was able to run mysqldump there. But in absence of that I don't know another way.

Thanks for insight.


texts: Not able to select secondary replica using failover wizard during manual failover

texts:
When I am performing failover using ssms 18.6 GUI from primary to secondary replica the replica selection box is disabled. The same task i can able to do using other version of ssms.
My team members using ssms 18.6 to perform failover and they can able to do that. Please anyone can guide me on this issue?.


texts:
I dumped all my databases in order to import them into a slave.

mysqldump --all-databases > dump.sql

The import seems to be very very slow.
Checking the processlist, I saw a simple insert on a 20 rows table that took 400+seconds.

I had to ignore some big and not critical tables from the dump  in order to import in a reasonable time.

I don't understand if I'm doing something wrong, the whole dump was about 800MB and the "big" table I ignored was no more than 170K records, which I don't think it's big at all...

Also I often dump a single database to load it on a testing environment, usually without problem. It seems the --all-databases dump is very slow.

Any suggestion welcome.


texts: Long INSERT on large table blocks INSERT on other tables

texts:
Server is MySQL 5.7 and the tables are InnoDB. The large table consists of 20+ GB of data. The large table has several indexes on it and also full_text_indexes!

Use-Case explained:

There are multiple long INSERT with each of 250 "VALUES()" rows. While those are executed, the server starts to delay other tiny INSERTs on other tables.

There is no obvious technical (no ON INSERT, TRIGGER or similar) or logical connection between the large table and the others. When inserting something into the large table it should not have an impact on other inserts, at least this is the goal ...

Is there something I can check/set to make requests on those other tables not waiting for the INSERT on this large table to be finished?


texts: SQL Server Query Store - Force Plan does not work

texts:
Here is my situation: I have a simple SP (containing giant joins and a few temp tables), which essentially returns a resultset, works in 15 seconds in 2008 compatibility level, but takes 3 hours when compatibility level changes to 2014. When I revert back to 2008, everything is dandy, then slow when I switch to 2014.

I want to know how I can force the plan (or how to in general force the SP) to run in the same way it did in 2008 CL (whether that's accomplished using Execution Plan forcing or not, doesn't matter to me).

Here is what I tried: I tried to FORCE execution plan using Query Store but that does not work. Because the SP generates a new QUERY ID every single time.. i don't understand how this tool is supposed to work, because no matter how much you force it, it'll be a new QUERY ID and therefore plan the next time. Here is what I did:

I can also confirm that this does NOT apply to me: Query Store Force Plan feature doesn't work


texts: How to use COLUMN and TABLE options in a RAISE statement

texts:
According to the docs, there are options to RAISE called TABLE and COLUMN. However , if I explicitly set them, they do not end up in the resulting error message. It seems only DETAIL and HINT are ever used in the error message. How can you get the other options to show up? E.g.:

RAISE EXCEPTION 'invalid_parameter' USING
    DETAIL = FORMAT('The specified user "%s" was not found.', _usr),
    HINT = 'Enter the username of an existing user.',
    COLUMN = 'username',
    TABLE = 'accounts';

Issues an error message:

... ERROR:  invalid_parameter
... DETAIL:  The specified user "aanders" was not found.
... HINT:  Enter the username of an existing user.
... CONTEXT:  PL/pgSQL function _api.find_user(text) line 24 at RAISE
... PL/pgSQL function _api.accounts_update_trigger() line 97 at assignment
... STATEMENT:
...     WITH pg_source AS (...

There is no "TABLE" or "COLUMN" shown.

UPDATE

The key term I needed was "VERBOSITY" - once I searched for that I found duplicate questions, e.g. https://stackoverflow.com/q/41959752/1571426
(not sure if a question on a different forum makes this "duplicate" or not.)


texts: Handling Strong and Weak entity types in a one-to-one relationship

texts:
I am modelling a database for a term project and I have come across something I do not have a definite answer to.
Diagram
I was supplied with the following Entity-Relationship diagram:

Description
So, as you can see, such diagram depicts the following entity types:

District, which has the attributes Number(set up as Primary Key), Type and Description.

Delegate, that holds the attributes Id (defined as Primary Key), Name, Sex and Description.

Where District is the strong entity type supporting Delegate, but the cardinality of this relationship is 1:1. Up to this point, I have seen 1:M relationship cardinalities regarding strong/weak entities, and I know how to map those to tables, but in this case, I am not so sure, mainly because I do not know how I could get around the possibility of repeating Delegates if the Delegate.Id and the District.Number were to be a composite key.
Questions

What shall I do at the implementation level?
Do I create a table to store Delegate instances, with the Number of the District as the only key?
Is my understanding of composites key wrong?


texts:
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?

This is a similar issue to the column creation one that is solved with this code:
https://stackoverflow.com/a/12603892/368511


texts:
So I've entered a couple hundred rows of data into a database manually and have begun implementing a Java frontend. However, I got an unexpected result and now when I go back into RazorSQL, the database is gone (I believe a DROP command was executed). Is all the data I entered gone forever?

Part of this I believe stems from me not fully understanding where databases are stored. I'm using RazorSQL on OS X and can't seem to find a path where the database is stored.


texts: Can DBA revoke access privilege from the owner of a table?

texts:
If I create a table T, I become the owner of the table.

Can DBA access T?

Can DBA also revoke my access or grant privileges on T?


texts:
My trigger is meant to calcalute the grade of a student base on the column value for every insert.But if a column value for the column is modified it the corresponding Grade should change. Currently i having error:"Invalid object name StudentMarks for each code section.
I did some search to the cause of the error but still can find one. Please advise

        -- ================================================
    -- Template generated from Template Explorer using:
    -- Create Trigger (New Menu).SQL
    --
    -- Use the Specify Values for Template Parameters
    -- command (Ctrl-Shift-M) to fill in the parameter
    -- values below.
    --
    -- See additional Create Trigger templates for more
    -- examples of different Trigger statements.
    --
    -- This block of comments will not be included in
    -- the definition of the function.
    -- ================================================
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:      TSQL PROGRAMMER
    -- Create date:
    -- Description: Calcute studnet Grade
    -- =============================================
    CREATE TRIGGER dbo.ComputeGrade
       ON  dbo.StudentMarks
       AFTER INSERT,UPDATE
       declare @markobtained  int =(Select MarkObtained from inserted i )
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        if(@markobtained between 0 and 49)
        BEGIN
        UPDATE StudentMarks SET Grade='Fail'
        END
        ELSE IF (@markobtained between 50  and 64)
        BEGIN
        UPDATE StudentMarks SET Grade='Fair'
        END
       ELSE IF(@markobtained between 65 and 74)
       BEGIN
       UPDATE StudentMarks SET Grade ='Good'
       END
       ELSE IF(@markobtained between 75 and 84)
       BEGIN
       UPDATE StudentMarks SET Grade='Very Good'
       END
       ELSE IF(@markObtained between 85 and 100)
       BEGIN
       UPDATE StudentMarks SET Grade ='Excellent'
       END
       ELSE

       PRINT'Invalid mark range'

     END
    GO


texts: Insert statement fails silently when a FK constaint can't be statically evaluated

texts:
I ran into an interesting issue which I'm not entirely sure how to address.

My tables:

CREATE TABLE entity (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  properties jsonb
);

CREATE TABLE hierarchy (
  parent_id int REFERENCES entity(id) NOT NULL,
  child_id int REFERENCES entity(id) NOT NULL,
  PRIMARY KEY(parent_id, child_id)
);

And some dummy data:

INSERT INTO entity (id, properties)
VALUES (1234, '{}'::jsonb);

The issue occurs when inserting data into hierarchy which violates this FK constraint. The following doesn't work, as expected, and returns a helpful error message:

WITH entity_result AS ( SELECT 5678 AS id )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 1234, id FROM entity_result;

-- [23503] ERROR: insert or update on table "hierarchy" violates foreign key constraint "hierarchy_child_id_fkey" Detail: Key (child_id)=(5678) is not present in table "entity".

However, when doing another simiiar query where the ID's value cannot be statically assessed, instead of reporting the error it just fails silently. For example, the following query:

WITH entity_result AS (
  SELECT id FROM entity
  WHERE properties->>'member_id' = '92385' )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 1234, id FROM entity_result;

-- ...silence

Simply fails, and without any indication as to why.

Is there any way to get some information about why a query like the above fails? It's baffling me!


texts:
Structure

1 table named: scan

2 columns named: id, and date

Problem

I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the condition is equal to 0.

Query 1

Show '1' if the latest ID row in the database has scan date of today otherwise show '0' when no today's date is present.

SELECT
  COUNT(scan.date) AS datecheck
FROM scan
WHERE scan.id = (SELECT
    MAX(scan.id) AS datecheck
  FROM scan
  WHERE scan.date = CURDATE())

Query 2

If there is no entry for today, meaning a results of '0' then create a new row with ID+1 and the current date.

INSERT INTO `scan`(`id`, `date`) SELECT (MAX(id)+1), CURDATE() FROM scan;

I have been trying for hours now with no luck yet, I would be grateful if you can tell me where I am going wrong and let me know what the correct query is. Thank you.


texts: user rights validation inside stored procedure?

texts:
You have an application with a Users table,
and the Users table has various columns to indicate
what rights a User has within the application.

Ignoring for the purposes of this question what should be done
in server-side app code to implement user rights authorization,
I am wondering from a dba's perspective (and security perspective) whether it is a good idea or an anti-pattern to include validation of the user's
rights inside of the stored procedure.

For instance, imagine a generic blog or CMS app.
It has a stored proc where the UserID is one of the
parameters to the proc. The Users table has a column to flag whether the User is
can be an Editor in a certain section of the CMS.
The stored proc then includes a step to verify against the Users
table that yes, given this UserID, this UserID has the
the Users.Editor flag set to true, so this user can
go on with the rest of the stored proc; if the user has
the flag set to false, then the proc has logic to not let
him continue.

Should the server-side app code (e.g., .NET) handle all of this,
or is adding a check inside the proc, even if somewhat redundant,
still worth doing?

Part of the reason I am curious is because beyond this article,
in which the author says, "...role checks inside stored procedures is nuts...",
I am not able to find much about the subject.
http://codebetter.com/jeremymiller/2005/07/05/good-and-evil-in-the-garden-of-stored-procedures/


texts: Trouble with Alerts and Notifications on SQL Server 2000

texts:
I'm trying to set up alerts and notifications in SQL Server 2000, but they are not working.  I'm testing them for Agent scheduled jobs, and I get a note saying:

  NOTE: Failed to notify '[operatorname]' via email.

Then in the Current Error log, I see an entry like:

  [264] An attempt was made to send an email when no email session has been established

xp_sendmail works fine, and I'm actually using it in these agent jobs.  The notifications are supposed to tell me the procedure completed successfully.  I'm only asking for completion notification to get this working, then I'll change it to failure notifications.

Anyone know why this is happening and how to correct?  I'd really like notifications to work.


texts: why will compatibility mode change amount of storage iops

texts:
We recently upgraded from SQL Server 2012 to SQL Server 2016.  We noticed an unusual amount of IOps on our Dev and Integration servers, but not on production.  The only difference is that production has not yet been set to SQL Server 2016 compatibility mode 130.

Running the following script I can turn off/on the extra IOps on our integration server. Why would compatibility mode make such a huge difference in storage?

EXEC sys.sp_MSforeachdb @command1 = N' ALTER DATABASE ?
    SET COMPATIBILITY_LEVEL = xxx'

Where xxx is 110 for higher IOps or 130 for lower IOps.


texts: Slow query with where and index on some fields

texts:
I'm working on a project with the PostgreSQL 12 database.
I have a table of about 500 MB.
The structure of the table is as follows:
create table table_name
(
    id serial not null
        constraint table_name_pkey
            primary key,
    rejected_at timestamp,
    reserved_until timestamp,
    reserved_id integer,
    deleted_at timestamp,
    accepted_at timestamp
);

For the following query:
select *
from table_name
where rejected_at is null and (
        reserved_until is null or
        reserved_until <= '2021-05-14 14:23:16'
    )
  and deleted_at is null

I defined the indexes:
create index table_name_deleted_at_index
    on table_name (deleted_at);

create index table_name_rejected_at_deleted_at_index
    on table_name (rejected_at, deleted_at);

create index table_name_reserved_until_index
    on table_name (reserved_until);

But it has a very low performance. What can I do to improve performance?
The result of the explain analysis:
Index Scan using table_name_deleted_at_index on table_name  (cost=0.42..3827.19 rows=15697 width=936) (actual time=106.780..261.118 rows=1534 loops=1)
  Index Cond: (deleted_at IS NULL)
  Filter: ((rejected_at IS NULL) AND ((reserved_until IS NULL) OR (reserved_until <= '2021-05-14 14:23:16'::timestamp without time zone)))
  Rows Removed by Filter: 21704
Planning Time: 0.130 ms
Execution Time: 261.333 ms

I created the suggested index:
CREATE INDEX ON table_name (COALESCE(reserved_until, TIMESTAMP '-infinity'));

select *
from table_name
where rejected_at is null and COALESCE(reserved_until, TIMESTAMP '-infinity') <= '2021-05-14 14:23:16'
  and deleted_at is null

but the query plan selects deleted_at index for the updated query:
(development database)
Index Scan using table_name_deleted_at_index on table_name (cost=0.42..3277.19 rows=4845 width=938) (actual time=15.144..15.201 rows=48 loops=1)
  Index Cond: (deleted_at IS NULL)
"  Filter: ((rejected_at IS NULL) AND (COALESCE(reserved_until, '-infinity'::timestamp without time zone) <= '2021-05-14 14:23:16'::timestamp without time zone))"
  Rows Removed by Filter: 18633
Planning Time: 0.209 ms
Execution Time: 15.251 ms


texts: Database Mirroring: Cannot set witness in Microsoft SQL Server 2014 using Azure

texts:
I am using Azure.

I am unable to connect to a witness server though Microsoft SQL Server 2014 for database mirroring.

Everything I have researched seems to point to this article https://support.microsoft.com/en-us/kb/940254.

I cannot seem to get the DNS settings correct, or figure out how to set the host file, if that is even the fix.

I was mostly following this guide https://msdn.microsoft.com/en-us/library/ms186384.aspx for steps on setting up database mirroring.

Here is a brief summary of my scenario.

Principal Server

Cloud Service: MP
VM: MP (Windows Server 2012 R2 Datacenter)

Mirror and Witness Server

Cloud Service: MF
VM mirror: MF (Windows Server 2012 R2 Datacenter)
VM witness: MW (Windows Server 2012 R2 Datacenter)

I am getting this error when trying to run the SQL COMMAND:

ALTER DATABASE database SET WITNESS = 'TCP://MF.cloudapp.net:5023';

  Msg 1456, Level 16, State 3, Line 1

  The ALTER DATABASE command could not be sent to the remote server
  instance 'TCP://MF.cloudapp.net:5023'. The database mirroring
  configuration was not changed. Verify that the server is connected,
  and try again.

I have opened specific ports on the firewall to allow connections and have set up Endpoints on the Azure portal.

Any assistance you can provided will be very much appreciated.

In the meantime, I am going to try giving the witness server its own cloud service so the end points will all be the same as mentioned on this page: http://go4answers.webhost4life.com/Example/trouble-setting-witness-182317.aspx

I also am going to try setting all 3 VMs on one cloud service then adding them to an availability set as mentioned on this page: https://cuteprogramming.wordpress.com/2014/10/16/database-mirroring-in-azure/


texts: Exporting table records to an INSERT script

texts:
I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required INSERT statements. In doing so, I can reset the development environment quickly after each test.

Can anyone tell how to accomplish this using SQL Server 2008, SQL Management Studio, and/or Visual Studio 2010?

Note: None of the tables have any foreign keys, I have full permissions in the source database, and the number of rows per table is no more than a few dozen.


texts: Improve performance on write-only table?

texts:
I am using MySQL 5.5 with innoDB. The basis of my server is Netty, JDBC and BoneCP.

I have a log table that contains user inputs(HTTP header, request body etc). This table will only be read very rarely for reasons like security and data recovery. Therefore the read performance is not something we care about.

There are five columns in this table.

Name       | Type
--------------------------------------------------
userNumber | medium integer
logTime    | timestamp
header     | varchar(100)
body       | varchar(200)

What are some tips that will improve the insert performance?


texts: How un-clustered is a CLUSTER USING table in PostgreSQL

texts:
I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL:

# CLUSTER table USING index_name;
# ANALYZE VERBOSE table;
# CLUSTER VERBOSE;

A maintenance task periodically runs CLUSTER VERBOSE to keep things fresh.  But is there a test I can run to see how fragmented the table is, prior to running CLUSTER VERBOSE? Maybe something like:

# CLUSTER ANALYZE
table 40000 records. 4000 observed clusters, 5000 potential clusters (20% fragmentation)

Note that I use CLUSTER so data accessed at the same time is "defragmented" into a small number of disk blocks.  For example I have thousands of attributes that go with each page.  a CLUSTER page_attribute USING page_id; puts all the attributes next to each other, greatly reducing disk load.


texts: Data Validation before entry into Couchbase server?

texts: Is it possible to validate the data before it is entered into couchbase? I did not find any documentation confirming this. But I believe there must be some way to do it.


texts: What are the two protocols used by SQL Server Windows authentication?

texts:
I was asked this question in an interview, and I have no idea what the answer is.

I know we have Windows authentication mode and SQL Server authentication. But the interviewer said that we have two protocols that Windows verify when using Windows authentication.

I just can't find anything on the Internet about this.


texts:
Is there a way to execute multiple operations using the WITH statement?

Something like

WITH T AS
(
  SELECT * FROM Tbl
)
BEGIN
  OPEN P_OUTCURSOR FOR
    SELECT * FROM T;

  SELECT COUNT(*) INTO P_OUTCOUNT FROM T;
END;

I want to select some data and the count of it...


texts: Is it normal that occasionally Page Life Expectancy drops to 0 and queries start giving timeouts in SQL Server?

texts:
Can somebody shed some light on if it's normal that Page Life Expectancy drops occasionally to 0 in SQL Server (2014) resulting in slow queries and timeouts?

I'm facing this on my developer machine when developing a web application and I don't know whether it is because of the development environment or something else. PLE may be over 10000 for one day or a couple of hours (during which queries work fast) until it drops to 0 and all my queries go from 15ms to 40 seconds or more.

I mean is there any way to control the performance of SQL Server in this matter or is this by design of the SQL Server that it may get slow, for example, due to memory pressure until it comes faster again etc.?

To me it looks like queries are read in a cache and at some point they're flushed out and queries start giving timeouts because everything is read from a disk. Is this something that can be avoided?


texts: MySQL, import SQL dump with another database

texts:
I have an SQL dump of db1 that contains this line:

"USE db1"

If I import that file into db2 with:

mysql -D db2 -uroot -p < /var/backups/db1.sql.gz

Is there the risk that db1 will be overriden?


texts: Changing a column data type from datetime to float

texts:
I have a table with a float column. All values in this column for all records are NULL.
I want to change the data type to datetime.

I executed the query:

ALTER TABLE MyTable ALTER Column MyColumn DATETIME NULL

It works. But when I try to revert the changes by setting the data type to float:

ALTER TABLE MyTable ALTER Column MyColumn FLOAT NULL

I get the exception:

  Implicit conversion from data type datetime to float is not allowed.
  Use the CONVERT function to run this query.

Note that column MyColumn is null for all records.


texts:
I'm fairly new to databases. I want to upload a .sql file to a mysql server. However the database I want to update contains many .frm .myi .myd files. Should I delete those files and replace with the .sql?

Any suggestions/tips would be greatly appreciated.


texts:
I'm doing an application and I have the following problem:

I'm doing a LOAD DATA INFILE into a table from a txt file, that has 2 million rows.
I want to see the table_rows of my table with:

SELECT table_rows FROM information_schema.tables WHERE table_name = 'my_table';

I want to do this while it's being loaded, I know how many rows the file has previously, so I get an approximate "progress" number.
The problem is that table_rows only refreshes when I do
"analyze table my_table"
(I had it being refreshed every second before but I think I changed some configuration and I screw it)
Is there a simpler way to do this? I'm getting crazy. It should be exact but I haven't found any way.

I guess what I could do in first place is set up the "Analyze tables update time" configuration to 5/10/20 seconds.

If there is a simpler way I'd appreciate it 50000 times.


texts:
I'm managing a server with medium to high traffic.

It serves a wordpress website.

It seems that it use a lot of CPU (over 300% at some points) and always stack around 15% of memory usage.

It is a MySQL 5.7 server with InnoDB engine under nginx and php7-fpm.

Server has 16GB RAM

I tried to tune with mysqltuner but that doesn't help

Here is my mysqld.cnf

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 64M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 192M
query_cache_type        = 1
innodb_buffer_pool_size = 10G

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/lib/mysql/Ubuntu-1604-xenial-64-minimal-slow.log
slow_query_log_file     = /var/log/mysql/mysql-slow.log
slow_query_log          = 1
#long_query_time = 5
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Let me know what to tune on the above

Kind Regards

Update

The problem was solved but not on the current server.
I created an image of the VPS and requested another virtual machine. Copied that on the new server and the problem solved.
I can only imagine that there was a faulty hard drive on the initial machine.

I accepted @Rick James answer as correct because after debugging mysql slow query log, as he suggested, I found that all the slow queries reported didn't make any sense.


texts:
Is there a way to create a SQL Server 2014 database so that all data tables will be compressed automatically?
Is there a way to compress all the existing tables of a SQL Server 2014 database?

Background: we run a data-gathering program that writes several multi-million row tables into a database which is then copied to a separate computer for offline analysis and reporting, often in a different physical location. The separate computer is not as powerful as the data-gathering computer, and has limited disk space, so the database size is an issue.

Edit: We use SQL Server 2014 Enterprise. Each table has a primary key and two additional indexes.


texts:
Can anyone please explain me the below one. I used the below query in getting the table columns info, but the max length seems to be x*2 of the actual col length. I mean if I have a col with datatype nvarchar(10) the output of my query has max_length value as '20'. I'm little confused here.. A'm I looking at the right one?

SELECT
    c.name  ColName,
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
     c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'

FROM
    sys.columns c
INNER JOIN
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('Table Name')


texts: ORA-39244:Event to disable dropping null bit image header during relational select

texts:
I was trying to import a dump file using IMPDP command and has end up with the following errors:

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_FULL_01 for user user1
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 798
ORA-39244: Event to disable dropping null bit image header during relational select
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 1825
ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"

I have searched on oracle forums and google for any solution. Not sure why this error is happening. Is this related to the db configuration?


texts: SQL Login can access Linked Server w/ impersonation, but Windows Login cannot

texts:
In SQL Server 2017 I have a Linked Server defined to use a fixed remote SQL Login for all local users.
EXEC master.dbo.sp_addlinkedserver
@server = 'RemoteServer', @srvproduct='SQL Server';

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='RemoteServer',@useself='False',
@locallogin=NULL,@rmtuser='remoteUser',@rmtpassword='pass123';

This Linked Server can be used successfully by any local SQL Login, but not by a Windows login.
USE [master]
CREATE LOGIN [MyDomain\myUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

When this Windows user is logged in, attempts to query the Linked Server timeout.
SELECT * FROM [RemoteServer].[RemoteDatabase].[dbo].[RemoteTable]

The exact error message is this:

OLE DB provider "SQLNCLI11" for linked server "RemoteServer" returned
message "Login timeout expired".

OLE DB provider "SQLNCLI11" for
linked server "RemoteServer" returned message "A network-related or
instance-specific error has occurred while establishing a connection
to SQL Server. Server is not found or not accessible. Check if
instance name is correct and if SQL Server is configured to allow
remote connections. For more information see SQL Server Books
Online.".

I know that network connectivity is not a problem because the Linked Server is working correctly for local SQL Logins.
What am I doing wrong?
Actually I was trying to run the above select from inside a stored procedure with the fixed execution context of a user linked to a SQL Login.
USE [master];
CREATE LOGIN [localUser] WITH PASSWORD='pass456', DEFAULT_DATABASE=[master];

USE [LocalDatabase];
CREATE USER [localUser] FOR LOGIN [localUser];
GO

CREATE PROCEDURE dbo.MyStoredProcedure
   WITH EXECUTE AS 'localUser'
   AS
   SELECT * FROM [RemoteServer].[RemoteDatabase].[dbo].[RemoteTable]

But executing the stored procedure is also timing out, which makes me think the impersonation doesn't affect the Linked Server query at all.
What can I change to get the above to work? Unfortunately, I'm not able to make changes to the remote server. Much grateful for any suggestions.


texts:
It seems that MySQL does not support window functions.
E.g. the simple: COUNT(*) OVER() AS cnt does not work.
What I am not sure is if this applies to commercial version as well (I assume the community version is limited subset).
If not, how does one work around this missing feature?


texts:
I need to know the file extension of the file types cdump, incident, trace, alert.

Actually I want to create files of the above types manually using mkfile command.

There are no existing files of above type but only main empty directories exist, may be since my server is fresh and didn't get opportunity to create these files.

If you mention even the directory (last sub-directory) in which these type of files are stored, would be a favor to me.

My ultimate purpose is to delete these files using adrci purge command which is my test scenario.

I am using Solaris OS and oracle Release 11.2.0.2.0.


texts:
Possible Duplicate:
  Should every table have a single-field surrogate/artificial primary key?

I can't imagine ever not using a unique ID with every table, which is why I'm asking this question. Should you ever not assign an unique ID to each row in a table?


texts: Problem with union casting integer to ceiling(decimal)

texts:
I have this scenario, it looks like MySQL is taking the largest decimal value and tries to cast the other values to that.

The problem is that this query is generated by an external library, so I don't have control over this code, at this level at least.
Do you have some idea how to fix this?

SELECT 20 AS x
  UNION SELECT null
  UNION SELECT 2.2;
+------+
| x    |
+------+
|  9.9 | -- why from 20 to 9.9
| NULL |
|  2.2 |
+------+

Expected Result

+------+
| x    |
+------+
|   20 | -- or 20.0, doesn't matter really in my case
| NULL |
|  2.2 |
+------+

Adding more context, I'm using Entity Framework 6 with an extension library http://entityframework-extensions.net/ to save changes in batches, specifically the method context.BulkSaveChanges();, this library creates queries using "select union".


texts: Upgrade Postgresql from 9.6 to 11 with different data directory

texts:
I am trying to upgrade PostgreSQL version from 9.6 to 11. I am following this link. The problem is I have changed my default location of PostgreSQL data directory from /var/lib/postgresql/ to /opt/postgresql/. So when I am using pg_lsclusters it is returning following

When I am running the sudo pg_upgradecluster -m upgrade 9.6 main its returning following error

Error: move_conffile: required configuration file /var/lib/postgresql/11/main/postgresql.conf does not exist
Error: Could not create target cluster


texts: why processlist list shows limited threads

texts:
Actually I have set max_connection value set to 500 but processes shows only 266 i.e when I run
show full processlist it shows only 266 processes not more than this however my connection value is 500.
I analyzed every query and found that every query is using single table there is no query which is using 2 tables.
Is any other settings which I am missing? Please suggest on this.Why it is showing only 266 processes?


texts: a user, that is a organizer can create conferences, but can also participante in conferences

texts:
In a conference management system, a user can create one ore many conferences. The user that creates a conference is the conference organizer. But a user can also register in a conference.
So there should be 3 entities: Conference, User, Organizer

Conference: id, name, description
Organizer: id, name, description
User: id, name, username, password, email, etc

Now, do you know how the relationships between this entities should be?
This model seems difficult to understand, because a organizer can organize multiple conferences and a conference is organized by one organizer.  So should be a 1 to many relationship between organizer and conference? But then, the organizer is also a user that besides create a conference can also register in conferences. So how should be the relation between the user and the event/organizer?


texts:
I was profiling a DB for queries against it but have noticed that when a table in the DB is accessed through a linked server this does not show in profiler.  Can anyone advise on why this is or what events I need to select to ensure this is captured in future?

Thanks


texts: Oracle 19 ORA-01450: maximum key length (6398) exceeded

texts:
I am using Oracle 19 and recently changed the collation to XGERMAN_AI. This required to change the MAX_STRING_SIZE to be EXTENDED as in the following link
https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321
Now, trying to create tables with unique constraints on varchar2 columns produce the error
ORA-01450: maximum key length (6398) exceeded.
Here is my table
CREATE TABLE MY_TABLE (
    ID NUMBER(38, 0) DEFAULT PKS_MY_TABLE_SEQ.nextval NOT NULL,
    VERSION NUMBER(38, 0) DEFAULT 0 NOT NULL,
    CREATED_BY VARCHAR2(50 CHAR) NOT NULL,
    CREATED_AT TIMESTAMP NOT NULL,
    MODIFIED_BY VARCHAR2(50 CHAR) NOT NULL,
    MODIFIED_AT TIMESTAMP NOT NULL,
    ID_OLD NUMBER(38, 0),
    RISK_LEVEL VARCHAR2(255 CHAR) NOT NULL,
    REMARKS VARCHAR2(255 CHAR),
    IS_DEACTIVATED NUMBER(1) DEFAULT 0 NOT NULL,
    COLOR VARCHAR2(255 CHAR) NOT NULL,
    CONSTRAINT MY_TABLE_PK PRIMARY KEY (ID),
    UNIQUE (RISK_LEVEL)
)

I googled this error, and all results say that it is becase the index size is bigger than the block size which is 8k. But in my case the column RISK_LEVEL is only 255 char long! (255*4 bytes = 1020 bytes), which is much less than 8k and also 6398
Any idea how to fix this?


texts: can't find column stats for newly created table

texts:
I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached.

database current settings are:

I started with creating a simple table:

create table recomp(num int)
go

Then I insert some rows:

insert into recomp
values (5)
go 100

and run my query below 5 times (I used where 1 = (select 1)) for full optimization:

select * from recomp
where 1 = (select 1)

in my plan cache I have the following:

Now the interesting part, I again insert new rows in order to trigger auto update stats for num column of the recomp table:

insert into recomp
values (8)
go 500

Here stats for num column has updated. The next what I am going to do is run my select query:

select * from recomp
where 1 = (select 1)

When I check my plan cache:

and an event which captures recompiles:

my question is: where can I find stats for num column?


texts: Does MySQL or MariaDB support GIS KNN Index Queries (k-nearest neighbors)?

texts:
Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT ST_AsText(geom), city
FROM person.address
ORDER BY geom <-> 'POINT(-121.626 47.8315)'
FETCH FIRST 7 ROWS ONLY;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address
WHERE SpatialLocation.STDistance(@g) IS NOT NULL
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

  The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

  Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point*, then ORDERing, and LIMITing.


texts: SQL Server CASE statement dependent on other rows in result set

texts:
I have some data that comes out of a cube.

I have one row in this order that has gone in as a substitution variance but I need to put it in the quantity variance bucket. The code that decides on whether it's a substitution is here at the lowest level (this is within a large CTE) (The code behind this cube is massive and exists on multiple levels)

CASE
   WHEN ISNULL(PC.ItemDimCostPrice, SC.ItemDimCostPrice) = 1
        AND (PC.ConfigId <> SC.ConfigId
             OR PC.InventColorId <> SC.InventColorId
             OR PC.InventSizeId <> SC.InventSizeId
             OR PC.InventStyleId <> SC.InventStyleId)
      THEN 1
   --New code here ... THEN 0
   WHEN PC.[Resource] IS NULL
        OR SC.[Resource] IS NULL
     THEN 1
   ELSE 0
END AS [HasSubstitutionVariance] ,

The logic that they have given me which I need to add into this is basically if the cost grouping is material (I know how to calculate this) and there exists another record with the same costgroupid and operationid (within the production order) then this will never be a substitution variance. I was thinking of using rownumber and partitioning on costgroupid and operationid but as far as I'm aware you can't use window functions in a case statement? Anyone have any ideas how I would do this? Thanks!


texts: SSMS connection to Availability group listener doesn't failover

texts:
I've set up an Availability Group on SQL Server (on Azure VMs) and created a Distributed Network Name as the listener for this. (SQL Server 2019 on Windows 2019)
If I connect to that listener in SMS then it works fine. If I manually failover and re-connect in SSMS it again works fine.  Running select @@SERVERNAME in both cases returns the primary server.
However, if I leave the original connection open, and later try select @@SERVERNAME then it still returns the server it connected to (which is now secondary). I can verify this by trying to INSERT a row and it tells me this server now needs a READ ONLY INTENT connection and I can't do an update.
Is it expected that you have to open a new connection after a (manual) failover event?


texts: Optimize Row Level Security expression in Postgres

texts:
I have a table which contains millions of rows, categorized into a few categories (lets say numbers 1-5). An application that accesses the database uses one single database account. However, the application has its own user accounts and each app user is allowed to access only certain categories. Therefore the list of allowed categories is passed to the database by a session variable:

SET SESSION mydb.allowed_categories = '1,3,5';

I use RLS to filter the rows according to the session variable:

CREATE POLICY table_select_policy ON big_table
FOR SELECT
USING (ARRAY[category] && string_to_array(current_setting('mydb.allowed_categories'),',')::int[]));

The problem is that with this approach the RLS row filtering takes some significant amount of time.
On the other hand, when I experimented by doing:

CREATE POLICY table_select_policy ON big_table
FOR SELECT
USING (category = 1 OR category = 3 OR category = 5);

the RLS filtering was almost 10 times faster. Of course, such hardcoding is a no go, since I want to change the list of allowed categories dynamically within the app.

The category column has a btree index, however since the number of categories is rather small, the query planner always favors the sequential scan for the RLS filter.

So my question is - is there a way to optimize the RLS expression so it gets at least closer to the hardcoded approach? Would you suggest a different solution to the problem? The application has a lot of users, so I don't want to create a database account for every single one.


texts:
I have several queries in the editor, each terminating with a semi-colon (I am teaching myself SQL and so in the editor is a mix of queries and notes like below):

SELECT *
FROM orders
WHERE order_date >= '2019-01-01';

-- AND operator

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' AND points > 1000;

-- OR operator

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'OR points > 1000 AND state = 'VA';

-- NOT operator

-- Exercise: FROM order_items table get the items
    -- for order #6
    -- where total price is greater than 30

SELECT *
FROM order_items
WHERE order_id = 6
AND quantity * unit_price >= 30;

When I first began using MYSQL, I would run a query and only the last SELECT statement would run and the results would show in the result tab. The ACTION OUTPUT would show the time-stamp which clearly indicated only the last SELECT statement in the editor was run.

Now, all queries ending with a semi-colon are running simultaneously and each appears on its own tab and in the ACTION OUTPUT section on the bottom (i.e., "orders43", "customers44", "customers45" and so on.)

The only way I can get the most recent result is by highlighting the specific SELECT statement and running the query. However, I want to know why the results aren't just "overriding" each other and the most recent SELECT statement being the results displayed in the same result tab...

At this rate, I'll have hundreds of result tabs for each query ending with a semi-colon, and this is clearly not what I want nor was it how the workbench was working for me up until recently.

I am not sure why this is occurring, or how to stop it.

Any help would be greatly appreciated.

Thanks!


texts: advantages and disadvantages of using direct sql querying in application

texts: could people please give their opinion on and give advantages and disadvantages on the use of sql queries directly within an application as opposed to creating procedures or functions within the database and using them.


texts: How to change the domain name or IP address in mysql database in every tables

texts:
I was using Openfire chat server in Ubuntu, whose IP address was 192.168.1.15. Then, I wanted to move it to other ip (192.168.1.55), so i took a backup of my database. The database I was using is MySQL, so I took the backup using the command mysqldump and I got a file called openfire.sql.

Now, after restoring that database onto my new server (ip 192.168.1.55) I can't get the user list in the chat server, because the server IP address has changed. How can I change it to 192.168.1.55 or my.local.lan in my database? Please guide me to change the domain name or its IP address. I don't know which table has all the ip addresses, is it possible to change on every table 192.168.1.15 into 192.168.1.55 or my.local.lan?.

Thank you everyone.


texts: Why does join syntax with stacked on clauses work?

texts:
I have three versions of the same select query (SQL Fiddle here):

CREATE TABLE MainTable(ID INT)
CREATE TABLE SubTable(ID INT, Type CHAR(2))
CREATE TABLE SubTableType(Type CHAR(2))

INSERT MainTable VALUES (1), (2)
INSERT SubTable VALUES (1, 'AB'), (2, 'AB'), (3, 'CD')
INSERT SubTableType VALUES ('AB')

SELECT  *
FROM    MainTable P
JOIN    SubTable L
ON  P.ID = L.ID
JOIN    SubTableType LT
ON  L.Type = LT.Type

SELECT  *
FROM    MainTable P
JOIN    SubTable L
JOIN    SubTableType LT
ON  L.Type = LT.Type
ON  P.ID = L.ID

SELECT  *
FROM    MainTable P
JOIN    SubTable L
JOIN    SubTableType LT
ON  P.ID = L.ID
ON  L.Type = LT.Type

The first two select statements work and produce the same result. The third reports the error,

  The multi-part identifier "P.ID" could not be bound.

This question appears to claim that the first two queries are semantically equivalent, though they may perform differently. My question is rather about the syntax. I've always written joins according to the first pattern, and I didn't even realize that the second pattern was permissible. Clearly it is, but then why isn't the third pattern also acceptable? The main documentation doesn't seem to touch on this, so I'm trying to find documentation that spells out what is and isn't permissible.


texts: Inline edit SQL Server database rows from Visual Studio

texts:
I'm pretty sure Microsoft have pulled one of the most useful features for performing quick edits on a SQL Server Database within the Visual Studio IDE. It seems to have only affected SQL 2012 instances, but from the Server Explorer I can no longer right click on a table "Show Table Data", pop open the SQL pane, query the data then perform inline edits on the results (as if I were modifying a spreadsheet).

This means I now need to go into SSMS to make these kind of quick updates. Does anybody know of a plugin I can use with VS 2012 to bring back this functionality? It seems odd to me that Microsoft have two different development trajectories with SSDT and SSMS? Are they designed to serve different purposes? Can SSMS be integrated into the Visual Studio IDE? I'd rather have a unified development environment if possible.

Any thoughts on a workaround for this problem would be much appreciated.

EDIT

I know some purists would quiver at the thought of treating a database table like a spreadsheet, but semantically they are not a world apart, plus this is supported in SSMS. I'm more in favour of relying on GUI based approaches where I can to speed up routine tasks, why some would balk at this I have no idea?


texts: MySQL - Old ibdata file shall I delete safely

texts:
On a production server I have changed "innodb_file_per_table=ON" and I can see separate tablespace files for each table on my server.
I have used parititioning for tables so for each partition there is a separate .ibd file .

But I can see previous "ibdata1" file of size 10 Gb. I am bit curious whether to delete it or not ?

Does MySQL still uses old shared table space "ibdata" file?
If I delete shared tablespace file "ibdata1" will there be any impact on server ?
Will it cause any problem ?


texts: How do I safely upgrade xampp without losing database i have created?

texts:
Also when i try to upgrade the with older version being installed, i get this error that port is being used or similar sorts. Do I complete installation by uninstalling?

I am searching for complete answer just to be sure about upgrading.


texts: PowerShell Pipeline Binding examples on SQL server?

texts:
I am learning PowerShell, my training machine (VM) is a Windows Server 2012 R2, with SQL 2014 and PS 4.0.

I am try get a handle on the two Pipeline Binding methods ByValue & ByPropertyName.  I am a hands on learner so I like to try things and play with them a bit to get the real feel of how they are doing what.

Some of the examples I am seeing are sub optimal for playing with

Get-Process | Stop-Process (**really bad idea, don't try this at home or work**)

Or they are focused on things not SQL (like IE)

I know how to read Get-Help x-x -Full looking for a couple of SQL centered pipelines I can play with that won't crash my server (nor be resume producing events).


texts:
Product : SQL Server 2008 (SP4)

CPU : 64

64-Bit system

Worker thread configuration = 0

SCOM Alerts : SQL DB 2008 Engine Thread Count is too high

After receiving this alert from SCOM i have queried [sys.dm_os_sys_info] and
[sys.dm_os_threads] to check the Total number of threads \ used \ available and found them as follow :

Total    ---     Used   ---     Free    ---    Used Percentage

1472     ---    1260    ---    212      -----      85 %

this server is used for online transactions processing. Is it normal that the Used threads are always between 1100 and 1300 throughout the day ? which obviously cause this alert to be sent when too many queries are executing on the server ?

Moreover, Is this related to the configuration of MAX Degree of Parallelism (Which is set to 0) that causes queries exceed threshold use parallel threads to finish ?

Thanks


texts:
I tried to connect the DB2 Express-C Version 10.1 to Eclipse Mars but it returns an exception when I tried to run the program.

com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException:
Error opening socket to server localhost/127.0.0.1 on port 50,000 with message
Connection refused. ERRORCODE=-4499, SQLSTATE=08001

I think there is an issue with the port number or connection url.

OS: Mac OS X El Capitan

Eclipse: Eclipse Mars

DB: DB2 Express-C 10.1


texts:
I am querying a SQL server 2008 database table having more than 500 million records.
The query I fired to move around 10 million records to temp table took 9 hours.
Similar query I fired which has been going for more than 10 hours.

How to know:

time it will take to complete the running query
time it will take to complete query before execution


texts:
I have this :

CREATE TABLE users
(
    first_name varchar(60) NOT NULL,
    last_name varchar(60) NOT NULL
);

INSERT INTO users
    (first_name, last_name)
VALUES
    ('Sylvia ', 'Hoeks'),
    ('Harrison', 'Ford');

I get the list like this

SELECT first_name || ' ' || last_name AS user_full_name FROM users;

But I would like to be able to search on the column user_full_name. Is it possible without a subquerie or CTE (like below)?

WITH users_list AS (
    SELECT first_name || ' ' || last_name AS user_full_name
    FROM users
)

SELECT * FROM users_list WHERE user_full_name ILIKE '%lvi%';

I tried with the first column !?

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE 1 ILIKE '%lvi%';
/*
ERROR:  operator does not exist: integer ~~* unknown
LINE 1: ...|| last_name AS user_full_name FROM users WHERE 1 ILIKE '%lv...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
*/

I would like to be able to do something like this.

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE user_full_name ILIKE '%lvi%';

For the moment I do:

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE first_name ILIKE '%lvi%' OR last_name ILIKE '%lvi%';

Fiddle if needed : https://www.db-fiddle.com/f/c3HANw8M9W7kSVeEwidpne/0


texts: Best way to update the content of large lookups tables

texts:
In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify).

Nightly we'd like to update this information by deleting all the content and running a script that run a series of inserts and updates. This script could be a couple of million lines long.

I was thinking to run this within a transaction.

What behaviour should I expect from the database? Will the tables be updated at the end of the transaction without downtime?

Is there something I should be aware of when running large transactions?

The database I'm using is Postgres 9.3

Thanks.


texts:
I have a small database PostgreSQL (v9.3) on Centos 6 x64 ( RAM: 8 GB ).

postgresql.conf

max_connections = 512
shared_buffers = 3000MB
temp_buffers = 8MB
work_mem = 2MB
maintenance_work_mem = 128MB
effective_cache_size = 3000MB

About 150 connections, PostgreSQL takes more than 6 GB of RAM (of course, other applications use about 200 MB of RAM), here my info:

Mem:  7062.945M total, 6892.410M used,  170.535M free, 6644.000k buffers
Swap:    0.000k total,    0.000k used,    0.000k free, 5378.922M cached

Questions:

Why does PG takes a lot of RAM ?
How can I reduce cached buffers of PG ?


texts: What is the difference between CHECK and CONSTRAINT CHECK?

texts:
is there any difference between the follow exemples?

ProjNum int
    CONSTRAINT nn_ProjNum NOT NULL
        CONSTRAINT C_ProjNum CHECK (ProjNum >= 10)

and

ProjNum int NOT NULL
    CHECK (ProjNum >= 10)

Thanks.


texts: Local Transport Fare design: how to relate departures, destinations and locations

texts:
I am doing a conceptual model for local transportation fare app

The goal is for users to quickly check the current fare and the previous fare from one location to the other. Sometimes, the government will increase the fare (which will be the current fare) and after a while or so, they will return to the previous fare.  Please see the final view of the app below.

Since there is a many to many relationship between Departure and Destination, I will need a bridge entity to solved that, but how do I relate that to location since I do not want to have repetition on locations?

Please see my ERD below

Thank you for you time, really appreciated


texts:
Sorry for duplicate question.

I am looking for the best way to install mysql 5.5 on ubuntu.

So far, there are few options:

Installing from tar.gz
Installing from PPA.
Alien on RPM.

All of them don't seem to be optimal to me. Installing from tar.gz takes long time and might have room for mistake. Installing from PPA is dangerous, I don't want to install something on production server which is non-official or might have differecens with official version. Running alien on RPM not a good way to install.

How did you install Mysql 5.5 on ubuntu server?

Version: Ubuntu 10.04.3 LTS


texts:
I have a query with a hard coded value which is also hard coded into my application (which has a comment saying that the value should be the same as the value hard coded into the query).

What would be considered the best way to make that value discoverable outside of the single query it is being used in?

A UDF immediately jumps to mind, but prior to 2019 it kills performance (don’t have 2019 so I can’t verify it, but it seems inlined UDFs should fix the performance problem).

A view or TFV might be a good alternative, but both seem overly complex and a misleading.

I don’t have a table/view of constants/lookup values.

Is there a good solution for this situation or just stick with comments in the relevant code?


texts:
I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?.

Removing the .frm files from my datadir has resolved the mysqldump issue of trying to dump non-existent tables. Now my question is how do I drop a table and have all references to it removed? I would have thought issuing the drop table would remove all associated files in the directory. I am using MyISAM so perhaps the issue is related to that.


texts:
I have a tar archive with full backup of my CentOS filesystem with PostgreSQL 9.1 installed.

How can I restore one database from it?

If I can only restore a full database cluster, how can I do that? Just copy-paste all the files?


texts:
I am working in Visual Studio 2008 on a SSIS package. It is really frustrating that I get "Microsoft Visual Studio is Busy" message quite often. It almost happens every time I try to open a data source, destination, etc.

From what I can see it seems VS tries to save Items. Is there any way to avoid this auto save? Generally how can we get rid of this message and interruptions?


texts:
A simplified version of my problem is as follows:

I have three tables Records, Inputs, and Outputs.

Records

Id

      DTS

1

2015-01-01 00:00

...

Inputs

Id

RecordId

InputId

Value

1

1

1

100

...

N

1

1

105

...

Outputs

Id

RecordId

OutputId

Value

1

1

1

200

...

M

1

1

10

...

This fragmentation was done to avoid limiting the total number of inputs or outputs due to constraints on the number of columns in a table. I am looking for the best (fastest) way to run queries against something similar to the following:

      DTS

Input1

...

InputN

Output1

...

OutputM

2015-01-01 00:00

100

...

105

200

...

10

...

My initial plan was to create a view based on pivoting the requisite inputs and outputs and joining them appropriately. The data only needs to be readonly and is essentially static, so I want to prevent it from being expanded for each query. I read that I could add an index to the view to 'materialize' it, but this can't be done if the view uses PIVOT.

Does anyone have any advice on how I might accomplish this? Or if I am approaching this the wrong way, I would happily take suggestions.


texts: Get time from Unix milliseconds timestamp

texts:
My MySQL version is 5.6; it has a FROM_UNIXTIME function. But it works for timestamps with 10 digits: 1447430881.

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'

Now I want to get the date from a milliseconds timestamp (e.g. 1556948248000), so using FROM_UNIXTIME I got NULL.

mysql> SELECT FROM_UNIXTIME(1556948248000);
        -> NULL

Isn't there a FROM_UNIXMILLITIME function?


texts: Find the second highest salary of employees

texts:
Imagine there is a table with these fields:

Employee: E-Number
          E-Name
          Department
          Salary

We need to write a query in order to find the second highest salary in this table.

I found this query :

select distinct salary
from Employee e1
where 2 = ( select count(distinct salary)
            from Employee e2
            where e1.salary < e2.salary)

I'm a bit confused by this part where 2 = because I have not seen something like that before.

Can anyone here explain more about the query and the part where 2 =? What is the result of the subquery and in what way are table rows (e1, e2) being compared with each other?

Actually this is the execution plan can anyone explain the steps more?


texts: How to link data from two spreadsheets imported to sql

texts:
I imported 2 spreadsheets to SQL Server tables. One sheet has data containing persons name & surname, the project they are working on as well as there banking details. The other spreadsheet has only the names & surnames  of persons and banking details.

Sheet 2 data needs to be grouped to the right projects like in sheet 1. The only connection I found is that the Reference column in Sheet 2 has numbers that match the ParentID on sheet 1 but with a suffix appended.

Beneficiaries with the same ParentID belong to the same Project. I need a way to extract the numeric values of Reference and match them to the corresponding ParentID in order to group the Beneficiaries in the right project.

sheet 1 :

BeneficiaryID      float         Checked
BeneficiaryName    nvarchar(255) Checked
BeneficiarySurname nvarchar(255) Checked
IDNumber           float         Checked
DOB                nvarchar(255) Checked
BranchnameID       nvarchar(255) Checked
BranchCode         float         Checked
AccountType        nvarchar(255) Checked
AccountNumber      float         Checked
TotalWages         float         Checked
[(No column name)] float         Checked
ParentId           float         Checked
PPRDate            datetime      Checked

sheet 2 :

Reference     nvarchar(255) Checked
[Account No]  nvarchar(255) Checked
[Branch No]   nvarchar(255) Checked
[Bank Name]   nvarchar(255) Checked
[Acc Type]    nvarchar(255) Checked
Amount        nvarchar(255) Checked
[Action Date] nvarchar(255) Checked
Status        nvarchar(255) Checked

Here is some sample
sheet 1

BeneficiaryID   BeneficiaryName     BeneficiarySurname   IDNumber   DOB Branchname ID   BranchCode  AccountNumber   TotalWages      ParentId    PPRDate    Caption

1202            Chris               Manda                123456     4/6/1995 1               ABC         123             120          10125    June 2018  Agriculture

sheet 2

Reference       Account_No Branch_No    Bank_Name   Amount  Action_Date Status

10125_Motsomane 1234       151258865    CDF         1200    15/01/2016 ERROR


texts: Run time fetch the value from a table and iterate these values and sent to another procedure

texts:
I am new to write store procedures in sql I want to use a store procedure into another store procedure
Test is a store procedure which works fine but in usage store procedure I want stock is a table and stocks is a variable having the result value of this table with three column i.e : siteId,productId,id so i need to send all these rows of this table to another store procedure

CREATE PROCEDURE USAGE AS BEGIN
   DECLARE stocks table(siteId int, stockId int, productId int)
   DECLARE @i int
   set stocks  = 'insert into @stocks (siteId , stockId , productId)' +
   '(select  siteId, id, productId from "Stock")'
SET @i = 1
 WHILE (@i<stocks.length)
   EXEC Test stocks[@i].siteId, stocks[@i].stockId, stocks[@i].productId
  END
  select stocks
 END

unable to get stocks in array/table format for accessing it
getting the error
Msg 156, Level 15, State 1, Procedure USAGE, Line 2 Incorrect syntax near the keyword 'table'