texts:
My mysql server died while running select queries.

Status of every table is good. When I went to the error log I found this:

Thread pointer: 0x2ab63e0f49e0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 40f34098 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7d08b5]
/usr/sbin/mysqld(handle_fatal_signal+0x4b4)[0x6a90e4]
/lib64/libpthread.so.0[0x2b4d4c6a6be0]
/usr/sbin/mysqld[0x904618]
/usr/sbin/mysqld[0x904725]
/usr/sbin/mysqld[0x90496c]
/usr/sbin/mysqld[0x848b18]
/usr/sbin/mysqld[0x848b7d]
/usr/sbin/mysqld[0x84e581]
/usr/sbin/mysqld[0x834878]
/usr/sbin/mysqld[0x82a885]
/usr/sbin/mysqld[0x7fb138]
/usr/sbin/mysqld[0x7f3b39]
/usr/sbin/mysqld[0x5c38c5]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x66)[0x5bd396]
/usr/sbin/mysqld[0x5c20d7]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0xca0)[0x5d7570]
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x12c)[0x5d8d7c]
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1cd)[0x5d982d]
/usr/sbin/mysqld[0x591282]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x34b0)[0x599580]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x33b)[0x59b88b]
/usr/sbin/mysqld[0x59c8a2]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1af2)[0x59ea62]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x167)[0x59f057]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x14f)[0x63af3f]
/usr/sbin/mysqld(handle_one_connection+0x51)[0x63b121]
/lib64/libpthread.so.0[0x2b4d4c69e77d]
/lib64/libc.so.6(clone+0x6d)[0x2b4d4d9ef9ad]

This is a percona xtradb cluster; version is mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1.


texts: PL/SQL group orders by date and sum the total

texts:
I need to group orders by date and sum the total,

My table looks like this :

TEST_DATE
TEST_ID - NUMBER
SUCC_ID - NUMBER

I need to group by date and sum the total, this is my query:

SELECT COUNT(*), TEST_ID, SUCC_ID
FROM MYTABS
GROUP BY TEST_DATE;

How can I?

thank you.


texts: Geography union via cursor returning null

texts:
I need to combine multiple geography records into a single entity to store in a spatial data table, but I've learned that the only available function in MSSQL2008R2 that accomplishes this task is STUnion which only works for two static geography records. I have upwards of 200 that I need to union and I'm loathe to hand code that kind of query.

On that note, I wrote up a one-off cursor to iteratively union each geography object from my staging table. However, when executed, the cursor returns a null value without any errors or messages.

DECLARE
    @ShapeUnion GEOGRAPHY,
    @Shape GEOGRAPHY

DECLARE curSpatial CURSOR FOR
SELECT
    geom
FROM
    dbo.Boundaries_Staging

OPEN curSpatial

FETCH NEXT FROM curSpatial INTO @Shape

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ShapeUnion = @ShapeUnion.STUnion(@Shape)

    FETCH NEXT FROM curSpatial INTO @Shape

END

CLOSE curSpatial
DEALLOCATE curSpatial

SELECT @ShapeUnion

Edit: Updated script for anyone curious about unioning multiple geography records. As stated in the comments, I initialized @ShapeUnion prior to the loop. Also, I added .STBuffer() to the field being unioned; this ensures that the miniscule spaces between the spatial data are completely filled.

DECLARE
    @ShapeUnion GEOGRAPHY,
    @Shape GEOGRAPHY

DECLARE curSpatial CURSOR FOR
SELECT
    geom
FROM
    dbo.Boundaries_Staging
WHERE
    geom IS NOT NULL

OPEN curSpatial

FETCH NEXT FROM curSpatial INTO @Shape

SET @ShapeUnion = @Shape

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @ShapeUnion = @ShapeUnion.STUnion(@Shape.STBuffer(1))

    FETCH NEXT FROM curSpatial INTO @Shape

END

CLOSE curSpatial
DEALLOCATE curSpatial

SELECT @ShapeUnion


texts: Can a partitioned table span on multiple files across different file groups?

texts:
In SQL Server 2008 and above, if a table is partitioned, can it span across multiple files across multiple filegroups?

Thanks


texts: SQL server 2008 Analysis Services DSO properties

texts:
We are planning to do Sharepoint and project server 2007 databases migration.

The share point website also processes cubes.

So we will be moving SSAS cubes from SQL 2005 to 2008 and then re-pointing the location from the sharepoint admin website.

The following document covers “Requirements for using SQL Server 2008 Analysis Services with the Project Server 2007 Cube Building Service”

http://technet.microsoft.com/en-us/library/dd285466%28office.12%29.aspx

the document is easy to follow but there is a section which mentions:

“You need to configure the Analysis Services DSO properties so that the repository database is configured to be used from DSO in SQL Server 2008 Analysis Services. Changes made during this process will be reflected in the Analysis Services configuration file (Msmdsrv.ini).” So this involves playing with DSO* properties under the SSAS server.

The existing SSAS server has only sharepoint cubes but the new SSAS server has cubes for other applications too.

I can do the settings following this document but I want to know if there are any risks involved in changing DSO properties on SSAS server.

Can anyone please help or put me in right direction?


texts: To get rid of heavy archive logs in PostgreSQL 9.1

texts: In our production server which is having PostgreSQL 9.1 installed, too heavy archive logs are being generated, approximately 41 GB/day. Heavy updates, deletes and inserts are the cause of this. Can anyone help us to reduce the amount of archive log generation in our production server?


texts: Deduplicate Data Using Having with Subquery

texts:
I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward.

I have this query:

SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1))
FROM `game`
group by matchid, ordinal
having dacount > 1
order by dacount desc

which correctly gives me the ids of the rows I need to delete. The problem with this though is that I can't use this as a subquery on the DELETE because of the dacount with the having parameter. Is there another way to do this?

Here was my plan:

DELETE FROM game WHERE id IN (SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1))
FROM `game`
GROUP BY matchid, ordinal
HAVING dacount > 1)


texts: Mimic "returning" in an oracle 12c updatable view

texts:
We would like to use updatable views in some cases where information is split across several tables for allowing the use of foreign key constraints. This works well utilizing "instead of triggers" (KEY PRESERVED cannot be used as the keys may come from different tables depending on the constellation).

We would like to use these updatable views like normal tables but unfortunately "returning" cannot be used on DML statements.

Is anyone aware of a way to use or mimic "returning" on inserts and updates (Essential in our case as it's not only about getting generated identity values, also timestamps et cetera)?

Thanks in advance!


texts: SQL query, group by and number of occurrences

texts:
I have the following table (only one column):

X
----
1
1
1
2
2
3
3
3
4
4
5
6
6
7
7
7

I need a SQL query which will output the following:

Occurrences | Number
1  1  * there are only 1 entry with one occurrence (number 5)
2  3  * there are three entries with two occurrences (numbers 2,4,6)
3  2  * there are two entries with three occurrences (numbers 1 and 7)

If I do something like this:

select count(*) FROM (SELECT count(*) as N FROM table 1 GROUP BY x) a GROUP BY a.N;

I get the correct results, but only the second column t with the number of occurrences.
Does anyone know how to get the complete output as in the example above?


texts: Oracle Equivalent of the following SQL Server syntax

texts:
I'm looking for the Oracle equivalent of the following SQL Server syntax:

select Cast(0x41424344 as varchar(max))

In SQL Server, it selects  'ABCD'.

0x41424344 is the hex of 'ABCD'


texts:
I'm trying to migrate my MySQL RDS to a different machine. The reason is that I need smaller disk, and you can't make disks smaller for RDS servers, only bigger.

I'm trying to migrate with this answer:
https://stackoverflow.com/questions/20638012/backup-mysql-amazon-rds#20645291
and this tutorial:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html#MySQL.Procedural.Importing.Start.Repl

Briefly :

Created replica and stopped
Migrated from replica to a new server
Created user on Source DB with:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'slavepass';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'
I've pointed to master from slave with this command:

CALL mysql.rds_set_external_master ('source.mydomain.com', 3306,'repl_user', 'slavepass', 'mysql-bin-changelog.000001', 107, 0);
I've started replication with :

CALL mysql.rds_start_replication;

When checking status with SHOW SLAVE STATUS, it shows that slave can't connect to master.
All servers are in the same security group. I was trying to connect to source with repl_user with MySQL command line and it worked.
I have no idea where to look next. I think it might be something with security in aws, but I was comparing new server with existing replicas and all seems the same.

I'm running MySQL 5.6 on both machines.


texts: Restore Verify Only option vs Manual Restore

texts: My question is related to best practices with SQL Server. I need to put together a document that lists our various databases and that the backups we have of these databases are valid and have been tested and we know that they will restore properly in case of a disaster recovery scenario. I'm using the Ola H. maintenance solution which backs up the databases with the RESTORE VERIFY option and gives a "succeeded" message to indicate that the backupset is valid. Is this enough to be confident or should we still be manually restoring these backups to a test server and running tests?


texts: Insert a sequence of number where some numbers could exist already / fill gaps?


texts: Do you need SQL Server AAG when using SQL Server FCI/RAID10?

texts:
If I have a 2-Node AlwaysOn Automatic Failover Cluster Instance with a shared disk and RAID-5 or RAID-10 to account for a SAN failure with the same setup in a second datacenter with data propagation via SAN Replication, is there a way to benefit from using AAG in this scenario?

We're not looking to use AAG for scaling-out, offloading backups or READ-ONLY requests. Scaling-out can be implemented via new nodes and NLB. A new node can be added to offload backups. I'm not seeing a use for AAG here. I think the one feature that we'd miss is automatic page repair. I'm not seeing any other uses. But I am new to AlwaysOn.

Thanks for the help!


texts: how to set timestamp in datetime field if the time is not specified?

texts:
I have a Stored Procedure having input parameter as @startDate as datetime and I am using this parameter to compare this fields to table column. I want to convert that @startDate field if the timestamp is not passed by the user in SP?

Example: Let's say if user passes @startDate = '01/14/2018 6:30:00 PM' then no it is should be taken as it is in the SP but if @startDate is passed as '01/14/2018' only then it should be converted to '01/14/2018 11:59:59 PM'.


texts:
I have a large production database in Amazon RDS MySQL (billions of rows) that I want to set up as a master, replicating to an Aurora Serverless instance. Unfortunately, it looks like the only way to copy the data over is by using mysqdump, which is going to be a serious pain in itself.
However, when I read about setting up a slave with this method, it looks like it requires the master to be basically locked down for the whole time that mysqdump is running, in order to preserve the binlog position, which makes it far too expensive in lost business.
I have already tried DMS - it appears to be unreliable, either losing data or, worse, inserting spurious rows, and I don't have the time or the insight to troubleshoot it. And as it turns out, snapshots cannot be restored into a serverless instance, for some reason.
Is there any way around these problem? I have seen mention of GTIDs, but so far I haven't found enough info to see if it is useful in this situation.


texts: How to change/add to schema in a SQL Server Always On Availability Group?

texts:
I have a 2-node Always On Availability group cluster in SQL Server 2016.  I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On Availability group without incurring downtime?

Is this fully automatic?  I.E. I simply go ahead and create my table on the primary in an availability database and it will then get replicated to the secondary?  Or will something weird happen?  I'm worried that there might be cases where it doesn't work and replication between the primary and secondary is halted.  For example, what happens if SQL Server Management Studio decides to drop and recreate the table?  Will this work as expected?  Are there any gotchas to be aware of?

Thanks in advance, and any help is greatly appreciated.  Am a long-time SQL Server user but am new to Availability Groups.


texts: Missing or lost transaction and no errors to show what went wrong

texts:
I have 2 missing records in SQL Server and I am trying to identify the problem that caused this issue.

New records are inserted using a stored procedure, which will return an error code to the application if something goes wrong.

The Application, SQL Server, System Logs do not show any errors during the time when these 2 transactions should have been inserted.

How do I go about identifying the issue here? My current idea is to find a way to read the Transaction Log (from a backup restore) to see if the transactions are even in the log and if they got rolled back.

Can anyone recommend anything else I can do to troubleshoot this? Are there DMVs or other (SQL Server hidden) logs?

I am worrying about this issue and what SQL Server did in this situation. (I have experienced issues recently with timeouts, but since a recent reboot, this problem was resolved....)


texts: Replication Monitor is not showing correct list of replications

texts:
I have one server, in which there are three instances of SQL Server 2019. In one instance, I had configured transactional replications, but all of those replications are currently dropped. But the replication monitor is still showing the old configured replications.
Also, when I am refreshing the monitor, then publisher is getting disconnected from its distributor and I have to reconnect again. Sometimes, I am connecting the publisher to the distributor using "Connect to Distributor" option and its getting disconnected automatically. In such case, I am trying to connect to distributor many times till I am able to see the wrong list of publications. I am sharing screen shots to clarify the issue below:

As shown in above screen shot, under Local Publicatons folder, there are no Publication, whereas Replication Monitor is showing 4 Publications which are no more existing. Please suggest.


texts: mysql master master replication, master log position not changing

texts:
I have setup master-master replication on database set on two servers (say 1.1.1.1 & 2.2.2.2), I have edited the my.cnf file to:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 800M
binlog_do_db            = mydb
relay-log               = /var/log/mysql/relay-bin
relay-log-index         = /var/log/mysql/relay-bin.index
relay-log-info-file     = /var/log/mysql/relay-bin.info
log_slave_updates       = 1

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 8888888800M
binlog_do_db            = mydb
relay-log               = /var/log/mysql/relay-bin
relay-log-index         = /var/log/mysql/relay-bin.index
relay-log-info-file     = /var/log/mysql/relay-bin.info
log_slave_updates       = 1

I checked the master status on 1.1.1.1 server:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.001185 |   110989 | mydb         |                  |
+------------------+----------+--------------+------------------+

And here is the slave status on 2.2.2.2:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 1.1.1.1
                  Master_User: replicator
                  Master_Port: 4791
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001185
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay-bin.000006
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.001185
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 549
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

When I restart mysql, on 2.2.2.2 it automatically replicates the previous log files data, I am not sure what I am doing wrong here. Can anyone help ? Thanks.


texts:
I have few SQLCLR functions implementing business logic that return SqlXml data. I would also like to make the Xml Schema Definitions in the dll available in Sql Server.

It's not a 'SqlUserDefinedType', as it is used indirectly by the Xml type. However, the collections node appears under 'types' in SSMS.

I would prefer to keep the xsd along with the functions to simplify deployment. Is this possible?


texts:
I've got a database with 2 tables.

companies
addresses

These are my tables.
Adresses

adress_id
adress_adress
adress_adressnumber
adress_zipcode
adress_city
adress_postbus
adress_country
adress_building
adress_unit

Companies

company_id
company_name
company_adress_id
company_postadress_id

A company can have a address but maby different postal address. I want to join these addresses in 1 query but when i join them only 1 address is shown because i think they're overlapping.
What can i do make this work? I'm really struggling.

Thanks in advance.

SELECT * FROM companies as c JOIN adresses as a ON c.company_adress_id = a.adress_id JOIN adresses as postaddress ON c.company_postadress_id = postaddress.adress_id


texts:
I am trying to implement some better security practices for our database systems. Right now, dbo is owned by SA. I plan to create new accounts on each server, with differing passwords, and assign SysAdmin to that local user. For many databases, dbo user is linked to the SA login. I can change these using ALTER AUTHORIZATION... no biggie.

My question is, I see MASTER and some other DB's have this mapping of DBO to SA. I don't believe that the DB Owner can be set to anything but SA on these db's.

Will disabling the SA account have impact on the opperation of the system databases when DBO is mapped to SA?

Thank you!

Wes


texts: What impact do queries have on a server's resources?

texts:
This is a really ignorant question, but I'm wondering what impact queries have on a server's resources in general and can't seem to find a straight answer through searching.

For example, suppose a query takes 0.1s to execute. Does that mean a CPU/core is tied up at 100% for that amount of time?

As an extreme example, what would happen if a query took a full minute to execute? Would the server be completely bogged down during that time, or do database management systems internally limit how much CPU, memory, etc., that they allow themselves to use so as to prevent locking up a system?


texts: SQL Server 2008 R2 - How does the SQL Trigger actually work in this case?

texts:
I have a table named _Test which has the following values stored in it.
Primary Key : Id

I also have designed a trigger on that table

ALTER Trigger [dbo].[_Test_Data_Save]
On [dbo].[_Test]
For Insert
As

--Some custom algorithm which takes about 2-3 seconds to complete

Now, 3 records were saved at the same time from 3 different sessions at the same microsecond like in the image below.

Question

Now my question is how will the trigger work for the last 3 records?
Will the last 3 records fire 3 different triggers:-

simultaneously
one by one (according to the order they are saved in)
in any random order SQL seems fit

If the trigger is not executed one by one then what can i do so that they execute the trigger for the record of Id=5 first then after completion of that Id=6 and then trigger for Id=7.


texts: How can I get hours worked totaled for a column?

texts:
I get good results until I try to perform calculations on an alias column. I am aware that that is not possible. However I need a breakthrough.  Can someone lend a hand please :) I have to finish this report and I know the answer to this will help me with many different scenarios as far as MYSQL Queries go.  HELP

SELECT
    `ohrm_user`.`emp_number`,
    `ohrm_attendance_record`.`punch_in_user_time`,
     `ohrm_attendance_record`.`punch_out_user_time`,
     `ohrm_user`.`user_name`,
     time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60 AS time_to_sec_TIMEDIFF_punc,
     SEC_TO_TIME(SUM(TIME_TO_SEC(ohrm_attendance_record.punch_out_user_time) - TIME_TO_SEC(ohrm_attendance_record.punch_in_user_time))) AS SEC_TO_TIME_SUM_TIME_TO_S,
     TIMEDIFF(MIN(ohrm_attendance_record.punch_in_user_time),
     MAX(ohrm_attendance_record.punch_out_user_time)) AS TIMEDIFF_MIN_ohrm_attenda
FROM
     `ohrm_attendance_record` `ohrm_attendance_record`
      INNER JOIN `ohrm_user` `ohrm_user` ON
     (`ohrm_user`.`emp_number` = `ohrm_attendance_record`.`employee_id`)
WHERE ( `ohrm_user`.`user_name` LIKE 'mel%' )
AND ( `ohrm_attendance_record`.`punch_in_user_time` > '2015-06-29 00:00:00' )
AND ( `ohrm_attendance_record`.`punch_out_user_time` < '2015-07-01 00:00:00' )
GROUP BY `ohrm_user`.`emp_number`,
         `ohrm_attendance_record`.`punch_in_user_time`,
         `ohrm_attendance_record`.`punch_out_user_time`,
         `ohrm_user`.`user_name`
ORDER BY `ohrm_user`.`user_name`,
         `ohrm_attendance_record`.`punch_in_user_time`

I cannot figure out how to calculate total hours for the user and date scope that is specified. The way it currently is the calculations only seem to total up each punch in and out in pairs.  I need a total for the hours worked in the searched period.  I am still learning about aggregates, sub queries and advanced normalization.


texts: Import and Export database in Mongo just like MySQL

texts: I am newbie to the Mongo.Before this I have worked only with MySQL.In MySQL we can import and export database easily.I want to know is there any option available in Mongo to import and export database easily.Any help and suggestions will be highly appreciable.


texts: EXPDP error ORA-39374 + ORA-13158 Oracle object does not exist

texts:
While exporting one of the tables in my db I got the following error :

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39374: Statistics failed to export. Failing error is
ORA-13158: Oracle object 3752503189677886288 does not exist
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

After this on the general print out where my export is running I saw . . Exported TABLE_NAME 51.31 GB 273283803 rows however the same is not printed in the expdp log..

Also the export command failed to return for almost 8 hours after which I forcefully tried to close it with Ctrl-C + Export> kill_job which also did not close the export session in a clean way.

dba_datapump_jobs:

+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+
| OWNER_NAME | JOB_NAME            | OPERATION | JOB_MODE | STATE     | DEGREE | ATTACHED_SESSIONS | DATAPUMP_SESSIONS |
+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+
| APPUSER    | SYS_EXPORT_TABLE_02 | EXPORT    | TABLE    | EXECUTING | 1      | 0                 | 2                 |
+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+

I am unable to figure out why my export job failed and more importantly how to close the active sessions in a clean way..

expdp command

expdp user/pass directory=TEST_DIR dumpfile=xxx.dmp logfile=xxx.log tables=TABLE_NAME


texts: Deleting old data in MySQL becomes slower over time

texts:
I have an application running on Amazon MySQL/RDS that needs to maintain 2 weeks of transactional history after which time data can (usually) be purged. Because this isn't a hard rule I cannot use partitioning by day or week as a means to delete old data most efficiently.

There are several tables that need purging, many of them have multiple indexes and foreign key constraints (parent/child).

I'm deleting data in chunks (1000-3000 rows at a time) and committing after each chunk. After a set number of chunks have been deleted the purge code pauses for a set amount of time.

My issue is that the process is very efficient for 10-15 minutes, after which I begin to see large delays in deleting data chunks. I believe my SQL is as efficient as it can be. Where in MySQL can I look to better understand the bottleneck? If I stop my code and wait 15-20 minutes the delete performance is restored for another 10 minutes.


texts: Decode everything that is not defined as 'internal' or 'Web' to 'external' (oracle)

texts:
I'm trying to decode a columns with about 30 different possible values down to 3 values.

I want the decoded value to be "internal" if the original value is "Interal KS & others",  or "Web" if the original field is "Webshop", all other values i want to decode as "external".

Is this possible? or do i have to define a really long decode definition. I know it is possible to use > or < for numbers but how do i do "everything else" for text values?


texts: How to extract dynamically data from another database in function with DBName as parameter?

texts:
I have one main database (MAIN) and several client DBs (CLIENTDB). I need to start query in MAIN and to get data from CLIENTDB. The client DB can be found also on linked server. My problem is that I need to do this in a function with EXEC, so I can get SELECT * FROM SRV.CLIENTDB, but this in not allowed by SQL Server. Is there another way to do that?

I need to have code like inside the function:

DECLARE @sSRV AS VARCHAR(128) = 'SRV';
DECLARE @sDB AS VARCHAR(128) = 'CLIENTDB1';

EXEC('SELECT * FROM ' + sSRV + '.' + @sDB + '.MyTable');

I need a function, because I need to make joins with it inside my engine.


texts: How to change SQL sever configuration manager settings using TSQL?

texts:
SQL server configuration manager is used to configure certain settings like connection protocols, service start up,  etc...
Is it possible to make these changes that is done in SQL server configuration manager by using TSQL statements, or in the SSMS?


texts: Cannot create a stored procedure in any db in one instance SQL Server 2012

texts:
Simple generic stored procedure not complicated, could return string or just date and all fail.

create procedure get_date
as
select getdate()

Error:

  Msg 3616, Level 16, State 2, Procedure get_date, Line 4
  An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

This same error in any db in the instance.


texts: Avoiding a sort on an already clustered index for group by

texts:
On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid:

select count(did), pid
from  T
group by pid

Here is the query plan:

GroupAggregate  (cost=21566127.88..22326004.09 rows=987621 width=8)
->  Sort  (cost=21566127.88..21816127.88 rows=100000000 width=8)
    Sort Key: pid
     ->  Seq Scan on tc  (cost=0.00..1442478.00 rows=100000000 width=8)

However, the table already has clustered index on pid.

Why doesn't Postgres simply scan the table and compute the group by? Why does it need to sort on pid again?

How can I force Postgres to use the clustered index for the group by?


texts: Best Practice: Persisted Computed vs Computed with Index vs?

texts:
I am an accidental DBA charged with performance tuning a mess of queries (both literal, and figurative).

One pattern we have is to always store Dates as DateTime2(7) regardless whether we need the time portion or not.

When querying these, one "smart" developer created a scalar function DateOnly() and used that everywhere (figuratively). As you know, and I found out recently, scalar functions usually cause a query to stay single threaded, so I've been working to inline the function as a simple cast(dateColumn as Date).

For performance reasons, I suggested converting the columns to just Date for the tables where Time is not important and was met with resistance.

I recently learned (I did mention that I'm an accidental DBA) about computed columns and wonder if maybe that's a good solution?  I'm just not sure about any performance relate issues here and was wondering if there is any sort of best practice.

Yes, I googled it, no I didn't find anything.  That's why I'm here.

How would you approach the problem, and why?

Inline conversion
Computed Column
Persisted Computed Column
Computed Column with Index


texts: Database Design Techniques, Notations and Terminology

texts:
I'm taking a Database course and I'm a bit confused with the different types of graphical representations terminology of a database design.

Are Data Model, Crows Foot Notation, Crows Foot Model, ERD & ERM all the same. Where do they differ?

My understanding is as follows:

Crows Foot Notation - Graphically illustrate the relationship (ex. zero or many, one or many, one and only one, zero or one)between two or more entities.
Crows Foot Model - Is this the same as Crow's Foot Notation?
Entity-relationship Diagram (ERD) - Portray each entitie's name, attributes and keys. Do I need to display the dataypes as well? Does this also consist of the Crows Foot notation?
ERM - Is this the same as ERD.
Data Model - I assume this is very general. Would this consist of a depiction of an entity and its name, and arrows pointing to the entities it relates to?


texts: Automatically rotate mongo logs in mongod.conf file

texts: cureetly we are not having log rotater in my mongod.conf file.my version is mongodb3.4.3  how can i give log rotater in my mongod.conf file please check my conf file and give me suggestion where to add the log rotator in my conf fileand what to add


texts:
My cloud provider has prevented me accessing fn_LocalTimeToUTC and fn_UTCToLocalTime.
How would I go about getting the same functionality that these two functions offer manually and directly in SQL without using these functions?
To try to simplify it further, All our users are in the UK.


texts:
I cannot find a good keyword to search for this kind of question, so here you go:

Suppose i have this table:

CREATE TABLE test (
 tr_uuid BINARY(16) NOT NULL,
 Customer VARCHAR(10) NOT NULL,
 Data BLOB NO NULL,
 DataTime DATETIME NOT NULL,
 PRIMARY KEY (tr_uuid, DataTime)
);
CREATE INDEX idx_test ON test(Customer);

Are there any bad side if I have this way of partitioning (monthly):

ALTER TABLE test PARTITION BY RANGE ( ( MONTH(DataTime)*10000+YEAR(DataTime) ) ) (
 PARTITION p_422018 VALUES LESS THAN (422018),
 PARTITION p_432018 VALUES LESS THAN (432018),
 PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

or like this weekly:

ALTER TABLE test PARTITION BY RANGE ( YEARWEEK(DataTime) )
(
  PARTITION p_test_201834 VALUES LESS THAN (201834),
  PARTITION p_test_201835 VALUES LESS THAN (201835),
  PARTITION p_test_201836 VALUES LESS THAN (201836),
  PARTITION p_test_201837 VALUES LESS THAN (201837),
  PARTITION p_test_future VALUES LESS THAN (MAXVALUE)
);

The idea is to partition weekly/monthly every year but I dont want to use SUBPARTITION.

Another thing, if there is requirement to store the data for 2 years with data growth of ~6mil/week, and the chance to query the data is quite rare. Using above way will end up with 53x2 partitions after 2 years, according to this blog, 50 partitions will make it inefficient. So are there any better solution for partitioning?

The purpose of partition is not only for query performance, but also to delete the old data smoothly.


texts: how to Enable these Windows Firewall rules using powershell?

texts:
I have a few servers where I see a blue question mark on the sql server service, as per the picture below.

This is the solution:

Enabling these Windows Firewall rules did the trick for me
Windows Management Instrumentation (WMI-In)
Windows Management
Instrumentation (DCOM-In)

Is there a way I could achieve this via powershell?


texts: How can we execute a log file from pg_xlogs directory postgresql?

texts: Is it possible to execute a Wal log that is copied from a machines pg_xlogs directory and all the queries inside it are executed on another machine? What command might be used to carry out this action (if there is any command)?


texts: Is it bad practice to leave the master database at a lower compatibility level after an in-place upgrade?

texts:
Based on this documentation, Microsoft intentionally leaves the master database at the previous compatibility level after an in-place upgrade.
I recently performed an in-place upgrade from 2012 to 2017 on AWS RDS. RDS architecture does not allow its customers to be db_owner for the master database, so therefore, I cannot change the compatibility level of master to 2017 to match the rest of the system and user databases. AWS tech support has confirmed this.
Is it safe to leave it this way? Is it bad practice? Will I lose any functionality by keeping it at 2012 on a 2017 instance?
It feels like this isn't ok - what happens in a couple years when I upgrade this instance a version or two further? At some point I'd imagine that master should be at a more recent compatibility level than 2012.


texts: Rolling Back Stored Procedures in Entity Framework

texts:
I currently working a on project that operates within a transaction in entity framework. Before now all of the changes were made using a DataContext. I need to add a call to a stored procedure using that same context. My question is will the stored procedure be rolled back as well in the case of an exception.

Below is how the SP is structured

CREATE PROCEDURE dbo.UpdateObject
@ObjectId VARCHAR(20),@ObjectStatus VARCHAR(20)='Yes'
AS
SET NOCOUNT ON;

DECLARE @object_status_enddate  DATE;
DECLARE @object_optout VARCHAR(10);
DECLARE @current_date  DATE=REPLACE(CONVERT(VARCHAR,GETDATE(),111),'/','-');

BEGIN TRY
    SELECT TOP(1)
     @object_status_enddate=a.EndDate,@object_optout=a.OptOut
    FROM xxx a
    WHERE ObjectId=@ObjectId
    ORDER BY LastModified DESC;
    --TERM EXISTING STATUS
    WITH CurrentStatus AS
    (
    SELECT TOP(1) *,
    ROW_NUMBER() OVER (ORDER BY LastModified DESC) AS RN
    FROM XXX
    WHERE ObjectId= @ObjectId
    )
    UPDATE CurrentStatus SET EndDate = @current_date, LastModified = CURRENT_TIMESTAMP

    --ADD NEW STATUS
    INSERT INTO [dbo].[XXX](ObjectId,ObjectIndicator,StartDate,EndDate,LastModified,ModifiedBy,OptOut)
    SELECT @ObjectId,@ObjectStatus,@current_date,@object_status_enddate,CURRENT_TIMESTAMP,USER,@object_optout
END TRY
BEGIN CATCH
    --SOMETHING WENT WRONG
    DECLARE @ErrorMessage NVARCHAR(MAX),@ErrorSeverity INT,@ErrorState INT;
    SELECT @ErrorMessage=ERROR_MESSAGE()+' Line '+CAST(ERROR_LINE() AS NVARCHAR(5)),@ErrorSeverity=ERROR_SEVERITY(),@ErrorState=ERROR_STATE();
    RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END CATCH;
GO


texts: Different execution plan with Rownum <NUM

texts:
I have a query like this:

select *
  from (select ecards0_.CDMAINAPPTYP ,
               . . .
               ecards0_.CDISSUMTHD   as col_23_0_
          from PSAM952.KCCARDS ecards0_
          left outer join PSAM952.KWV_CUSTOMER_INFO ecustomer3_
            on ecards0_.CFCIFNO = ecustomer3_.Cus_No, PSAM952.KFTEMPLATE
         ecardtempl1_, PSAM952.KWV_CUSTOMER_INFO ecustomer2_
         where ecards0_.TMPLTID = ecardtempl1_.TMPLTID
           and ecards0_.CBC_CFCIFNO = ecustomer2_.Cus_No
    and ecards0_.CDISUDT <= TO_DATE('2016/06/30 1:00:01', 'YYYY/MM/DD HH:MI:SS')
    and ecards0_.CDISUDT >= TO_DATE('2016/06/01 1:00:01', 'YYYY/MM/DD HH:MI:SS')
           and (ecards0_.CDMAINAPPTYP in (4, 3))
         order by ecards0_.CDISUDT DESC)
 where rownum <= 20

When I use where rownum <= 20 the query cost in the execution plan is lower than when I don't use that where clause. Also the execution plans are different.
I can't understand why?

Plan with where clause:

Plan without where clause:


texts: FROM keyword not found where expected (Oracle) ORA-00923

texts:
I'm getting the error in the following query:

SELECT DISTINCT UNIQUE_ID as uid, CONFIDENCE_IS_SAME FROM
    (
    (SELECT UNIQUE_ID, CONFIDENCE_IS_SAME,  FIRST_NAME, LAST_NAME, POSTAL_CODE
    FROM DANIEL.UNIQUE_PHYSICIAN  WHERE
    DANIEL.UNIQUE_PHYSICIAN.FIRST_NAME = ''
    AND
    DANIEL.UNIQUE_PHYSICIAN.LAST_NAME = ''
    AND
    DANIEL.UNIQUE_PHYSICIAN.IS_ROOT_PHYS = 0
    AND
    DANIEL.UNIQUE_PHYSICIAN.POSTAL_CODE = '')

INNER JOIN

(SELECT
MAX(CONFIDENCE_IS_SAME) OVER (PARTITION BY ROOT_ID) max_conf
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE DANIEL.UNIQUE_PHYSICIAN.FIRST_NAME = '' AND
DANIEL.UNIQUE_PHYSICIAN.LAST_NAME = '' AND DANIEL.UNIQUE_PHYSICIAN.IS_ROOT_PHYS = 0
AND DANIEL.UNIQUE_PHYSICIAN.POSTAL_CODE = '')

ON CONFIDENCE_IS_SAME = max_conf);


texts: How to connect to a Database made by Oracle SQL Database?

texts:
So I am fairly new at this, so if you could keep that in mind in your answers, it would be much appreciated. I installed Oracle SQL Database on my Windows PC. It came in two zip files. I installed it and the online portion of it works fine. I can login with Username: sys and Password: **. What I am trying to do is connect to this newly created database on another computer through SQL Developer. I have read that in order to do this, you need to change the hostname of the Database from "localhost" to an IP Address. How do I do that and is there anything else I need to do to make this work?

I also found this LINK. Is this something I should do? I do not have a Domain though.

listener.ora

# listener.ora Network Configuration File:
C:\app\hyderz\product\11.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
   (SID_NAME = CLRExtProc)
   (ORACLE_HOME = C:\app\hyderz\product\11.2.0\dbhome_2)
   (PROGRAM = extproc)
   (ENVS = "EXTPROC_DLLS=ONLY:C:\app\hyderz\product\11.2.0\dbhome_2\bin\oraclr11.dll")
 )
)

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
  )
)

ADR_BASE_LISTENER = C:\app\hyderz

tnsnames.ora

# tnsnames.ora Network Configuration File:
C:\app\hyderz\product\11.2.0\dbhome_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
(CONNECT_DATA =
  (SID = CLRExtProc)
  (PRESENTATION = RO)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = orcl)
)
)


texts:
Event Viewer, I can fins this event logged :

  2016-07-10 11:45:01.83 Logon       Error: 18456, Severity: 14, State: 38.
  2016-07-10 11:45:01.83 Logon       Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified
  database. [CLIENT: ]

So,I ran trace and found the database name,but how can I delete the missing database completely. Attached image.

I cannot find the database name in sp_helpdb or sys.databases.

  Cannot open database "DBTest" requested by the login. The login
  failed.


texts:
One of the databases I am going to support does not have a validate log file location.  The drive which the log file is pointing to does not exist.   But I don't see any error in database error log because of this.

Is there a default location for SQL server log file when the log file path is not set right?

Thanks


texts: Configuring log shipping with one primary and two secondary instances

texts:
I have three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3).I want configure three (or more) servers:

SQLServer-1 – primary
SQLServer-2 – secondary
SQLServer-3 – secondary

I want the database in standby mode on the secondary servers – database available for querying, and users can access it, but in read-only mode.

I am able to configure this for two servers (one primary and one secondary), but when I try three servers, log shipping setup and secondary server (SQLInstance-3) gets stuck in restore mode, and the log shipping SQL Agent jobs on the second secondary (SQLInstance-3) fail repeatedly. I am confused here what to do?

The second secondary (SQLInstance-3) is showing No Last_Restore_file. I checked Transactional Log Shipping status and it has the same status for the restore file.

Does this mean log shipping is not configured properly between SQL Instance 1 & 3?

I set up log shipping originally following the process outlined in Configure Log Shipping (SQL Server). I didn't use the wizard, I restored the log backups on secondary, with SQL Agent jobs created for log restores. The copy and restore jobs failed.

I have checked Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)? but it won't work for me. As suggested, I started from scratch and re-initialized the log shipping on secondary (2), jobs on secondary (1) failed with an error, and jobs on secondary (2) are running fine. Both secondaries are set up the same way, and I got successful notifications for log shipping, but jobs failed on one secondary.

How should I setup log shipping with one primary and multiple secondary servers?


texts: is it possible to extract 'LifeCycle Status' using emcli ( Oracle Enterprise Manager )

texts:
It's easy to set the LifeCycle status ( and other properties ) with emcli,

emcli set_target_property_value -property_records="sometestdatabase:rac_database:LifeCycle Status:Test"

but getting the current values isn't clear - I'm not seeing a verb in the reference.

Is the only way via SQL against the repository?

Thanks
Simon


texts:
I have a LEMP server in a Ubuntu instance and my MySQL are shutting down automatically... I have no idea what's happening.

I had to manually restart it to keep the site online

Here is my mysql log

171228  9:33:37 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use$
171228  9:33:37 [Note] Plugin 'FEDERATED' is disabled.
171228  9:33:37 InnoDB: The InnoDB memory heap is disabled
171228  9:33:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
171228  9:33:37 InnoDB: Compressed tables use zlib 1.2.8
171228  9:33:37 InnoDB: Using Linux native AIO
171228  9:33:37 InnoDB: Initializing buffer pool, size = 32.0M
171228  9:33:37 InnoDB: Completed initialization of buffer pool
171228  9:33:37 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
171228  9:33:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
171228  9:33:37  InnoDB: Waiting for the background threads to start
171228  9:33:38 InnoDB: 5.5.53 started; log sequence number 19047428748
171228  9:33:38 InnoDB: !!! innodb_force_recovery is set to 1 !!!
171228  9:33:38 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
171228  9:33:38 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
171228  9:33:38 [Note] Server socket created on IP: '0.0.0.0'.
171228  9:33:38 [Warning] 'user' entry 'prestashop@%' has both a password and an authentication plugin specified. The password will be ignored.
171228  9:33:38 [Warning] 'user' entry 'tanda@%' has both a password and an authentication plugin specified. The password will be ignored.
171228  9:33:38 [Note] Event Scheduler: Loaded 0 events
171228  9:33:38 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.53-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

[Edit][Solved]
Apparently it was lack of memory. I enabled Swap memory and the problem was solved.


texts: Insert self referencing entry into SQL Server

texts:
I have a Person table that has a created_by column that references the primary id of the table itself. So, it could be an employee that adds another employee to the database. It works fine.

But People can also add themselves (signup). So the value in the created_by column should be the auto-incremented value of the id column. But that value is obviously not available until after the insert.

So I could either (a) make the reference not to check the values, (b) add a default value in the beginning or (c) make the column nullable. All options seem bad to me.

The MySQL's dialect has this:

SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO EMPLOYEE VALUES('12345','67890'),('67890','12345');
SET FOREIGN_KEY_CHECKS = 1;

...but I could not find something similar for SQL Server's T-SQL.


texts:
Having trouble indexing and executing a query in O (log n) time.

The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can be indexed and executed in O (log n) time (or thereabouts) if a non-equality operator is not used on more than one column. In this case, I believe the INNER JOIN does count as an equality operator and the non-equality operator would be the ORDER BY portion of the query. This table has upwards of 10,000,000 rows and needs to handle several reads and writes per second.

Using PostgreSQL. This is what the table looks like. As you can see, the column 'Names' is a list property and it is the column that the INNER JOIN goes against:

Age Names                       Date
34  ['carla', 'john', 'sam']    3/13/2011
26  ['json', 'cindy', 'joel']   3/13/2011
72  ['beth', 'amber', 'susie']  3/13/2011
14  ['john', 'jim', 'debie']    3/13/2011

This is the query that we are trying to do:

SELECT * FROM the_table WHERE Age==26 AND Names=='john' ORDER BY Date

My background is from using App Engine's Big Table, so I've used equality operators here to indicate that 'john' should be one of the names in the Names column. This would be an acceptable query in GAE's big table, it would execute in O (log N) time as all Big Table queries are reqyured to do. I am assuming there is a way to do this in PostgreSQL as well since PostgreSQL accepts list data types as columns.

Is this possible to do in PostgreSQL?

If so, how should the index be set up (we can't figure out how to set up an index that takes into account the three properties)?


texts:
I came up with the great idea that to begin the normalization of characters and collate to utf8, I found the sentences that would help me to carry out this activity, but I have to go table by table. Then I thought about not doing a stored procedure to help me with this.

Everything was perfect, I'm even setting the name of the database and I automatically look for the tables to work, but when making an ALTER TABLE I'm generating the following error.

  Error Code: 1146. Table 'ARMADILLO_BASE.tableNameFull' does not exist

Below I share my storage procedure.

CREATE DEFINER=`root`@`%` PROCEDURE `setCollate`(
IN dbName VARCHAR(255),
OUT foundRows INTEGER
)
BEGIN
    -- ###############################################################
    -- GLOBAL VARS
    -- ###############################################################
    DECLARE tableName, tableNameFull VARCHAR(255) DEFAULT FALSE;
    DECLARE noMoreRowsOne BOOLEAN DEFAULT FALSE;
    DECLARE querydbName CURSOR FOR
        SELECT
            TABLE_NAME
        FROM
            INFORMATION_SCHEMA.TABLES
        WHERE
            TABLE_SCHEMA = dbName
                AND TABLE_NAME NOT IN (SELECT
                    TABLE_NAME
                FROM
                    INFORMATION_SCHEMA.VIEWS
                WHERE
                    TABLE_SCHEMA = dbName);
    -- set noMoreRowsOne
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRowsOne = TRUE;
    -- ###############################################################
    -- PROCESS CURSOR
    -- ###############################################################
    -- open cursor
    OPEN querydbName;
        -- set @foundRows
        SELECT FOUND_ROWS() INTO foundRows;
        -- begin LOOP
        querydbName_LOOP: LOOP
             -- fetch tableName
            FETCH querydbName INTO tableName;
            -- SET tableNameFull
            SET tableNameFull = CONCAT('`',dbName,'.',tableName,'`');
            -- SELECT tableNameFull;
            -- validate noMoreRowsOne
            IF noMoreRowsOne THEN
                CLOSE querydbName;
                LEAVE querydbName_LOOP;
            END IF;
            -- SET utf8 and utf8_general_ci
            SET foreign_key_checks = 0;
            ALTER TABLE tableNameFull CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
            SET foreign_key_checks = 1;
        END LOOP;
END

They help me? What am I failing? ... Or is this not possible?


texts: MySQL error #1064 while creating a trigger

texts:
DELIMITER $$
DROP TRIGGER IF EXISTS brandRep $$
CREATE TRIGGER brandRep AFTER UPDATE ON sales_flat_order
 FOR EACH ROW
BEGIN
IF (NEW.status <> OLD.status AND OLD.applied_rule_ids <=> 45182) THEN
    UPDATE reps_transaction_history_ids
    SET order_status = NEW.status
    WHERE order_id = OLD.increment_id;
ENDIF $$

END $$
DELIMITER;


texts: Trying to return a complete column from one table, and a count from another table

texts:
I currently have two tables that I am trying to pull data from, one being Customers and one being Bills.

Customers is a static table that just lists all the customers, but the reason I need this table is because it provides the actual human-friendly customer name instead of the less helpful numeric customer ID.

Bills is a frequently updated table and I want to count the number of updates per day for each customer. So I came up with the following:

SELECT c.name, COUNT(DISTINCT b.billid) from customers c
JOIN bills b
ON b.customerid = c.customerid
WHERE b.createdate = CURDATE() -INTERVAL 1 DAY
GROUP BY b.customerid;

This works wonderfully with one problem: There are some clients that don't process on a given day. And while this is currently a small select overall (I get about 24 out of a max of 31 rows back) this data is going to be used in metrics reports and transposing the data with these missing rows could eventually become a problem.

What I want is to be able to get EVERY name from Customers showing up in the first column, and then if there is no data from Bills for that customer that day, I'd like it to show up as 0 or NULL. I've been banging my head against the wall for a while now, and I'm not sure I can actually do this or not in MySQL.


texts: WiredTiger - "too many open files error" during resync of a secondary replica set member

texts:
I'm upgrading a secondary replica set member to wiredTiger. I have upgraded it from MongoDB 2.6.3 to 3.0.4 and changed the storage engine to wiredTiger. Now it is resyncing all data from the primary.
At some point the following error is received, and the process starts all over again:

  2015-07-22T13:18:55.658+0000 I INDEX    [rsSync]         building index using bulk method

  2015-07-22T13:18:55.664+0000 I INDEX    [rsSync] build index done.  scanned 1591 total records. 0 secs

  2015-07-22T13:18:56.397+0000 E STORAGE  [rsSync] WiredTiger (24) [1437571136:397083][20413:0x7f3d9ed29700], file:WiredTiger.wt, session.create: WiredTiger.turtle: fopen:
  Too many open files

  2015-07-22T13:18:56.463+0000 E REPL     [rsSync] 8 24: Too many open files

  2015-07-22T13:18:56.463+0000 E REPL     [rsSync] initial sync attempt failed, 9 attempts remaining

The same machine was previously running 2.6.3 version without any open file limits issues. I'm aware that wiredTiger might be creating much more files, so it must be it, but does it keep them all open simultaneously ?

For reference:

  cat /proc/sys/fs/file-max

  10747371

In /etc/init.d/mongod the configuration is:

  ulimit -n 64000

According to the documentation it seems that mongo holds a file descriptor for every data file. As in wiredTiger this results in a file for each collection + a file for each index, according to a calculation for our usecase, can add up to over 700K.

So I can change the ulimit to 700000 or higher, but I'm wondering whether this is the most correct solution, and what alternatives exist.


texts:
I am thinking about using Riak for a SasS application I started working on recently. I was originally thinking about MongoDB but after running into Riak it seems like it may be the better option. The application is for server monitoring and goggle anylatics type data. From my understanding it is pretty easy to do a map reduce job in Riak and its easy to put http caching tools like varnish/nginx to make raik your a close friend. The php drive seems pretty good also. I just woundering if there is anything I am missing in the understand of why you would not want raik for this type of data?

Raik does not support database + tables it simply uses buckets?
Riak is very easy to cluster adding new nodes in is said to be very easy, and very easy to cache?
Supports simple querys, search, and map/reduce, but how is the overall index support to make sure my qouerys are quick and how do search and simple querys perform on when there are a large number of nodes in the cluster?
Any gochas i should be aware of for Riak and its PHP, Ruby, or Python drivers?

Thank you so much :)

If anyone thinks i am crazy and should be looking at another database please feel free to let me know.


texts: Is it possible to give the optimizer more or all time it needs?

texts:
Given that the optimizer cannot take all the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off.

I was wondering if this can be overridden so that you can give the optimizer all the time in needs (or a certain amount of milliseconds).

I don't have a need for this (atm) but I can imagine a scenario where a complex query is executed in a tight loop and you want to come up with the optimal plan and cache it before hand.

Of course it you have a tight loop you should rewrite the query so it goes away but bear with me.

This is more a question out of curiosity and also to see if there is sometimes a difference between a short circuited optimization and a full one.

It turns out that you can give the optimizer more time with trace flag 2301.  It's not exactly what I was asking but it comes close.

The best information I found on this is in Query Processor Modelling Extensions in SQL Server 2005 SP1 by Ian Jose.

Use this trace flag with care! But it can be useful when coming up with better plans. See also:

Articles tagged "optimization level" by Grant Fritchey.
Before you upgrade to SQL Server 2008… by Brent Ozar.
Tuning options for SQL Server when running in high performance workloads by Microsoft Support.

I was thinking about queries with lots of joins where the solution space for join order explodes exponentially. The heuristics that SQL Server uses are pretty good but I was wondering if the optimizer would propose a different order if it had more time (in the range of seconds or even minutes).


texts: Implementing Feature max limit (Logical)

texts:
so this is a more of a logical question.

I'm having trouble deciding on what I will use on implementing limit, let's say for example ticket limit where I have 2 plans, one is standard which have 100 ticket limit, and premium which have 500 ticket limit.

Which of these two is better for implementing such logic? I have 2 ideas:

A. Just count the number of unique tickets in the database for a specific user, and if it reached 100 or 500, implement a method that will restrict further ticket opening.

B. Create a table, which will have the user ID, and a column which will have an upper limit of 100 or 500 depending on the plan availed of the user. Then, each opening of ticket, will subtract from the upper limit in the table. And if the record reached 0, implement a method which will restrict further opening of ticket.

Or if you have any other ideas, I will be glad to have it


texts: Combine intersecting ranges to produce new ranges based on the points of intersection

texts:
I have a table with data like this:

ID  valid_from          valid_to             item_a    Item_b    Item_c
1   20.5.2016 15:28:02  21.5.2016 7:12:49    1         2          -
1   21.5.2016 7:12:50   17.6.2016 8:49:29    1         4          -
1   17.6.2016 8:49:30   31.12.2999           1         5          -
1   24.6.2016 11:23:13  31.12.2999           -         -          6

The last two rows have validity ranges that intersect. More specifically, one of the ranges is longer, so the period from 24.6.2016 11:23:13 to 31.12.2999 is covered by both ranges. I want that to be returned as a single row for all three items. And I want the longer range to be cut off at the beginning of the common subrange, i.e. at 24.6.2016 11:23:13.

So I would like the output to look like this:

ID  valid_from          valid_to             Item_a    Item_b    Item_c
1   20.5.2016 15:28:02  21.5.2016 7:12:49    1         2          -
1   21.5.2016 7:12:50   17.6.2016 8:49:29    1         4          -
1   17.6.2016 8:49:30   24.6.2016 11:23:13   1         5          -
1   24.6.2016 11:23:13  31.12.2999           1         5          6

How should I write my query to solve this problem?


texts:
I want to create this table, but i can’t,

Look this error.

CREATE TABLE ATTR(

    window character varying(64) NOT NULL

);

  ERROR:  syntax error at or near "window"
  LINE 2:     window character varying(64) NOT NULL


texts: Check for records that break a foreign constraint (created with foreign_key_checks = 0)?

texts:
Is there an easy way to check for any records in a table that breaks any constraint?

The constraint was created on a populated table using "foreign_key_checks=0".


texts: How to make PostgreSQL default_tablespace work properly?

texts:
My question is, "Why isn't database 'screwy' created in tablespace 'screwy'?

I made the following script, with it's output following, to show the problem I am having :

#!/bin/bash
#
export OWNER=yourself
export OBJECT=screwy
export OTHER=not${OBJECT}
export PWD='seecret'
#
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OWNER};"
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OBJECT};"
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OTHER};"
sudo -u postgres psql -tc "DROP TABLESPACE IF EXISTS ${OBJECT};"
sudo -u postgres psql -tc "DROP ROLE IF EXISTS ${OWNER};"
#
str=$(cat /etc/lsb-release | grep DESC)
echo "Linux version is : ${str#*=}"
echo "Postgres version is :"
sudo -u postgres psql -qtc "SELECT version();"
#
echo "CREATE ROLE ${OWNER} WITH CREATEDB LOGIN PASSWORD '${PWD}'"
sudo -u postgres psql -tc "CREATE ROLE ${OWNER} WITH CREATEDB LOGIN PASSWORD '${PWD}';"
echo "CREATE TABLESPACE ${OBJECT} OWNER ${OWNER} LOCATION '/home/${OWNER}/data';"
sudo -u postgres psql -tc "CREATE TABLESPACE ${OBJECT} OWNER ${OWNER} LOCATION '/home/${OWNER}/data';"
sudo -u postgres psql -tc "SELECT 'Tablespace \"' || spcname || '\" is owned by ' || usename \
                             FROM pg_catalog.pg_tablespace, pg_catalog.pg_user \
                            WHERE spcname='${OBJECT}' AND usesysid = spcowner;"
echo "CREATE DATABASE ${OWNER} TABLESPACE ${OBJECT};"
sudo -u postgres psql -tc "CREATE DATABASE ${OWNER} TABLESPACE ${OBJECT};"
#
echo "ALTER ROLE ${OWNER} SET DEFAULT_TABLESPACE TO ${OBJECT};"
sudo -u postgres psql -tc "ALTER ROLE ${OWNER} SET DEFAULT_TABLESPACE TO ${OBJECT};"
echo "GRANT CREATE ON TABLESPACE ${OBJECT} TO ${OWNER};"
sudo -u postgres psql -tc "GRANT CREATE ON TABLESPACE ${OBJECT} TO ${OWNER};"
#
echo "## As ${OWNER}"
echo "*:*:*:yourself:${PWD}" | cat > ~/.pgpass
chmod 600 ~/.pgpass
#
echo "SHOW DEFAULT_TABLESPACE;"
psql -tc "SHOW DEFAULT_TABLESPACE;"
#
echo "CREATE DATABASE ${OBJECT};"
psql -tc "CREATE DATABASE ${OBJECT};"
#
echo "CREATE DATABASE ${OTHER};"
psql -tc "CREATE DATABASE ${OTHER} TABLESPACE ${OBJECT};"
#
echo "## As postgres"
sudo -u postgres psql -qtc "SELECT 'Database \"' || d.datname || '\" is in tablespace \"' || t.spcname || '\" in disk directory \"' || pg_tablespace_location(t.oid) || '\".' FROM pg_database d \
                         LEFT JOIN pg_catalog.pg_tablespace t \
                                ON t.oid = d.dattablespace \
                             WHERE datistemplate = false \
                              AND datname IN ('${OTHER}', '${OBJECT}', '${OWNER}');"

When I execute it, it looks like this :

DROP DATABASE
DROP DATABASE
DROP DATABASE
DROP TABLESPACE
DROP ROLE
Linux version is : "Ubuntu 14.04 LTS"
Postgres version is :
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit

CREATE ROLE yourself WITH CREATEDB LOGIN PASSWORD 'seecret'
CREATE ROLE
CREATE TABLESPACE screwy OWNER yourself LOCATION '/home/yourself/data';
CREATE TABLESPACE
 Tablespace "screwy" is owned by yourself

CREATE DATABASE yourself TABLESPACE screwy;
CREATE DATABASE
ALTER ROLE yourself SET DEFAULT_TABLESPACE TO screwy;
ALTER ROLE
GRANT CREATE ON TABLESPACE screwy TO yourself;
GRANT
##  As yourself
SHOW DEFAULT_TABLESPACE;
 screwy

CREATE DATABASE screwy;
CREATE DATABASE
CREATE DATABASE notscrewy TABLESPACE screwy;
CREATE DATABASE
## As postgres
 Database "yourself" is in tablespace "screwy" in disk directory "/home/yourself/data".
 Database "screwy" is in tablespace "pg_default" in disk directory "".
 Database "notscrewy" is in tablespace "screwy" in disk directory "/home/yourself/data".

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'?


texts: Select arbitrary single value for GROUP BY: What's the fastest option?

texts:
I have a query that I use to indicate locations in a map where there are overlapping points:
select
    min(objectid) as min_objectid,
    longitudex,
    latitudey,
    count(1) as count,
    min(shape) as min_shape
from
    workorders
group by
    longitudex,
    latitudey
having
    count(1) > 1

In the mapping software that I use, I need to include columns like objectid and shape. For those columns, it doesn't matter which of the grouped-by rows the values come from, just as long as there is a value.
Presently, I'm using min() to get an arbitrary value for those columns. However, I don't know if that's the fastest option since finding the minimum value would require calculation — and I wonder if that time spent is unnecessary.
What is the fastest option for getting an arbitrary/single value for GROUP BY in an Oracle query?


texts: mariadb won't let me access from "remote"

texts:
I have one Linux computer(CentOS7.4) as server, one windows computer. I have setup the mariadb server. On windows, I use mysql-workbench as client.

However, I can access the server via 'tcp/ip over ssh', but when I connect using 'tcp/ip' I get 10060 error.

By the way, I can ping 192.168.1.11, it seems the network is ok. Therefore, what's wrong with my setting?

three things to check

the user and host:

and 3. the settings file in /etc/my.cnf:

server.cnf

mysqld

bind-adress and skip-networking


texts:
Ok calling an Oracle expert, DBA or general good guy

The problem is simple but trawling Mr Google has not come up with an easy to use solution.

I am testing data input.

I want to compare master.dmp with testers.dmp these have been created using exp in Oracle 11g  the schema will be the same it is only the data input that will change.

The master.dmp was created when I put the data in myself using some sql and typing. Now I have to use the app and a tester(me) to see if the results are the same.

I can use ODBC and import data in Excel and uses Excel Compare Ok but it would be quicker to just compare the two .dmp files and highlight the changes .

Has any on done this?
Has anyone a link to a tool or software that will compare two dmp files?

or is there a nice easy way to compare the data.


texts: Is pgtune still valid for newer PostgreSQL versions?

texts:
Since pgtune has not been updated since more than 4 years ago, should it still be used with newer PostgreSQL versions? (say, >=9.3)

What about the online version?


texts:
I loaded a 10GB database into my local MongDB server, and now if I stop/start it, it takes about 10 minutes to fully come "up". Attempting to connect to the local database with mongo returns the error:

2017-06-19T21:39:23.486-0400 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, in(checking socket for error after poll), reason: Connection refused
2017-06-19T21:39:23.486-0400 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:237:13
@(connect):1:6
exception: connect failed

However, service mongo status shows it's running, top -c shows it's consuming a fair amount of CPU, and the log only shows:

2017-06-19T21:35:38.392-0400 I CONTROL  [main] ***** SERVER RESTARTED *****
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten] MongoDB starting : pid=29452 port=27017 dbpath=/var/lib/mongodb 64-bit host=localhost
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten] db version v3.4.5
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten] git version: 520b8f3092c48d934f0cd78ab5f40fe594f96863
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten] allocator: tcmalloc
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten] modules: none
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten] build environment:
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten]     distmod: ubuntu1604
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten]     distarch: x86_64
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten]     target_arch: x86_64
2017-06-19T21:35:38.440-0400 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { bindIp: "127.0.0.1", port: 27017 }, storage: { dbPath: "/var/lib/mongodb", journal: { enabled: true } }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongodb/mongod.log", quiet: true } }
2017-06-19T21:35:38.510-0400 I -        [initandlisten] Detected data files in /var/lib/mongodb created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2017-06-19T21:35:38.510-0400 I STORAGE  [initandlisten]
2017-06-19T21:35:38.510-0400 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2017-06-19T21:35:38.510-0400 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
2017-06-19T21:35:38.522-0400 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=15439M,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),

Oddly, mongo doesn't seem to be taking up a lot of memory during this time, so it doesn't seem to be spending this time to load anything into memory.

Why is mongo taking so long to load? How do I speed this up?

Edit: My /etc/mongod.conf

storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true

systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

net:
  port: 27017
  bindIp: 127.0.0.1


texts: Error when creating Trigger (PostgreSQL 9.4)

texts:
I created a table 'Company', and I want to create a trigger so a company can only present in one city, but it can be present in several different cities in the same country. For example, there's only one company A (com_id 1) in NYC, but at the same time company A(com_id 2) can be present in Washington, so in US there are two company A(com_id 1 & 2). It is not allowed to have com_id 1 & 2 both be present in NYC.

So I found this page and worte my code as this: enter link description here

    CREATE TABLE "Company"
(
  com_id integer NOT NULL,
  com_name character(50) NOT NULL,
  city character(50) NOT NULL,
  CONSTRAINT com_pk PRIMARY KEY (com_id),
  CONSTRAINT com_fk FOREIGN KEY (city)
      REFERENCES "City" (city) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "Company"
  OWNER TO postgres;
GRANT ALL ON TABLE "Company" TO postgres;
GRANT TRIGGER ON TABLE "Company" TO public;
GRANT TRIGGER ON TABLE "Company" TO public;

CREATE TRIGGER 'company_t' AFTER INSERT ON 'Company'
FOR EACH ROW EXECUTE PROCEDURE tfunc ();

  CREATE FUNCTION tfunc () RETURNS TRIGGER AS $$
    BEGIN
      DELETE FROM Company
      WHERE com_id IN (SELECT com_id
                   FROM (SELECT com_id
                         row_number() over (partition BY com_name, city ORDER BY com_id) AS rnum
                     FROM Company) t
              WHERE t.rnum > 1);
    END;
    $$language plpgsql;

However, I got this error message:

So my questions are:
1. If my code can fulfill the function required.
2. If my code are not correct, how can I fix it.

Any help?


texts: SQL Index order and performance based on cardinality and data

texts:
I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting this thing to run faster and I think I've finally tracked it down to an improper index. A new and more appropriate index should fix the problem, but I'm not entirely sure how this should be ordered as there appears to be different ways for different data.

The query itself isn't terribly complicated. Here's a brief rundown:

SELECT
...
B.TYPE_CODE,
B.FIELD,
...
FROM A
INNER JOIN B
ON A.ID1 = B.A_ID1
AND A.ID2 = B.A_ID2
--A few inner joins and some left joins--
WHERE
B.TYPE_CODE IN (:parameter) --No more than four values within the IN clause

A few details about these tables:

A has about 900k rows and B has about 2.7 million.
B.TYPE_CODE has 15 possible values. Stats are accurate. The four
parameters together are contained in about 400k rows (about 15% of
all entries in the table). The A_ID1/2 columns are much more unique,
occurring 15 times at most, of course.
B already has an index on (TYPE_CODE, A_ID1, A_ID2). This is being
used, but it's not optimal at all
There are 6 rows being selected by the DB2-optimized query. It grabs
A_ID1 and 2 along with TYPE_CODE and FIELD, of course, but it's also
grabbing B.ID1 and B.ID2, which together form the primary key for B.
These fields are not used in the original query, but the optimizer is
including them, although I don't think they're used in any joins or
filters. They're just there.
B will have far more reads than write operations on it

The join between A and B is taking a third of the query cost. The explain plan shows a FETCH operation that uses a particular index four times. I was able to get a VERY detailed explain plan report and found that this operation is returning all 400k rows, then performing the join, which only needs about 26k of them.

I found a short-circuit method that added this to the predicate:

AND COALESCE(B.TYPE_CODE, B.TYPE_CODE) = B.TYPE_CODE

Logically this doesn't affect the result set, but it does tweak the optimizer. Adding this cut the query time in half. Checking the detailed explain showed that it added an additional filter factor that reduced the number of estimated rows in that FETCH operation to 26k or so, which is about right. This in turn reduced the estimate for the number of rows from A that would be returned overall and enabled index scans on A that hadn't been used prior due to the optimizer thinking it could be grabbing nearly half of A. The result was a much faster query. However, the COALESCE is a hacky bit and not suited for production, but it was handy for finding an issue.

So now it falls to creating a new index on B to fix this problem. The DB2 query analyzer suggested an index on all six fields, starting with B.TYPE_CODE and following with the rest in no particular logical order. This would cover all selected fields and the table itself would not need to be touched, which certainly has its advantages. It has also been suggested that an index on (B.A_ID1, B.A_ID2, B.TYPE_CODE) could be used as the more selective columns are first, which would narrow the results faster. I've seen different suggestions based on the cardinality of the data, so I'm wondering if anyone has some advice on how to construct the index here. I've been doing a lot of reading on indexes lately, but it can be difficult to find a good guide on certain aspects.

UPDATE: Well, DB2 refuses to use the new index and keeps using the existing index of (TYPE_CODE, B.ID1, B.ID2), and those latter two are the primary key for B, not to be confused with B.A_ID1, B.A_ID2. I think it has to to do with the optimized query grabbing B.ID1 and B.ID2 even though the original query doesn't touch those fields and using them as a SORTKEY. It may be that any index will need to include those fields.


texts:
I am looking for a solution to track object usage in my database.  Objects such as tables, stored procedures, and functions.

I discovered SQL Auditing and was under the impression that it's exactly what I needed.

So I set it up to audit SELECT, INSERT, UPDATE, DELETE on my tables, and EXECUTE on my stored procedures and functions.

I set it up to target the Windows Security event log via Event Viewer Utility.

I made sure to allow permissions and set object access in Local Security Policy and using auditpol.exe.

Then I ran some INSERTs on tables and EXECUTEs on stored procedures. I then opened Event Viewer expecting to see logs of the time each of these statements where executed and on what object, however all I see is this:

Did I skip a step, am looking in the wrong place, or did I make a mistake from the beginning about what SQL Audit actually does?

EDIT: OK, I switch the target to a file and viewed the file via SSMS (right clicking Audit -> View Audit File)... and here the INSERTs and other actions are indeed being logged....So clearly I'm looking in the wrong place in the Event Viewer or missing something.


texts: How to make a line/string to multiple records

texts:
So, I want to create a teacher-degree (1:m) table. I have this example:

One professor has 3 degrees.

This is the actual string I have(multiple of these actually):

  B.A, 1983, University of China; M.B.A, 1999, University of Taiwan;
  Ph.D, 1999, University of Southern California,

The fields are: professorID, degreeType, degreeYear, Institute

The expected table data:

  Prof-13434, B.A, 1983, University of China

  Prof-13434, M.B.A, 1999, University of Taiwan

  Prof-13434, Ph.D, 2003, University of  California

That is my goal, I have tried everything but I cant figure anything out. Trying to go from one string to multiple records is harder than I thought.


texts:
Ok, I've installed SQL Server 2008 R2.

I'm trying to add a "SQL Server Database" to an ASP MVC project.

But it keeps telling me I need SQL Server for it to work.
What do I need to do to setup the SQL Server properly for it to work with VS2010 ?

I've tried messing with the SQL Server Management Studio, and can't seem to connect to anything through there.

What am I doing wrong ? How can I create a working SQL service ?


texts: Running MySQL Backups to network location

texts:
We have a MySQL server with several databases for development work and we want to back up some of the DBs to a NAS device.
These have been backed up previously but the backups have not worked for some time I have found.
There is a scheduled task in the Windows scheduler to run a batch script, this script should run the backup for the specified databases and save them to the NAS. When I run the task, I can see the folders and files being created on the NAS but the resulting backup file is always 0 KB so there is clearly something not working correctly.
I've queried the sizes of the DBs on the server and have seen that the DBs I'm trying to back up are not 0 KB.
I'm thinking it must be something in the script which is not working correctly so would appreciate some assistance.

del \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day1 /Y
rmdir \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day1 /S /Q
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day2 Day1
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day3 Day2
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day4 Day3
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day5 Day4
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day6 Day5
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day7 Day6
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day8 Day7
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day9 Day8
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day10 Day9
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day11 Day10
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day12 Day11
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day13 Day12
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day14 Day13
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day15 Day14
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day16 Day15
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day17 Day16
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day18 Day17
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day19 Day18
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day20 Day19
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day21 Day20
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day22 Day21
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day23 Day22
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day24 Day23
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day25 Day24
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day26 Day25
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day27 Day26
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day28 Day27
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day29 Day28
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day30 Day29
md \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day30
mysqldump -u[user] -p[password] -databases [db1] [db2] [db3]> \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day30\vmmysqldevw7.sql

rem Send mail to alerts.infrastructure on completion
mailsend -sub "Backup of VMmySQLdevW7 Completed" < InputFile.txt

The email alerts appear not to work either...
The "inputFile.txt" file contains the mail server's IP address, domain name, MySQL server's name and the alerts email address.


texts:
What happens when I repeat the same column in the criteria part but with different values . For example

col1 ='val1' or col1 = 'val2' or col1 = 'val3' .

Assume col1 is not indexed. Will mysql perform entire table scan for each comparison? What will be its behaviour?


texts:
I am trying to make something like the following work:
WITH results(n, string ) AS (
            SELECT 1,'lol'
            UNION ALL
            SELECT n+1, string + ' lol'
            FROM results
            WHERE n<6
            )
            SELECT * FROM results

But SQL doesn't seem to recognize the string concatenation in the second column and returns the error:

Types don't match between the anchor and the recursive part in column "string" of recursive query "results".

My desired output would be something like
1, lol
2, lol lol
3, lol lol lol
and so on


texts: Debuging import of a large dump file to MySQL

texts:
I'm trying to import a 50GB dump file to AWS RDS instance and have an issue. I get the following error:

ERROR 2013 (HY000) at line 15850: Lost connection to MySQL server during query

In the beaning i got this error after a few minutes, Then i increased the parameter max_allowed_packet to 1G. Now i get this error after 2.5 hours. What could be the issue? Is there any way to debug this issue? I couldn't find any relevant logs.

Here are some of the relevant DB paramameters:

mysql> SHOW VARIABLES like '%innodb_bu%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 50503614464    |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)

mysql>  SHOW VARIABLES LIKE '%_timeout';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
12 rows in set (0.00 sec)

mysql> SHOW VARIABLES like '%max_all%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576000 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

Please advise.


texts: Reason of ORA-00604 - ORA-00904: on online Redefining

texts:
I made Redefining Tables Online on a table . It worked fine but later when I tried to truncate the table I got this error .

ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "CURRENT_SCN": invalid identifier.

also I get the same error when I move the table:

 alter table tab1 move;

what is the reason of the error ? If you need more information please ask me.


texts: Locating all records relevant for a specific date

texts:
I have a MySQL InnoDB table with about 1GB of records and growing.
each record in this table has, among other fields two date fields :

AddedDateRecord - The date the record was added (datetime).
LastUpdatedRecord - The date the record was last updated (datetime).

The problem is that if i want to find all records relevant for a single date i have to use 3 "OR" statements. assuming "date" is the date I need :

AddedDateRecord >= date:00:00:00 AND AddedDateRecord <= date:23:59:59

OR

LastUpdatedRecord >= date:00:00:00 AND LastUpdatedRecord <= date:23:59:59

OR

AddedDateRecord <= date:00:00:00 AND LastUpdatedRecord >= date:00:00:00

I'm sure you can see the performance hit for using these 3 ORs in a Query on a 1GB records table;

I'm looking for a solution that does not involve moving away from MySQL.

Any help would be greatly appreciated.

Thanks


texts: Auto increment id column vs index VARCHAR columns

texts:
I'm wondering the differences or comparisons of having an Auto Increment int Id column or an indexed VARCHAR(32) column. Which one would be faster if I had a second table that references the first table by joining? Like in the second table, would I store the Auto Increment INT id or the indexed VARCHAR column? What are the advantages or disadvantages, if any?

Which would be the more common practice? Create Auto Increment id column for almost every table? Or we can simply index a column that has a unique 36-char ID (like in SugarCRM) so the other tables can just use that when doing joins? I hope I explained my question fairly clear.


texts: Is rowversion unique across the database? Or per table?

texts:
If I have a rowversion on two different tables in a database, are they guaranteed to be unique across both tables? If not, is there any way to set this behaviour?

I have a view that joins two tables together, I'm wondering if this behaviour is guaranteed, so that I could take the largest row number from either table, to form a rowversion for the view.


texts:
An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.

Do you think that indexes going to reduce query run time?

Any ideas how can i pass through the error code?

SQL Query takes 10.1 secs and got over 380 times execution in slow log.

SELECT
  l.ID,
  post_title,
  post_content,
  post_name,
  post_parent,
  post_author,
  post_status,
  post_modified_gmt,
  post_date,
  post_date_gmt
FROM
  (
    SELECT
      wp_posts.ID
    FROM
      wp_posts
    WHERE
      wp_posts.post_status IN ('publish')
      AND wp_posts.post_type = 'post'
      AND wp_posts.post_password = ''
      AND wp_posts.post_date != '0000-00-00 00:00:00'
    ORDER BY
      wp_posts.post_modified ASC
    LIMIT
      100 OFFSET 214000
  ) o
  JOIN wp_posts l ON l.ID = o.ID

Indexes that i need to create;

MariaDB [database]> ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_type_post_passw_post_statu` (`post_type`,`post_password`,`post_status`);

  ERROR 1071 (42000): Specified key was too long; max key length is 1000
  bytes

MariaDB [database]> ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_modified` (`post_modified`);
Query OK, 453289 rows affected (10.839 sec)
Records: 453289  Duplicates: 0  Warnings: 0

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT 0,
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`),
  KEY `wp_posts_idx_post_modified` (`post_modified`)
) ENGINE=MyISAM AUTO_INCREMENT=463265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: How to get the DDL for Oracle object without extra characters

texts:
When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script.
For example, if I create this sample procedure:

CREATE OR REPLACE PROCEDURE TEST
IS
BEGIN
 NULL;
END;
/

then dbms_metadata.get_ddl for this procedure returns extra newline and two space characters at very beginning:

select dbms_lob.substr(dbms_metadata.get_ddl('PROCEDURE','TEST', USER), 37, 1 ) text
, dump(dbms_lob.substr(dbms_metadata.get_ddl('PROCEDURE','TEST', USER), 37, 1 )) dump_text
FROM dual;

Result is:
  CREATE OR REPLACE PROCEDURE "TEST"
Typ=1 Len=37: 10,32,32,67,82,69,65,84,69,32,79,82,32,82,69,80,76,65,67,69,32,80,
82,79,67,69,68,85,82,69,32,34,84,69,83,84,34

As you can see there are 10,32,32,, just before CREATE OR REPLACE.
Any idea how to get rid of these whitespaces?
Also there are extra " around procedure name.


texts: CHECKDB fails on MSDB and Master system databases

texts:
I get the following error within my maintenance plans:

  Executing the query "DBCC CHECKDB(N'master')  WITH NO_INFOMSGS  "
  failed with the following error: "The database could not be
  exclusively locked to perform the operation.  Check statement aborted.
  The database could not be checked as a database snapshot could not be
  created and the database or table could not be locked. See Books
  Online for details of when this behavior is expected and what
  workarounds exist. Also see previous errors for more details.".
  Possible failure reasons: Problems with the query, "ResultSet"
  property not set correctly, parameters not set correctly, or
  connection not established correctly.

I get the following when I run the code from the query analyzer:

USE [master]
GO
DBCC CHECKDB(N'master')  WITH NO_INFOMSGS,  PHYSICAL_ONLY

  Msg 5030, Level 16, State 12, Line 1 The database could not be
  exclusively locked to perform the operation. Msg 7926, Level 16, State
  1, Line 1 Check statement aborted. The database could not be checked
  as a database snapshot could not be created and the database or table
  could not be locked. See Books Online for details of when this
  behavior is expected and what workarounds exist. Also see previous
  errors for more details.

I've searched high and low trying to find a fix, but no dice.

FACTS:

SQL Server 2008 R2 Standard 64-bit (10.50.4000.0)
Windows Server 2008 R2 64-bit w/SP1
VMWare Virtual Machine
Production server, so I can't reboot server/instance without internal change management process
SQL Server Agent & Database Engine service accounts have full access to the folders and files where the MDFs and LDFs are housed
Restarted SQL Server Agent via Config Mgr.
SQL Server Agent has read permissions on the entire volume
There are no read-only FGs
DBs are not in Single-user mode, read-only, or in emergency mode
Not running TABLOCK with DBCC command
The DBs are on NTFS

The following are a few places I've read out of many, but these are the most applicable to my matter as I see it.  I've performed the suggested "fixes" with no change in result or error message output from the SQL Server Error Logs.

Paul Randal's Input

Microsoft


texts: What types of methods can we call from a .NET assembly in SQL Server

texts: After we have registered a DLL in SQL Server (in Programmability -> Assemblies), can we call any type of method from that .NET assembly in SQL Server? Or just static ones?


texts:
begin
     if TO_CHAR('23-OCT-1994','DAY') = 'FRIDAY' THEN
        DBMS_OUTPUT.PUT_LINE('FRIDAY');
     else
        DBMS_OUTPUT.PUT_LINE('Not Friday');
     end if;
     end;
     /

I am trying to see if 10/23/1994 is Friday.
If it is Friday, it is going to return FRI, if not, NOT FRI

However I get
"PL/SQL: numeric or value error: character to number conversion error".


texts: Does a read-only MySQL slave need log-bin in addition to relay-bin logs?

texts:
I'm running out of disk space on a newly initiated MySQL slave server. The bulk of disk space is in /var/log/mysql where I see both mysql-bin.000XXX and mysql-relay-bin.000XXX logs, each 1.1G.

Does a traditional read-only MySQL slave need both of these? mysql-bin.000XXX goes all the way back to 000001 which leads me to believe these aren't used.

Here's the relevant information from my my.cnf:

## Slave my.cnf
binlog_format                   = mixed
log_bin                         = /var/log/mysql/mysql-bin.log
sync_binlog                     = 1
pid_file                        = /var/run/mysqld/mysqld.pid
log_error                       = /var/log/mysql/error.log
general_log                     = 0
general_log_file                = /var/log/mysql/general.log
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 3

server_id                       = 2

relay-log                       = /var/log/mysql/mysql-relay-bin.log
replicate-wild-do-table         = my_table_prefix_%.%
log_slave_updates               = 1
read_only                       = 1

Here's a ls -lah of my log directory:

-rw-r-----  1 mysql mysql 1.1G Mar 29 19:48 mysql-bin.000001
-rw-r-----  1 mysql mysql 1.1G Mar 29 19:55 mysql-bin.000002
-rw-r-----  1 mysql mysql 1.1G Mar 29 20:03 mysql-bin.000003
...
-rw-r-----  1 mysql mysql 1.1G Mar 29 20:11 mysql-bin.000286
...
-rw-r-----  1 mysql mysql  254 Mar 31 12:27 mysql-relay-bin.000015
-rw-r-----  1 mysql mysql 1.1G Mar 31 12:27 mysql-relay-bin.000016
-rw-r-----  1 mysql mysql  326 Mar 31 12:27 mysql-relay-bin.000017
...
-rw-r-----  1 mysql mysql 1.1G Mar 31 12:27 mysql-relay-bin.000769

Slave is slowly catching up to master and purging mysql-relay-bin files appropriately. But it looks like mysql-bin files will stack up forever.

And slave status:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000291
          Read_Master_Log_Pos: 15703910
               Relay_Log_File: mysql-relay-bin.000016
                Relay_Log_Pos: 662979789
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: my_table_prefix_%.%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 662979592
              Relay_Log_Space: 271579588573
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 729594
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100
                  Master_UUID: 5e12-f712611e5-b2f1-f206553c91-3b124
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:


texts:
I've got a table that grows under Postgres 9.1. It's a high-throughput table with lots of inserts and deletes, but it should generally remain between 1-2k in terms of number or records. Auto-vacuum is enabled, and we've run manual vacuums. We've checked for locks and there don't appear be any. Running "vacuum verbose;" says that while there are millions of dead records, but "found 0 removable".

What might I have not thought about?


texts:
I have create a new table on SQL Server 2008.

This table has an identity column, defined like this:

[ID] [int] IDENTITY(1,1) NOT NULL

However when I insert any data, the first record starts from 0, instead of 1.

I want the records to start from 1. Why does this not work?

If I delete the record and reseed it to 0, it works.

Delete from [tablename]
DBCC CHECKIDENT('[tablename]', RESEED, 0)

However it does not work on newly created tables.

Even if I delete and reseed a newly created table, it still starts adding record from 0, instead of 1.

Can you anyone please suggest what I am doing wrong.

Thanks